Connexion au serveur PostgreSQL

Serveur

Le cours et les TP

• Serveur PostGreSQL
• Machine dédiée : etu-pgsql.math.univ-paris-diderot.fr

Clients

En salle TP, vous pourrez choisir entre trois clients

• dbeaver, conseillé car ce client dispose d’une interface graphique.
• psql,
• pgcli.

dbeaver est un client graphique qui permet d’attaquer une grande famille de SGBDs.

psql et pgcli sont très proches. Ce sont des applications qui fonctionnent en mode ligne de commande. pgcli est un peu plus conviviale que psql avec un système de complétion plus performant. L’ensemble des commandes spéciales proposées par pgcli est un peu moins vaste que celui proposé par psql.

Tous ces clients doivent utiliser des connexions sécurisées ssh.

Dans cette page, vous apprendrez dans un premier temps à établir une connexion ssh avec la machine etu-pgsql.math.univ-paris-diderot.fr pour lancer un client en ligne de commande fonctionnant (hébergé) sur cette machine.

Puis vous aprendrez à établir un tunnel ssh avec redirection de port pour travailler sur un client hébergé par votre poste de travail.

Une fois que vous aurez appris à faire ces deux manipulations, vous pourrez utiliser le client dBeaver sur votre poste de travail en salle de TP comme à l’extérieur.

Connexions ssh (Linux/MacOS/Windows)

Pour accéder au serveur Postgres (SGBD), il faut d’abord se connecter à la machine qui héberge ce serveur etu-pgsql.math.univ-paris-diderot.fr.

Pour se connecter à etu-pgsql.math.univ-paris-diderot.fr, on utilise le protocole ssh, avec son identifiant et son mot de passe ENT.

Il faut ouvrir un terminal (de préférence PowerShell sous Windows) puis lancer la commande suivante en remplaçant username par votre nom de login.

$ ssh username@etu-pgsql.math.univ-paris-diderot.fr
(username@etu-pgsql.math.univ-paris-diderot.fr) Password for username@etu-pgsql.math.univ-paris-diderot.fr:
Last login: ..................
....
....
This fortune brought to you by:
$FreeBSD$
[username@etu-pgsql ~]$ ...

Attention : pas d’écho lorsque vous saisissez votre mot de passe.

On peut maintenant se connecter au serveur Postgres, toujours avec son identifiant et son mot de passe ENT

Ici, nous proposons d’utiliser le client pgcli exécuté sur etu-pgsql.math.univ-paris-diderot.fr :

[username@etu-pgsql ~]$ pgcli -d bd_2023-24 
Password for username: 
Server: PostgreSQL 13.8
Version: 3.5.0
Home: http://pgcli.com
userame@/tmp:bd_2023-24> 

NotePourquoi préciser -d bd_2023-24 ?

bd_2023-24 est un des catalogues hébergés par le serveur PostGres.
bd_2023-24 contient les schemas sur lesquels nous travaillerons.

Nous sommes maintenant dans une session du serveur

username@/tmp:bd_2023-24> \conninfo
You are connected to database "bd_2023-24" as user "username" on socket "/tmp" at port "None".
Time: 0.000s

Nous nous intéresserons en général à un ensemble de tables formant un schéma. Ici, nous choisissons comme schéma par défaut world et nous affichons les tables du schéma.

username@/tmp:bd_2023-24> set search_path to world ;
SET
Time: 0.001s
username@/tmp:bd_2023-24> \d
+--------+-----------------+-------+-----------+
| Schema | Name            | Type  | Owner     |
|--------+-----------------+-------+-----------|
| world  | city            | table | boucheron |
| world  | country         | table | boucheron |
| world  | countrylanguage | table | boucheron |
+--------+-----------------+-------+-----------+
SELECT 3
Time: 0.011s
username@/tmp:bd_2023-24> ...

Important

Pour quitter le client pgcli, il faut entrer la commande quit.

Ensuite il faut fermer la connexion ssh avec la commande logout.

Connexions ssh avec tunnel

Mais utiliser une connexion ssh et un client base de données qui s’exécute sur le serveur n’est pas la manière la plus confortable de travailler.

Il est plus pratique d’utiliser un client base de données qui s’exécute sur sa propre machine (en local) et qui interagit avec le serveur PostGres au travers d’un tunnel ssh.

La commande suivante établit un tunnel (remplacer `username` par votre nom de login).

$ ssh username@etu-pgsql.math.univ-paris-diderot.fr -L 5436:localhost:5432
(username@etu-pgsql.math.univ-paris-diderot.fr) Password for username@etu-pgsql.math.univ-paris-diderot.fr:
$ 

Une fois le tunnel établi, la fenêtre terminal n’est disponible que pour les commandes ssh (en particulier logout pour clore la connexion). Si on veut utliser les clients en ligne de commande pgcli ou psql, il faut utiliser une autre fenêtre de terminal.

NoteRedirection de port -L 5436:localhost:5432

L’option -L 5436:localhost:5432 demande la redirection du port 5436 de la machine locale (localhost) vers le port 5432 de la machine distante (etu-pgsql.math.univ-paris-diderot.fr).

Qu’est-ce qu’un port ?

Dans le proptocole TCP/IP qui permet à une machine de s’adresser à une autre machine sur Internet (l’inter-network mondial), le nom etu-pgsql.math.univ-paris-diderot.fr correspond à une adresse numérique unique (adresse IP). Mais le protocole TCP/IP ajoute à ces adresses des numéros de port. De même qu’on utilise plusieurs boîtes au lettres dans un immeuble pour ne pas mélanger le courrier des différents habitants, de même, on utilise des numéros de port pour ne pas mélanger les données envoyées aux différents serveurs de la machines distantes. On appelle serveur un logiciel qui écoute (attend des données).

Pourquoi faut-il faire une redirection de port ?

Un serveur PostGres écoute (attend) d’éventuels clients sur le port officiel 5432. Le serveur que nous utiliserons attend effectivement ses clients sur le port 5432 de la machine qui l’héberge. Notre client local ne va pas s’adresser directement au port 5432 de etu-pgsql.math.univ-paris-diderot.fr (c’est interdit pour des raisons de sécurité). Notre client local s’adressera au port 5436 de la machine qui héberge le client (localhost) et qui est lui-même renvoyé via le tunnel ssh vers le port 5432 de la machine qui héberge le serveur.

On peut maintenant lancer un client sur sa propre machine (localhost) en précisant qu’on s’adresse au port local 5436 (ou le port que vous choisissez), la requête de connexion au serveur PostGres distant sera transmise par le tunnel : elle sera envoyée sur le port officiel 5432 de la machine distante. Une fois la session établie, tout se passsera comme précédemment (ou presque).

N’oubliez pas de remplacer username par votre nom de login !

$ pgcli -d bd_2023-24 -h localhost -p 5436 -u username -W
Server: PostgreSQL 13.8
Version: 3.5.0
Home: http://pgcli.com
bd_2023-24> \d
+----------------+--------------+
| Name           | Owner        |
|----------------+--------------|
...
...

Client dbeaver

Le mécanisme du tunnel ssh peut être utilisé pour connecter un client plus ambitieux au serveur. Le client dbeaver est particulièrement facile à utiliser. Voir la page Client dBeaver pour son utilisation.

NoteTunnel en tâche de fond ou pas ?

Pour les spécialistes, il est possible sous Linux ou Mac de lancer le tunnel en tâche de fond en ajoutant l’option -f à la commande ssh.

Si le lancement du tunnel en tâche de premier plan peut paraître moins pratique que le lancement du tunnel en tâche de fond, la première solution présente un avantage. Lorsque le tunnel cesse de fonctionner (en général parce qu’on ne s’en est pas servi depuis quelques minutes), il faut terminer (tuer) le processus qui contrôle le tunnel, pour pouvoir récupérer l’usage du port local ; si le tunnel est contrôlé par une tache de premier plan, c’est trivial (CTRL+C sous Linux/Windows, Pomme+C sous Mac). Si le tunnel est contrôlé par une tâche de fond, il faut déterminer le processus contrôleur, puis le terminer explicitement ($ kill -9 pid).