Construir una jerarquía de roles de mínimo privilegio (GRANT + ALTER DEFAULT PRIVILEGES)

Comando

-- 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;

Qué hace

Construye una jerarquía de roles de grupo propietario, de solo lectura y de lectura-escritura; concede los privilegios adecuados sobre tablas/secuencias existentes y futuras, y autoriza a los usuarios de aplicación solo mediante la pertenencia a grupos.

Cuándo usarlo

Al configurar un esquema de aplicación nuevo, al corregir aplicaciones que se conectan como superusuario o como propietario de las tablas, y para simplificar los privilegios antes de una auditoría.

Precaución

ALTER DEFAULT PRIVILEGES solo se aplica a los objetos creados DESPUÉS por el rol indicado en FOR ROLE; si la herramienta de migración crea las tablas con otro usuario, los privilegios no se aplican, así que ejecute el DDL con SET ROLE app_owner. GRANT ... ON ALL TABLES solo cubre los objetos que existen en ese momento. Las bases de datos creadas en PG15+ ya no conceden CREATE sobre el esquema public a PUBLIC; las bases de datos actualizadas desde versiones antiguas necesitan el REVOKE. REVOKE ALL ON DATABASE ... FROM PUBLIC también quita CONNECT y TEMP; concédalos por separado a los demás roles que los necesiten. Use \password de psql en lugar de poner las contraseñas en la línea de comandos (así no quedan en los logs ni en el historial).