Notes à propos du SGBD Postgres SQL.

POSTGRES, développé à l'Université de Californie Berkeley à partir de 1986, projet piloté initialement par Michael Stonebraker. 20 ans de maturité, une des bases de données objets relationnelle de référence de l'opensource. Fut sponsorisée par:

• DARPA: Defense Advanced Research Project Agency
• ARO: Army Research Office
• NSF: National Science Fondation

En 94, Andrew Yu et Jolly Chen implementent le SQL, POSTGRES devient Postgres95 sous licence Opensource. Reecriture en C ANSI, reduction du code 25% amélioration perf et maintenabilité. En 1996, adoption du nom PostgreSQL

1. Création du groupe de base de données = groupe de catalogues ;
2. Initialiser un emplacement de stockage pour les bases de données avec la commande initdb ;
3. commande à invoquer sous l’utilisateur exécutant le serveur PostgreSQL
4. La commande se charge de créer la base systeme pg_database et le super utilisateur postgres

$ sudo mkdir /var/pgsql/data
$ sudo chown postgres /var/pgsql/data
$ su postgres
$ initdb -D /var/pgsql/data

#lancer le serveur en ligne de commande postgres -D /var/postgres/data > /var/log/postgresql.log 2>&1 &

#utiliser le script de gestion du serveur

pg_ctl start -l /var/log/postgresql.log

Les droits d'accès aux éléments (objets) gérés par le SGDB PostgreSQL sont régis par le concept de roles. Les rôles postgres sont distincts des utilisateurs et des groupes du système hôte.

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.

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

# depuis le compte root local
su - postgres
psql

Les rôles sont enregistrés dans la table pg_roles, on peut également les lister via une requête SQL:

SELECT rolname FROM pg_roles;

Pour lister les rôles autorisés à se connecter :

SELECT rolname FROM pg_roles WHERE rolcanlogin;

Depuis psql, on peut également utiliser la méta-commande \du :

\du

Pour créer un nouveau rôle

CREATE ROLE mon_role;

Pour supprimer un rôle existant:

DROP ROLE mon_role;

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)

psql -U postgres

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 CREATE ROLE ou à la modification via la commande ALTER ROLE.

CREATE ROLE yoann LOGIN ;
 
-- equivalent
CREATE USER yoann ;

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.

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

CREATE USER yoann PASSWORD 'toto31' ;

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

CREATE ROLE aRoleName NOINHERIT ;

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.

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

CREATE ROLE users ;

Une fois que le rôle existe, on peut ajouter ou retirer des membres via les commandes GRANT et REVOKE

-- 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, ... ;

Les membres d'un rôle peuvent utiliser les droits associés à leur rôle de deux manières :

1. 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.
2. 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.

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

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 :

SET ROLE ADMIN;

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 :

RESET ROLE
 
-- équivalent
SET ROLE NONE;
 
-- équivalent
SET ROLE joe;

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.

Droits des roles SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE Les commandes GRANT et REVOKE permettent de donner ou de retirer les droits aux roles Tout objet créé a un proprietaire, par defaut le role qui execute la commande de creation. Le mot clé ALL désigne tous les droits et PUBLIC tous le roles utilisateurs.

#creation d'un groupe (role sans attribut connexion) CREATE ROLE users;

# Affectation d'utilisateurs au groupe

GRANT users TO yoann, thomas, olivier, nicolas

Creer une base de données

CREATE DATABASE nom_base OWNER nom_role;

Une base de données est un ensemble nommé d'objets SQL. Chaque objet appartient à une et une seule base. Cependant certains catalogues tels que pg_database appartiennent à tout le groupe de base de données et sont accessible dans toutes les bases.

Hiérarchie: Serveur » bases » Schemas » objets

# Détruire une base de données avec:

DROP DATABASE nom_database

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 \ et ne se terminent pas par ; : 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 ;.

Aide sur les métacommandes psql :

\?

Aide mémoire sur les commandes SQL :

\h

Afficher les informations de connexion :

\conninfo

Afficher ou définir l'encodage du client :

\encoding

Affiche l'utilisateur courant :

SELECT CURRENT_USER;

Afficher la base de données courante :

SELECT current_catalog;
 
-- équivalent
SELECT current_database();

Afficher le rôle courant :

SELECT current_role;

Afficher les variables d'environnement :

\set

Afficher la variable USER :

\echo :USER

On liste les base via la commande \list

\list

\connect database

Lister les tables de la base courante :

\dt

Pour afficher les informations détaillées de la table a_table_name (nom des champs, type, clés etc):

\d a_table_name

\d+ a_table_name

Terminer l' exécution du client:

\q

• https://severalnines.com/database-blog/postgresql-privileges-user-management-what-you-should-know