Différences

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

--- software:applications:postgresql:start [2021/08/10 07:50] – yoann
+++ software:applications:postgresql:start [2025/03/25 21:52] (Version actuelle) – yoann
@@ Ligne 21: / Ligne 21: @@
 </note>
-  - Création du groupe de base de données = groupe de catalogues;
+  - Création du groupe de base de données = groupe de catalogues ;
-  - Initialiser un emplacement de stockage pour les bases de données avec la commande initdb
+  - Initialiser un emplacement de stockage pour les bases de données avec la commande **initdb** ;
-  - commande à invoquer sous l'utlisateur executant le serveur PostgreSQL
+  - commande à invoquer sous l’utilisateur exécutant le serveur PostgreSQL
   - La commande se charge de créer la base systeme pg_database et le super utilisateur postgres
@@ Ligne 41: / Ligne 41: @@
 </code>
-Dans PostgreSQL les droits d'acces aux bases de données sont gérés régis via le concept de roles. Les roles sous PostgreSQL correspondent a la notions d'utilisateur et de groues sur le systeme de fichiers. Les roles sont une logique interne de PostgreSQL; ils n'existent pas sur le  systeme de fichier (pas d'utilisateurs ou groupes).
+===== Les rôles =====
-Un rôle possède des objet et peut affecter des droits d'utilisation sur ces objets a d'autres rôles.
-Les rôles définis sont communs au groupe de catalogue (groupe de base de données).
-Lister les rôles existants:
+Les droits d'accès aux éléments (objets) gérés par le SGDB PostgreSQL sont régis par le concept de [[https://www.postgresql.org/docs/17/user-manag.html|roles]]. Les rôles postgres sont distincts des utilisateurs et des groupes du système hôte.
-<code>
-\du
+<note>
+Un rôle peut être considéré soit comme un utilisateur soit comme un groupe d'utilisateurs selon la manière dont il est définit.
+</note>
+Un rôle peut être le propriétaire d'un objet (relations, tables, fonctions etc) et peut autoriser l'accès de ses objets à d'autres rôles.
+Il est possible d'associer le membre d'un rôle à un autre rôle pour lui faire bénéficier des droits/privilèges du second rôle.
+<note>
+Le concept de rôle généralise celui d'utilisateurs et de groupes qui étaient des entités différentes présentes dans les anciennes versions (postgres < 8.1). Un rôle peut se comporter comme un utilisateur, un groupe ou les deux.
+</note>
+Les rôles définis sont communs au groupe de catalogue (groupe de base de données ou cluster).
+Afin de pouvoir démarrer et initialiser le SGBDR, un rôle super-utilisateur ayant le droit se connecter est créé. Ce role est souvent nommé **postgres**.
+Afin de pouvoir gérer d'autres rôles, il faut se connecter avec ce role initial
+<code bash>
+# depuis le compte root local
+su - postgres
+psql
 </code>
+==== Lister les rôles ====
 Les rôles sont enregistrés dans la table pg_roles, on peut également les lister via une requête SQL:
 <code SQL>
 SELECT rolname from pg_roles;
 </code>
+Pour lister les rôles autorisés à se connecter :
+<code sql>
+SELECT rolname FROM pg_roles WHERE rolcanlogin;
+</code>
+Depuis psql, on peut également utiliser la méta-commande ''\du'' :
+<code>
+\du
+</code>
+==== Créer/supprimer un rôle ====
 Pour créer un nouveau rôle
@@ Ligne 65: / Ligne 101: @@
 DROP ROLE mon_role;
 </code>
+Pour simplifier la gestion des rôles depuis la ligne de commande, il existe également les commandes **createuser** et **dropuser**.
 # Par convention un rôle est propriétaire du groupe de catalogues créé, postgres (équivalent super utilisateur)
@@ Ligne 72: / Ligne 111: @@
 </code>
-Les attributs des rôles doivent être explicitement définis lors de la création (CREATE) ou modification (ALTER)
+===== Les attributs du rôle =====
-Yoann est un rôle avec autorisation d’établir une connexion a la base de données (équivaut à un utilisateur)
+Les attributs associés au rôle permettent de définir ses privilèges/droits.
+Les attributs doivent être explicitement définis lors de la création via la commande [[https://www.postgresql.org/docs/17/sql-createrole.html|CREATE ROLE]] ou à la modification via la commande [[https://www.postgresql.org/docs/17/sql-alterrole.html|ALTER ROLE]].
 <code sql>
-CREATE ROLE yoann LOGIN
+CREATE ROLE yoann LOGIN ;
-ALTER ROLE yoann SET PASSWORD TO 'toto';
+-- equivalent
+CREATE USER yoann ;
 </code>
-# Principaux attributs des roles
+Dans l'exemple ci-dessus ''yoann'' est un rôle avec autorisation d’établir une connexion a la base de données (équivaut à un utilisateur). La requête ''CREATE USER'' est équivalente à ''CREATE ROLE'', elle inclus automatiquement l'attribut ''LOGIN''.
-# SUPERUSER: le role a tous les droits  creation d'un superutilisateur
-# CREATEDB: le role peut creer de nouvelles bases de données
-# LOGIN: le role peut initer une connexion au serveur de base de données.
-# CREATEROLE: le role peut creer d'autres roles
-# PASSWORD: authentification
+==== Quelques attributs ====
+  * **CREATEDB** : pour permettre la création de base de données ;
+  * **CREATEROLE** : gérer les rôles ;
+  * **PASSWORD** : n'est significatif que si la méthode d'authentification du client requiert de fournir un mot de passe. C'est le cas pour les méthodes d'authentification **password** ou **md5**. L'authentification par mot de passe auprès du SGBD est indépendante de celle du système hôte.
+  * **CONNECTION LIMIT** : spécifier le nombre de connexions en parallèle autorisées pour un même role (par défaut -1 = illimité).
+<code sql>
+CREATE USER yoann PASSWORD 'toto31' ;
+</code>
+==== Héritage des privilèges ====
+Par défaut, un rôle **hérite des privilèges**/droits associés aux rôles desquels il est membre. On peut modifier ce comportement en spécifiant l'attribut **NOINHERIT**
+<code sql>
+CREATE ROLE aRoleName NOINHERIT ;
+</code>
+Il est également possible de redéfinir spécifiquement le comportement de l'héritage sur une requête ''GRANT'' en utilisant ''WITH INHERIT TRUE'' ou ''WITH INHERIT FALSE''.
+===== Appartenance à un role =====
+En général, on utilise les groupes d'utilisateurs pour gérer plus facilement les droits : de cette manière les opérations d'affectation (GRANT) ou de révocation (REVOKE) de droits sur le groupe s'appliquent à l'ensemble des utilisateurs membres.
+Dans Postgres on crée un role qui représente le groupe. En général un role représentant un groupe n'a pas d'attribut LOGIN (même s'il peut être définit si besoin).
+<code sql>
+CREATE ROLE users ;
+</code>
+Une fois que le rôle existe, on peut ajouter ou retirer des membres via les commandes [[https://www.postgresql.org/docs/17/sql-grant.html|GRANT]] et [[https://www.postgresql.org/docs/17/sql-revoke.html|REVOKE]]
+<code sql>
+-- Affecter les privilèges du role aGroupeRole à n autres roles
+GRANT aGroupRole TO aUserRole1, aUserRole2, ... ;
+-- Retirer les privilèges du role aGroupeRole à n autres roles
+REVOQUE aGroupRole FROM aUserRole1, aUserRole2, ... ;
+</code>
+<note>
+Le SGDB ne permettra pas :
+  * Les références circulaires ;
+  * L'affectation des attributs d'un rôle à ''PUBLIC'' ; **NB : A valider/reformuler**
+</note>
+Les membres d'un rôle peuvent utiliser les droits associés à leur rôle de deux manières :
+  - Les membres affectés avec l'option ''SET TRUE'' positionnée peuvent utiliser la commande ''SET ROLE'' pour devenir temporairement le rôle parent lui-même. De cette manière la session à accès aux privilèges du rôle de groupe à la place du rôle de login initial et tout objet créé est considéré comme appartenant au rôle de groupe et non au rôle de login initial.
+  - Les membres affectés avec l'option ''INHERIT'' au rôle de groupe, bénéficient de l'ensemble des droits hérités directement ou indirectement au rôle jusqu’à ce que la chaine soit interrompue par un membre n'ayant pas l'option d'héritage active.
+<code sql>
+-- Creation d'un role "de type utilisateur"
+CREATE ROLE joe LOGIN;
+-- Creation de roles "de type groupes"
+CREATE ROLE admin;
+CREATE ROLE wheel;
+CREATE ROLE island;
+-- joe hérite des privilèges du role admin
+GRANT admin TO joe WITH INHERIT TRUE;
+-- le role admin est membre de wheel mais les droits spécifiques à
+-- wheel ne pourront pas être transmis
+GRANT wheel TO admin WITH INHERIT FALSE;
+-- joe hérite de island mais ne pourra pas
+GRANT island TO joe WITH INHERIT TRUE, SET FALSE;
+</code>
+{{role_membership.png}}
+Immédiatement après connexion avec le rôle ''joe'' la session bénéficie des privilèges associés directement au role ''joe'' plus ceux des rôles ''admin'' et ''island'' car ''joe'' hérite de leurs privilèges.
+Toutefois les privilèges associés à ''wheel'' ne sont pas disponibles pour ''joe'' car même s'il est indirectement un membre de ''wheel'' cette appartenance se fait au travers du role ''admin'' sans héritage (INHERIT FALSE).
+Après la commande :
+<code sql>
+SET ROLE ADMIN;
+</code>
+La session bénéficiera des privilèges associés exclusivement à ''admin'' et non ceux associés à ''joe'' ou ''island''.
+Après exécution de la commande :
+<cdoe sql>
+SET ROLE wheel;
+</code>
+La session bénéficiera des privilèges associés exclusivement à ''wheel'' et non ceux associés à ''joe'' ou ''admin''. Pour restaurer les droits initiaux :
+<code sql>
+RESET ROLE
+-- équivalent
+SET ROLE NONE;
+-- équivalent
+SET ROLE joe;
+</code>
+<note>
+La commande **''SET ROLE''** permet de sélectionner tous les rôles auxquels le rôle de login est directement ou indirectement associé par l'enchainement des appartenances et pour lesquels l'option ''SET TRUE'' est positionnée.
+Dans l'exemple ci-dessus il n'est pas nécessaire de devenir admin avant de passer wheel mais il est impossible de devenir island. Le role joe bénéficie des droits de island seulement via l'héritage.
+</note>
+Les attributs **LOGIN**, **SUPERUSER**, **CREATEDB** et **CREATEROLE** sont des droits spécifiques et ne peuvent pas être hérités comme les autres privilèges. Vous devez utiliser la commande ''SET ROLE'' pour utiliser un role ayant ces attributs.
+Pour continuer avec l'exemple précédent, on pourrait affecter au rôle ''admin'' à les attributs ''CREATEDB'' et ''CREATEROLE''. La session initiée avec le role de login ''joe'' n'aura pas accès à ces privilèges immédiatement : elle devra utiliser la commande **''SET ROLE admin''**.
+Lorsque un rôle de type groupe est supprimé via la commande **''DROP ROLE aGroupeRole''** les roles membres du groupe sont automatiquement retirés mais ne sont pas autrement affectés.
+===== Suppression des roles =====
 Droits des roles
 SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE
@@ Ligne 94: / Ligne 254: @@
 Le mot clé ALL désigne tous les droits et PUBLIC tous le roles utilisateurs.
-#creation d'un groupe (role sans attribut connexion
+#creation d'un groupe (role sans attribut connexion)
 CREATE ROLE users;
@@ Ligne 119: / Ligne 279: @@
 ===== Commandes élémentaires =====
-Quelques commandes élémentaires a exécuter depuis le client postgres **psql** après authentification.
+Quelques commandes élémentaires à exécuter depuis le client postgres **psql** après authentification.
+Il faut différencier :
+  * les **métacommandes** qui commencent par le caractère <key>\</key> et ne se terminent pas par <key>;</key> : elles sont propres au **client psql** et sont interprétées directement par lui  ;
+  * Les **commandes SQL** qui sont transmises au SGBD et qui se terminent par <key>;</key>.
+==== Aide ====
+Aide sur les métacommandes psql :
+<code>
+\?
+</code>
+Aide mémoire sur les commandes SQL :
+<code>
+\h
+</code>
+==== Affichage du contexte ====
+Afficher les informations de connexion :
+<code>
+\conninfo
+</code>
+Afficher ou définir l'encodage du client :
+<code>
+\encoding
+</code>
+Affiche l'utilisateur courant :
+<code sql>
+SELECT current_user;
+</code>
+Afficher la base de données courante :
+<code sql>
+SELECT current_catalog;
+-- équivalent
+SELECT current_database();
+</code>
+<note>
+Dans la norme SQL on appelle la base de données **catalogue** et les tables **relations**.
+</note>
+Afficher le rôle courant :
+<code sql>
+SELECT current_role;
+</code>
+Afficher les variables d'environnement :
+<code>
+\set
+</code>
+Afficher la variable ''USER'' :
+<code>
+\echo :USER
+</code>
 ==== Lister les bases ====
@@ Ligne 135: / Ligne 357: @@
 ==== Lister les tables ====
+Lister les tables de la base courante :
 <code>
 \dt
 </code>
+Pour afficher les informations détaillées de la table ''a_table_name'' (nom des champs, type, clés etc):
+<code>
+\d a_table_name
+\d+ a_table_name
+</code>
 ==== Quitter ====

wikinotes

Outils pour utilisateurs

Outils du site

Différences

Outils de la page