PythonAnywhere: administrer ses DB PosgreSQL chez l'hébergeur PythonAnywhere avec PgAdmin + SSH

Introduction

PythonAnywhere propose un service d'hébergement pour solutions Python (services déjà abordés dans cet article), inclus base de données et batteries included comme dans n'importe quel environnement Python.

PostgreSQL est une puissante base de donnée objet relationnel

Le but de cet article est de démontrer qu'il est possible d'administrer sa DB PostgreSQL hébergée chez PythonAnywhere depuis l'interface graphique pgAdmin tournant sur votre PC Windows.

PgAdmin - un outil très conviviale pour Administrer ses DBs PostgreSQL

Tout cela est possible grâce à la connexion SSH que l'on obtient en disposant d'un compte payant chez PythonAnywhere... et cela, c'est de la balle!
Les base de données PostgreSQL sont proposées dans des plans tarifaires personnalisés (la raison: vous disposez d'un serveur séparé pour faire tourner votre/vos DB au prix abordable de 10$/mois).

A la fin de ce tutoriel, vous saurez:

Comment activer votre serveur de base de donnée PostgreSQL
Créer une DB de test (avec la console Postgres de PythonAnywhere)
Etablir une connexion SSH avec votre serveur d'application PythonAnywhere
Etablir un Tunnel SSH et administrer votre DB PostgreSql depuis votre PC avec PgAdmin (ou psql)

Ce tutoriel fait suite à une série d'autres articles déjà par le passé... vous pouvez y accéder facilement grâce au liens suivants:

Pourquoi Postgres et pas MySql?
PythonAnywhere offre des DB MySql en standard dans ses offres... alors pourquoi sélectionner une DB PostgreSQL?

MySql représente certainement un excellent choix dans de nombreuses circonstances. Je n'ai absolument rien contre MySql mais j'ai un très très gros faible pour PostgreSql. Avant de peter les plombs avec M!cr0$0ft (waiting too much may kill you), j'étais vraiment raid dingue de SqlServeur et de son environnement. Décortiquer les stats, les tables, les problèmes de logs, dead-locking et indexation... j'adorais cela. Mais bon ça c'était avant de couler la bielle :-)
En virant dans le libre pour les développements futurs (même ici, dans mon emploi actuel), j'ai forcement fureté un peu avant de sélectionner le moteur DB qui allait soutenir l'expansion de l'entreprise. C'est là que j'ai croisé PostgreSql.
D'abord méfiant, craignant de trouver un monstre, j'ai finalement adopté PostgreSql qui se révèle être incroyablement puissant et facile à gérer. Bref, je suis au petit bonheur comme jadis avec mon SqlServeur - sic!

Les outils nécessaires
Sous Windows, nous arrons besoin des éléments suivants:

PostgreSQL 9.4 pour disposer de PgAdmin l'interface d'administration graphique
Putty pour les connexions SSH mais surtout le tunnel SSH

Recommandation
Tout au long de cet article, nous allons utiliser le login et mot de passe de l'administrateur du serveur de base de donnée PostgreSQL (le serveur chez PythonAnywhere). Nous avons opté pour cette approche afin de ne pas rendre cet article "plus complexe" que nécessaire.

En production, ou même en phase de développement, il est vivement recommandé de réserver le login "super" pour des tâches administratives exceptionnelles.
Pour la sécurité de vos données, de vos clients, de votre serveur Sql, créez un compte PostgreSQL Utilisateur dès que cela est possible :-).

SSH tunneling - le principe
Nous ne pouvons pas contacter directement le serveur Postgres de PythonAnywhere depuis notre PC. Les Firewalls de PythonAnywhere vont simplement refuser les connexions entrante de type Postgres.

Par contre, nous disposons nous pouvons établir une connexion SSH vers notre serveur chez PythonAnywhere (si nous avons un compte payant). Les connexions SSH sont acceptées par les firewall de PythonAnywhere.
La fonction primaire de SSH est d'offrir une ligne de commande sur le serveur via un canal sécurisé. Ce qu'il y a de bien avec SSH c'est qu'il est possible:

de faire des transferts FTP via SSH
d'établir des tunnels de communications via SSH (un peu comme du "VPN" pour ceux qui connaissent)

En établissant un tunnel via SSH grâce à Putty, nous pourrons établir une connexion avec pgAdmin (console d'administration graphique) fonctionnant sur notre PC >---> via le tunnel ssh >---> vers notre serveur PythonAnywhere QUI lui est habilité a contacter directement notre serveur de base de donnée PostgreSQL

Limite de cette approche
pas de tunnel SSH = pas de connection sur la DB.

Ni Putty, ni PythonAnywhere ne peuvent garantir le maintien de la connexion SSH.
Par conséquent, cette option ne peut être envisagée que pour des tâches d'administration... pas des connexions pour des outils de production.

Note: Chez Sam&Max (des pros Python) j'ai eu l'occasion de lire un article sur les meilleurs bibliothèques Python disponibles. Parmi elles, il y avait des outils d'administration à distance (via SSH, ftp, etc). Il pourrait donc être possible -- c'est du conditionnel -- d'établir une connexion ssh à la demande et qui sait, peut être même un tunnel.
Si c'est le cas, tout devient possible puisque vous pourriez établir des connexions SSH + Tunnel à la demande vers PythonAnywhere. Puis utiliser ce tunnel pour permettre à votre programme d'établir une connexion DB pour y faire les mise-à-jour nécessaires.
Très hypothétique pour l'instant... mais revenons à nos moutons.

Login et Mot de passe
Pour rendre cet article aussi tangible que possible, nous avons utilisé des informations existantes et le compte "gnomgnom" déjà utilisé dans de précédents
articles.
Il va de soit que le serveur Postgres, service SSH, mot de passe et DB seront détruit dès la parution de cet article.

PostgreSQL chez PythonAnywhere
Au moment de l'écriture de cet article, PythonAnywhere propose un PostgreSQL en version 9.4.

Une fois votre compte configuré correctement, vous aurez le droit d'initialiser votre serveur PostgreSQL.
Voici les étapes à suivre et l'information que nous allons en retirer.

Voici l'information que nous en retirons.
* L'adresse du serveur Postgres est: gnomgnom-103.postgres.pyhtonanywhere-services.com
* Le port à utiliser pour le contacter est 10054
* L'administrateur du serveur se nomme "super"

Ces information seront très utiles plus tard lorsque nous voudrons contacter le serveur depuis notre PC sous Windows.

LA PREMIERE CHOSE A FAIRE est d'initialiser le mot de passe de l'utilisateur super.

Pour cet article nous allons utiliser "miam-miam" comme mot de passe.

Console Postgres "en ligne"
Nous l'avons déjà eu l'occasion de découvrir les services de PythonAnywhere dans l'article "découverte des services de PythonAnywhere".
Nous allons par ailleurs réutiliser le compte de démo "gnomgnom" pour cet article.

Dans le même lignée, PythonAnywhere propose également une console Postgres.

Ouverture d'une Console PostgreSQL

Une fois lancée, vous devriez-voir l'invite qui ressemble à ceci:

Console PostgreSQL en ligne (dans votre navigateur Internet)

Nous allons l'utiliser pour créer une petite DB de démo.

Encodez le texte suivant pour créer la base de donnée "pawtest"

create database pawtest;

Nous allons maintenant nous "brancher" sur cette DB.

\c pawtest

Et créer notre table de test nommée "entries"
Copiez/collez le script suivant dans votre console.

drop table if exists entries;

create table entries (

  entry_id serial not null,

  title varchar(50) not null,

  text text not null

);

Nous allons aussi remplir notre table avec quelques informations
Copiez/collez les lignes suivantes dans la console.

insert into entries (title,text) values ('Yo!', 'Comment est ce que cela se présente?' );

insert into entries (title,text) values ('Powerfull', 'C''est vraiment simple à réaliser' );

insert into entries (title,text) values ('Essai', 'Un petit essai pour se rendre compte que la DB Postgres chez PythonAnyware peut être contrôlée avec <strong>pgAdmin</strong> depuis mon PC' );

insert into entries (title,text) values ('pgAdmin', 'pgAdmin est l''interface graphique permettant l''administration des serveurs et DB Postgres.<br />C''est un outil vraiment pratique' );

Nous pouvons vérifier le contenu de notre table à l'aide de la commande

select * from entries;

Vous devriez alors voir le résultat suivant:

Resultat d'un "select" dans la console Postgre

Utiliser Putty SSH
Nous allons maintenant utiliser la connexion SSH depuis notre PC pour avoir une commande shell sur notre serveur PythonAnywhere.
Les connexions SSH son autorisée dès que vous disposez d'un compte PythonAnywhere payant.

Se connecter en SSH depuis une machine Unix/Max est trivial.
Sous Windows, vous pouvez librement télécharger et installer l'utilitaire Putty (www.putty.org).
Ce dernier permet d'établir des connexions SSH depuis un système Windows mais permet aussi de faire du tunneling SSH (nous verrons cela plus tard)

Pour établir une connexion SSH, démarrez Putty

Saisissez l'adresse du serveur ssh de PythonAnywhere (ssh.pythonanywhere.com).
Vérifiez ensuite le port (22) et l'option (SSH).

Pressez simplement le bouton [OPEN] pour ouvrir la session SSH.

Une fois la connexion établie, vous devez encoder votre login et mot de passe de votre compte PythonAnywhere.

login as: gnomgnom

<<<<<<:>~ PythonAnywhere SSH. Help @ https://www.pythonanywhere.com/wiki/SSHAccess

gnomgnom@ssh.pythonanywhere.com's password:

Connexion SSH avec le serveur PythonAnywhere en utilisant Putty sous Windows

Comme vous pouvez le voir sur la capture d'écran, nous utilisons le compte de démonstration "gnomgnom".
Veillez à utiliser votre login et mot de passe PythonAnywhere.

Une fois cette connexion établie, c'est comme si vous étiez dans la console bash sur le site Web de PythonAnywhere.

L'utilitaire psql
psql (wikipedia, anglais) est un utilitaire en ligne de commande permettant d'interagir avec un serveur PostgreSQL. C'est un utilitaire pratique pour la gestion au quotidien (même si le mode texte peut paraître barbare de prime abord).

Vous pouvez l'utiliser en ligne de commande depuis un terminal... donc depuis la console Bash de PythonAnywhere OU dans une connexion ssh :-)

Maintenant que nous sommes connectés en SSH sur notre serveur PythonAnywhere (voir point ci-dessus), nous pouvons aussi utiliser l'utilitaire "psql" pour attaquer notre serveur PostGres.
Ce que nous faisons ici revient à utiliser la console PostGres disponible sur le site de PythonAnywhere sauf cette fois, tout part directement de notre PC.

Cet exercice est purement didactique... mais nous apprendra quelques petites choses bien utiles.

Etablisez votre connexion SSH avec le serveur PythonAnywhere. Puisque nous sommes sur notre Serveur PythonAnywhere, nous pouvons donc utiliser directement l'utilitaire psql.

La commande psql s'utilise comme suit:
psql -h serveur_postgres -p port_a_contacter nom_DB nom_utilisateur

Ce qui se traduit dans notre exemple par:

11:02 ~ $ : psql -h gnomgnom-103.postgres.pythonanywhere-services.com -p 10054 pawtest super

Nous pouvons utiliser un "select" pour voir le contenu de notre table "entries"

select * from entries;

ce qui produirait le résultat suivant:

Utilisation de psql directement sur le serveur PythonAnywhere
(sur-lequel nous somme connecté en SSH avec Putty)

Utilisez \q pour quitter psql
Utilisez ensuite exit pour terminer la connexion ssh

PgAdmin + SSH Tunneling + PythonAnywhere PostgreSQL
PostgreSQL propose un formidable outil d'administration qui s'appelle PgAdmin.
Cet utilitaire est en mode graphique ce qui rend les tâches d'administration très confortables et conviviales.

PgAdmin - Interface d'administration graphique pour PostgreSQL

Jusqu'à maintenant, nous avons découvert et manipulé différents éléments/outils.

Nous allons maintenant faire un grand saut en avant en utilisant les techniques décrites ci-dessus en réalisant un tunnel de communication SSH (ssh tunneling).

Ce tunnel, va permettre à notre outil PgAdmin (exécuté sur notre PC) de contacter le serveur PostgreSQL chez PythonAnywhere.

Il sera ainsi possible d'administrer notre Serveur et ses bases de données sans se prendre la tête.

PostgreSQL 9.4 sur notre PC
Occupons nous maintenant de notre PC où il faut installer PgAdmin.

Au moment de l'écriture de cet article, PythonAnywhere propose un PostgreSQL en version 9.4.
Nous allons donc prévoir un environnement dans cette même version (le mien date un peu... c'est un 9.1, l'upgrade ne fera pas de mal)

Nous allons installer la dernière version de Postgres (la 9.4 au minimum puisque c'est aussi la version utilisé chez PythonAnywhere) de façon à avoir 1) le moteur de base de donnée et 2) PgAdmin.

Pour cela, nous nous rendons sur www.postgresql.org

Télécharger PostgreSQL 9.4

Voila qui est fait, nous pouvons démarrer l'utilitaire PgAdmin et en profiter pour fureter sur notre serveur PostgeSQL local (celui installé sur notre PC)

SSH Tunneling avec Putty
Maintenant, nous allons créer un tunnel SSH entre notre PC Local et notre serveur PythonAnywhere.

Nous allons écouter sur le port 10054 de notre ordinateur (soit 127.0.01) et tout renvoyez vers ssh@pythonanywhere.com

Commençons par créer notre connection SSH et sauvons le profile sous le nom "gnomgnom @ PAW" (PAW signifiant PythonAnyWhere).

Ensuite, nous nous rendons dans le configuration du "Tunnel"

Nous y indiquons que nous voulons rediriger le port local 10054 vers notre serveur PostgreSQL (donc vers gnomgnom-103.postgres.pyhtonanywhere-services.com sur le port 10054) via la connexion SSH

Sur notre serveur PythonAnywhere les informations arriverons sur le port 5432 qui est normalement libre, ce dernier est le port standard pour un serveur PostgreSQL... mais il n'y en a pas sur notre serveur PythonAnywhere.

Arrivé là (sur notre serveur PythonAnywhere), les paquets pourrons transiter vers notre serveur PostgreSQL (celui qui s'appelle gnomgnom-103.xxxx ).

Ouvrons notre connexion SSH + Tunneling.

Retournez dans la liste des sessions, sélectionnez "gnomgnom @ PAW" puis pressez le bouton [OPEN]

Sélectionner le Tunnel PUIS revenir dans les sessions

Sélectionner la session souhaitée PUIS ouvrir

Et voici l'invite SSH où nous devons saisir notre login et mot de passe SSH (identique à notre login PythonAnywhere.... "gnomgom" dans notre cas)

Ouverture de la session SSH (qui ouvrira aussi le tunnel SSH)

Voila notre sessions SSH + Tunnel établie.
Nous allons pouvoir connecter notre console PgAdmin sur notre serveur PostgreSQL hébergé chez PythonAnywhere

PgAdmin via Putty SSH Tunneling
Dans le point précédent, nous avons établit une connexion SSH + Tunneling, nous allons maintenant l'emprunter avec PgAdmin pour nous brancher sur notre serveur PostgreSql gnomgnom-103.xxx chez PythonAnywhere.

Notes: Les puristes vous dirons que PgAdmin (PostgreSQL 9.4) permet d'établir directement une connexion SSH sans passer par Putty. Par contre, la fenêtre de configuration est très sensible aux erreurs... et la connexion explose parfois sans prévenir (du moins "sous Windows") alors que le tunnel SSH s'est révélé très stable sous Putty.

Commençons par créer une nouvelle connexion serveur.

Sélectionnez le menu "File | Add Server" (ou "Fichier | Ajouter Serveur").

Puis remplissez le contenu comme suit:

Enregistrer un Serveur PostgreSQL
(en utilisant le tunnel SSH ouvert à l'aide de Putty)

Nous y indiquons un nom explicite comme "PAW Postgres" pour identifier notre serveur PostgreSQL chez PythonAnywhere.

Le hôte à contacter (Host), c'est l'entrée de notre tunnel ouvert sur notre PC Local (127.0.0.1 et port 10054).
Pour rappel ce tunnel est redirigé vers notre serveur PostgresSQL donc vers gnomgnom-103.postgres.pyhtonanywhere-services.com sur le port 10054

Le nom d'utilisateur est "super" tel que définit sur notre serveur PostgreSql chez PythonAnywhere.

*** Ne stockez pas le mot de passe! c'est source d'ennui ***

Une fois la configuration terminée, double cliquez sur le lien "Paw Postgres" pour ouvrir la connexion serveur.

Double cliquer sur le noeud pour activer la connexion avec le serveur PostgreSQL distant
(assurez-vous bien d'avoir ouvert la connexion SSH + tunneling)

Cette connexion demande le mot de passe de l'utilisateur PostgreSQL qui ne nomme "super". Souvenez-vous, lors de la création du serveur chez PythonAnywhere, nous avions fixé le mot de passe à "miam-miam".

Une fois connecté, nous pouvons clairement voir notre DB de test "pawtest" précédemment créée avec l'utilitaire "psql"

[05

Une fois connecté, nous pouvons accéder à notre serveur et ses bases de donnée.
La DB pawtest créé précédemment dans cet article est maintenant accessible dans PgAdmin.

PgAdmin offre en autre un outil SQL très efficace et simple d'emploi

Sélectionner la DB "pawtest" et ouvrir l'outil SQL

Dans lequel nous pouvons également exécuter nos scripts SQL (le séparateur est le ; )

Outil SQL de PgAdmin - nettement plus convivial que psql.

PgAdmin + native SSH Tunneling
Depuis PostgresSQL 9.4, PGAdmin inclus des options permettant d'établir directement un tunnel SSH sans avoir recours à Putty.
La configuration d'une telle option est encore très sensible et une simple erreur de frappe (ou paramètre mal sélectionné) et la configuration de la connexion échoue (il faudra alors l'effacer et la recommencer).

En tout état de cause IL NE FAUT JAMAIS STOCKER AUCUN MOT DE PASSE sinon votre configuration de connexion échouera!

Nous allons donc désactiver le tunnel SSH établit avec Putty.

Puis nous allons dans PGAdmin pour enregistrer un nouveau serveur (via le menu File | Add Server... )

Rendons nous dans le volet "SSH Tunnel" et activons l'option "Use SSH tunneling".

Volet "SSH Tunnel" dans l'enregistrement d'un serveur sur PgAdmin

Tunnel Host : l'hôte du tunnel (vers où l'ouvrons nous) ssh.pythonanywhere.com
Port : le port reste le n° 22
Username : utilisateur/identifiant utilisé pour votre connexion ssh, donc votre login PythonAnywhere. Dans notre cas, il s'agit de "gnomgnom"
Password : le mot de passe associé au compte PythonAnywhere. Encodez le MAIS NE LE SAUVEZ PAS.

Revenons maintenant à notre volet "Properties" pour y encoder les informations de connexion sur le Serveur PostgreSQL.

Enregistrement du serveur PostgreSQL
(celui qui sera atteignable derrière la connexion SSH)

Name: utilisez un Nom qui ait du sens. "Postgres @ PAW (ssh)" indique le serveur Postgres chez PythonAnyWhere via SSH.
Host: le serveur PostgreSQL de l'autre côté du tunnel SSH. Il s'agit de notre serveur Postgres. Soit gnomgnom-103.postgres.pyhtonanywhere-services.com
Port: Très important, chez PythonAnywhere le port Postgres est 10054 (et non 5432 comme d'usage sur une installation locale)
Username: le nom d'utilisateur utilisé pour se connecter sur le serveur PostgreSQL. il s'agit de "super" dans notre cas puisque PythonAnywhere fixe l'administrateur du serveur PostgreSQL comme tel.
Mot de passe: Encodez le mot passe pour super, soit "miam-miam" MAIS NE SAUVEZ PAS LE MOT DE PASSE

Cliquez sur [OK] pour fermer la boite de dialogue.

Dès la fermeture de la boite de dialogue PgAdmin essaye d'établir la connexion.
En cas d'erreur de connexion (ou configuration), il vous faudra effacé l'enregistrement du serveur et recommencer la procédure.

Bien que cela fonctionne, je dois avouer qu'il est assez pénible de réaliser une configuration correcte du premier coup.
J'ai bien dut m'y rependre à 5 fois pendant que j'écrivais cet article.

Ressources

PythonAnywhere - Hébergement de solution Python
Putty - Site officiel www.putty.org
PostgreSQL - Site officiel www.postgresql.org
Plus d'info sur le SSH de PythonAnywhere https://www.pythonanywhere.com/wiki/SSHAccess