PostgreSQL Privilege Management: Roles, Default Privileges, and Least Privilege

-- Create base roles (not login roles)
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE app_admin;

-- Grant privileges to roles
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_admin;

-- Create login users and assign roles
CREATE USER reporting_user WITH LOGIN PASSWORD 'r3port!';
GRANT readonly TO reporting_user;

CREATE USER app_service WITH LOGIN PASSWORD 'srv_p@ss';
GRANT readwrite TO app_service;

-- Without this, new tables won't inherit role grants
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

-- Also for sequences (needed for INSERT with serial/bigserial)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO readwrite;

-- PostgreSQL 14 and earlier: public has CREATE on public schema by default
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydb FROM PUBLIC;

-- PostgreSQL 15+: public schema CREATE revoked by default

-- Check table privileges
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY table_name, grantee;

-- Check role memberships
SELECT r.rolname AS role, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
ORDER BY role, member;

-- Each app gets its own schema
CREATE SCHEMA app1;
CREATE SCHEMA app2;

CREATE USER app1_user WITH LOGIN;
GRANT USAGE ON SCHEMA app1 TO app1_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app1 TO app1_user;
-- app1_user cannot see app2 schema at all