Vous trouverez en ligne les différentes versions complètes de ce document.

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

Pour gérer des bases, il faut savoir les créer, les configurer et les supprimer. Il faut surtout comprendre qui a le droit de faire quoi, et comment. Ce chapitre détaille chacune des opérations possibles concernant les bases sur une instance.

La liste des bases de données est disponible grâce à un catalogue système appelé pg_database. Il suffit d’un SELECT pour récupérer les méta-données sur chaque base :

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_database \gx

-[ RECORD 1 ]--+------------------------------------
oid            | 5
datname        | postgres
datdba         | 10
encoding       | 6
datlocprovider | c
datistemplate  | f
datallowconn   | t
datconnlimit   | -1
datfrozenxid   | 8885214
datminmxid     | 1
dattablespace  | 1663
datcollate     | fr_FR.UTF-8
datctype       | fr_FR.UTF-8
daticulocale   | 
daticurules    | 
datcollversion | 2.36
datacl         | 
-[ RECORD 2 ]--+------------------------------------
oid            | 18770
datname        | cave
datdba         | 18769
encoding       | 6
datlocprovider | c
datistemplate  | f
datallowconn   | t
datconnlimit   | -1
datfrozenxid   | 722
datminmxid     | 1
dattablespace  | 1663
datcollate     | fr_FR.UTF-8
datctype       | fr_FR.UTF-8
daticulocale   | 
daticurules    | 
datcollversion | 2.36
datacl         | 
-[ RECORD 3 ]--+------------------------------------
oid            | 1
datname        | template1
datdba         | 10
encoding       | 6
datlocprovider | c
datistemplate  | t
datallowconn   | t
datconnlimit   | -1
datfrozenxid   | 722
datminmxid     | 1
dattablespace  | 1663
datcollate     | fr_FR.UTF-8
datctype       | fr_FR.UTF-8
daticulocale   | 
daticurules    | 
datcollversion | 2.36
datacl         | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 4 ]--+------------------------------------
oid            | 4
datname        | template0
datdba         | 10
encoding       | 6
datlocprovider | c
datistemplate  | t
datallowconn   | f
datconnlimit   | -1
datfrozenxid   | 722
datminmxid     | 1
dattablespace  | 1663
datcollate     | fr_FR.UTF-8
datctype       | fr_FR.UTF-8
daticulocale   | 
daticurules    | 
datcollversion | 
datacl         | {=c/postgres,postgres=CTc/postgres}
…

Voici la signification des principales colonnes :

• oid : identifiant système de la base ;
• datname : nom de la base ;
• datdba : l’identifiant de l’utilisateur propriétaire de cette base (voir l’OID correspondant à cet identifiant dans le catalogue système pg_roles) ;
• encoding : avec la fonction pg_encoding_to_char(), on peut voir que les valeurs 6 correspondent à UTF8, ce qui est généralement recommandé ;
• datlocprovider : c indique que les collations sont fournies par la biliothèque libc du système, un i indiquerait l’utilisation de la bibliothèque ICU (indépendante du système), et dans ce dernier cas les champs daticulocale et daticurules sont remplies ;
• datistemplate : à true si cette base est utilisable comme modèle ;
• datallowconn : à true s’il est autorisé de se connecter à cette base ;
• datconnlimit : limite du nombre de connexions simultanées pour les utilisateurs standards sur cette base (0 interdit toute connexion, et -1 ne pose pas de limite, jusque max_connections du moins) ;
• datfrozenxid : plus ancien identifiant de transaction géré par cette base (cela a une importance dans le recyclage des numéros de transaction) ;
• dattablespace : identifiant du tablespace par défaut de cette base (1663 indique généralement le tablespace pg_default, de fait le répertoire PGDATA ; les emplacements des tablespaces peuvent se trouver dans la table pg_tablespace ou par la fonction pg_tablespace_location()) ;
• datacl : droits pour cette base (un champ vide indique qu’il n’y a pas de droits spécifiques).

Pour avoir une vue plus simple, il est préférable d’utiliser la métacommande \l dans psql (vue raccourcie pour la mise en page) :

postgres=# \l
                                          List of databases
   Name     |   Owner   | Enc. | Locale P. |   Collate   |…|   Access privileges   
------------+-----------+------+-----------+-------------+-+-----------------------
 cave       | caviste   | UTF8 | libc      | fr_FR.UTF-8 | | 
 pgbench    | pgbench   | UTF8 | libc      | fr_FR.UTF-8 | | 
 postgres   | postgres  | UTF8 | libc      | fr_FR.UTF-8 | | 
 template0  | postgres  | UTF8 | libc      | fr_FR.UTF-8 | | =c/postgres          +
            |           |      |           |             | | postgres=CTc/postgres
 template1  | postgres  | UTF8 | libc      | fr_FR.UTF-8 | | =c/postgres          +
            |           |      |           |             | | postgres=CTc/postgres
 tpc        | tpc_owner | UTF8 | libc      | fr_FR.UTF-8 | | 
…

Avec \l+, il est possible d’avoir plus d’informations (notamment la taille de la base ou le commentaire).

Noter que si l’on veut savoir où psql va chercher ces informations, il est possible de lui demander d’afficher la requête qu’il envoie au serveur :

$ psql --echo-hidden -c '\l+'

********* QUERY **********
SELECT
  d.datname as "Name",
  pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
  CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider",
  d.datcollate as "Collate",
  d.datctype as "Ctype",
  d.daticulocale as "ICU Locale",
  d.daticurules as "ICU Rules",
  pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
       ELSE 'No Access'
  END as "Size",
  t.spcname as "Tablespace",
  pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
**************************
…

(suivent les résultats.)

La requête affichée montre bien que psql accède au catalogue pg_database, ainsi qu’à des fonctions système permettant d’éviter d’avoir à faire soi-même les jointures.

Toute création de base se fait à partir d’un modèle. Par défaut, PostgreSQL utilise le modèle template1.

Tout objet ajouté dans le modèle est copié dans la nouvelle base. Cela concerne le schéma (la structure) comme les données. Il est donc intéressant d’ajouter des objets directement dans template1 pour que ces derniers soient copiés dans les prochaines bases qui seront créées. Pour éviter malgré tout que cette base soit trop modifiée, il est possible de créer des bases qui seront ensuite utilisées comme modèle.

L’ordre CREATE DATABASE est le seul moyen avec PostgreSQL de créer une base de données. Il suffit d’y ajouter le nom de la base à créer pour que la création se fasse. Il est néanmoins possible d’y ajouter un certain nombre d’options :

• OWNER, pour préciser le propriétaire de la base de données (si cette option n’est pas utilisée, le propriétaire est celui qui exécute la commande) ;
• TEMPLATE, pour indiquer le modèle à copier (par défaut template1) ;
• ENCODING, pour forcer un autre encodage que celui du serveur (à noter qu’il faudra utiliser le modèle template0 dans ce cas) ;
• LC_COLLATE et LC_CTYPE, pour préciser respectivement l’ordre de tri des données textes et le jeu de caractères (par défaut, il s’agit de la locale utilisée lors de l’initialisation de l’instance) ;
• STRATEGY (depuis la version 15), pour indiquer la stratégie employée pour créer la base de donnée. Deux choix sont disponibles :
  • FILE_COPY : C’est la méthode historique, seule possible jusqu’en version 14 incluse. Le contenu des répertoires d’une base de données est intégralement copié pour initialiser la nouvelle base, avec juste une trace dans les journaux de transaction. Elle implique deux checkpoints parfois gênants, mais peut être intéressante pour copier de grosses bases en générant moins de journaux ;
  • WAL_LOG : C’est la méthode par défaut à partir de la version 15. L’opération est entièrement journalisée, et la liste des objets à copier et générée via le catalogue de PostgreSQL. Cette méthode évite les checkpoints et sécurise la copie en garantissant que toutes les opérations sont tracées, tout en évitant la copie accidentelle de fichier orphelins de la base modèle vers la base cible. Cette opération peut écrire beaucoup de journaux dans le cas où la base modèle est grosse, mais elle est idéale pour les créations de nouvelles bases presque vides ;
• TABLESPACE, pour stocker la base dans un autre tablespace que le répertoire des données ;
• ALLOW_CONNECTIONS, pour autoriser ou non les connexions à la base ;
• CONNECTION LIMIT, pour limiter le nombre de connexions d’utilisateurs standards simultanées sur cette base (illimité par défaut, tout en respectant le paramètre max_connections) ;
• IS_TEMPLATE, pour configurer ou non le mode template.

La copie se fait par clonage de la base de données modèle sélectionnée. Tous les objets et toutes les données faisant partie du modèle seront copiés sans exception. Par exemple, avant la 9.0, on ajoutait le langage PL/pgSQL dans la base de données template1 pour que toutes les bases créées à partir de template1 disposent directement de ce langage. Ce n’est plus nécessaire à partir de la 9.0 car le langage PL/pgSQL est activé dès la création de l’instance. Mais il est possible d’envisager d’autres usages de ce comportement (par exemple installer une extension ou une surcouche comme PostGIS dans chaque base).

À noter qu’il peut être nécessaire de sélectionner le modèle template0 en cas de sélection d’un autre encodage que celui par défaut (comme la connexion est interdite sur template0, il y a peu de chances que des données textes avec un certain encodage aient été enregistrées dans cette base).

Voici l’exemple le plus simple de création d’une base :

CREATE DATABASE b1 ;

Cet ordre crée la base de données b1. Elle aura toutes les options par défaut. Autre exemple :

CREATE DATABASE b2 OWNER u1;

Cette commande SQL crée la base b2 et s’assure que le propriétaire de cette base soit l’utilisateur u1 (il faut que ce dernier existe au préalable).

Tous les utilisateurs n’ont pas le droit de créer une base de données. L’utilisateur qui exécute la commande SQL doit avoir soit l’attribut SUPERUSER soit l’attribut CREATEDB. S’il utilise un autre modèle que celui par défaut, il doit être propriétaire de ce modèle ou le modèle doit être marqué comme étant un modèle officiel (autrement dit la colonne datistemplate doit être à true).

Voici un exemple complet :

postgres=# CREATE DATABASE b1;

CREATE DATABASE

postgres=# CREATE USER u1;

CREATE ROLE

postgres=# CREATE DATABASE b2 OWNER u1;

CREATE DATABASE

postgres=# CREATE USER u2 CREATEDB;

CREATE ROLE

NB : pour que la connexion qui suit fonctionne, et sans mot de passe, il faut paramétrer pg_hba.conf pour autoriser la connexion de cet utilisateur. Ce sera traité plus bas.

postgres=# \c postgres u2

You are now connected to database "postgres" as user "u2".

postgres=> CREATE DATABASE b3;

CREATE DATABASE

postgres=> CREATE DATABASE b4 TEMPLATE b2;
ERROR:  permission denied to copy database "b2"
postgres=> CREATE DATABASE b4 TEMPLATE b3;

CREATE DATABASE

postgres=> \c postgres postgres

You are now connected to database "postgres" as user "postgres".

postgres=# ALTER DATABASE b2 IS_TEMPLATE=true;

ALTER DATABASE

postgres=# \c postgres u2

You are now connected to database "postgres" as user "u2".

postgres=> CREATE DATABASE b5 TEMPLATE b2;

CREATE DATABASE

postgres=> \c postgres postgres
postgres=# \l

postgres=# \l
                                                 List of databases
  Name    |  Owner   | Enc… |   Collate   |    Ctype    | … |   Access privileges   
----------+----------+------+-------------+-------------+---+---------------------
b1        | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |   | 
b2        | u1       | UTF8 | en_US.UTF-8 | en_US.UTF-8 |   | 
b3        | u2       | UTF8 | en_US.UTF-8 | en_US.UTF-8 |   | 
b4        | u2       | UTF8 | en_US.UTF-8 | en_US.UTF-8 |   | 
b5        | u2       | UTF8 | en_US.UTF-8 | en_US.UTF-8 |   | 
postgres  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |   | 
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |   | =c/postgres          +
          |          |      |             |             |   | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |   | =c/postgres          +
          |          |      |             |             |   | postgres=CTc/postgres

L’outil système createdb se connecte à la base de données postgres et exécute la commande CREATE DATABASE, exactement comme ci-dessus. Appelée sans aucun argument, createdb crée une base de donnée portant le nom de l’utilisateur connecté (si cette dernière n’existe pas). L’option --echo de cette commande permet de voir exactement ce que createdb exécute :

$ createdb --echo --owner u1 b6

SELECT pg_catalog.set_config('search_path', '', false)
CREATE DATABASE b6 OWNER u1;

Avec une configuration judicieuse des traces de PostgreSQL (log_min_duration_statement = 0, log_connections = on, log_disconnections = on), il est possible de voir cela complètement du point de vue du serveur :

[unknown] - LOG:  connection received: host=[local]
[unknown] - LOG:  connection authorized: user=postgres database=postgres
createdb - LOG:  duration: 1.018 ms
                 statement: SELECT pg_catalog.set_config('search_path', ''Z, false)
createdb - CREATE DATABASE b6 OWNER u1;
createdb - LOG:  disconnection: session time: 0:00:00.277 user=postgres
                                                          database=postgres
                                                          host=[local]

Supprimer une base de données supprime tous les objets et toutes les données contenues dans la base. La destruction d’une base de données ne peut pas être annulée.

La suppression se fait uniquement avec l’ordre DROP DATABASE. Seuls les superutilisateurs et le propriétaire d’une base peuvent supprimer cette base. Cependant, pour que cela fonctionne, il faut qu’aucun utilisateur ne soit connecté à cette base. Si quelqu’un est connecté, un message d’erreur apparaîtra :

postgres=# DROP DATABASE b6;

ERROR:  database "b6" is being accessed by other users
DETAIL:  There are 1 other session(s) using the database.

Il faut donc attendre que les utilisateurs se déconnectent, ou leur demander de le faire, voire les déconnecter autoritairement :

postgres=# SELECT pg_terminate_backend(pid)
       FROM pg_stat_activity
       WHERE datname='b6';

 pg_terminate_backend
--------------------
 t

postgres=# DROP DATABASE b6;

DROP DATABASE

Là-aussi, PostgreSQL propose un outil système appelé dropdb pour faciliter la suppression des bases. Cet outil se comporte comme createdb. Il se connecte à la base postgres et exécute l’ordre SQL correspondant à la suppression de la base :

$ dropdb --echo b5

SELECT pg_catalog.set_config('search_path', '', false)
DROP DATABASE b5;

Contrairement à createdb, sans nom de base, dropdb ne fait rien.

À partir de la version 13, il est possible d’utiliser la clause WITH (FORCE) de l’ordre DROP DATABASE ou l’option en ligne de commande --force de l’outil dropdb pour forcer la déconnexion des utilisateurs.

Avec la commande ALTER DATABASE, il est possible de modifier quelques méta-données :

• le nom de la base ;
• son propriétaire ;
• la limite de connexions ;
• le tablespace de la base.

Dans le cas d’un changement de nom ou de tablespace, aucun utilisateur ne doit être connecté à la base pendant l’opération.

Il est aussi possible d’ajouter, de modifier ou de supprimer une configuration spécifique pour une base de données en utilisant la syntaxe suivante :

ALTER DATABASE base SET paramètre TO valeur;

La configuration spécifique de chaque base de données surcharge toute configuration reçue sur la ligne de commande du processus postgres père ou du fichier de configuration postgresql.conf. L’ajout d’une configuration avec ALTER DATABASE sauvegarde le paramétrage mais ne l’applique pas immédiatement. Il n’est appliqué que pour les prochaines connexions. Notez que les utilisateurs peuvent cependant modifier ce réglage pendant la session ; il s’agit seulement d’un réglage par défaut, pas d’un réglage forcé.

Voici un exemple complet :

b1=# SHOW work_mem;

 work_mem
----------
 4MB

b1=# ALTER DATABASE b1 SET work_mem TO '2MB';

ALTER DATABASE

b1=# SHOW work_mem;

 work_mem
----------
 4MB

b1=# \c b1

You are now connected to database "b1" as user "postgres".

b1=# SHOW work_mem;

 work_mem
----------
 2MB

Cette configuration est présente même après un redémarrage du serveur. Elle n’est pas enregistrée dans le fichier de configuration postgresql.conf, mais dans un catalogue système appelé pg_db_role_setting :

b1=# ALTER DATABASE b2 SET work_mem TO '32MB';

ALTER DATABASE

b1=# ALTER USER u1 SET maintenance_work_mem TO '256MB';

ALTER ROLE

b1=# SELECT * FROM pg_db_role_setting;

 setdatabase | setrole |          setconfig
-------------+---------+------------------------------
       16384 |       0 | {work_mem=2MB}
       16386 |       0 | {work_mem=32MB}
           0 |   16385 | {maintenance_work_mem=256MB}

b1=# SELECT d.datname AS "Base", r.rolname AS "Utilisateur",
     setconfig AS "Configuration"
     FROM pg_db_role_setting
     LEFT JOIN pg_database d ON d.oid=setdatabase
     LEFT JOIN pg_roles r ON r.oid=setrole
     ORDER BY 1, 2;

 Base | Utilisateur |        Configuration
------+-------------+------------------------------
 b1   |             | {work_mem=2MB}
 b2   |             | {work_mem=32MB}
      | u1          | {maintenance_work_mem=256MB}

b1=# ALTER DATABASE b3 SET work_mem to '10MB';

ALTER DATABASE

b1=# ALTER DATABASE b3 SET maintenance_work_mem to '128MB';

ALTER DATABASE

b1=# ALTER DATABASE b3 SET random_page_cost to 3;

ALTER DATABASE

b1=# SELECT d.datname AS "Base", r.rolname AS "Utilisateur",
     setconfig AS "Configuration"
     FROM pg_db_role_setting
     LEFT JOIN pg_database d ON d.oid=setdatabase
     LEFT JOIN pg_roles r ON r.oid=setrole
     ORDER BY 1, 2;

 Base | Utilisateur |                         Configuration
------+-------------+---------------------------------------------------------------
 b1   |             | {work_mem=2MB}
 b2   |             | {work_mem=32MB}
 b3   |             | {work_mem=10MB,maintenance_work_mem=128MB,random_page_cost=3}
      | u1          | {maintenance_work_mem=256MB}

Pour annuler la configuration d’un paramètre, utilisez :

ALTER DATABASE base RESET paramètre;

Par exemple :

b1=# ALTER DATABASE b3 RESET random_page_cost;

ALTER DATABASE

b1=# SELECT d.datname AS "Base", r.rolname AS "Utilisateur",
     setconfig AS "Configuration"
     FROM pg_db_role_setting
     LEFT JOIN pg_database d ON d.oid=setdatabase
     LEFT JOIN pg_roles r ON r.oid=setrole
     ORDER BY 1, 2;

 Base | Utilisateur |               Configuration
------+-------------+--------------------------------------------
 b1   |             | {work_mem=2MB}
 b2   |             | {work_mem=32MB}
 b3   |             | {work_mem=10MB,maintenance_work_mem=128MB}
      | u1          | {maintenance_work_mem=256MB}

Si vous copiez avec CREATE DATABASE … TEMPLATE une base dont certains paramètres ont été configurés spécifiquement pour elle, ces paramètres ne sont pas appliqués à la nouvelle base de données.

Un rôle peut être vu soit comme un utilisateur de la base de données, soit comme un groupe d’utilisateurs de la base de données, suivant la façon dont le rôle est conçu et configuré. Les rôles peuvent être propriétaires d’objets de la base de données (par exemple des tables) et peuvent affecter des droits sur ces objets à d’autres rôles pour contrôler l’accès à ces objets. De plus, il est possible de donner l’appartenance d’un rôle à un autre rôle, l’autorisant ainsi à utiliser les droits affectés au rôle dont il est membre.

Nous allons voir dans cette partie comment gérer les rôles, en allant de leur création à leur suppression, en passant par leur configuration.

Les rôles sont disponibles depuis la version 8.1. Auparavant, PostgreSQL avait la notion d’utilisateur et de groupe. Pour conserver la compatibilité avec les anciennes applications, les ordres SQL pour les utilisateurs et les groupes ont été conservés. Il est donc toujours possible de les utiliser mais il est actuellement conseillé de passer par les ordres SQL pour les rôles.

La liste des rôles est disponible grâce à un catalogue système appelé pg_roles. Il suffit d’un SELECT pour récupérer les méta-données sur chaque rôle :

postgres=# \x

Expanded display is on.

postgres=# SELECT * FROM pg_roles LIMIT 3;

-[ RECORD 1 ]--+---------------------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | t
rolconfig      |
oid            | 10
-[ RECORD 2 ]--+---------------------
rolname        | pg_monitor
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | f
rolreplication | f
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | f
rolconfig      |
oid            | 3373
-[ RECORD 3 ]--+---------------------
rolname        | pg_read_all_settings
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | f
rolreplication | f
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | f
rolconfig      |
oid            | 3374

Voici la signification des différentes colonnes :

• rolname, le nom du rôle ;
• rolsuper, le rôle a-t-il l’attribut SUPERUSER ? ;
• rolinherit, le rôle hérite-t-il automatiquement des droits des rôles dont il est membre ? ;
• rolcreaterole, le rôle a-t-il le droit de créer des rôles ? ;
• rolcreatedb, le rôle a-t-il le droit de créer des bases ? ;
• rolcanlogin, le rôle a-t-il le droit de se connecter ? ;
• rolreplication, le rôle peut-il être utilisé dans une connexion de réplication ? ;
• rolconnlimit, limite du nombre de connexions simultanées pour ce rôle (0 indiquant « pas de connexions possibles », -1 permet d’indiquer qu’il n’y a pas de limite en dehors de la valeur du paramètre max_connections) ;
• rolpassword, mot de passe du rôle (non affiché) ;
• rolvaliduntil, date limite de validité du mot de passe ;
• rolbypassrls, le rôle court-circuite-t-il les droits sur les lignes ? ;
• rolconfig, configuration spécifique du rôle ;
• oid, identifiant système du rôle.

Pour avoir une vue plus simple, il est préférable d’utiliser la métacommande \du dans psql :

postgres=# \du

List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}
-[ RECORD 2 ]----------------------------------------------------------
Role name  | u1
Attributes |
Member of  | {}
-[ RECORD 3 ]----------------------------------------------------------
Role name  | u2
Attributes | Create DB
Member of  | {}

Il est à noter que les rôles systèmes ne sont pas affichés. Les rôles systèmes sont tous ceux commençant par pg_.

La métacommande \du ne fait qu’accéder aux tables systèmes. Par exemple :

$ psql -E postgres

psql (13.0)
Type "help" for help.

postgres=# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************

List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}
[...]

La requête affichée montre bien que psql accède aux catalogues pg_roles et pg_auth_members.

L’ordre CREATE ROLE est le seul moyen avec PostgreSQL de créer un rôle. Il suffit d’y ajouter le nom du rôle à créer pour que la création se fasse. Il est néanmoins possible d’y ajouter un certain nombre d’options :

• SUPERUSER, pour que le nouveau rôle soit superutilisateur (autrement dit, ce rôle a le droit de tout faire une fois connecté à une base de données) ;
• CREATEDB, pour que le nouveau rôle ait le droit de créer des bases de données ;
• CREATEROLE, pour que le nouveau rôle ait le droit de créer un rôle ;
• INHERIT, pour que le nouveau rôle hérite automatiquement des droits des rôles dont il est membre ;
• LOGIN, pour que le nouveau rôle ait le droit de se connecter ;
• REPLICATION, pour que le nouveau rôle puisse se connecter en mode réplication ;
• BYPASSRLS, pour que le nouveau rôle puisse ne pas être vérifié pour les sécurités au niveau ligne ;
• CONNECTION LIMIT, pour limiter le nombre de connexions simultanées pour ce rôle ;
• PASSWORD, pour préciser le mot de passe de ce rôle ;
• VALID UNTIL, pour indiquer la date limite de validité du mot de passe ;
• IN ROLE, pour indiquer à quel rôle ce rôle appartient ;
• IN GROUP, pour indiquer à quel groupe ce rôle appartient ;
• ROLE, pour indiquer les membres de ce rôle ;
• ADMIN, pour indiquer les membres de ce rôles (les nouveaux membres ayant en plus la possibilité d’ajouter d’autres membres à ce rôle) ;
• USER, pour indiquer les membres de ce rôle ;
• SYSID, pour préciser l’identifiant système, mais est ignoré.

Par défaut, un rôle n’a aucun attribut (ni superutilisateur, ni le droit de créer des rôles ou des bases, ni la possibilité de se connecter en mode réplication, ni la possibilité de se connecter).

Voici quelques exemples simples :

postgres=# CREATE ROLE u3;

CREATE ROLE

postgres=# CREATE ROLE u4 CREATEROLE;

CREATE ROLE

postgres=# CREATE ROLE u5 LOGIN IN ROLE u2;

CREATE ROLE

postgres=# CREATE ROLE u6 ROLE u5;

CREATE ROLE

postgres=# \du

List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}
-[ RECORD 2 ]----------------------------------------------------------
Role name  | u1
Attributes |
Member of  | {}
-[ RECORD 3 ]----------------------------------------------------------
Role name  | u2
Attributes | Create DB
Member of  | {}
-[ RECORD 4 ]----------------------------------------------------------
Role name  | u3
Attributes | Cannot login
Member of  | {}
-[ RECORD 5 ]----------------------------------------------------------
Role name  | u4
Attributes | Create role, Cannot login
Member of  | {}
-[ RECORD 6 ]----------------------------------------------------------
Role name  | u5
Attributes |
Member of  | {u2,u6}
-[ RECORD 7 ]----------------------------------------------------------
Role name  | u6
Attributes | Cannot login
Member of  | {}

Tous les rôles n’ont pas le droit de créer un rôle. Le rôle qui exécute la commande SQL doit avoir soit l’attribut SUPERUSER soit l’attribut CREATEROLE. Un utilisateur qui a l’attribut CREATEROLE pourra créer tout type de rôles sauf des superutilisateurs.

Voici un exemple complet :

postgres=# CREATE ROLE u7 LOGIN CREATEROLE;

CREATE ROLE

postgres=# \c postgres u7

You are now connected to database "postgres" as user "u7".

postgres=> CREATE ROLE u8 LOGIN;

CREATE ROLE

postgres=> CREATE ROLE u9 LOGIN CREATEDB;

CREATE ROLE

postgres=> CREATE ROLE u10 LOGIN SUPERUSER;

ERROR:  must be superuser to create superusers

postgres=> \du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u1        |                                                            | {}
 u2        | Create DB                                                  | {}
 u3        | Cannot login                                               | {}
 u4        | Create role, Cannot login                                  | {}
 u5        |                                                            | {u2,u6}
 u6        | Cannot login                                               | {}
 u7        | Create role                                                | {}
 u8        |                                                            | {}
 u9        | Create DB                                                  | {}

Il est toujours possible d’utiliser les ordres SQL CREATE USER et CREATE GROUP. PostgreSQL les comprend comme étant l’ordre CREATE ROLE. Dans le premier cas (CREATE USER), il ajoute automatiquement l’option LOGIN.

Il est possible de créer un utilisateur (dans le sens, rôle avec l’attribut LOGIN) sans avoir à se rappeler de la commande SQL. Le plus simple est certainement l’outil createuser, livré avec PostgreSQL, mais c’est aussi possible avec n’importe quel autre outil d’administration de bases de données PostgreSQL.

L’outil système createuser se connecte à la base de données postgres et exécute la commande CREATE ROLE, exactement comme ci-dessus, avec par défaut l’option LOGIN. L’option --echo de cette commande nous permet de voir exactement ce que createuser exécute :

$ createuser --echo u10 --superuser

SELECT pg_catalog.set_config('search_path', '', false)
CREATE ROLE u10 SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

Il est à noter que createuser est un programme interactif. Avant la version 9.2, si le nom du rôle n’est pas indiqué, l’outil demandera le nom du rôle à créer. De même, si au moins un attribut n’est pas explicitement indiqué, il demandera les attributs à associer à ce rôle :

$ createuser u11

Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

Depuis la version 9.2, il crée un utilisateur avec les valeurs par défaut (équivalent à une réponse n à toutes les questions). Pour retrouver le mode interactif, il faut utiliser l’option --interactive.

La suppression d’un rôle se fait uniquement avec l’ordre DROP ROLE. Seuls les utilisateurs disposant des attributs SUPERUSER et CREATEROLE peuvent supprimer des rôles. Cependant, pour que cela fonctionne, il faut que le rôle à supprimer ne soit pas propriétaire d’objets dans l’instance. S’il est propriétaire, un message d’erreur apparaîtra :

postgres=> DROP ROLE u1;

ERROR:  role "u1" cannot be dropped because some objects depend on it
DETAIL:  owner of database b2

Il faut donc changer le propriétaire des objets en question ou supprimer les objets. Vous pouvez utiliser respectivement les ordres REASSIGN OWNED et DROP OWNED pour cela.

Un rôle qui n’a pas l’attribut SUPERUSER ne peut pas supprimer un rôle qui a cet attribut :

postgres=> DROP ROLE u10;

ERROR:  must be superuser to drop superusers

Par contre, il est possible de supprimer un rôle qui est connecté. Le rôle connecté aura des possibilités limitées après sa suppression. Par exemple, il peut toujours lire quelques tables systèmes mais il ne peut plus créer d’objets.

Là-aussi, PostgreSQL propose un outil système appelé dropuser pour faciliter la suppression des rôles. Cet outil se comporte comme createrole : il se connecte à la base PostgreSQL et exécute l’ordre SQL correspondant à la suppression du rôle :

$ dropuser --echo u10

SELECT pg_catalog.set_config('search_path', '', false)
DROP ROLE u10;

Sans spécifier le nom de rôle sur la ligne de commande, dropuser demande le nom du rôle à supprimer.

Avec la commande ALTER ROLE, il est possible de modifier quelques méta-données du rôle :

• son nom ;
• son mot de passe ;
• sa limite de validité ;
• ses attributs :
  • SUPERUSER ;
  • CREATEDB ;
  • CREATEROLE ;
  • CREATEUSER ;
  • INHERIT ;
  • LOGIN ;
  • REPLICATION ;
  • BYPASSRLS.

Toutes ces opérations peuvent s’effectuer alors que le rôle est connecté à la base.

Il est aussi possible d’ajouter, de modifier ou de supprimer une configuration spécifique pour un rôle en utilisant la syntaxe suivante :

ALTER ROLE rôle SET paramètre TO valeur;

La configuration spécifique de chaque rôle surcharge toute configuration reçue sur la ligne de commande du processus postgres père ou du fichier de configuration postgresql.conf, mais aussi la configuration spécifique de la base de données où le rôle est connecté. L’ajout d’une configuration avec ALTER ROLE sauvegarde le paramétrage mais ne l’applique pas immédiatement. Il n’est appliqué que pour les prochaines connexions. Notez que les rôles peuvent cependant modifier ce réglage pendant la session ; il s’agit seulement d’un réglage par défaut, pas d’un réglage forcé.

Voici un exemple complet :

$ psql -U u2 postgres

psql (13.0)
Type "help" for help.

postgres=> SHOW work_mem;

 work_mem
----------
 4MB

postgres=> ALTER ROLE u2 SET work_mem TO '20MB';

ALTER ROLE

postgres=> SHOW work_mem;

 work_mem
----------
 4MB

postgres=> \c - u2

You are now connected to database "postgres" as user "u2".

postgres=> SHOW work_mem;

 work_mem
----------
 20MB

Cette configuration est présente même après un redémarrage du serveur. Elle n’est pas enregistrée dans le fichier de configuration postgresql.conf mais dans un catalogue système appelé pg_db_role_setting :

b1=# SELECT d.datname AS "Base", r.rolname AS "Utilisateur",
     setconfig AS "Configuration"
     FROM pg_db_role_setting
     LEFT JOIN pg_database d ON d.oid=setdatabase
     LEFT JOIN pg_roles r ON r.oid=setrole
     ORDER BY 1, 2;

 Base | Utilisateur |               Configuration
------+-------------+--------------------------------------------
 b1   |             | {work_mem=2MB}
 b2   |             | {work_mem=32MB}
 b3   |             | {work_mem=10MB,maintenance_work_mem=128MB}
      | u1          | {maintenance_work_mem=256MB}
      | u2          | {work_mem=20MB}

Il est aussi possible de configurer un paramétrage spécifique pour un utilisateur et une base donnés :

postgres=# ALTER ROLE u2 IN DATABASE b1 SET work_mem to '10MB';

ALTER ROLE

postgres=# \c postgres u2

You are now connected to database "postgres" as user "u2".

postgres=> SHOW work_mem;

 work_mem
----------
 20MB

postgres=> \c b1 u2

You are now connected to database "b1" as user "u2".

b1=> SHOW work_mem;

 work_mem
----------
 10MB

b1=> \c b1 u1

You are now connected to database "b1" as user "u1".

b1=> SHOW work_mem;

 work_mem
----------
 2MB

b1=> \c postgres u1

You are now connected to database "postgres" as user "u1".

postgres=> SHOW work_mem;

 work_mem
----------
 4MB

b1=# SELECT d.datname AS "Base", r.rolname AS "Utilisateur",
     setconfig AS "Configuration"
     FROM pg_db_role_setting
     LEFT JOIN pg_database d ON d.oid=setdatabase
     LEFT JOIN pg_roles r ON r.oid=setrole
     ORDER BY 1, 2;

 Base | Utilisateur |               Configuration
------+-------------+--------------------------------------------
 b1   | u2          | {work_mem=10MB}
 b1   |             | {work_mem=2MB}
 b2   |             | {work_mem=32MB}
 b3   |             | {work_mem=10MB,maintenance_work_mem=128MB}
      | u1          | {maintenance_work_mem=256MB}
      | u2          | {work_mem=20MB}

Pour annuler la configuration d’un paramètre pour un rôle, utilisez :

ALTER ROLE rôle RESET paramètre;

Après sa création, il est toujours possible d’ajouter et de supprimer un rôle dans un autre rôle. Pour cela, il est possible d’utiliser les ordres GRANT et REVOKE :

GRANT role_groupe TO role_utilisateur;

Il est aussi possible de passer par la commande ALTER GROUP de cette façon :

ALTER GROUP role_groupe ADD USER role_utilisateur;

Certaines méthodes d’authentification n’ont pas besoin de mot de passe (comme peer pour les connexions depuis le serveur même), ou délèguent l’authentification à un système extérieur (comme ldap). Par défaut, les utilisateurs n’ont pas de mot de passe. Si la méthode en exige un, ils ne pourront pas se connecter.

On peut le créer ainsi :

ALTER ROLE u1 PASSWORD 'supersecret';

À partir de là, avec une méthode d’authentification bien configurée, le mot de passe sera demandé. Il faudra, dans cet exemple, saisir « supersecret » pour que la connexion se fasse.

$ grep PASSWORD $PGDATA/log/traces.log

psql - LOG: duration: 1.865 ms
       statement: ALTER ROLE u1 PASSWORD 'supersecret';

Il est donc essentiel de s’arranger pour que seules des personnes de confiance aient accès aux traces et vues systèmes. Il est certes possible de demander temporairement la désactivation des traces pendant le changement de mot de passe (si l’on est superutilisateur) :

SET log_min_duration_statement TO -1;
SET log_statement TO none;
ALTER ROLE u1 PASSWORD 'supersecret';

$ grep PASSWORD $PGDATA/log/postgresql-Mon.log
[rien]

Cependant, cela ne règle pas le cas de pg_stat_statements et pg_stat_activity.

De manière générale, il est donc chaudement conseillé de ne renseigner que des mots de passe chiffrés. C’est très simple en mode interactif avec psql, la métacommande \password opère le chiffrement :

\password u1

Saisissez le nouveau mot de passe :
Saisissez-le à nouveau :

L’ordre effectivement envoyé au serveur et éventuellement visible dans les traces sera :

ALTER USER u1 PASSWORD 'SCRAM-SHA-256$4096:KcHoLSZE…Hj81r3w='

De même si on crée le rôle depuis le shell avec createuser :

createuser --login --echo --pwprompt u1

Saisir le mot de passe pour le nouveau rôle :
Le saisir de nouveau :
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE u1 PASSWORD 'SCRAM-SHA-256$4096:/LVaGESxmDwyF92urT…hS0kOxIpwWAbTpW1i9peGIfg='
NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOREPLICATION NOBYPASSRLS;

Le mot de passe est chiffré en interne, et visible dans les sauvegardes avec pg_dumpall -g, ou dans la vue système pg_authid.

Il existe deux méthodes de chiffrement : SCRAM-SHA-256 (fortement conseillée) et l’historique MD5. Elles sont éventuellement utilisables simultanément pour des utilisateurs différents.

L’exemple suivant montre que la méthode de chiffrement peut différer selon les rôles, en fonction de la valeur du paramètre password_encryption au moment de la mise en place du mot de passe :

SET password_encryption TO "scram-sha-256";

CREATE ROLE u12 LOGIN PASSWORD 'supersecret';

SELECT * FROM pg_authid  WHERE rolname IN ('u1', 'u12') ORDER BY 1;

-[ RECORD 1 ]--+-----------------------------------------------------------
rolname        | u1
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolbypassrls   | f
rolconnlimit   | -1
rolpassword    | md5fb75f17111cea61e62b54ab950dd1268
rolvaliduntil  |
-[ RECORD 2 ]--+-----------------------------------------------------------
rolname        | u12
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolbypassrls   | f
rolconnlimit   | -1
rolpassword    | SCRAM-SHA-256$4096:0/uC6oDNuQWO8H9pMaVg8g==$nDUpGSefHOZMd
                 TcbWR13NPELJubGg7PduiJjX/Hyt/M=:PSUzE+rP5g4f6mb5sFDRq/Hds
                 OrLvfYew9ZIdzO/GDw=
rolvaliduntil  |

Chiffrement SCRAM-SHA-256 :

SCRAM-SHA-256 n’est la méthode par défaut que depuis PostgreSQL 14, bien que disponible depuis PostgreSQL 10. Elle est plus complexe et plus sûre que l’ancienne méthode MD5. Notamment, le même mot de passe entré plusieurs fois pour un même utilisateur, même sur la même instance, donnera des versions chiffrées à chaque fois différentes, mais interchangeables.

Un chiffrement SCRAM-SHA-256 est de la forme :

SCRAM-SHA-256$<sel>:<nombre d'itérations>$<hash>

Pour quelques détails d’implémentation et une comparaison avec MD5, voir par exemple cette présentation de Jonathan Katz.

Générer soi-même des mots de passe chiffrés en SCRAM-SHA-256 en-dehors de psql est plus compliqué qu’avec MD5, et les outils comme \password s’appuient souvent sur les fonctions fournies par la bibliothèque libpq. Cette dernière sert aussi de base à la bibliothèque python psycopg3 et sa fonction PGconn.encrypt_password(). Indépendamment de la libpq, il existe aussi un script python de Jonathan Katz (version 3.6 minimum).

Depuis la version 16 il est possible d’ajuster le nombre d’itérations avec le paramètre scram_iterations. Le défaut de 4096 est un compromis. Monter plus haut permet de lutter contre les attaques par force brute. Réduire ce paramètre permet de parer aux problèmes de performances depuis certains terminaux peu puissants, ou en cas de connexions très fréquentes (le début de la discussion entre les développeurs illustre bien ces deux contraintes).

Chiffrement MD5 :

Le chiffrement md5 est celui par défaut jusque PostgreSQL 13 inclus. Il consiste à calculer la somme MD5 du mot de passe concaténé au nom du rôle (pour que deux utilisateurs de même mot de passe n’ait pas la même version chiffrée) ; puis « md5 » est ajouté devant. De manière plus générale, l’algorithme MD5 est considéré comme trop faible de nos jours.

Un autre problème de sécurité est que la version chiffrée d’un mot de passe est identique sur deux instances différentes pour un même nom d’utilisateur, ce qui ouvre la possibilité d’attaques par rainbow tables.

Un inconvénient plus mineur du chiffrement MD5 est qu’il utilise le nom de l’utilisateur : en cas de changement de ce nom, il faudra de nouveau rentrer le mot de passe pour que son stockage chiffré soit correct.

Pour éviter de fournir un mot de passe en clair à PostgreSQL, il est facile de le chiffrer en MD5 avant de le fournir à PostgreSQL :

$ echo -n "supersecretu1" | md5sum
fb75f17111cea61e62b54ab950dd1268  -

ALTER ROLE u1 PASSWORD 'md5fb75f17111cea61e62b54ab950dd1268';

Dans les traces on ne trouvera que la version chiffrée :

psql - LOG:  duration: 2.100 ms  statement: ALTER ROLE u1
                                 PASSWORD 'md5fb75f17111cea61e62b54ab950dd1268';

Ceci fait la même chose sous forme de script :

set +o history    # suspend l'historique du shell
MDP=supersecret
U=u1
psql -X --echo-all -c \
"$(echo ALTER ROLE ${U} PASSWORD \'md5$(echo -n ${MDP}${U}|md5sum|cut -f1 -d' '))';"

…
ALTER ROLE u1 PASSWORD 'md5fb75f17111cea61e62b54ab950dd1268';
…

Cohabitation de mots de passe SCRAM-SHA-256 et MD5 :

Lors de la mise à jour d’une ancienne instance, il n’est pas forcément possible de ré-entrer immédiatement tous les mots de passe existants chiffrés en MD5. De plus, certains outils clients un peu anciens pourraient ne pas supporter SCRAM-SHA-256 (voir le wiki). Changer la méthode d’authentification selon l’utilisateur est donc utile.

La méthode d’authentification SCRAM-SHA-256 doit rester le défaut, et tout mot de passe réentré sera chiffré en SCRAM-SHA-256 :

password_encryption = "scram-sha-256"

Si le mot de passe est stocké au format SCRAM-SHA-256, une authentification paramétrée sur md5 ou password dans le fichier pg_hba.conf fonctionnera aussi. Cela facilite la migration progressive des utilisateurs de md5 à scram-sha-256, qui peuvent réentrer leur mot de passe quand ils veulent.

Par contre, indiquer la méthode scram-sha-256 dans pg_hba.conf impose un chiffrage en SCRAM-SHA-256 et interdit d’utiliser MD5.

On peut mixer les deux méthodes dans pg_hba.conf si le besoin se fait sentir, par exemple pour n’utiliser md5 que pour une seule application (avec un compte dédié) avec un ancien client :

# vieille application (avant le cas général)
host   compta  mathusalem   192.168.66.66/32    md5
# authentification habituelle
host   all     all          192.168.66.0/24     scram-sha-256

Dates de validité des mots de passe :

Les mots de passe ont une date de validité mais pas les rôles eux-mêmes. Par exemple, il reste possible de se connecter en local par la méthode peer même si le mot de passe a expiré.

Sécurité des mots de passe :

Enfin, il est à noter que PostgreSQL ne vérifie pas la faiblesse d’un mot de passe. Il est certes possible d’installer une extension appelée passwordcheck (voir sa documentation).

postgres=# ALTER ROLE u1 PASSWORD 'supersecret';

ERROR:  password must contain both letters and nonletters

Il est possible de modifier le code source de cette extension pour y ajouter les règles convenant à votre cas, ou d’utiliser la bibliothèque Cracklib. Des extensions de ce genre, extérieures au projet, existent. Cependant, ces outils exigent que le mot de passe soit fourni en clair, et donc sujet à une fuite (dans les traces par exemple), ce qui, répétons-le, est fortement déconseillé !

Un rôle peut tenter de se connecter autant de fois qu’il le souhaite, ce qui expose à des attaques de type force brute. Il est possible d’interdire toute connexion à partir d’un certain nombre de connexions échouées si vous utilisez une méthode d’authentification externe qui le gère (comme PAM, LDAP ou Active Directory). Vous pouvez aussi obtenir cette