wikinotes

Outils pour utilisateurs

Outils du site

Piste : • configuration • workflow • introduction_client_postgres_psql • notes
software:applications:postgresql:introduction_client_postgres_psql

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentesRévision précédente
Prochaine révision		Révision précédente
software:applications:postgresql:introduction_client_postgres_psql [2022/11/10 09:51] – supprimée - modification externe (Unknown date) 127.0.0.1software:applications:postgresql:introduction_client_postgres_psql [2022/11/10 11:24] (Version actuelle) – yoann
Ligne 1: Ligne 1:
 +{{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/

Outils de la page

Sauf mention contraire, le contenu de ce wiki est placé sous les termes de la licence suivante : GNU Free Documentation License 1.3
GNU Free Documentation License 1.3 Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki