PostgreSQL 15 : ouvrir des droits utilisateur nativement, sans extension

Il existe des extensions très utiles pour gérer les permissions PostgreSQL de façon industrielle — ASGARD en est un bon exemple dans l’écosystème de la géomatique publique française. Mais ASGARD impose une philosophie : il faut l’avoir intégré dès le départ, avant l’import des données, pour que ses tables de référence soient cohérentes avec les objets réels de la base.

Quand vos schémas et tables ont déjà été importés via psql avec un rôle owner dédié, et que vous devez ouvrir des accès rapidement et proprement, la gestion native PostgreSQL 15 est la meilleure option. C’est ce que j’ai appliqué sur la base stareau, et voici la méthode complète.

Contexte

#

• Base : stareau, owned par le rôle db_owner (superuser technique)
• Objectif : donner à l’utilisateur db_user un accès lecture + écriture sur l’ensemble des données
• Approche retenue : passer par un groupe (g_stareau_prod) dont db_user est déjà membre, plutôt que de lui accorder des droits directs

La hiérarchie des rôles en place :

db_owner (superuser, owner de la base et des objets)
└── g_stareau_prod (groupe, NOLOGIN)
    └── db_user (utilisateur applicatif)

Pourquoi éviter ASGARD en reprise de base existante ?

#

ASGARD fonctionne en référençant chaque schéma dans sa table asgard.gestion_schema_usr. Si vos objets ont été créés en dehors de ce périmètre — ce qui est le cas d’un import psql classique — ASGARD ne les “connaît” pas. Résultat : les fonctions d’initialisation ne couvrent pas vos schémas réels, et vous passez plus de temps à réconcilier l’état de la base avec l’état d’ASGARD qu’à régler le problème initial.

ASGARD a toute sa place dans une architecture propre, initialisée dès le départ. Pour une reprise en main rapide, le natif est plus sûr et plus lisible.

La méthode native, étape par étape

#

1. Créer le groupe et l’utilisateur

#

Si le groupe et l’utilisateur n’existent pas encore, créez-les en premier. La bonne pratique PostgreSQL est de ne jamais accorder des droits directement à un utilisateur : on accorde les droits à un groupe (rôle sans LOGIN), et on rattache l’utilisateur à ce groupe. Cela simplifie la gestion à mesure que les équipes évoluent.

-- Création du groupe (rôle sans LOGIN)
CREATE ROLE g_stareau_prod WITH
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  INHERIT
  NOLOGIN
  NOREPLICATION
  NOBYPASSRLS
  CONNECTION LIMIT -1;

COMMENT ON ROLE g_stareau_prod IS 'Groupe éditeurs — accès lecture/écriture sur la base stareau.';

-- Création de l'utilisateur applicatif
CREATE ROLE db_user WITH
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  INHERIT
  LOGIN
  NOREPLICATION
  NOBYPASSRLS
  CONNECTION LIMIT -1;

COMMENT ON ROLE db_user IS 'Utilisateur applicatif, membre de g_stareau_prod.';

-- Rattachement de l'utilisateur au groupe
GRANT g_stareau_prod TO db_user;

Grâce à INHERIT, db_user hérite automatiquement de tous les droits accordés à g_stareau_prod — sans qu’il soit nécessaire de faire un SET ROLE explicite à chaque session.

2. Audit préalable : qui possède quoi ?

#

Avant de toucher quoi que ce soit, vérifiez l’état réel de la base.

-- Schémas et leurs owners
SELECT nspname AS schema,
       pg_catalog.pg_get_userbyid(nspowner) AS owner
FROM pg_catalog.pg_namespace
WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY nspname;

-- Tables et leurs owners
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

Cela vous donne une vision claire de ce que db_owner possède, et donc de ce sur quoi vous devez agir.

3. Ouvrir les droits sur un schéma donné

#

Pour chaque schéma à ouvrir, exécutez le bloc suivant en tant que db_owner (ou tout superuser) :

-- Accès à la base
GRANT CONNECT ON DATABASE stareau TO g_stareau_prod;

-- Accès au schéma
GRANT USAGE ON SCHEMA nom_schema TO g_stareau_prod;

-- Droits sur les tables existantes
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA nom_schema
    TO g_stareau_prod;

-- Droits sur les séquences existantes (indispensable pour les colonnes serial/identity)
GRANT USAGE, SELECT, UPDATE
    ON ALL SEQUENCES IN SCHEMA nom_schema
    TO g_stareau_prod;

-- Droits par défaut pour les futurs objets créés par db_owner
ALTER DEFAULT PRIVILEGES FOR ROLE db_owner IN SCHEMA nom_schema
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO g_stareau_prod;

ALTER DEFAULT PRIVILEGES FOR ROLE db_owner IN SCHEMA nom_schema
    GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO g_stareau_prod;

Le ALTER DEFAULT PRIVILEGES est souvent oublié. Sans lui, chaque nouvelle table créée par db_owner sera inaccessible aux autres rôles jusqu’au prochain GRANT manuel.

4. Le coup de maître : SQL dynamique sur tous les schémas

#

Si votre base compte plusieurs schémas — ce qui est presque toujours le cas sur une base métier — répéter le bloc ci-dessus manuellement est fastidieux et risqué. La solution : générer les instructions SQL dynamiquement depuis le catalogue PostgreSQL.

SELECT
    'GRANT USAGE ON SCHEMA ' || nspname || ' TO g_stareau_prod;' || E'\n' ||
    'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA '
        || nspname || ' TO g_stareau_prod;' || E'\n' ||
    'GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA '
        || nspname || ' TO g_stareau_prod;'
FROM pg_namespace
WHERE nspname NOT IN (
    'pg_catalog', 'information_schema', 'pg_toast',
    'asgard', 'asgard_recette'
)
  AND nspname NOT LIKE 'pg_%'
ORDER BY nspname;

Copiez-collez la sortie dans votre client SQL et exécutez. En quelques secondes, tous vos schémas métier sont couverts — sans risque d’en oublier un.

Astuce : pensez à exclure les schémas techniques que vous ne voulez pas exposer. La clause WHERE est votre filtre, adaptez-la à votre contexte.

5. Vérification

#

Testez l’accès en impersonnant db_user depuis votre session admin :

SET ROLE db_user;

-- Test lecture
SELECT * FROM nom_schema.nom_table LIMIT 5;

-- Test écriture (adapter à votre modèle)
-- INSERT INTO nom_schema.nom_table (...) VALUES (...);

RESET ROLE;

Si la lecture et l’écriture fonctionnent sans erreur de permission, c’est bon.

Récapitulatif des commandes clés

#

Et ASGARD dans tout ça ?

#

ASGARD reste pertinent pour une gouvernance long terme, notamment si vous voulez :

• centraliser la politique de droits dans une table de référence
• déléguer la gestion des schémas à des profils non-superusers
• industrialiser les ouvertures d’accès sur plusieurs bases

Si vous souhaitez l’intégrer à terme, la démarche est la suivante :

1. Vérifier l’installation : SELECT * FROM pg_extension WHERE extname = 'asgard';
2. Référencer vos schémas dans asgard.gestion_schema_usr avec les bons rôles
3. Appeler asgard.asgard_initialise_schema() pour qu’ASGARD prenne la main

Mais faites-le sur une base propre, ou dans le cadre d’une migration planifiée — pas en urgence sur une base de production déjà peuplée.

Conclusion

#

La gestion native PostgreSQL, c’est robuste, lisible, et parfaitement adapté à PostgreSQL 15. Quand les données sont déjà là et que le besoin est d’ouvrir des accès proprement, inutile de sur-architecturer. Les GRANT standards, combinés à ALTER DEFAULT PRIVILEGES et au SQL dynamique sur pg_namespace, couvrent l’essentiel — et vous évitent de vous battre avec une extension que vous n’avez pas eu le temps d’intégrer correctement dès le départ.