Rollenhierarchie nach Least Privilege aufbauen (GRANT + ALTER DEFAULT PRIVILEGES)

Befehl

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

Was es tut

Baut eine Hierarchie aus Owner-, Read-only- und Read-write-Gruppenrollen auf; vergibt die passenden Rechte auf bestehende und künftige Tabellen/Sequences und berechtigt Anwendungsbenutzer nur über Gruppenmitgliedschaft.

Wann einsetzen

Beim Einrichten eines neuen Anwendungsschemas, beim Korrigieren von Anwendungen, die sich als Superuser oder Tabelleneigentümer verbinden, und zum Vereinfachen der Rechte vor einem Audit.

Vorsicht

ALTER DEFAULT PRIVILEGES gilt nur für Objekte, die SPÄTER von der in FOR ROLE genannten Rolle angelegt werden; legt das Migrationstool Tabellen als anderer Benutzer an, werden die Rechte nicht angewendet, also DDL mit SET ROLE app_owner ausführen. GRANT ... ON ALL TABLES umfasst nur Objekte, die in diesem Moment existieren. Ab PG15 angelegte Datenbanken gewähren PUBLIC ohnehin kein CREATE auf dem Schema public; von älteren Versionen aktualisierte Datenbanken brauchen das REVOKE. REVOKE ALL ON DATABASE ... FROM PUBLIC entzieht auch CONNECT und TEMP; gewähre sie anderen Rollen, die sie brauchen, separat. Nutze psql \password, statt Passwörter auf die Kommandozeile zu schreiben (sie bleiben so aus Logs und Historie heraus).