PostgreSQL : Introduction au client psql

Pour s'essayer sans conséquences, on crée un conteneur Docker temporaire exécutant le serveur PostgreSQL:

docker container run --detach --rm --name pg_test -e POSTGRES_PASSWORD="secret" postgres:9.6-buster

On exécute ensuite un shell interactif dans le conteneur pour lancer le client PostgreSQL psql:

docker container exec --interactive --tty pg_test /bin/bash -i -l
 
# depuis le shell interactif et l'utilisateur root
id
uid=0(root) gid=0(root) groups=0(root)

Si on invoque le client psql sans argument, le rôle et le nom de la base utilisés par défaut sont déduit du nom de l'utilisateur courant dans notre cas 'root'. Cependant le rôle root et la base de donnée root n'existent pas:

psql
psql: FATAL:  role "root" does not exist
 
# On peut spécifier le rôle à utiliser via l'option -U ou via la variable d'environnement **PGUSER**
psql -U postgres
 
# Equivalent
PGUSER=postgres psql

A la création du serveur PostgreSQL, un rôle postgres et une base postgres sont créés:

• Le rôle postgres à tous les droits sur le serveur de base de données, c'est l'administrateur du service PostgreSQL.

Lorsque le client s'exécute en mode interactif, il affiche un prompt de la forme ma_base=> pour un utilisateur ou ma_base=# pour un super-utilisateur. Le prompt ma_base-> indique que le client attend la suite de la commande.

Le client psql dispose de méta-commandes qui sont interprétées par psql lui-même. Elles ont été pensées pour l'administration et l'usage de scripts:

• Elles commencent par un backslash '\' en dehors de guillemets simples;
• Les mnémoniques sont en minuscules;
• Elles se terminent par un retour à la ligne ou sont séparées par '\\' et non par un ';' car ce ne sont pas des commandes SQL.

-- Afficher l'aide concernant les meta-commandes de psql
\?
 
-- Lister les bases de données
\list
\l
 
-- Afficher les objets (display) tables, vues, séquences etc
\d
 
-- Afficher également les objets système
\dS
 
-- Afficher les objets système avec des infos complémentaires
\dS+
 
-- Afficher l'aide des commandes SQL
\h
 
-- Lire des commandes depuis un fichier de script
\i import_file.sql
 
-- Écrire dans un fichier
\o outputfile.txt
 
-- Afficher les variables internes psql
\SET
 
\echo :USER
 
-- Exécuter une commande externe
\! ls -lh
 
-- Quitter le client en mode interactif
\q

Pour le débogage des scripts ou des commandes, il peut être intéressant de démarrer psql avec l'option -s qui permet d'afficher et de valider chaque commande avant exécution:

psql -s -U alice dbtest

Si psql n'a pas été lancé avec l'option, on peut activer le mode pas à pas en définissant la variable SINGLESTEP:

\set SINGLESTEP on

Pour quitter le mode pas à pas on supprime la variable:

\unset SINGLESTEP

La notion de rôle a permit d'unifier utilisateurs et groupes. Pour afficher les rôles existants:

-- Affiche les rôles et leurs attributs (droits)
\du
 
-- Équivalent
\dg
 
-- Retourne les noms des rôles uniquement
SELECT rolname AS roles FROM pg_roles;

Dans les exemples qui suivent on manipule les rôles pour créer des utilisateurs et des groupes:

• Un utilisateur équivaut à un rôle avec le privilège LOGIN;
• Un groupe équivaut à un rôle sans droit de connexion (NOLOGIN).

-- Affecter des droits à un rôle
CREATE ROLE john WITH NOLOGIN;
 
-- Modifier un rôle existant
ALTER ROLE john WITH LOGIN CREATEDB;
 
-- Affecter un mot de passe à un rôle existant
ALTER ROLE john password 'secretPass';
 
-- Annuler le mot de passe associé à un rôle
ALTER ROLE john WITH password NULL;
 
CREATE ROLE alice WITH LOGIN PASSWORD 'alisson1';
 
-- Définir une date limite de validité
CREATE ROLE bob WITH LOGIN PASSWORD 'secret_phrase' VALID UNTIL '2022-11-30';

On affecte un ensemble de privilèges à un groupe (rôle sans droit de connexion) puis on affecte le rôle aux utilisateurs. Les utilisateurs héritent alors des droits du groupe.

-- Création d'un groupe nommé db_manager
CREATE ROLE db_manager WITH NOLOGIN;
 
-- Équivalent
CREATE ROLE db_manager;
 
-- Équivalent (syntaxe dépréciée)
CREATE GROUP db_manager;

On affecte un ensemble de droits au groupe:

-- Le role db_manager permet la création de bases de données
ALTER ROLE db_manager WITH CREATEDB; 

On peut ensuite associer un ou des utilisateurs au groupe. Cela revient à affecter le rôle à l’utilisateur. Les utilisateurs héritent alors des droits du groupe:

-- Affecter le rôle db_manager à l'utilisateur alice
GRANT db_manager TO alice;
 
-- Équivalent (syntaxe dépréciée)
ALTER GROUP db_manager ADD USER alice;

Pour retirer le rôle db_manager à l'utilisateur alice;

REVOKE db_manager FROM alice;

On peut à présent tester les droits en se reconnectant avec alice

psql -W -U alice postgres

-- Retourne le rôle à la connexion (alice)
SELECT current_role;
\echo :USER

-- Change de rôle
SET ROLE db_manager;

-- Retourne le rôle choisit (ici db_manager)
SELECT current_role;

-- Création d'une base
CREATE DATABASE db_test;

-- Modification du rôle actif
SET ROLE alice;

\q

Afficher les bases:

\list
\l

Pour créer une base

CREATE DATABASE mabase;

Réaffecter un base à un utilisateur existant:

ALTER DATABASE mabase OWNER TO john;

Supprimer la base.

DROP DATABASE mabase;

• https://www.educba.com/postgresql-user-password/
• https://www.educba.com/postgresql-roles/
• https://dataschool.com/learn-sql/meta-commands-in-psql/
• https://www.educba.com/postgresql-functions/