{{tag>software sysadmin dbadmin postgres postresql sql}}

:TODO: :TODO_DOCUPDATE:

====== PostgreSQL : Introduction au client psql =====

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

<code bash>
docker container run --detach --rm --name pg_test -e POSTGRES_PASSWORD="secret" postgres:9.6-buster
</code>

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

<code bash>
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)
</code>

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:

<code bash>
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
</code>

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.

===== Les méta commandes =====

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.


<code 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
</code>

===== Exécution pas à pas =====

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:

<code bash>
psql -s -U alice dbtest
</code>

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

<code>
\set SINGLESTEP on
</code>

Pour quitter le mode pas à pas on supprime la variable:
<code>
\unset SINGLESTEP
</code>

===== Gestion des rôles =====

La notion de rôle a permit d'unifier utilisateurs et groupes. Pour afficher les rôles existants:
<code sql>
-- 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;
</code>

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).


<code sql>
-- 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';
</code>

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.
<code sql>

-- 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;
</code>

On affecte un ensemble de droits au groupe:
<code sql>
-- Le role db_manager permet la création de bases de données
ALTER ROLE db_manager WITH CREATEDB; 
</code>

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:

<code sql>
-- 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;
</code>

Pour retirer le rôle db_manager à l'utilisateur alice; 
<code sql>
REVOKE db_manager FROM alice;
</code>

On peut à présent tester les droits en se reconnectant avec alice
<code bash>
psql -W -U alice postgres
</code>

<code>
-- 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
</code>

===== Gestion des bases =====

Afficher les bases:
<code>
\list
\l
</code>

Pour créer une base
<code sql>
CREATE DATABASE mabase;
</code>

Réaffecter un base à un utilisateur existant:
<code sql>
ALTER DATABASE mabase OWNER TO john;
</code>

Supprimer la base.
<code sql>
DROP DATABASE mabase;
</code>

===== Importer des données =====


===== Références =====

  * 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/