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 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 positionnée peuvent utiliser la commande SET ROLE pour devenir temporairement le role 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 role de groupe, bénéficient de l'ensemble des droits hérités directement ou indirectement au role jusqu'a 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 ne pourra pas transmettre héritage de privileges
GRANT wheel TO admin WITH INHERIT FALSE;
 
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).

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 a exécuter depuis le client postgres psql après authentification.

On liste les base via la commande \list

\list

\connect database

\dt

Terminer l' exécution du client:

\q

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