Build a least-privilege role hierarchy (GRANT + ALTER DEFAULT PRIVILEGES)

Command

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

What it does

Builds a hierarchy of owner, read-only and read-write group roles; grants the right privileges on existing and future tables/sequences, and authorizes application users only through group membership.

When to use it

When setting up a new application schema, fixing applications that connect as superuser or table owner, and simplifying privileges before an audit.

Caution

ALTER DEFAULT PRIVILEGES applies only to objects created LATER by the role named in FOR ROLE; if the migration tool creates tables as a different user, the privileges are not applied, so run DDL with SET ROLE app_owner. GRANT ... ON ALL TABLES covers only objects that exist at that moment. Databases created on PG15+ already do not grant CREATE on the public schema to PUBLIC; databases upgraded from older versions need the REVOKE. REVOKE ALL ON DATABASE ... FROM PUBLIC also removes CONNECT and TEMP; grant them separately to other roles that need them. Use psql \password instead of putting passwords on the command line (they stay out of logs and history).