Roles & Permissions

-- ===== Create =====
CREATE ROLE app_dev WITH LOGIN PASSWORD 'devpw';      -- a 'user' (can log in)
CREATE ROLE readonly;                                  -- a 'group' (NOLOGIN by default)

-- ===== Grant =====
GRANT CONNECT ON DATABASE app TO app_dev;
GRANT USAGE ON SCHEMA public TO app_dev;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_dev;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_dev;

-- ===== Default privileges (for tables created in future) =====
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO readonly;

-- ===== Group membership =====
GRANT readonly TO app_reporter;
-- Now app_reporter inherits readonly's privileges.

REVOKE readonly FROM app_reporter;

-- ===== Role attributes =====
ALTER ROLE app_dev WITH SUPERUSER;           -- danger; almost never give to app
ALTER ROLE app_dev WITH BYPASSRLS;
ALTER ROLE app_dev WITH CREATEDB;
ALTER ROLE app_dev WITH CREATEROLE;
ALTER ROLE app_dev WITH REPLICATION;
ALTER ROLE app_dev WITH NOLOGIN;             -- pure group

-- ===== Password rotation =====
ALTER ROLE app_dev WITH PASSWORD 'new_pw';
-- Or use SCRAM-SHA-256 (default in PG 14+).

-- ===== Inspect =====
\du                                          -- psql: list roles
SELECT rolname, rolsuper, rolinherit, rolcreatedb, rolcanlogin
FROM pg_roles;

SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'app_dev';

-- ===== Drop =====
-- Must reassign owned objects first:
REASSIGN OWNED BY old_user TO new_user;
DROP OWNED BY old_user;
DROP ROLE old_user;

-- ===== Row-Level Security (RLS) =====
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int);

-- In session:
SET app.tenant_id = 42;
SELECT * FROM orders;   -- only rows where tenant_id = 42

-- Bypass RLS for admin connections only:
GRANT BYPASSRLS TO admin_role;

-- ===== Suggested role tiers =====
-- ddl_admin    full DDL on the schema; deploy-time only
-- app_writer   SELECT INSERT UPDATE DELETE on app tables
-- app_reader   SELECT only
-- backup       SELECT + USAGE on sequences

-- ===== Connection limits =====
ALTER ROLE app_dev CONNECTION LIMIT 50;

-- ===== Session settings =====
ALTER ROLE app_writer SET statement_timeout = '5s';
ALTER ROLE app_writer SET lock_timeout = '2s';
ALTER ROLE app_writer SET idle_in_transaction_session_timeout = '60s';

-- ===== Patterns to internalise =====
-- - One role per workload, not per human (use IAM auth / OIDC for humans)
-- - ALTER DEFAULT PRIVILEGES so new tables get the right grants
-- - statement_timeout per app role -> bounds runaway queries
-- - RLS for multi-tenant isolation; test with EXPLAIN to verify policies fire

-- ===== Pitfalls =====
-- - Granting SUPERUSER to app roles
-- - Forgetting ALTER DEFAULT PRIVILEGES — new tables silently lack grants
-- - Multi-tenant without RLS -> code bugs leak across tenants
-- - Storing the password in pg_hba.conf rather than in a vault

CREATE ROLE app_user LOGIN PASSWORD 's3cret';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;