Construire une hiérarchie de rôles à moindre privilège (GRANT + ALTER DEFAULT PRIVILEGES)

Commande

-- Group roles (NOLOGIN): object owner, read-only, read-write
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
GRANT app_readonly TO app_readwrite;
CREATE SCHEMA app AUTHORIZATION app_owner;

-- Restrict PUBLIC's default privileges
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Existing objects
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

-- Objects app_owner will create IN THE FUTURE
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- Login users get privileges only through group membership
CREATE ROLE web_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readwrite;
CREATE ROLE report_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readonly;

-- Configured default privileges (psql: \ddp)
SELECT pg_get_userbyid(d.defaclrole) AS owner_role,
       n.nspname AS schema_name,
       d.defaclobjtype AS object_type,
       d.defaclacl
FROM pg_default_acl d
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;

Ce que fait la commande

Construit une hiérarchie de rôles de groupe propriétaire, lecture seule et lecture-écriture ; accorde les bons privilèges sur les tables/séquences existantes et futures, et n'autorise les utilisateurs applicatifs que par appartenance à un groupe.

Quand l'utiliser

Lors de la mise en place d'un nouveau schéma applicatif, pour corriger les applications qui se connectent en superutilisateur ou en propriétaire des tables, et pour simplifier les privilèges avant un audit.

Attention

ALTER DEFAULT PRIVILEGES ne s'applique qu'aux objets créés ULTÉRIEUREMENT par le rôle nommé dans FOR ROLE ; si l'outil de migration crée les tables sous un autre utilisateur, les privilèges ne sont pas appliqués, exécutez donc le DDL avec SET ROLE app_owner. GRANT ... ON ALL TABLES ne couvre que les objets existant à ce moment-là. Les bases créées en PG15+ n'accordent déjà plus CREATE sur le schéma public à PUBLIC ; les bases mises à niveau depuis des versions plus anciennes nécessitent le REVOKE. REVOKE ALL ON DATABASE ... FROM PUBLIC retire aussi CONNECT et TEMP ; accordez-les séparément aux autres rôles qui en ont besoin. Utilisez psql \password au lieu de mettre les mots de passe en ligne de commande (ils restent hors des journaux et de l'historique).