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 fonctionnalité en utilisant un outil comme fail2ban. Sa configuration est détaillée dans notre base de connaissances.

Pour bien comprendre l’intérêt des utilisateurs, il faut bien comprendre la gestion des droits. Les droits sur les objets vont permettre aux utilisateurs de créer des objets ou de les utiliser. Les commandes GRANT et REVOKE sont essentielles pour cela. Modifier la définition d’un objet demande un autre type de droit, que les commandes précédentes ne permettent pas d’obtenir.

Donner des droits à chaque utilisateur peut paraître long et difficile. C’est pour cela qu’il est généralement préférable de donner des droits à une entité spécifique dont certains utilisateurs hériteront.

Par défaut, seul le propriétaire a des droits sur son objet. Les superutilisateurs n’ont pas de droit spécifique sur les objets mais étant donné leur statut de superutilisateur, ils peuvent tout faire sur tous les objets.

Le propriétaire d’un objet peut décider de donner certains droits sur cet objet à certains rôles. Il le fera avec la commande GRANT :

GRANT droits ON type_objet nom_objet TO role

Les droits disponibles dépendent du type d’objet visé. Par exemple, il est possible de donner le droit SELECT sur une table mais pas sur une fonction. Une fonction ne se lit pas, elle s’exécute. Il est donc possible de donner le droit EXECUTE sur une fonction.

La liste complète des droits figure dans la documentation officielle.

Il faut donner les droits aux différents objets séparément. De plus, donner le droit ALL sur une base de données donne tous les droits sur la base de données, autrement dit l’objet base de donnée, pas sur les objets à l’intérieur de la base de données. GRANT n’est pas une commande récursive. Prenons un exemple :

b1=# CREATE ROLE u20 LOGIN;

CREATE ROLE

b1=# CREATE ROLE u21 LOGIN;

CREATE ROLE

b1=# \c b1 u20

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

b1=> CREATE SCHEMA s1;

ERROR:  permission denied for database b1

b1=> \c b1 postgres

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

b1=# GRANT CREATE ON DATABASE b1 TO u20;

GRANT

b1=# \c b1 u20

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

b1=> CREATE SCHEMA s1;

CREATE SCHEMA

b1=> CREATE TABLE s1.t1 (c1 integer);

CREATE TABLE

b1=> INSERT INTO s1.t1 VALUES (1), (2);

INSERT 0 2

b1=> SELECT * FROM s1.t1;

 c1
----
  1
  2

b1=> \c b1 u21

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

b1=> SELECT * FROM s1.t1;

ERROR:  permission denied for schema s1
LINE 1: SELECT * FROM s1.t1;
                      ^

b1=> \c b1 u20

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

b1=> GRANT SELECT ON TABLE s1.t1 TO u21;

GRANT

b1=> \c b1 u21

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

b1=> SELECT * FROM s1.t1;

ERROR:  permission denied for schema s1
LINE 1: SELECT * FROM s1.t1;
                      ^

b1=> \c b1 u20

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

b1=> GRANT USAGE ON SCHEMA s1 TO u21;

GRANT

b1=> \c b1 u21

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

b1=> SELECT * FROM s1.t1;

 c1
----
  1
  2

b1=> INSERT INTO s1.t1 VALUES (3);

ERROR:  permission denied for relation t1

Le problème de ce fonctionnement est qu’il faut indiquer les droits pour chaque utilisateur, ce qui peut devenir difficile et long. Imaginez avoir à donner le droit SELECT sur les 400 tables d’un schéma… Il est néanmoins possible de donner les droits sur tous les objets d’un certain type dans un schéma. Voici un exemple :

GRANT SELECT ON ALL TABLES IN SCHEMA s1 to u21;

Notez aussi que, lors de la création d’une base, PostgreSQL ajoute automatiquement un schéma nommé public. Avant la version 15, tous les droits sont donnés sur ce schéma à un pseudo-rôle, lui aussi appelé public, et dont tous les rôles existants et à venir sont membres d’office. À partir de la version 15, le schéma public appartient au propriétaire de la base et aucun droit par défaut n’est donné aux autres utilisateurs.

Dans une logique de sécurisation, avant la version 15, il faut donc penser à enlever les droits à public. Une fausse bonne idée est de tout simplement supprimer le schéma public, ou de le récréer (par défaut, sans droits pour le groupe public). Cependant, une sauvegarde logique serait restaurée dans une base qui, par défaut, aurait à nouveau un schéma public ouvert à tous. Une révocation explicite des droits se retrouvera par contre dans une sauvegarde :

REVOKE ALL ON SCHEMA public FROM public ;

(Noter la subtilité de syntaxe : GRANT... TO... et REVOKE... FROM...)

Cette modification peut être faite aussi dans la base template1 (qui sert de modèle à toute nouvelle base), sur toute nouvelle instance.

Enfin il est possible d’ajouter des droits pour des objets qui n’ont pas encore été créés. En fait, la commande ALTER DEFAULT PRIVILEGES permet de donner des droits par défaut à certains rôles. De cette façon, sur un schéma qui a tendance à changer fréquemment, il n’est plus nécessaire de se préoccuper des droits sur les objets.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO public ;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT ON TABLES TO utilisateur ;

Lorsqu’un droit est donné à un rôle, par défaut, ce rôle ne peut pas le donner à un autre. Pour lui donner en plus le droit de donner ce droit à un autre rôle, il faut utiliser la clause WITH GRANT OPTION comme le montre cet exemple :

b1=# CREATE TABLE t2 (id integer);

CREATE TABLE

b1=# INSERT INTO t2 VALUES (1);

INSERT 0 1

b1=# SELECT * FROM t2;

 id
----
  1

b1=# \c b1 u1

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

b1=> SELECT * FROM t2;

ERROR:  permission denied for relation t2

b1=> \c b1 postgres

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

b1=# GRANT SELECT ON TABLE t2 TO u1;

GRANT

b1=# \c b1 u1

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

b1=> SELECT * FROM t2;

 id
----
  1

b1=> \c b1 u2

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

b1=> SELECT * FROM t2;

ERROR:  permission denied for relation t2

b1=> \c b1 u1

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

b1=> GRANT SELECT ON TABLE t2 TO u2;

WARNING:  no privileges were granted for "t2"
GRANT

b1=> \c b1 postgres

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

b1=# GRANT SELECT ON TABLE t2 TO u1 WITH GRANT OPTION;

GRANT

b1=# \c b1 u1

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

b1=> GRANT SELECT ON TABLE t2 TO u2;

GRANT

b1=> \c b1 u2

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

b1=> SELECT * FROM t2;

 id
----
  1

Les colonnes *acl des catalogues systèmes indiquent les droits sur un objet. Leur contenu est un codage au format aclitem indiquant le rôle concerné, ses droits, et qui lui a fourni ces droits (ou le propriétaire de l’objet, si celui qui a fourni les droits est un superutilisateur).

Les droits sont codés avec des lettres. Les voici avec leur signification :

• r pour la lecture (SELECT) ;
• w pour les modifications (UPDATE) ;
• a pour les insertions (INSERT) ;
• d pour les suppressions (DELETE) ;
• D pour la troncation (TRUNCATE) ;
• x pour l’ajout de clés étrangères ;
• t pour l’ajout de triggers ;
• X pour l’exécution de routines ;
• U pour l’utilisation (d’un schéma par exemple) ;
• C pour la création d’objets permanents (tables ou vues par exemple) ;
• c pour la connexion (spécifique aux bases de données) ;
• T pour la création d’objets temporaires (tables ou index temporaires) ;
• s pour la modification d’un paramètre superutilisateur avec SET ;
• A pour la modification d’un paramètre avec ALTER SYSTEM.

Les droits sur les objets ne concernent pas le changement des méta-données et de la structure de l’objet. Seul le propriétaire (et les superutilisateurs) peut le faire. S’il est nécessaire que plusieurs personnes puissent utiliser la commande ALTER sur l’objet, il faut que ces différentes personnes aient un rôle qui soit membre du rôle propriétaire de l’objet. Prenons un exemple :

b1=# \c b1 u21

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

b1=> ALTER TABLE s1.t1 ADD COLUMN c2 text;

ERROR:  must be owner of relation t1

b1=> \c b1 u20

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

b1=> GRANT u20 TO u21;

GRANT ROLE

b1=> \du

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

b1=> \c b1 u21

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

b1=> ALTER TABLE s1.t1 ADD COLUMN c2 text;

ALTER TABLE

Pour assigner un propriétaire différent aux objets ayant un certain propriétaire, il est possible de faire appel à l’ordre REASSIGN OWNED. De même, il est possible de supprimer tous les objets appartenant à un utilisateur avec l’ordre DROP OWNED. Voici un exemple de ces deux commandes :

b1=# \d

       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | t1   | table | u2
 public | t2   | table | u21

b1=# REASSIGN OWNED BY u21 TO u1;

REASSIGN OWNED

b1=# \d

       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | t1   | table | u2
 public | t2   | table | u1

b1=# DROP OWNED BY u1;

DROP OWNED

b1=# \d

       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | t1   | table | u2

Certaines fonctionnalités nécessitent l’attribut SUPERUSER alors qu’il serait bon de pouvoir les effectuer sans avoir ce droit suprême. Cela concerne principalement l’administration, la sauvegarde et la supervision.

Après beaucoup de discussions, les développeurs de PostgreSQL ont décidé de créer des rôles systèmes permettant d’avoir plus de droits. Le premier rôle de ce type est pg_signal_backend qui donne le droit d’exécuter les fonctions pg_cancel_backend() et pg_terminate_backend(), même en simple utilisateur sur des requêtes autres que les siennes :

postgres=# \c - u1

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

postgres=> SELECT usename, pid FROM pg_stat_activity WHERE usename IS NOT NULL;

 usename |  pid
---------+-------
 u2      | 23194
 u1      | 23195

postgres=> SELECT pg_terminate_backend(23194);

ERROR:  must be a member of the role whose process is being terminated
        or member of pg_signal_backend

postgres=> \c - postgres

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

postgres=# GRANT pg_signal_backend TO u1;

GRANT ROLE

postgres=# \c - u1

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

postgres=> SELECT pg_terminate_backend(23194);

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

postgres=> SELECT usename, pid FROM pg_stat_activity WHERE usename IS NOT NULL;

 usename |  pid
---------+-------
 u1      | 23212

Par contre, les connexions des superutilisateurs ne sont pas concernées.

Parmi ces rôles, pg_read_all_stats permet de lire les tables de statistiques d’activité. pg_read_all_settings permet de lire la configuration de tous les paramètres. pg_stat_scan_tables permet d’exécuter les procédures stockées de lecture des statistiques. pg_monitor est le rôle typique pour de la supervision : il combine les trois rôles précédents. Leur utilisation est identique à pg_signal_backend.

En version 15 arrive le rôle pg_checkpoint. Ce dernier permet à un rôle non SUPERUSER de lancer un CHECKPOINT.

À partir de la version 16 apparaît un rôle pg_use_reserved_connections, qui donne accès à un pool de connexions réservées défini par le paramètre reserved connections (0 par défaut). Cela peut servir à garantir un accès à la supervision ou à divers utilitaires sans en faire des superutilisateurs.

pg_read_server_files permet d’autoriser la lecture de fichiers auxquels le serveur peut accéder avec la commande SQL COPY et toutes autres fonctions d’accès de fichiers. pg_write_server_files permet la même chose en écriture. Cela sous-entend aussi les sauvegardes de fichiers côté serveur, par exemple avec pg_basebackup (à partir de la version 15) ou d’autres outils. pg_execute_server_program autorise les utilisateurs membres d’exécuter des programmes en tant que l’utilisateur qui exécute le serveur PostgreSQL au travers de la commande SQL COPY et de toute fonction permettant l’exécution d’un programme sur le serveur.

La version 14 ajoute trois nouveaux rôles. pg_read_all_data permet de lire toutes les données des tables, vues et séquences, alors que pg_write_all_data permet de les écrire. Quant à pg_database_owner, il permet de se comporter comme le propriétaire des bases de données.

En version 16 apparaît pg_create_subscription pour gérer des souscriptions en réplication logique.

Plutôt que d’avoir à donner les droits sur chaque objet à chaque ajout d’un rôle, il est beaucoup plus simple d’utiliser le système d’héritage des droits.

Supposons qu’une nouvelle personne arrive dans le service de facturation. Elle doit avoir accès à toutes les tables concernant ce service. Sans utiliser l’héritage, il faudra récupérer les droits d’une autre personne du service pour retrouver la liste des droits à donner à cette nouvelle personne. De plus, si un nouvel objet est créé et que chaque personne du service doit pouvoir y accéder, il faudra ajouter l’objet et ajouter les droits pour chaque personne du service sur cet objet. C’est long et sujet à erreur. Il est préférable de créer un rôle facturation, de donner les droits sur ce rôle, puis d’ajouter chaque rôle du service facturation comme membre du rôle facturation. L’ajout et la suppression d’un objet est très simple : il suffit d’ajouter ou de retirer le droit sur le rôle facturation, et cela impactera tous les rôles membres.

Voici un exemple complet :

b1=# CREATE ROLE facturation;

CREATE ROLE

b1=# CREATE TABLE factures(id integer, dcreation date, libelle text,
montant numeric);

CREATE TABLE

b1=# GRANT ALL ON TABLE factures TO facturation;

GRANT

b1=# CREATE TABLE clients (id integer, nom text);

CREATE TABLE

b1=# GRANT ALL ON TABLE clients TO facturation;

GRANT

b1=# CREATE ROLE r1 LOGIN;

CREATE ROLE

b1=# GRANT facturation TO r1;

GRANT ROLE

b1=# \c b1 r1

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

b1=> SELECT * FROM factures;

 id | dcreation | libelle | montant
----+-----------+---------+---------
(0 rows)

b1=# CREATE ROLE r2 LOGIN;

CREATE ROLE

b1=# \c b1 r2

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

b1=> SELECT * FROM factures;

ERROR:  permission denied for relation factures

Par défaut, un utilisateur se connecte avec un rôle de connexion. Il obtient les droits et la configuration spécifique de ce rôle. Dans le cas où il hérite automatiquement des droits des rôles dont il est membre, il obtient aussi ces droits qui s’ajoutent aux siens. Dans le cas où il n’hérite pas automatiquement de ces droits, il peut temporairement les obtenir en utilisant la commande SET ROLE. Il ne peut le faire qu’avec les rôles dont il est membre.

b1=# CREATE ROLE r31 LOGIN;

CREATE ROLE

b1=# CREATE ROLE r32 LOGIN NOINHERIT IN ROLE r31;

CREATE ROLE

b1=# \c b1 r31

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

b1=> CREATE TABLE t1(id integer);

CREATE TABLE

b1=> INSERT INTO t1 VALUES (1), (2);

INSERT 0 2

b1=> \c b1 r32

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

b1=> SELECT * FROM t1;

ERROR:  permission denied for relation t1

b1=> SET ROLE TO r31;

SET

b1=> SELECT * FROM t1;

 id
----
  1
  2

b1=> \c b1 postgres

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

b1=# ALTER ROLE r32 INHERIT;

ALTER ROLE

b1=# \c b1 r32

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

b1=> SELECT * FROM t1;

 id
----
  1
  2

b1=> \c b1 postgres

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

b1=# REVOKE r31 FROM r32;

REVOKE ROLE

b1=# \c b1 r32

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

b1=> SELECT * FROM t1;

ERROR:  permission denied for relation t1

b1=> SET ROLE TO r31;

ERROR:  permission denied to set role "r31"

Le changement de rôle peut se faire uniquement au niveau de la transaction. Pour cela, il faut utiliser la clause LOCAL. Il peut se faire aussi sur la session, auquel cas il faut passer par la clause SESSION.

Lors d’une connexion, l’utilisateur fournit, explicitement ou non, plusieurs informations. PostgreSQL va choisir une méthode d’authentification en se basant sur les informations fournies et sur la configuration d’un fichier appelé pg_hba.conf.

Tous les outils fournis avec la distribution PostgreSQL (par exemple createuser) acceptent des options en ligne de commande pour fournir les informations en question :

• -h pour la socket Unix ou l’adresse/alias IP ;
• -p pour le numéro de port ;
• -d pour le nom de la base ;
• -U pour le nom du rôle.

Si l’utilisateur ne passe pas ces informations, plusieurs variables d’environnement sont vérifiées :

• PGHOST pour la socket Unix ou l’adresse/alias IP ;
• PGPORT pour le numéro de port ;
• PGDATABASE pour le nom de la base ;
• PGUSER pour le nom du rôle.

Au cas où ces variables ne seraient pas configurées, des valeurs par défaut sont utilisées :

• la socket Unix (/var/run/postgresql, parfois /tmp) en lieu d’un nom de machine ;
• le port 5432 ;
• la base postgres ou le nom de l’utilisateur PostgreSQL demandé, (suivant l’outil) ;
• le nom de l’utilisateur au niveau du système d’exploitation pour le nom du rôle.

Autrement dit, quelle que soit la situation, PostgreSQL remplacera les informations non fournies explicitement par des informations provenant des variables d’environnement, voire par des informations par défaut.

Lorsque le serveur PostgreSQL récupère une demande de connexion, il connaît le type de connexion utilisé par le client (socket Unix, connexion TCP SSL, connexion TCP simple, etc.). Il connaît aussi l’adresse IP du client (dans le cas d’une connexion via une socket TCP), le nom de la base et celui de l’utilisateur.

PostgreSQL va donc chercher les lignes correspondantes dans le tableau enregistré dans le fichier pg_hba.conf (HBA est l’acronyme de Host Based Authentication). Ce fichier est présent à côté de postgresql.conf, donc par exemple dans /var/lib/pgsql/16/data (défaut du packaging Red Hat) ou /etc/postgresql/16/main (défaut du packaging Debian). L’accès doit bien sûr en être le plus restreint possible.

Le contenu du fichier se présente en principe ainsi :

• 4 colonnes d’informations ;
• 1 colonne indiquant la méthode à appliquer ;
• 1 colonne optionnelle d’options.

Depuis PostgreSQL 16, il est possible d’indiquer ou plusieurs fichiers externes :

include pg_hba.ansible.conf
include "/chemin/fichier 2.conf"

(Existent aussi include_if_exists, qui ne tombe pas en erreur si le fichier n’existe pas, et include_dir, pour inclure tous les fichiers .conf d’un répertoire dans l’ordre de leurs noms.)

pg_hba.conf ne peut pas être modifié depuis PostgreSQL même, uniquement depuis le système d’exploitation. Après une modification, il faut dire explicitement à PostgreSQL de le recharger. Selon l’installation et l’OS, cet ordre peut être :

pg_ctl reload -D /mnt/base_pg/
systemctl reload postgresql-16
pg_ctlcluster 16 main reload

ou depuis PostgreSQL même :

SELECT pg_reload_conf() ;

Il y a une exception : sous Windows, le fichier est relu dès modification.

Exemple de trace problématique :

…user=,db=,app=,client= LOG:  invalid connection type "hsot"
…user=,db=,app=,client= CONTEXT:  line 121 of configuration file "/etc/postgresql/16/main/pg_hba.conf"
…user=,db=,app=,client= FATAL:  could not load /etc/postgresql/16/main/pg_hba.conf

Depuis une session utilisateur, il est possible de consulter le fichier via la vue pg_hba_file_rules, tel qu’il est sur le disque, et tel qu’il serait compris par PostgreSQL après un rechargement. La colonne error est très pratique pour repérer immédiatement les erreurs de syntaxe ou de frappe. La colonne file_name indique le fichier source (à partir de PostgreSQL 16).

PostgreSQL lit le fichier dans l’ordre. La première ligne correspondant à la connexion demandée lui précise la méthode d’authentification à utiliser. Nous verrons que certaines méthodes comme scram-sha-256 ou md5 (les plus courantes) exigent un mot de passe, d’autres non, comme ldap ou cert. Il ne reste plus qu’à appliquer cette méthode. Si elle fonctionne, la connexion est autorisée et se poursuit.

Si elle ne fonctionne pas, quelle qu’en soit la raison, la connexion est refusée. Aucune autre ligne du fichier ne sera lue ! La raison du refus est tracée sur le serveur de manière beaucoup plus complète que pour le client :

2023-11-03 10:41:24 CET [1805700]: [2-1] user=attaquant,db=erp,
app=[unknown],client=::1 DETAIL:  Role "mechant_hacker" does not exist.
Connection matched file "/etc/postgresql/16/defo/pg_hba.conf"
line 110: "host    all      all      ::1/128           scram-sha-256"

Il est donc essentiel de bien configurer ce fichier pour avoir une protection maximale, et de faire très attention à l’ordre des entrées.

La colonne type peut contenir quatre valeurs différentes. La valeur local concerne les connexions via la socket Unix, donc depuis un compte sur le serveur même. Elle est généralement couplée à la méthode peer. En pratique, on s’en sert surtout pour les connexions administratives (de l’utiliseur système postgres au rôle postgres), pour les sauvegardes depuis le serveur même, ou pour une connexion depuis une application qui tourne sur la même machine.

Toutes les autres valeurs concernent les connexions via le réseau (socket TCP). La différence réside dans l’utilisation forcée ou non du SSL :

• host : SSL au choix du client ;
• hostssl : impérativement avec chiffrage SSL ;
• hostnossl : impérativement sans SSL.

Il est à noter que l’option hostssl n’est utilisable que si le paramètre ssl du fichier postgresql.conf est à on (c’est le cas par défaut sous Debian, pas forcément ailleurs).

La seconde colonne de pg_hba.conf peut recueillir le nom d’une base, le nom de plusieurs bases en les séparant par des virgules, le nom d’un fichier contenant la liste des bases ou quelques valeurs en dur. La valeur all indique toutes les bases.

Enfin, la valeur sameuser spécifie une base de données de même nom que le rôle demandé (l’utilisateur durand peut se connecter à la base durand), alors que la valeur samerole spécifie que le rôle demandé doit être membre du rôle portant le même nom que la base de données demandée (par exemple, si l’utilisateur durand est membre du groupe compta, il peut se connecter à la base compta).

Il est possible d’indiquer un fichier (préfixé de @) qui contiendra une liste de rôles. Il peut être à côté de pg_hba.conf mais on peut fournir un chemin complet.

Depuis PostgreSQL 16, il est possible d’utiliser des expressions régulières, qui doivent être précédées de / (voir exemple plus bas).

La valeur replication est utilisée pour définir les connexions de réplication physique (mais pas logique). Il n’est pas nécessaire d’avoir une base nommée replication, toutes les bases sont concernées par une réplication physique.

La troisième colonne peut recueillir :

• le nom d’un rôle, ou plusieurs rôles séparés par des virgules ;
• le nom d’un groupe (en le précédant d’un signe +) : tous les membres de ce groupe pourront se connecter avec leur rôle propre ;
• le nom d’un fichier (précédé de @) contenant une liste de rôles ;
• la valeur all qui indique tous les rôles ;
• une regex (précédée de /) pour indiquer plusieurs rôles (nouveauté de la version 16).

Exemple :

# TYPE    DATABASE      USER              ADDRESS       METHOD
hostssl   erp_hr        u_virginie,u_sharon 10.1.17.67/32 scram-sha-256
hostssl   all           +grp_admin        10.0.0.0/8    scram-sha-256
hostssl   /erp_dev[1-5] @prestataires.txt 10.1.0.0/16   scram-sha-256
hostssl   /erp.*        /u_.*             10.0.0.0/8    scram-sha-256

Ici, ne peuvent se connecter à la base erp_hr que deux utilisatrices nommées, et uniquement depuis une IP précise. Les membres du groupe grp_admin peuvent se connecter à toutes les bases depuis tout le réseau privé. Aux bases erp_dev1 à erp_dev5 peuvent se connecter les prestataires dont les rôles sont dans le fichier prestataires.txt, depuis un certain sous-réseau. Et tous les utilisateurs dont le rôle commence par u_ peuvent se connecter aux bases dont le nom commence par erp_.

La colonne de l’adresse IP permet d’indiquer une adresse IP ou un sous-réseau IP. Il est donc possible de filtrer les connexions par rapport aux adresses IP, ce qui est une excellente protection. Voici deux exemples d’adresses IP au format adresse et masque de sous-réseau :

192.168.168.1 255.255.255.255
192.168.168.0 255.255.255.0

Et voici quelques exemples d’adresses IP (ou plages d’adresses) au format CIDR :

192.168.0.0/8       # réseau 192.168.x.y
192.168.66.0/24     # réseau 192.168.66.x
192.168.168.1/32    # 1 IP 
0.0.0.0/0           # toutes les IPv4
::0/0               # toutes les IPv6
::1/128             # localhost en IPv6
fe80::7a31:c1ff:0000:0000/96  # un réseau en IPv6

Dans le doute, la vue pg_hba_file_rules indique le masque calculé.

Les alias samehost et samenet désignent le serveur (avec toutes ses adresses) et tous les réseaux auquels il appartient.

Il est possible d’utiliser un nom d’hôte ou un domaine DNS au prix d’une recherche DNS pour chaque hostname présent, pour chaque nouvelle connexion.

La dernière colonne est généralement la méthode d’authentification, mais certaines acceptent des options dans une colonne supplémentaire. Nous verrons plus bas les différentes méthodes d’authentification, internes ou externes.

Le serveur a toujours le dernier mot pour imposer une méthode d’authentification, mais pour des raisons de sécurité le client peut aussi exiger celle qu’il attend (depuis PostgreSQL 16). Dans l’exemple suivant, le client exige la méthode scram-sha-256, mais le serveur est encore configuré en md5 et la connexion échoue.

psql 'host=s1 dbname=postgres user=postgres require_auth=scram-sha-256'
psql: error: connection to server at "s1" (192.168.74.65), port 54325 failed:
 authentication method requirement "scram-sha-256" failed:
 server requested a hashed password

D’autres variantes pour require_auth sont :

• require_auth=scram-sha-256,md5
• require_auth=!password
• require_auth=cert

Les options disponibles dépendent de la méthode d’authentification sélectionnée. Cependant, toutes les méthodes externes permettent l’utilisation de l’option map. Cette option a notamment pour but d’indiquer la carte de correspondance à sélectionner dans le fichier pg_ident.conf (voir plus bas).

La méthode trust est certainement la pire. À partir du moment où le rôle est reconnu, aucun mot de passe n’est demandé. Si le mot de passe est fourni malgré tout, il n’est pas vérifié. Il est donc essentiel de proscrire cette méthode d’authentification.

La méthode password force la saisie d’un mot de passe. Cependant, ce dernier est envoyé en clair sur le réseau. Il n’est donc pas conseillé d’utiliser cette méthode, surtout sur un réseau non sécurisé. (Noter que PostgreSQL chiffrera toujours le mot de passe chiffré avec une des méthodes ci-dessous.)

La méthode md5 est encore très utilisée mais obsolète. La saisie du mot de passe est forcée. Le mot de passe transite chiffré en md5. Cette méthode souffre néanmoins de certaines faiblesses décrites dans la section Mot de passe, on préférera la suivante.

La méthode scram-sha-256 est la plus sécurisée, elle offre moins d’angles d’attaque que md5. Elle est à privilégier.

La méthode reject sert pour interdire définitivement un accès à un utilisateur, un réseau ou une base quelles que soient les lignes suivantes de pg_hba.conf. Par exemple, on veut que le rôle u1 puisse se connecter à la base de données b1 mais pas aux autres. Voici un moyen de le faire (pour une connexion via les sockets Unix) :

local  b1   u1  scram-sha-256
local  all  u1  reject

Certaines méthodes permettent d’utiliser des annuaires d’entreprise comme RADIUS, LDAP ou ActiveDirectory. Certaines méthodes sont spécifiques à un système d’exploitation.

La méthode ldap utilise un serveur LDAP pour authentifier l’utilisateur.

La méthode radius permet d’utiliser un serveur RADIUS pour authentifier l’utilisateur.

La méthode gss (GSSAPI) correspond au protocole du standard de l’industrie pour l’authentification sécurisée définie dans RFC 2743. PostgreSQL supporte GSSAPI avec l’authentification Kerberos suivant la RFC 1964 ce qui permet de faire du Single Sign-On. C’est la méthode à utiliser avec Active Directory. sspi (uniquement dans le monde Windows) permet d’utiliser NTLM faute d’Active Directory.

La méthode cert utilise un certificat SSL sur le client, sans mot de passe.

La méthode ident permet d’associer les noms des utilisateurs du système d’exploitation aux noms des utilisateurs du système de gestion de bases de données. Un démon fournissant le service ident est nécessaire. (Les paquets RPM la mettaient en place jusque PostgreSQL 12 inclus pour une connexion depuis localhost, mais peer est maintenant recommandé.)

La méthode peer permet d’associer les noms des utilisateurs du système d’exploitation aux noms des utilisateurs du système de gestion de bases de données. Ceci n’est possible qu’avec une connexion locale. Par défaut, les paquets d’installation l’utilisent pour autoriser l’utilisateur système postgres à se connecter à l’instance locale, sans passer par le réseau, en tant que postgres sans mot de passe.

La méthode pam authentifie l’utilisateur en passant par les Pluggable Authentication Modules (PAM) fournis par Linux et d’autres Unix. De manière similaire, la méthode bsd utilise le système d’authentification BSD sur OpenBSD.

Ce fichier comporte plusieurs erreurs. Tout d’abord,

host    all       all         127.0.0.1/32    md5

autorise tous les utilisateurs, en IP, en local (127.0.0.1) à se connecter à toutes les bases, ce qui est en contradiction avec :

local   sameuser  all                         peer

qui, sera appliqué d’abord et restreint à la base de même nom.

Dans cette règle :

host    all       all         89.192.0.3/8    md5

le masque CIDR /8 est incorrect, et donc, au lieu d’autoriser seulement 89.192.0.3 à se connecter, on autorise tout le réseau 89.*.

L’entrée :

hostssl recherche recherche   89.192.0.4/32   scram-sha-256

est bonne, mais inutile, car masquée par la ligne précédente : toute ligne correspondant à cette entrée correspondra aussi à la ligne précédente. Le fichier étant lu séquentiellement, cette dernière entrée ne sert à rien.

Enfin, il aurait mieux valu que tous les mots de passe utilisent le chiffrage scram-sha-256 et non md5.

En utilisant une méthode d’authentification externe, locale ou à distance, le nom de l’utilisateur authentifié ne correspond pas forcément au nom du rôle avec lequel on souhaite se connecter. On peut le préciser avec -U mais il y a plus simple et sécurisé.

Par exemple, deux DBA ont les comptes joe et zoe sur le système d’exploitation. Ils souhaitent pouvoir se connecter en tant que rôle postgres sans saisie de mot de passe depuis leur compte, sans devoir faire un sudo -iu postgres pour lesquels on ne va pas forcément leur donner les droits.

Ceci n’est pas possible avec la configuration par défaut :

joe$ psql -U postgres
psql: error: FATAL:  Peer authentication failed for user "postgres"

Mais cela devient possible en configurant correctement les fichiers pg_hba.conf et pg_ident.conf. Il est tout d’abord nécessaire d’indiquer trois informations dans le fichier pg_ident.conf, une « map », les utilisateurs authentifiés (ici joe et zoe) et le rôle de connexion (ici postgres) :

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
pgadmins        joe                     postgres
pgadmins        zoe                     postgres

À partir de la version 15, il est possible de vérifier que la configuration de ce fichier est bonne. Pour cela, il faut lire la vue pg_ident_file_mappings (et vérifier que le champ error est vide).

SELECT * FROM pg_ident_file_mappings;

 line_number | map_name | sys_name | pg_username | error
-------------+----------+----------+-------------+-------
          43 | pgadmins | joe      | postgres    |
          44 | pgadmins | zoe      | postgres    |

Il faut ensuite ajouter cette map à la ligne de configuration d’authentification en haut du fichier pg_hba.conf :

local    all   all     peer      map=pgadmins

Après rechargement de la conf, la connexion de joe ou zoe à postgres en local se fera. Noter que la sécurité de l’accès à PostgreSQL repose à présent sur la sécurisation des utilisateurs système joe et zoe !

Tout ceci n’est valable que pour les connexions depuis le système d’exploitation même. En pratique, pg_ident.conf est utilisé pour se connecter directement en tant que postgres depuis un compte utilisateur sur le serveur, ou avec une authentification LDAP.

Depuis PostgreSQL 16, pg_ident.conf supporte les mêmes directives d’inclusion que pg_hba.conf (include, include_if_exists, include_dir), et le fichier d’origine d’une ligne est visible dans pg_ident_file_mappings.

PostgreSQL sait externaliser la vérification du mot de passe ou authentifier depuis une autre méthode qu’un mot de passe. Néanmoins, PostgreSQL n’externalise jamais la configuration d’un rôle.

Au delà de son nom et de son mot de passe, un rôle possède des options (SUPERUSER, LOGIN, etc.), des héritages, des privilèges, des privilèges par défaut pour chaque base voire pour chaque schéma, des paramètres globaux ou par base. Tout cela reste dans PostgreSQL, même si l’authentification est externe.

L’outil ldap2pg permet de centraliser les rôles dans l’annuaire d’entreprise. ldap2pg est un binaire (programmé en Go) à installer à côté de PostgreSQL ou ailleurs.

ldap2pg traite une configuration avancée au format YAML décrivant toutes les recherches LDAP énumérant les rôles à provisionner dans l’annuaire. Par exemple : la liste des DBA, la liste des développeurs d’une application, etc.

À chaque recherche LDAP est associé un modèle de rôle où sont substitués des attributs LDAP fournit par l’annuaire. ldap2pg sait également configurer les paramètres propres à chaque rôle, les privilèges par défaut, et les privilèges sur les objets.

La recherche LDAP des rôles d’une instance est indépendante de l’authentification. On peut très bien rechercher dans l’annuaire la liste des DBA avec le protocole LDAP et authentifier les connexions avec Kerberos.

Page du projet : https://labs.dalibo.com/ldap2pg

PostgreSQL demande peu de maintenance par rapport à d’autres SGBD. Néanmoins, un suivi vigilant de ces tâches participera beaucoup à conserver un système performant et agréable à utiliser.

La maintenance d’un serveur PostgreSQL revient à s’occuper de trois opérations :

• le VACUUM, pour éviter une fragmentation trop importante des tables ;
• l’ANALYZE, pour mettre à jour les statistiques sur les données contenues dans les tables ;
• le REINDEX, pour reconstruire les index.

Il s’agit donc de maintenir, voire d’améliorer, les performances du système. Il ne s’agit en aucun cas de s’assurer de la stabilité du système.

Généralement on se reposera sur le processus d’arrière-plan autovacuum, qui s’occupe des VACUUM et ANALYZE (mais pas REINDEX) en fonction de l’activité, et prend soin de ne pas la gêner. Il est possible de planifier des exécutions régulières avec cron (ou tout autre ordonnanceur) , notamment pour des REINDEX.

Un appel explicite est parfois nécessaire, notamment au sein de batchs ou de gros imports… L’autovacuum n’a pas forcément eu le temps de passer entre deux étapes, et les statistiques ne sont alors pas à jour : le planificateur pense que les tables sont encore vides et peut choisir un plan désastreux. On lancera donc systématiquement au moins un ANALYZE sur les tables modifiées après les modifications lourdes. Un VACUUM ANALYZE est parfois encore plus intéressant, notamment si des données ont été modifiées ou effacées, ou si les requêtes suivantes peuvent profiter d’un parcours d’index seul (Index Only Scan).

PostgreSQL ne supprime pas des tables les versions périmées des lignes après un UPDATE ou un DELETE, elles deviennent juste invisibles. La commande VACUUM permet de récupérer l’espace utilisé par ces lignes afin d’éviter un accroissement continuel du volume occupé sur le disque.

Une table qui subit beaucoup de mises à jour et suppressions nécessitera des nettoyages plus fréquents que les tables rarement modifiées. Le VACUUM « simple » (VACUUM nomdematable ;) marque les données expirées dans les tables et les index pour une utilisation future. Il ne tente pas de rendre au système de fichiers l’espace utilisé par les données obsolètes, sauf si l’espace est à la fin de la table et qu’un verrou exclusif de table peut être facilement obtenu. L’espace inutilisé au début ou au milieu du fichier ne provoque pas un raccourcissement du fichier et ne redonne pas d’espace mémoire au système d’exploitation. De même, l’espace d’une colonne supprimée n’est pas rendu.

Cet espace libéré n’est pas perdu : il sera disponible pour les prochaines lignes insérées et mises à jour, et la table n’aura pas besoin de grandir.

Un VACUUM peut être lancé sans aucune gêne pour les utilisateurs. Il va juste générer des écritures supplémentaires. On verra plus loin que l’autovacuum s’occupe de tout cela en tâche de fond et de manière non intrusive, mais il arrive encore que l’on lance un VACUUM manuellement. Noter qu’un VACUUM s’occupe également de quelques autres opérations de maintenance qui ne seront pas détaillées ici.

L’option VERBOSE vous permet de suivre ce qui a été fait. Dans l’exemple suivant, 100 000 lignes sont nettoyées dans 541 blocs, mais 300 316 lignes ne peuvent être supprimées car une autre transaction reste susceptible de les voir.

VACUUM VERBOSE livraisons ;

INFO:  vacuuming "public.livraisons"
INFO:  "livraisons": removed 100000 row versions in 541 pages
INFO:  "livraisons": found 100000 removable, 300316 nonremovable
                                         row versions in 2165 out of 5406 pages
DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 6249883
There were 174 unused item pointers.
Skipped 0 pages due to buffer pins, 540 frozen pages.
0 pages are entirely empty.
CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.08 s.
VACUUM
Temps : 88,990 ms

L’ordre VACUUM possède de nombreux paramètres que nous ne décrirons pas ici car ils servent peu au quotidien (au besoin, voir le module M5).

Supervision :

La vue pg_stat_progress_vacuum permet de suivre un VACUUM simple pendant son déroulement.

La vue pg_stat_user_tables contient pour chaque table la date du dernier passage d’un VACUUM simple (champ last_vacuum) celle du dernier passage automatique (last_autovacuum). Pour les passages précédents, il faudra se rabattre sur les traces (on conseille de positionner log_autovacuum_min_duration suffisamment bas, ou à 0). Il est important de vérifier que les tables actives sont régulièrement nettoyées.

Outil en ligne de commande :

L’outil vacuumdb permet d’exécuter depuis le shell un VACUUM sur une ou toutes les bases. C’est l’outil idéal lorsque l’on planifie un nettoyage quotidien ou hebdomadaire.

Il sait exécuter des VACUUM sur plusieurs tables en parallèle, ou profiter des optimisations liées aux nettoyages en masse apparues en v16.

$ vacuumdb --echo --all

SELECT pg_catalog.set_config('search_path', '', false);
vacuumdb : exécution de VACUUM sur la base de données « pgbench »
RESET search_path;
SELECT c.relname, ns.nspname FROM pg_catalog.pg_class c
 JOIN pg_catalog.pg_namespace ns ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid
 LEFT JOIN pg_catalog.pg_class t ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid
 WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array['r', 'm'])
 ORDER BY c.relpages DESC;
SELECT pg_catalog.set_config('search_path', '', false);
VACUUM (SKIP_DATABASE_STATS) public.pgbench_accounts;
VACUUM (SKIP_DATABASE_STATS) pg_catalog.pg_proc;
VACUUM (SKIP_DATABASE_STATS) pg_catalog.pg_attribute;
VACUUM (SKIP_DATABASE_STATS) pg_catalog.pg_description;
VACUUM (SKIP_DATABASE_STATS) pg_catalog.pg_statistic;
…
…
VACUUM (ONLY_DATABASE_STATS);
SELECT pg_catalog.set_config('search_path', '', false);
vacuumdb : exécution de VACUUM sur la base de données « postgres »
…
…
VACUUM (ONLY_DATABASE_STATS);

Un VACUUM simple fait rarement gagner de l’espace disque. Il faut utiliser l’option FULL pour ça : la commande VACUUM FULL nomtable ; réécrit la table en ne gardant que les données actives, et au final libère donc l’espace consommé par les lignes périmées ou les colonnes supprimées, et le rend au système d’exploitation. Les index sont réécrits au passage.

Inconvénient principal : VACUUM FULL acquiert un verrou exclusif sur chaque table concernée : personne ne peut plus y écrire ni même lire avant la fin de l’opération, et les sessions accédant aux tables sont mises en attente. Cela peut être long pour de grosses tables. D’autre part, le VACUUM FULL peut lui-même attendre la libération d’un verrou, tout en bloquant les transactions suivantes (phénomène d’empilement des verrous). Il est conseillé d’opérer par exemple ainsi :

SET lock_timout TO '3s';
VACUUM (FULL, VERBOSE) nomtable ;

Ainsi l’ordre VACUUM FULL sera annulé s’il n’obtient pas son verrou assez vite.

Autre inconvénient : VACUUM FULL écrit la nouvelle version de la table à côté de l’ancienne version avant d’effacer cette dernière : l’espace occupé peut donc temporairement doubler. Si vos disques sont presque pleins, vous ne pourrez donc pas faire un VACUUM FULL d’une grosse table pour récupérer de l’espace !

L’autovacuum ne procédera jamais à un VACUUM FULL, vous devrez toujours le demander explicitement. On le réservera aux périodes de maintenance, dans les cas où il est vraiment nécessaire.

En effet, il ne sert à rien de chercher à réduire au strict minimum la taille des tables par des VACUUM FULL répétés. Dans une base active, les espaces libres sont vite réutilisés par de nouvelles données. Le bloat (l’espace inutilisé d’une table) se stabilise généralement dans une proportion dépendant des débits d’insertions, suppressions et modifications dans la table.

Supervision :

La vue pg_stat_progress_cluster permet de suivre un VACUUM FULL (à partir de PostgreSQL 12). Son nom provient de la proximité avec la commande CLUSTER (voir plus bas).

Les VACUUM FULL ne sont pas tracés dans la vue pg_stat_user_tables.

Outil en ligne de commande :

L’outil vacuumdb possède une option --full.

Quand faut-il utiliser VACUUM sur une table ?

• pour des nettoyages réguliers ;
• entre les étapes d’un batch ;
• si vous constatez que l’autovacuum ne passe pas assez souvent et qu’un changement de paramétrage ne suffit pas ;
• et ce, pendant que votre base tourne.

Quand faut-il utiliser VACUUM FULL sur une table ?

• après des suppressions massives de données ;
• si le verrou exclusif ne gêne pas la production ;
• dans le cadre d’une maintenance exceptionnelle.

Des VACUUM standards et une fréquence modérée sont une meilleure approche que des VACUUM FULL, même non fréquents, pour maintenir des tables mises à jour fréquemment : faites confiance à l’autovacuum jusque preuve du contraire.

VACUUM FULL est recommandé dans les cas où vous savez que vous avez supprimé ou modifié une grande partie des lignes d’une table, et que les espaces libres ne seront pas à nouveau remplis assez vite, de façon à ce que la taille de la table soit réduite de façon conséquente.

Les deux outils peuvent se lancer à la suite. Après un VACUUM FULL (bloquant) sur une table, on lance souvent immédiatement un VACUUM ANALYZE. Cela semble inutile du point de vue des données, mais les autres opérations de maintenance impliquées peuvent améliorer les performances.

L’optimiseur de requêtes de PostgreSQL s’appuie sur des informations statistiques calculées à partir des données des tables. Ces statistiques sont récupérées par la commande ANALYZE, qui peut être invoquée seule ou comme une option de VACUUM. Il est important d’avoir des statistiques relativement à jour sans quoi des mauvais choix dans les plans d’exécution pourraient pénaliser les performances de la base.

L’autovacuum de PostgreSQL appelle au besoin ANALYZE si l’activité de la table le nécessite. C’est généralement suffisant, même s’il est fréquent de modifier le paramétrage sur de grosses tables.

Il est possible de programmer ANALYZE périodiquement (le dimanche, la nuit par exemple, à l’aide d’une commande cron par exemple), éventuellement couplé à un VACUUM :

VACUUM ANALYZE nomdematable ;

Il existe des cas où lancer un ANALYZE manuellement est nécessaire :

• en mode « batch » : l’autovacuum n’a pas forcément le temps de passer entre deux étapes, on peut être amené à intercaler un VACUUM ANALYZE sur des tables modifiées ;
• quand certains plans de requêtes affichent des statistiques aberrantes : la mise à jour des statistiques peut suffire (et l’on regardera ensuite dans pg_stat_user_tables.last_autoanalyze si l’autovacuum a tardé et s’il y a un ajustement à faire ce côté) ;
• après un ALTER TABLE [...] ALTER COLUMN, car les statistiques de la colonne peuvent disparaître, ou bien sûr lors de l’ajout d’une colonne pré-remplie ;
• lors de l’ajout d’un index fonctionnel : l’ANALYZE mène à la création d’une nouvelle entrée dans pg_statistics ;
• lors de l’utilisation des tables temporaires : l’autovacuum ne les voit pas.

Le paramètre default_statistics_target définit l’échantillonnage par défaut des statistiques pour les colonnes de chacune des tables. La valeur par défaut est de 100. Ainsi, pour chaque colonne, 30 000 lignes sont choisies au hasard, et les 100 valeurs les plus fréquentes et un histogramme à 100 bornes sont stockés dans pg_statistics en guise d’échantillon représentatif des données.

Des valeurs supérieures provoquent un ralentissement important d’ANALYZE, un accroissement de la table pg_statistics, et un temps de calcul des plans d’exécution plus long. On conserve généralement la valeur 100 par défaut (sauf peut-être sur certaines grosses bases aux requêtes complexes et longues, comme des entrepôts de données).

Voici la commande à utiliser si l’on veut modifier cette valeur pour une colonne précise, la valeur ainsi spécifiée prévalant sur la valeur de default_statistics_target :

ALTER TABLE ma_table ALTER ma_colonne SET STATISTICS 200 ;

ANALYZE ma_table ;

Sans l’ANALYZE explicite, la mise à jour attendrait le prochain passage de l’autovacuum.

La vue pg_stat_user_tables contient aussi les dates du dernier passage d’un ANALYZE manuel (champ last_analyze) ou automatique (last_autoanalyze). Là encore, vérifier que les tables actives sont régulièrement analysées.

La version 13 apporte une vue appelée pg_stat_progress_analyze qui permet de suivre l’exécution des ANALYZE en cours.

REINDEX reconstruit un index en utilisant les données stockées dans la table, remplaçant l’ancienne copie de l’index. La même commande peut réindexer tous les index d’une table :

REINDEX INDEX nomindex ;
REINDEX (VERBOSE) TABLE nomtable ;

Les pages d’index qui sont devenues complètement vides sont récupérées pour être réutilisées. Il existe toujours la possibilité d’une utilisation inefficace de l’espace : même s’il ne reste qu’une clé d’index dans une page, la page reste allouée. La possibilité d’inflation n’est pas indéfinie, mais il est souvent utile de planifier une réindexation périodique pour les index fréquemment modifiés.

De plus, pour les index B-tree, un index tout juste construit est plus rapide qu’un index qui a été mis à jour plusieurs fois. En effet, dans un index nouvellement créé, les pages logiquement adjacentes sont aussi physiquement adjacentes.

La réindexation est aussi utile dans le cas d’un index corrompu. Ce cas est heureusement très rare, et souvent lié à des problèmes matériels.

Les index « invalides » sont inutilisables et ignorés, et doivent également être reconstruits. Ce statut apparaît en bas de la description de la table associée :

# \d+ pgbench_accounts
…
Index :
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid) INVALID

Un index peut devenir invalide pour deux raisons. La première ne concerne plus les versions supportées : avant PostgreSQL 10, des index de type hash (uniquement) pouvaient devenir invalides après un redémarrage brutal, car ils n’étaient alors pas journalisés. La seconde raison est une conséquence de la clause CONCURRENTLY des ordres CREATE INDEX et REINDEX. Cette clause permet de créer/réindexer un index sans bloquer les écritures dans la table. Cependant, si, au bout de deux passes, l’index n’est toujours pas complet, il est considéré comme invalide, et doit être soit détruit, soit reconstruit avec la commande REINDEX.

Noter que, sans CONCURRENTLY, un REINDEX bloque non seulement les écritures, mais aussi souvent les lectures. On préférera donc le CONCURRENTLY si la table est utilisée :

REINDEX (VERBOSE) INDEX nomindex CONCURRENTLY ;

Enfin, depuis la version 14, il est possible de réindexer un index tout en le changeant de tablespace. Pour cela, il faut utiliser la clause TABLESPACE avec en argument le nom du tablespace de destination.

Il est à savoir que l’opération VACUUM (sans FULL) ne provoque pas de réindexation. Une réindexation est effectuée lors d’un VACUUM FULL.

La commande système reindexdb peut être utilisée pour réindexer une table, une base ou une instance entière.

La commande CLUSTER provoque une réorganisation des données de la table en triant les lignes suivant l’ordre indiqué par l’index. Du fait de la réorganisation, le résultat obtenu est équivalent à un VACUUM FULL dans le contexte de la fragmentation. Elle verrouille tout aussi complètement la table et nécessite autant de place.

Attention, cette réorganisation est ponctuelle, et les données modifiées ou insérées par la suite n’en tiennent généralement pas compte. L’opération peut donc être à refaire après un certain temps.

Comme après un VACUUM FULL, lancer un VACUUM ANALYZE manuellement peut être bénéfique pour les performances.

En ligne de commande, l’outil associé clusterdb permet de lancer la réorganisation de tables ayant déjà fait l’objet d’une « clusterisation ».

La vue pg_stat_progress_cluster permet de suivre le déroulement du CLUSTER.

L’exécution des commandes VACUUM, ANALYZE et REINDEX peut se faire manuellement dans certains cas. Il est cependant préférable de mettre en place une exécution automatique de ces commandes. La plupart des administrateurs utilise cron sous Unix et les tâches planifiées sous Windows. pgAgent peut aussi être d’une aide précieuse pour la mise en place de ces opérations automatiques.

Peu importe l’outil. L’essentiel est que ces opérations soient réalisées et que le statut de leur exécution soit vérifié périodiquement.

La fréquence d’exécution dépend principalement de la fréquence des modifications et suppressions pour le VACUUM et de la fréquence des insertions, modifications et suppressions pour l’ANALYZE.

L’automatisation du vacuum par cron est simple à mettre en place. Cependant, elle s’exécute pour toutes les tables, sans distinction. Que la table ait été modifiée plusieurs millions de fois ou pas du tout, elle sera traitée par le script. À l’inverse, l’autovacuum est un outil qui vérifie l’état des tables et, suivant le dépassement d’une limite, déclenche ou non l’exécution d’un VACUUM ou d’un ANALYZE, voire des deux.

L’autovacuum est activé par défaut, et il est conseillé de le laisser ainsi. Son paramétrage permet d’aller assez loin si nécessaire selon la taille et l’activité des tables.

Voici un script créé pour un client dans le but d’automatiser la réindexation uniquement pour les index le méritant. Pour cela, il vérifie les index fragmentés avec la fonction pgstatindex() de l’extension pgstattuple (installable avec un simple CREATE EXTENSION pgstattuple ; dans chaque base).

Au-delà de 30 % de fragmentation (par défaut), l’index est réindexé. Pour minimiser le risque de blocage, le script utilise CREATE INDEX CONCURRENTLY en priorité, et REINDEX dans les autres cas (clés primaires et contraintes uniques).

La version 12 permet d’utiliser l’option CONCURRENTLY avec REINDEX. Ce script pourrait l’utiliser après avoir détecté qu’il se trouve sur une version compatible.

#!/bin/bash
# Script de réindexation d'une base
# ce script va récupérer la liste des index disponibles sur la base
# et réindexer l'index s'il est trop fragmenté ou invalide

# Mode debug
#set -x

# Récupération de la base maintenance
if test -z "$PGDATABASE"; then
  export PGDATABASE=postgres
fi

# quelques constantes personnalisables
TAUX_FRAGMENTATION_MAX=30
NOM_INDEX_TEMPORAIRE=index_traitement_en_cours
NB_TESTS=3
BASES=""

# Quelques requêtes
REQ_LISTEBASES="SELECT array_to_string(array(
  SELECT datname
  FROM pg_database
  WHERE datallowconn AND datname NOT IN ('postgres', 'template1')), ' ')"
REQ_LISTEINDEX="
SELECT n.nspname as \"Schéma\", tc.relname as \"Table\", ic.relname as \"Index\",
  i.indexrelid as \"IndexOid\",
  i.indisprimary OR i.indisunique as \"Contrainte\", i.indisvalid as \"Valide?\",
  round(100-(pgstatindex(n.nspname||'.'||ic.relname)).avg_leaf_density)
    as \"Fragmentation\",
  pg_get_indexdef(i.indexrelid) as \"IndexDef\"
FROM pg_index i
JOIN pg_class ic ON i.indexrelid=ic.oid
JOIN pg_class tc ON i.indrelid=tc.oid
JOIN pg_namespace n ON tc.relnamespace=n.oid
WHERE n.nspname <> 'pg_catalog'
  AND n.nspname !~ '^pg_toast'
ORDER BY ic.relname;"

# vérification de la liste des bases
if test $# -gt 1; then
  echo "Usage: $0 [nom_base]"
  exit 1
elif test $# -eq 1; then
  BASE_PRESENTE=$(psql -XAtqc \
  "SELECT count(*) FROM pg_database WHERE datname='$1'" 2>/dev/null)
  if test $BASE_PRESENTE -ne 1; then
    echo "La base de donnees $BASE n'existe pas."
    exit 2
  fi

  BASES=$1
else
  BASES=$(psql -XAtqc "$REQ_LISTEBASES" 2>/dev/null)
fi

# Pour chaque base
for BASE in $BASES
do
    # Afficher la base de données
    echo "########## $BASE ##########"

  # Vérification de la présence de la fonction pgstatindex
  FONCTION_PRESENTE=$(psql -XAtqc \
  "SELECT count(*) FROM pg_proc WHERE proname='pgstatindex'" $BASE 2>/dev/null)
  if test $FONCTION_PRESENTE -eq 0; then
    echo "La fonction pgstatindex n'existe pas."
    echo "Veuillez installer le module pgstattuple."
    exit 3
  fi

  # pour chaque index
  echo "Récupération de la liste des index (ratio cible $TAUX_FRAGMENTATION_MAX)..."
  psql -XAtF " " -c "$REQ_LISTEINDEX" $BASE | \
  while read schema table index indexoid contrainte validite fragmentation definition
  do
  #  NaN (not a number) est possible si la table est vide
  #  dans ce cas, une réindexation est rapide
    if test "$fragmentation" = "NaN"; then
      fragmentation=0
    fi

  #  afficher index, validité et fragmentation
      if test "$validite" = "t"; then
        chaine_validite="valide"
      else
        chaine_validite="invalide"
      fi
    echo "Index $index, $chaine_validite, ratio libre ${fragmentation}%"

  #  si index fragmenté ou non valide
      if test "$validite" = "f" -o $fragmentation -gt $TAUX_FRAGMENTATION_MAX; then
  #  verifier les verrous sur l'index, attendre un peu si nécessaire
        verrous=1
        tests=0
        while test $verrous -gt 0 -a $tests -le $NB_TESTS
        do
      if test $tests -gt 0; then
        echo \
    "  objet verrouillé, attente de $tests secondes avant nouvelle tentative..."
        sleep $tests
      fi
      verrous=$(psql -XAtqc \
      "SELECT count(*) FROM pg_locks WHERE relation=$indexoid" 2>/dev/null)
      tests=$(($tests + 1))
        done
      if test $verrous -gt 0; then
        echo "  objet toujours verrouillé, pas de reindexation pour $schema.$index"
        continue
      fi

  #    si contrainte, reindexation simple
        if test "$contrainte" = "t"; then
          echo -n "  reindexation de la contrainte... "
        psql -Xqc "REINDEX INDEX $schema.$index;" $BASE
        if test $? -eq 0; then
          echo "OK"
        else
          echo "PROBLEME!!"
          continue
        fi
  #    sinon
        else
  #    renommer <ancien nom> en <index_traitement_en_cours>
            echo -n "  renommage... "
        psql -Xqc \
        "ALTER INDEX $schema.$index RENAME TO $NOM_INDEX_TEMPORAIRE;" $BASE
        if test $? -eq 0; then
          echo "OK"
        else
          echo "PROBLEME!!"
          continue
        fi
  #    create index <ancien nom>
            echo -n "  création nouvel index..."
        psql -Xqc "$definition;" $BASE
  #    si create OK, drop index <index_traitement_en_cours>
        if test $? -eq 0; then
          echo "OK"
              echo -n "  suppression ancien index..."
          psql -Xqc "DROP INDEX $schema.$NOM_INDEX_TEMPORAIRE;" $BASE
          if test $? -eq 0; then
            echo "OK"
          else
            echo "PROBLEME!!"
            continue
          fi
  #    sinon, renommer <index_traitement_en_cours> en <ancien nom>
        else
          echo "PROBLEME!!"
              echo -n "  renommage inverse..."
          psql -Xqc \
          "ALTER INDEX $schema.$NOM_INDEX_TEMPORAIRE RENAME TO $index;" $BASE
          if test $? -eq 0; then
            echo "OK"
          else
            echo "PROBLEME!!"
            continue
          fi
        fi
      fi
    fi
  done
done

À l’installation de PostgreSQL, il est essentiel de s’assurer de la sécurité du serveur : sécurité au niveau des accès, au niveau des objets, ainsi qu’au niveau des données.

Ce chapitre va faire le point sur ce qu’un utilisateur peut faire par défaut et sur ce qu’il ne peut pas faire. Nous verrons ensuite comment restreindre les droits. Enfin, nous verrons les possibilités de chiffrement et de non-corruption de PostgreSQL.

Par défaut, un utilisateur a beaucoup de droits.

Il peut accéder à toutes les bases de données. Il faut modifier le fichier pg_hba.conf pour éviter cela. Il est aussi possible de supprimer ce droit avec l’ordre suivant :

REVOKE CONNECT ON DATABASE nom_base FROM nom_utilisateur ;

Avant la version 15, l’utilisateur peut créer des objets dans le schéma disponible par défaut (nommé public) sur chacune des bases de données où il peut se connecter, même si la base ne lui appartient pas. Il est assez courant de supprimer ce droit :

REVOKE CREATE ON SCHEMA public FROM nom_utilisateur ;

ou de manière globale :

REVOKE CREATE ON SCHEMA public FROM public ;

Certains DBA suppriment même le schéma de la base. Dans tous les cas, il faut faire attention à ce que le schéma ne réapparaisse pas lors d’une restauration de sauvegarde logique, car sa création est implicite.

L’utilisateur peut créer des objets temporaires sur chacune des bases de données où il peut se connecter. Il est possible de supprimer ce droit avec l’ordre suivant :

REVOKE TEMP ON DATABASE nom_base FROM nom_utilisateur ;

L’utilisateur peut créer des fonctions, uniquement avec les langages de confiance, uniquement dans les schémas où il a le droit de créer des objets (donc dans le schéma public de toute base avant la V15). Il existe deux solutions :

• supprimer le droit d’utiliser un langage :

REVOKE USAGE ON LANGUAGE nom_langage FROM nom_utilisateur ;

• supprimer le droit de créer des objets dans un schéma :

REVOKE CREATE ON SCHEMA nom_schema FROM nom_utilisateur ;

L’utilisateur peut exécuter n’importe quelle fonction, y compris définie par quelqu’un d’autre, à condition que la fonction soit dans un schéma où il a accès (dont le schéma public, par défaut) (et donc public, par défaut) Il est possible d’empêcher cela en supprimant le droit d’exécution d’une fonction :

REVOKE EXECUTE ON FUNCTION nom_fonction FROM nom_utilisateur ;

Il peut récupérer des informations sur l’instance car il a le droit de lire tous les catalogues systèmes. Par exemple, en lisant pg_class, il peut connaitre la liste des tables, vues, séquences, etc. En parcourant pg_proc, il dispose de la liste des fonctions. Il n’y a pas de contournement à cela : un utilisateur doit pouvoir accéder aux catalogues systèmes pour travailler normalement.

Il peut visualiser les sources des vues et des fonctions. Il existe des modules propriétaires de chiffrement (ou plutôt d’obfuscation) du code mais rien de natif. Le plus simple est certainement de coder les fonctions sensibles en C.

Un utilisateur peut agir sur de nombreux paramètres au sein de sa session pour modifier les valeurs par défaut du postgresql.conf ou ceux imposés à son rôle ou à sa base.

Un cas courant consiste à modifier la liste des schémas par défaut où chercher les tables :

SET search_path TO rh,admin,ventes,public ;

L’utilisateur peut aussi décider de s’octroyer plus de mémoire de tri :

SET work_mem TO '500MB' ;

Il est impossible d’interdire cela. Toutefois, cela permet de conserver un paramétrage par défaut prudent, tout en autorisant l’utilisation de plus de ressources quand cela s’avère nécessaire.

Les exemples suivants modifient le fuseau horaire du client, désactivent la parallélisation le temps de la session, et changent le nom de l’applicatif visible dans les outils de supervision :

SET timezone TO GMT ;
SET max_parallel_workers_per_gather TO 0 ;
SET application_name TO 'batch_comptabilite' ;

Pour une session lancée en ligne de commande, pour les outils qui utilisent la libpq, on peut fixer les paramètres à l’appel grâce à la variable d’environnement PGOPTIONS :

PGOPTIONS="-c max_parallel_workers_per_gather=0 -c work_mem=4MB" psql < requete.sql

La valeur en cours est visible avec :

SHOW parametre ;

ou :

SELECT current_setting('parametre') ;

ou encore :

SELECT * FROM pg_settings WHERE name = 'parametre' ;

Sous psql à partir de la version 15 du client, la métacommande \dconfig affiche les paramètres qui n’ont pas leur valeur par défaut.

Ce paramétrage est limité à la session en cours, et disparaît avec elle à la déconnexion, ou si l’on demande un retour à la valeur par défaut :

RESET parametre ;

Enfin, on peut n’appliquer des paramètres que le temps d’une transaction, c’est-à-dire jusqu’au prochain COMMIT ou ROLLBACK :

SET LOCAL work_mem TO '100MB' ;

De nombreux paramètres sont cependant non modifiables, ou réservés aux superutilisateurs.

Un utilisateur standard ne peut pas créer de bases et de rôles. Il a besoin pour cela d’attributs particuliers (respectivement CREATEDB et CREATEROLE).

Il ne peut pas accéder au contenu (aux données) d’objets créés par d’autres utilisateurs. Ces derniers doivent lui donner ce droit explicitement : GRANT USAGE ON SCHEMA secret TO utilisateur ; pour lire un schéma, ou GRANT SELECT ON TABLE matable TO utilisateur ; pour lire une table.

De même, il ne peut pas modifier le contenu et la définition d’objets créés par d’autres utilisateurs. Là-aussi, ce droit doit être donné explicitement : GRANT INSERT,DELETE,UPDATE,TRUNCATE ON TABLE matable TO utilisateur;.

Il existe d’autres droits plus rares, dont :

• GRANT TRIGGER ON TABLE ... autorise la création de trigger ;
• GRANT REFERENCES ON TABLE ... autorise la création d’une clé étrangère pointant vers cette table (ce qui est interdit par défaut car cela interdit au propriétaire de supprimer ou modifier des lignes, entre autres) ;
• GRANT USAGE ON SEQUENCE... autorise l’utilisation d’une séquence.

Par facilité, on peut octroyer des droits en masse :

• GRANT ALL PRIVILEGES ON TABLE matable TO utilisateur ;
• GRANT SELECT ON TABLE matable TO public ;
• GRANT SELECT ON ALL TABLES IN SCHEMA monschema ;

Pour sécuriser plus fortement une instance, il est nécessaire de restreindre les droits des utilisateurs.

Connexions :

Cela commence par la gestion des connexions. Les droits de connexion sont généralement gérés via le fichier de configuration pg_hba.conf. Cette configuration a déjà été abordée dans le chapitre Droits de connexion de ce module de formation.

GRANT & REVOKE :

Cela passe ensuite par les droits sur les objets. On dispose pour cela des instructions GRANT et REVOKE, qui ont été expliquées dans le chapitre Droits sur les objets de ce module de formation.

SECURITY LABEL :

Il est possible d’aller plus loin avec l’instruction SECURITY LABEL. Un label de sécurité est un commentaire supplémentaire pris en compte par un module de sécurité qui disposera de la politique de sécurité. Le seul module de sécurité actuellement disponible est un module contrib pour l’intégration à SELinux, appelé sepgsql.

SECURITY DEFINER & LEAKPROOF :

Certains objets disposent de droits particuliers. Par exemple, les fonctions disposent des clauses SECURITY DEFINER et LEAKPROOF.

SECURITY DEFINER indique au système que la fonction doit s’exécuter avec les droits de son propriétaire (et non pas avec ceux de l’utilisateur l’exécutant). Cela permet d’éviter de donner des droits d’accès à certains objets.

LEAKPROOF permet de dire à PostgreSQL que cette fonction ne peut pas occasionner de fuites d’informations. Ainsi, le planificateur de PostgreSQL sait qu’il peut optimiser l’exécution des fonctions.

Vues avec security_barrier :

Quant aux vues, elles disposent d’une option appelée security_barrier. Certains utilisateurs créent des vues pour filtrer des lignes, afin de restreindre la visibilité sur certaines données. Or, cela peut se révéler dangereux si un utilisateur malintentionné a la possibilité de créer une fonction car il peut facilement contourner cette sécurité si cette option n’est pas utilisée. Voici un exemple complet :

CREATE TABLE elements (id int, contenu text, prive boolean);

CREATE TABLE

INSERT INTO elements (id, contenu, prive)
VALUES (1, 'a', false), (2, 'b', false), (3, 'c super prive', true),
       (4, 'd', false), (5, 'e prive aussi', true) ;

INSERT 0 5

SELECT * FROM elements ;

 id |    contenu    | prive
----+---------------+-------
  1 | a             | f
  2 | b             | f
  3 | c super prive | t
  4 | d             | f
  5 | e prive aussi | t

La table elements contient cinq lignes, dont deux considérées comme privées. Nous allons donc créer une vue ne permettant de voir que les lignes publiques, sauf pour un utilisateur particulier :

CREATE OR REPLACE VIEW elements_public AS
  SELECT * FROM elements
  WHERE CASE WHEN current_user = 'guillaume' THEN TRUE
            ELSE NOT prive
        END ;

CREATE VIEW

Ce nouvel utilisateur ne verra donc pas les lignes privées masquées par la vue :

CREATE USER u1;

CREATE ROLE

GRANT SELECT ON elements_public TO u1;

GRANT

\c - u1

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

SELECT * FROM elements ;

ERROR:  permission denied for relation elements

SELECT * FROM elements_public ;

 id | contenu | prive
----+---------+-------
  1 | a       | f
  2 | b       | f
  4 | d       | f

L’utilisateur u1 n’a pas le droit de lire directement la table elements mais a le droit d’y accéder via la vue elements_public, uniquement pour les lignes dont le champ prive est à false.

Avec une simple fonction, u1 peut contourner le problème. (Il devra toutefois avoir le droit d’écrire dans un schéma de la base, ce qui est par défaut possible dans public avant la version 15.)

CREATE SCHEMA schemau1;
CREATE OR REPLACE FUNCTION schemau1.abracadabra (integer, text, boolean)
RETURNS bool AS $$
BEGIN
  RAISE NOTICE '% - % - %', $1, $2, $3 ;
  RETURN true;
END$$
LANGUAGE plpgsql
COST 0.0000000000000000000001 ;

CREATE SCHEMA
CREATE FUNCTION

SELECT * FROM elements_public
WHERE schemau1.abracadabra(id, contenu, prive) ;

NOTICE:  1 - a - f
NOTICE:  2 - b - f
NOTICE:  3 - c super prive - t
NOTICE:  4 - d - f
NOTICE:  5 - e prive aussi - t
 id | contenu | prive
----+---------+-------
  1 | a       | f
  2 | b       | f
  4 | d       | f

Les lignes secrètes ne font toujours pas partie du résultat, mais la fonction est capable d’y accéder, et de les afficher. Que s’est-il passé ? Pour comprendre, il suffit de regarder le plan d’exécution de cette requête :

EXPLAIN SELECT * FROM elements_public
WHERE schemau1.abracadabra(id, contenu, prive) ;

                       QUERY PLAN
------------------------------------------------------------
 Seq Scan on elements  (cost=0.00..28.75 rows=208 width=37)
   Filter: (schemau1.abracadabra(id, contenu, prive)
           AND CASE WHEN (CURRENT_USER = 'guillaume'::name) THEN true ELSE (NOT prive) END)

La fonction abracadrabra a un coût délibérément si faible que PostgreSQL l’exécute avant le filtre de la vue : la fonction voit toutes les lignes de la table.

L’option security_barrier permet d’interdire cette optimisation au planificateur :

\c - postgres

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

ALTER VIEW elements_public SET (security_barrier);

ALTER VIEW

\c - u1

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

SELECT * FROM elements_public WHEREschemau1.abracadabra(id, contenu, prive);

NOTICE:  1 - a - f
NOTICE:  2 - b - f
NOTICE:  4 - d - f
 id | contenu | prive
----+---------+-------
  1 | a       | f
  2 | b       | f
  4 | d       | f

La fonction n’affiche plus les données privées. Son plan montre qu’elle filtre par la vue, puis appelle la fonction :

EXPLAIN SELECT * FROM elements_public
WHERE schemau1.abracadabra(id, contenu, prive);

                                         QUERY PLAN                                          
----------------------------------------------------------------------------------
 Subquery Scan on elements_public  (cost=0.00..35.00 rows=208 width=37)
   Filter: schemau1.abracadabra(elements_public.id, elements_public.contenu, elements_public.prive)
   ->  Seq Scan on elements  (cost=0.00..28.75 rows=625 width=37)
         Filter: CASE WHEN (CURRENT_USER = 'guillaume'::name) THEN true ELSE (NOT prive) END

Noter que security_barrier peut avoir un effet négatif sur les performances puisqu’il interdit de « pousser » des critères de recherche dans la vue. Voir aussi cet article, par Robert Haas.

Vue WITH CHECK OPTION :

Écrire à travers une vue permet de modifier les lignes même si la nouvelle valeur les lui rend inaccessibles :

UPDATE elements_public
SET prive = true
WHERE id = 1 ;

UPDATE 1

SELECT * FROM elements_public ;

 id | contenu | prive
----+---------+-------
  2 | b       | f
  4 | d       | f

Pour bloquer ce comportement, la vue peut porter l’option WITH CHECK OPTION :

CREATE OR REPLACE VIEW elements_public
WITH (security_barrier)
AS
  SELECT * FROM elements
  WHERE CASE WHEN current_user = 'guillaume'
  THEN true ELSE NOT prive END
WITH CHECK OPTION ;

\c - u1

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

UPDATE elements_public
SET prive = true
WHERE id = 2 ;

ERROR:  new row violates check option for view "elements_public"
DETAIL:  Failing row contains (prive) = (t).

Les fonctions pg_cancel_backend et pg_terminate_backend sont le plus souvent utilisées. Le paramètre est le numéro du processus auprès de l’OS. À partir de la version 14, pg_terminate_backend comprend un deuxième argument, dont la valeur par défaut est 0. Si cet argument n’est pas indiqué ou vaut 0, la fonction renvoie le booléen true si elle a réussi à envoyer le signal. Ce résultat n’indique donc pas la bonne terminaison du processus serveur visé. À une valeur supérieure à 0, la fonction attend que le processus visé soit arrêté. S’il ne s’est pas arrêté dans le temps indiqué par cette valeur (en millisecondes), la valeur false est renvoyée avec un message de niveau WARNING.

La première permet d’annuler une requête en cours d’exécution. Elle requiert un argument, à savoir le numéro du PID du processus postgres exécutant cette requête. Généralement, l’annulation est immédiate. Voici un exemple de son utilisation.

L’utilisateur, connecté au processus de PID 10901 comme l’indique la fonction pg_backend_pid, exécute une très grosse insertion :

b1=# SELECT pg_backend_pid();

 pg_backend_pid
----------------
          10901

b1=# INSERT INTO t4 SELECT i, 'Ligne '||i
FROM generate_series(2000001, 3000000) AS i;

Supposons qu’on veuille annuler l’exécution de cette requête. Voici comment faire à partir d’une autre connexion :

b1=# SELECT pg_cancel_backend(10901);

 pg_cancel_backend
-------------------
 t

L’utilisateur qui a lancé la requête d’insertion verra ce message apparaître :

ERROR:  canceling statement due to user request

Si la requête du INSERT faisait partie d’une transaction, la transaction elle-même devra se conclure par un ROLLBACK à cause de l’erreur. À noter cependant qu’il n’est pas possible d’annuler une transaction qui n’exécute rien à ce moment. En conséquence, pg_cancel_backend ne suffit pas pour parer à une session en statut idle in transaction.

Il est possible d’aller plus loin en supprimant la connexion d’un utilisateur. Cela se fait avec la fonction pg_terminate_backend qui se manie de la même manière :

b1=# SELECT pid, datname, usename, application_name, state
     FROM pg_stat_activity WHERE backend_type = 'client backend';

 procpid | datname |  usename  | application_name | state
---------+---------+-----------+------------------+--------
   13267 | b1      | u1        | psql             | idle
   10901 | b1      | guillaume | psql             | active

b1=# SELECT pg_terminate_backend(13267);

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

b1=# SELECT pid, datname, usename, application_name, state
     FROM pg_stat_activity WHERE backend_type = 'client backend';

 procpid | datname |  usename  | application_name | state
---------+---------+-----------+------------------+--------
   10901 | b1      | guillaume | psql             | active

L’utilisateur de la session supprimée verra un message d’erreur au prochain ordre qu’il enverra. psql se reconnecte automatiquement mais cela n’est pas forcément le cas d’autres outils client.

b1=# select 1 ;

FATAL:  terminating connection due to administrator command
la connexion au serveur a été coupée de façon inattendue
        Le serveur s‘est peut-être arrêté anormalement avant ou durant le
        traitement de la requête.
La connexion au serveur a été perdue. Tentative de réinitialisation : Succès.
Temps : 7,309 ms

Depuis la ligne de commande du serveur, un kill <pid> (c’est-à-dire kill -SIGTERM ou kill -15) a le même effet qu’un SELECT pg_terminate_backend (<pid>). Cette méthode n’est pas recommandée car il n’y a pas de vérification que vous tuez bien un processus postgres.

N’utilisez jamais kill -9 <pid> (ou kill -SIGKILL), ou (sous Windows) taskkill /f /pid <pid> pour tuer une connexion : l’arrêt est alors brutal, et le processus principal n’a aucun moyen de savoir pourquoi. Pour éviter une corruption de la mémoire partagée, il va arrêter et redémarrer immédiatement tous les processus, déconnectant tous les utilisateurs au passage !

L’utilisation de pg_terminate_backend et pg_cancel_backend n’est disponible que pour les utilisateurs appartenant au même rôle que l’utilisateur à déconnecter, les utilisateurs membres du rôle pg_signal_backend (à partir de la 9.6) et bien sûr les superutilisateurs.

Par défaut, les sessions ne sont pas chiffrées. Les requêtes et les données passent donc en clair sur le réseau. Il est possible de les chiffrer avec SSL, ce qui aura une conséquence négative sur les performances. Il est aussi possible d’utiliser les certificats (au niveau serveur et/ou client) pour augmenter encore la sécurité des connexions.

PostgreSQL ne chiffre pas les données sur disque. Si l’instance complète doit être chiffrée, il est conseillé d’utiliser un système de fichiers qui propose cette fonctionnalité. Attention au fait que cela ne vous protège que contre la récupération des données sur un disque non monté. Quand le disque est monté, les données sont lisibles suivant les règles d’accès d’Unix.

Néanmoins, il existe un module contrib appelé pgcrypto, permettant d’accéder à des fonctions de chiffrement et de hachage. Cela permet de protéger les informations provenant de colonnes spécifiques. Le chiffrement se fait du côté serveur, ce qui sous-entend que l’information est envoyée en clair sur le réseau. Le chiffrement SSL est donc obligatoire dans ce cas.

Même si PostgreSQL est conçu pour être le plus robuste possible, un arrêt brutal est toujours possible, pour des causes externes (perte de courant, plantage du système d’exploitation, corruption de fichier sur le disque…), liées à l’utilisation de PostgreSQL (requêtes menant à une saturation mémoire sur un système mal paramétré…) ou exceptionnellement internes (extension buggée, bug de PostgreSQL).

Dans l’écrasante majorité des cas, le système de journalisation de PostgreSQL vous permettra de redémarrer sans perte de données (sinon ce qui n’a pas été committé). Le rejeu des journaux est souvent si rapide qu’un redémarrage peut passer inaperçu.

Malheureusement, les statistiques d’activités sont perdues lors d’un arrêt brutal (ou d’un arrêt en mode immediate, ou d’une restauration physique). Cela peut provoquer par la suite un retard dans le déclenchement de l’autovacuum et la mise à jour des statistiques. Pour éviter cela, le DBA doit penser à relancer un ordre ANALYZE, ainsi que, dans l’idéal, un ordre VACUUM.

Surtout, il faut comprendre quelle est la cause pour que le problème ne se reproduise pas.

PostgreSQL ne verrouille pas tous les fichiers dès son ouverture. Sans mécanisme de sécurité, il est donc possible de modifier un fichier sans que PostgreSQL s’en rende compte, ce qui aboutit à une corruption silencieuse.

L’apparition des sommes de contrôles (checksums) permet de se prémunir contre des corruptions silencieuses de données. Leur mise en place est fortement recommandée sur une nouvelle instance.

À titre d’exemple, créons une instance sans utiliser les checksums, et une autre qui les utilisera :

$ initdb --pgdata /tmp/sans_checksums/
$ initdb --pgdata /tmp/avec_checksums/ --data-checksums

Insérons une valeur de test, sur chacune des deux instances :

postgres=# CREATE TABLE test (name text);

CREATE TABLE

postgres=# INSERT INTO test (name) VALUES ('toto');

INSERT 0 1

On récupère le chemin du fichier de la table pour aller le corrompre à la main (seul celui sans checksums est montré en exemple).

postgres=# SELECT pg_relation_filepath('test');

 pg_relation_filepath
----------------------
 base/14415/16384

Instance arrêtée (pour ne pas être gêné par le cache), on va s’attacher à corrompre ce fichier, en remplaçant la valeur « toto » par « goto » avec un éditeur hexadécimal :

$ hexedit /tmp/sans_checksums/base/base/14415/16384

Enfin, on peut ensuite exécuter des requêtes sur ces deux instances.

Sans checksums :

postgres=# TABLE test;

 name
------
 goto

Avec checksums :

postgres=# TABLE test;

WARNING:  page verification failed, calculated checksum 29393
          but expected 24228
ERROR:  invalid page in block 0 of relation base/14415/16384

L’outil pg_verify_checksums, disponible depuis la version 11 et renommé pg_checksums en 12, permet de vérifier une instance complète :

$ pg_checksums -D /tmp/avec_checksums

pg_checksums: error: checksum verification failed
  in file "/tmp/avec_checksums/base/14415/16384",
  block 0: calculated checksum 72D1 but block contains 5EA4
Checksum operation completed
Files scanned:  919
Blocks scanned: 3089
Bad checksums:  1
Data checksum version: 1

En pratique, si vous utilisez PostgreSQL 9.5 au moins et si votre processeur supporte les instructions SSE 4.2 (voir dans /proc/cpuinfo), il n’y aura pas d’impact notable en performances. Par contre vous générerez un peu plus de journaux.

L’outil pg_checksums permet aussi d’activer et de désactiver la gestion des sommes de contrôle (instance arrêtée). Ceci n’était pas possible avant la version 12. Il fallait donc le faire dès la création de l’instance.

À titre pédagogique et pour alimenter un rapport pgBadger plus tard, toutes les requêtes vont être tracées.

Dans postgresql.conf, positionner ceci :

log_min_duration_statement = 0
log_temp_files = 0
log_autovacuum_min_duration = 0
lc_messages = 'C'
log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h '

Puis passer à on les paramètres suivants s’ils ne le sont pas déjà :

log_checkpoints
log_connections
log_disconnections
log_lock_waits

Recharger la configuration.

Laisser une fenêtre ouverte pour voir défiler le contenu des traces.

Activer la méthode d’authentification scram-sha-256 dans postgresql.conf si elle n’est pas déjà en place.

Consulter les droits définis dans pg_hba.conf au travers de la vue système pg_hba_file_rules.