Gestion des permissions sous PostgreSQL

Vallée de Munster, 28 juin 2024

La gestion des permissions sous PostgreSQL peut parfois être la source de certaines (mauvaises) surprises. Nous vous proposons un article qui a pour objectif de donner quelques informations sur la gestion des droits, et certaines problématiques rencontrées par certains clients qui souhaitent permettre l’accès à une base à plusieurs rôles.

Mise en place

Imaginons qu’il est demandé à un DBA (owner) de donner l’accès à un schéma (v dans nos exemples) d’une base (db2) à plusieurs rôles daniel, michel et owner.

Notre DBA va dans un premier temps initialiser une base, le schéma et les rôles. Pour ce faire, il peut utiliser les commandes suivantes :

$ cat init.sql
CREATE ROLE owner WITH LOGIN;
CREATE DATABASE db2 OWNER owner;
CREATE ROLE daniel WITH LOGIN;
CREATE ROLE michel WITH LOGIN;

$ psql -f init.sql
CREATE ROLE
CREATE DATABASE
CREATE ROLE
CREATE ROLE
CREATE ROLE
GRANT ROLE

Le rôle owner peut maintenant créer un schéma au sein de la base db2 (car il en est le propriétaire) :

owner@db2=> CREATE schema v;
CREATE SCHEMA

Et y créer une table qui va être utilisée pour nos tests :

owner@db2=> CREATE TABLE v.weather(city VARCHAR(50));
CREATE TABLE

Pour l’attribution des permissions, notre DBA a plusieurs options. Nous vous proposons d’en voir quelques-unes dans la suite de l’article.

Donner des permissions sur les objets existants

Après avoir validé ces accès, owner (qui est propriétaire de la base) va créer des tables ou d’autres objets, et donner (avec la commande GRANT) des accès aux autres rôles en fonction de ces besoins.

Par exemple, pour donner le droit de SELECT sur notre table weather, owner peut utiliser la commande suivante :

owner@db2=> GRANT SELECT ON TABLE v.weather TO daniel;
GRANT

Lorsque le rôle daniel essaye de lire le contenu de la table v.weather, PostgreSQL va lui renvoyer l’erreur suivante :

daniel@db2=> SELECT * from v.weather;
ERROR:  permission denied for schema v
LINE 1: SELECT * from v.weather

En effet, en complément du droit de SELECT sur la table, le propriétaire du schéma (ou un SUPERUSER) doit aussi permettre l’utilisation de ce dernier. Cette modification se fait de la façon suivante :

owner@db2=> GRANT USAGE ON SCHEMA v TO daniel,michel;
GRANT

Après cette manipulation, l’utilisateur daniel est maintenant en mesure de consulter la table v.weather :

daniel@db2=> SELECT * from v.weather;
 city 
------
(0 rows)

Nonobstant, l’ajout (INSERT) reste bien évidement (et heureusement) impossible pour ce rôle :

daniel@db2=> INSERT INTO v.weather VALUES('x');
ERROR:  permission denied for table weather

Donner des permissions sur plusieurs objets dans un schéma

Comme nous l’avons vu, la commande GRANT permet de donner des droits sur un ou plusieurs objets précis, en spécifiant le type de droit et le schéma contenant lesdits objets.

Dans certains cas, on souhaite donner un ou plusieurs privilèges pour l’ensemble des tables d’un schéma. (Par exemple, permettre à un ou plusieurs rôles de lire les tables d’une application qui utilise un schéma spécifique.) Pour répondre à ce besoin, PostgreSQL propose les options du style ALL <OBJECT> IN SCHEMA, où OBJECT peut prendre différentes valeurs (par exemple TABLES, FUNCTIONS, …).

Imaginons que notre DBA owner souhaite permettre à daniel de consulter toutes les tables du schéma v, il peut donc lancer la requête suivante (on ajoute au passage une table pour l’exercice) :

owner@db2=> CREATE TABLE v.country(name VARCHAR(50));
CREATE TABLE
owner@db2=> GRANT SELECT ON ALL TABLES IN SCHEMA v TO daniel;
GRANT

On constate que l’ajout des permissions fonctionne sans surprise :

daniel@db2=> SELECT * from v.weather;
 name
------
(0 rows)
daniel@db2=> SELECT * from v.country;
 name
------
(0 rows)

Mais que se passe-t-il lors de l’ajout d’une table dans notre schéma ? Le rôle daniel pourra-t-il consulter une nouvelle table ?

Ajoutons une table v.province :

owner@db2=> CREATE TABLE v.province(name VARCHAR(50));
CREATE TABLE

Et observons le comportement lors d’une tentative de lecture :

daniel@db2=> SELECT * FROM v.province;
ERROR:  permission denied for table province

Cette erreur semble surprenante et incohérente. Surtout au regard de la documentation qui ne semble pas loquace concernant ce fonctionnement déroutant :

There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, functions, and procedures. ALL TABLES also affects views and foreign tables, just like the specific-object GRANT command. ALL FUNCTIONS also affects aggregate and window functions, but not procedures, again just like the specific-object GRANT command. Use ALL ROUTINES to include procedures.

En réalité, la commande GRANT s’applique uniquement aux objets déjà en place. Pour modifier les permissions des objets à venir, il est nécessaire de modifier les privilèges par défaut en utilisant la commande ALTER DEFAULT PRIVILEGES. En s’appuyant uniquement sur la commande GRANT, les permissions devront évoluer à mesure des évolutions (de contenu) du schéma.

Donner des accès sur tous les objets (existants et à venir)

Vu notre récente expérience avec l’ajout d’une table dans un schéma, et la non-accessibilité de cette dernière par un rôle qui, pourtant, s’était vu accorder le droit en lecture sur toutes les tables du schéma (avec ALL TABLES IN <mon_schema>), voyons comment pallier ce problème via la commande ALTER DEFAULT PRIVILEGES et les implications possibles lorsque plusieurs rôles ont accès à un schéma.

Imaginons que notre DBA owner souhaite donner des accès à une table (ou d’autres objets) déjà existante, mais aussi aux tables (objets) futures. Pour cela l’utilisateur propriétaire (ou un SUPERUSER) doit utiliser une combinaison de commandes GRANT et ALTER DEFAULT PRIVILEGES.

Par exemple, owner peut donner des privilèges (SELECT pour les tables existantes et à venir dans le schéma v) avec les commandes suivantes :

owner@db2=> GRANT SELECT ON ALL TABLES IN SCHEMA v TO daniel,michel;
owner@db2=> ALTER DEFAULT PRIVILEGES IN SCHEMA v
    GRANT SELECT ON TABLES TO daniel,michel;
ALTER DEFAULT PRIVILEGES

On constate que les rôles daniel et michel peuvent lire le contenu de cette table :

daniel@db2=> SELECT * FROM v.weather;
 city
------
(0 rows)

michel@db2=> SELECT * FROM v.weather;
 city
------
(0 rows)

Mais aussi des nouvelles tables ajoutées par owner :

owner@db2=> CREATE TABLE v.pays(city VARCHAR(50));
CREATE TABLE

michel@db2=> SELECT * FROM v.pays;
 city
------
(0 rows)

Sur la base de ces tests, nous pouvons légitimement nous demander quel va être le comportement si notre DBA owner donne la possibilité à michel d’écrire dans notre schéma v.

Pour permettre la création de tables par michel dans le schéma v, owner doit lancer la commande suivante :

owner@db2=> GRANT CREATE ON SCHEMA v TO michel;

On constate ensuite que michel peut (toujours) lire le contenu de la table v.weather, mais aussi créer une nouvelle table dans le schéma v.

michel@db2=> SELECT * FROM v.weather;
 city
------
(0 rows)

michel@db2=> CREATE TABLE v.capital(name VARCHAR(50));
CREATE TABLE

Nous pouvons essayer de lire le contenu de cette nouvelle table avec le rôle daniel :

daniel@db2=> SELECT * FROM v.capital;
ERROR:  permission denied for table capital

Et constater de façon déroutante, que notre requête SELECT dysfonctionne pour un problème de permission non accordée sur la table. Cela peut paraître surprenant étant donné que owner a bien modifié les privilèges par défaut pour permettre le SELECT sur les tables du schéma v à daniel et michel.

En réalité cet accès n’est pas autorisé et ce pour plusieurs raisons :

• Le rôle owner n’est pas propriétaire de la table v.capital

  owner@db2=> \dt v.*;
           List of relations
   Schema |   Name   | Type  | Owner
  --------+----------+-------+--------
   v      | capital  | table | michel
   v      | country  | table | owner
   v      | province | table | owner
   v      | weather  | table | owner
  (4 rows)
  

• ALTER DEFAULT PRIVILEGES utilise de façon implicite le paramètre FOR ROLE avec pour valeur (par défaut) le rôle connecté à la base (dans notre cas, il s’agissait de owner). Ce paramètre permet d’indiquer qu’on modifie les privilèges par défaut pour les objets créés uniquement par ce rôle. Dans notre cas, lors de la modification des privilèges par défaut par owner, on aurait pu expliciter cette valeur comme ceci :

  owner@db2=> ALTER DEFAULT PRIVILEGES FOR ROLE owner IN SCHEMA v
      GRANT SELECT ON TABLES TO daniel,michel;
  

Pour permettre à daniel de lire la table v.capital ajoutée par michel, il existe plusieurs options :

• soit michel ou un SUPERUSER change les privilèges sur la table, ou l’ensemble des tables du schéma, après création de ladite table en utilisant la commande GRANT ;
• soit le rôle michel (ou toujours un SUPERUSER) modifie les privilèges par défaut avant de créer sa table pour permettre aux autres rôles de consulter ses tables en exécutant un ALTER DEFAULT PRIVILEGES avec ou sans l’option FOR ROLE en fonction de qui est connecté à la base.

À noter que, même si l’utilisateur owner ne peut pas lire la table capital créée par michel :

owner@db2=> SELECT * FROM v.capital;
ERROR:  permission denied for table capital

il reste propriétaire du schéma v et peut donc supprimer cette table :

owner@db2=> DROP TABLE v.capital;
DROP TABLE

Groupes de rôles

Une autre solution pour faciliter la gestion des permissions est d’utiliser l’appartenance d’un rôle à un autre rôle (équivalent à un ou des groupes). Par exemple, on pourrait imaginer un rôle write avec la permission de créer des objets dans le schéma v, et un second rôle read avec le droit de lecture (SELECT) sur les tables.

Sur base de ces rôles, on pourrait donner des permissions à nos DBA michel et daniel sans modifier directement leurs permissions.

Par exemple pour créer les rôles (groupes) :

postgres@~=# CREATE ROLE write NOLOGIN;
postgres@~=# CREATE ROLE read NOLOGIN;

owner@db2=> GRANT USAGE ON SCHEMA v TO read;
GRANT
owner@db2=> GRANT CREATE,USAGE ON SCHEMA v TO write;
GRANT
owner@db2=> ALTER DEFAULT PRIVILEGES IN SCHEMA v
    GRANT SELECT ON TABLES TO read;

On peut maintenant ajouter nos rôles dans les rôles (groupes) read et write en fonction de nos besoins :

postgres@~=#  GRANT write TO michel;
GRANT
postgres@~=#  GRANT read TO daniel;
GRANT

Cette solution, qui simplifie la vie des DBA, vient aussi avec certaines contraintes. Par exemple, dans certaines situations les utilisateurs doivent utiliser la commande SET ROLE pour modifier le rôle connecté, afin de profiter des avantages de cette approche.

Par exemple :

michel@db2=> SET ROLE write; 
michel@db2=> CREATE TABLE v.state(city VARCHAR(50));
michel@db2=> \d
         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 v      | state   | table | write

Si michel oublie d’utiliser la commande SET ROLE à la création d’un objet, il ne sera pas utilisable (SELECT dans notre cas) par les membres du rôle read, car la table appartiendra au rôle michel, et non au rôle write. Pour changer ce fonctionnement, il est possible d’utiliser l’option INHERIT (ou NOINHERIT) lors de la création des rôles : elle force en quelque sorte l’utilisation de la commande SET ROLE pour créer ou lire des objets (tables) avec le bon rôle.

Il est possible de combiner le paramètre NOINHERIT avec l’utilisation des commandes suivantes afin de forcer le passage par un rôle spécifique lors de la connexion à une base :

postgres@~=# ALTER ROLE michel IN DATABASE db2 SET ROLE write;
ALTER
postgres@~=# ALTER ROLE daniel IN DATABASE db2 SET ROLE read;
ALTER

Ce qui aura pour effet de forcer l’utilisation du rôle write pour michel lors de la connexion à la base, et du rôle read pour daniel.

Bien que cette solution semble plus élégante que distribuer des permissions à un grand nombre de rôles, elle est aussi source de quelques contraintes dont il faut tenir compte lors de la conception de notre stratégie de définition des permissions :

• il n’est pas possible de prendre en charge des profils différents en fonction du schéma, cette option semble uniquement paramétrable au niveau de la base ;
• la propriété des objets est « masquée » par le ou les rôles de rôles. Dans notre exemple, lorsque michel crée une table, elle sera vue comme appartenant au rôle write.

Conclusion

La gestion des permissions sous PostgreSQL va se complexifier, au fur et à mesure que vos instances prendront une place de plus en plus importante au sein de votre système d’information. Notre SGBD favori offre un large éventail d’options pour paramétrer et définir les accès et permissions. De par la complexité inhérente à toutes ces possibilités, ne pas tomber dans un piège ou sur une configuration inadaptée demande rigueur et maitrise de tous ces concepts.

Nous vous proposerons bientôt un second article pour présenter quelques solutions afin de simplifier l’automatisation de l’attribution des permissions. Nous y évoquerons entre autre les rôles prédéfinis pg_read_all_data et pg_write_all_data disponibles depuis la version 14 de PostgreSQL.