Dans des tables utilisateurs, on peut avoir besoin de stocker une référence aux rôles PostgreSQL, par exemple pour représenter des droits d’accès à des fonctionnalités applicatives, ou des méta-données associées aux comptes. Se pose alors la question de ce qu’on peut utiliser comme référence pour désigner un rôle.

Bien sûr un rôle en tant qu’objet dans le catalogue a une clef unique, et même deux. Il a premièrement un OID (entier 32 bits non signé) attribué automatiquement, et deuxièmement le nom du rôle qui est aussi évidemment unique. Mais l’OID n’est pas portable d’une instance à l’autre, et un nom de rôle peut aussi changer dans le dos de l’applicatif, si bien qu’on peut vouloir chercher une troisième voie. Regardons ça de plus près.

Le stockage des rôles

Un rôle est stocké avec ses propriétés principales dans la table système pg_catalog.pg_authid, qui est partagée par toutes les bases:

postgres=# \d pg_authid
                        Table "pg_catalog.pg_authid"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 oid            | oid                      |           | not null | 
 rolname        | name                     |           | not null | 
 rolsuper       | boolean                  |           | not null | 
 rolinherit     | boolean                  |           | not null | 
 rolcreaterole  | boolean                  |           | not null | 
 rolcreatedb    | boolean                  |           | not null | 
 rolcanlogin    | boolean                  |           | not null | 
 rolreplication | boolean                  |           | not null | 
 rolbypassrls   | boolean                  |           | not null | 
 rolconnlimit   | integer                  |           | not null | 
 rolpassword    | text                     | C         |          | 
 rolvaliduntil  | timestamp with time zone |           |          | 
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"

Avec une version 11 ou inférieure, on ne verra pas la première colonne oid visible ici, mais elle est quand même là, et avec un index unique pg_authid_oid_index qui porte dessus. La sortie ci-dessus est faite avec PostgreSQL 12, dans laquelle l’OID est devenue une colonne normale.

Généralement on accèdera plutôt aux rôles via la vue pg_roles, qui porte aussi cet OID mais qui diffère en ce que:

  • elle n’est pas réservée aux super-utilisateurs.

  • rolpassword est forcé en dur à '********' pour ne rien révéler des mots de passe.

  • elle a en plus rolconfig avec les variables de configuration optionnelles associées au rôle, qu’on va justement utiliser pour ajouter des méta-données un peu plus loin dans ce billet.

postgres=# \d pg_roles
                         View "pg_catalog.pg_roles"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           |          | 
 rolsuper       | boolean                  |           |          | 
 rolinherit     | boolean                  |           |          | 
 rolcreaterole  | boolean                  |           |          | 
 rolcreatedb    | boolean                  |           |          | 
 rolcanlogin    | boolean                  |           |          | 
 rolreplication | boolean                  |           |          | 
 rolconnlimit   | integer                  |           |          | 
 rolpassword    | text                     |           |          | 
 rolvaliduntil  | timestamp with time zone |           |          | 
 rolbypassrls   | boolean                  |           |          | 
 rolconfig      | text[]                   | C         |          | 
 oid            | oid                      |           |          |

Pas de clef étrangère possible

Si pg_authid était une table normale, on pourrait écrire une déclaration s’y référant via une clef étrangère, du style:

CREATE TABLE nomtable(u_id oid REFERENCES pg_authid(oid), ...);

Mais comme c’est une table système, ce n’est pas possible. Si on essaie, on obtient:

ERREUR: droit refusé : « pg_authid » est un catalogue système

Et même si on pouvait faire des contraintes d’intégrité référentielles vers des tables systèmes, il est probable que pg_authid serait exclue pour une autre raison: c’est une table partagée par toutes les bases, donc à chaque fois qu’une ligne change, il faudrait que Postgres vérifie dans toutes les bases pour tester la validité de la contrainte, et ce ne serait pas compatible avec son architecture étanche où une connexion établie sur une base donnée n’a un accès direct qu’à cette seule base.

Le login comme clef de référence?

Le login ou nom du rôle est conservé lors d’un pg_restore, et il est unique, mais est-ce une bonne clef primaire?

Un renommage peut être fait avec un ordre SQL du genre:

ALTER ROLE nom_role RENAME to nouveau_nom;

qui peut être exécuté de n’importe quelle base.

Cet ordre ne déclenche pas de trigger, parce que comme répondu dans la discussion Event trigger and CREATE/ALTER ROLE/USER sur pgsql-hackers, un trigger est rattaché à une seule base alors qu’un rôle est global à l’instance:

If you were to create an event trigger in database A, then a user gets created in database B, your function would not be invoked, which becomes a problem.

Pour être tranquille, il faudrait interdire les changements de login, par principe. Mais imposer qu’un compte ne changera jamais de login n’est pas forcément réaliste quand des comptes sont associés à des personnes physiques. Une personne dont le login est son nom de famille peut vouloir le changer pour des raisons liées à son état civil. Ou encore quand un utilisateur hérite du compte nominatif d’un prédécesseur, difficile de lui imposer d’utiliser ad aeternam le nom de l’autre personne.

Les OIDs ne sont pas portables entre instances

Quand on exporte une instance avec pg_dumpall, les rôles sont exportés en commandes de création du style:

CREATE ROLE foo PASSWORD '...' ;

L’ennui est qu’il n’y a rien dans cette commande qui conserverait l’OID. Au moment de la restauration, les rôles seront donc certainement recréés avec des OIDs différents, qui ne correspondront plus aux références stockées dans les tables utilisateurs.

On aura d’ailleurs le même problème avec toute forme de réplication autre que réplication physique, où le réplicat est un clone de la source à tous points de vue.

Les OIDs via le type regrole comme référence?

Cette possibilité qui m’a été suggérée sur Twitter permet de faire face élégamment au problème de changement des OIDs par pg_restore, ainsi qu’à un éventuel renommage du rôle. regrole est un type alias d’OID, c’est-à-dire qu’au stockage c’est physiquement un OID, mais lorsqu’on y accède en lecture ou écriture, le nom du rôle remplace dynamiquement la valeur numérique de l’OID. La documentation v11 exprime ça en ces termes:

Les types alias d’OID ne disposent pas d’opérations propres à l’exception des routines spécialisées de saisie et d’affichage. Ces routines acceptent et affichent les noms symboliques des objets système, plutôt que la valeur numérique brute que le type oid utilise.

Voyons un exemple:

CREATE TABLE users(
 id regrole,
 fonction text,
 permissions text[]
);

En entrée dans la table, on peut utiliser le nom du rôle en tant que littéral:

CREATE ROLE ope_front;
INSERT INTO users VALUES ('ope_front', 'Opérateur Front Office', '{saisie}');

En sortie, il ressort aussi en littéral:

SELECT * FROM users;

    id     |        fonction        | permissions 
-----------+------------------------+-------------
 ope_front | Opérateur Front Office | {saisie}

Et c’est pareil en export avec pg_dump, où les données de la table ressortiront sous cette forme:

COPY public.users (id, fonction, permissions) FROM stdin;
ope_front	Opérateur Front Office	{saisie}
\.

L’intérêt est que si ce dump est rejoué dans une instance où l’utilisateur a été recréé, l’OID qui se retrouvera dans users.id sera correct contrairement à ce qui se passerait si l’OID était en format numérique dans l’export.

Par ailleurs, un éventuel changement de nom du rôle est transparent pour notre table users, ce qui est un avantage par rapport à une colonne de type text.

Par exemple:

ALTER USER ope_front RENAME TO operateur_front;
SELECT * FROM users;

       id        |        fonction        | permissions 
-----------------+------------------------+-------------
 operateur_front | Opérateur Front Office | {saisie}

SYSID comme clef de référence?

CREATE ROLE a un paramètre SYSID qui semble avoir été pensé au départ pour être un identifiant se référant à l’extérieur de Postgres. Mais on peut refermer cette piste tout de suite car il est obsolète et ignoré si on essaie de l’utiliser:

=# CREATE USER test SYSID 42;
NOTICE:  SYSID can no longer be specified
CREATE ROLE

Un paramètre de configuration comme clef de référence?

On peut associer à un rôle des valeurs pour certains paramètres de configuration qu’on trouve dans postgresql.conf, comme work_mem, default_transaction_isolation, etc. via ALTER ROLE, par exemple:

ALTER ROLE web_user SET work_mem TO '128MB';

Depuis PostgreSQL 9.3, cette possibilité est étendue à n’importe quel paramètre personnalisé déclaré avec un préfixe (avant il fallait une déclaration préalable dans custom_variable_classes). En fait le préfixe est plutôt prévu pour être le nom d’une extension, mais ça fonctionne très bien aussi sans extension, si bien qu’on peut faire, par exemple, en étant super-utilisateur:

ALTER ROLE nom_role SET users.id = '759f5abb-64b1-4dbc-8b71-f1d7bd18ad85';

Les choix de users comme espace de nom et id comme nom de paramètre sont arbitraires. Tous ces paramètres sont de toute manière exportés par pg_dumpall sous forme de commandes ALTER ROLE.

Les paramètres de configuration sont accessibles dans la colonne pg_roles.rolconfig de type text[], contenant un tableau avec des chaînes au format nom=valeur. Pour extraire le nom et la valeur, on peut utiliser la fonction plpgsql ci-dessous inspirée de la fonction ParseLongOption() dans le code de Postgres en langage C:

CREATE FUNCTION parse_option(string text, name OUT text,  value OUT text)
  RETURNS record
AS $$
declare
 p int := strpos(string, '=');
begin
  if (p > 0) then
    name := replace(left(string, p-1), '-', '_');
    value := substr(string, p+1);
  else
    name := replace(string, '-', '_');
    value := NULL;
  end if;
end
$$ LANGUAGE plpgsql immutable strict;

On peut donc obtenir l’ID personnalisé d’un rôle à partir de son login avec une requête du style:

SELECT o.value
 FROM pg_roles,
   unnest(rolconfig) as c(x),
   parse_option(c.x) as o
WHERE rolname = :'nom_du_role'
AND o.name = 'users.id';

Si c’est juste pour le compte avec lequel on est connecté, pas la peine de faire si compliqué, un SHOW suffira:

SHOW users.id; -- ou SELECT pg_catalog.current_setting('users.id')

Si une session a besoin d’accéder répétitivement à cette information sur tous les rôles, elle peut avoir intérêt à créer une table temporaire pour l’avoir en accès rapide, ce qui peut se faire via une requête de ce style:

CREATE TEMP TABLE cache_users AS
 SELECT r.oid, r.rolname, o.value AS user_id
 FROM pg_roles,
   unnest(rolconfig) as c(x),
   parse_option(c.x) as o
 WHERE o.name='users.id';

Conclusion

En utilisant le type regrole, on peut éviter les principaux inconvénients d’une référence directe à l’OID ou au nom du rôle.

En attribuant à des rôles des IDs synthétiques stockés sous forme de paramètres de configuration, on atteint les buts suivants:

  • ils sont conservés par pg_dumpall / pg_restore.
  • ils sont accessibles à l’identique dans toutes les bases, comme pg_roles.
  • ils peuvent être vraiment immuables, puisqu’on n’a jamais aucune raison de changer un ID arbitraire.

En revanche, dans les tables utilisateurs, avoir une vraie contrainte d’intégrité référentielle vers cette information reste impossible, quelle que soit l’option retenue.