24 — Security, Roles & Permissions

-- A login role (a "user") — has LOGIN + PASSWORD
CREATE ROLE alice LOGIN PASSWORD 'secret_passphrase_here';

-- A group role (no LOGIN) — exists only for privilege inheritance
CREATE ROLE analytics_team;

-- Add alice to the group (alice INHERITS the group's privileges by default)
GRANT analytics_team TO alice;

-- Grant privileges to the GROUP, not to alice directly
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analytics_team;
-- Now alice inherits SELECT on reporting tables via analytics_team.
-- To remove alice's access: REVOKE analytics_team FROM alice (one command,
-- not per-table). This is O(teams) management, not O(users × tables).

CREATE ROLE app_user LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
-- LOGIN: can connect
-- SUPERUSER: bypasses ALL permission checks (dangerous — avoid for apps)
-- CREATEDB: can create databases
-- CREATEROLE: can create/drop roles
-- REPLICATION: can stream replication
-- BYPASSRLS: bypasses Row-Level Security (dangerous for app roles)
-- CONNECTION LIMIT n: max concurrent connections for this role
-- VALID UNTIL 'timestamp': password expiry

ALTER ROLE app_user CONNECTION LIMIT 50;          -- pool size cap
ALTER ROLE alice VALID UNTIL '2026-12-31';        -- password expires
ALTER ROLE alice PASSWORD 'new_passphrase';       -- rotate password

-- Grant on a specific table
GRANT SELECT, INSERT, UPDATE ON orders TO app_role;
-- app_role can read, insert, and update orders, but NOT delete or truncate

-- Grant with GRANT OPTION: the grantee can grant this privilege to others
GRANT SELECT ON orders TO alice WITH GRANT OPTION;
-- alice can now: GRANT SELECT ON orders TO bob;
-- Use sparingly — this spreads authorization power unpredictably.

-- Grant on all current tables in a schema (snapshot — applies to EXISTING tables only)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_team;

-- Grant on all sequences in a schema
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;
-- USAGE: needed for nextval(). SELECT: needed for currval().
-- Without USAGE on the sequence, INSERT into a table with a SERIAL/IDENTITY
-- column fails — the user can't call nextval on the sequence.

-- Revoke
REVOKE INSERT ON orders FROM app_role;
REVOKE ALL ON orders FROM app_role;           -- remove all privileges
REVOKE SELECT ON orders FROM alice CASCADE;   -- also revokes grants alice made

-- GRANT ON ALL TABLES applies to EXISTING tables only. New tables created
-- after the grant are NOT covered — the creator owns them, and no one else
-- has access until you grant explicitly. This is a common "new table is
-- invisible to the app" bug.

-- DEFAULT PRIVILEGES: grants that apply to FUTURE objects created by a
-- specified role. The most important security pattern in PostgreSQL.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO app_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_role;

-- Now any table created in `public` by the role (specified implicitly as
-- current_user) automatically gets these grants. No more "forgot to grant
-- on the new table" outages.

-- Per-creator: DEFAULT PRIVILEGES are scoped to the creator.
-- If alice and bob both create tables, you need DEFAULT PRIVILEGES
-- for EACH creator:
ALTER DEFAULT PRIVILEGES FOR ROLE alice IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;
ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;
-- Or: use a single migration role that creates all tables, and set
-- DEFAULT PRIVILEGES for that role only.

-- Grant access to only some columns (protect PII like salary, ssn)
GRANT SELECT (id, name, email, department) ON employees TO analytics_team;
-- analytics_team can SELECT id, name, email, department but NOT salary, ssn.
-- SELECT * FROM employees → ERROR: permission denied for table employees
-- SELECT id, name FROM employees → OK

-- Column-level grants are fiddly:
-- 1. SELECT * is denied (it includes un-granted columns)
-- 2. Views are usually a cleaner alternative (expose only safe columns)
-- 3. Column grants don't restrict ROWS — combine with RLS for row + column

-- Enable RLS on the orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- By default: NO policies exist → ALL access is DENIED (deny-by-default).
-- Without a policy, even the table owner can't SELECT (unless FORCE RLS
-- is off — the owner bypasses by default, see below).

-- A SELECT policy: users can see only their own orders
CREATE POLICY own_orders ON orders
  FOR SELECT
  TO authenticated_users                    -- applies to this role only
  USING (user_id = current_setting('app.user_id')::bigint);
  -- USING: the predicate added to SELECT/UPDATE/DELETE.
  -- Every SELECT on orders gets: WHERE user_id = current_setting('app.user_id')
  -- The user CANNOT see rows where user_id doesn't match — enforced by the DB.

-- An INSERT policy: users can insert only their own orders
CREATE POLICY insert_own_orders ON orders
  FOR INSERT
  TO authenticated_users
  WITH CHECK (user_id = current_setting('app.user_id')::bigint);
  -- WITH CHECK: the predicate validated on INSERT/UPDATE.
  -- A user can't insert an order with a different user_id.

-- An UPDATE policy: users can update only their own orders
CREATE POLICY update_own_orders ON orders
  FOR UPDATE
  TO authenticated_users
  USING (user_id = current_setting('app.user_id')::bigint)         -- can SEE the row
  WITH CHECK (user_id = current_setting('app.user_id')::bigint);   -- can SET these values
  -- USING: which rows are visible (the user can only update rows they can see)
  -- WITH CHECK: what the new values can be (can't reassign to another user)

-- A DELETE policy: users can delete only their own orders
CREATE POLICY delete_own_orders ON orders
  FOR DELETE
  TO authenticated_users
  USING (user_id = current_setting('app.user_id')::bigint);
  -- DELETE uses USING (which rows are visible for deletion). No WITH CHECK.

-- Multiple permissive policies (default) combine with OR:
-- a row visible to ANY permissive policy is visible.
CREATE POLICY p1 ON orders FOR SELECT USING (status = 'active');
CREATE POLICY p2 ON orders FOR SELECT USING (user_id = 42);
-- Visible rows: status='active' OR user_id=42

-- AS RESTRICTIVE policies combine with AND:
-- a row must pass ALL restrictive policies (in addition to the permissive OR).
CREATE POLICY restrict_active_tenant ON orders
  FOR SELECT
  AS RESTRICTIVE
  USING (tenant_id = current_setting('app.tenant')::bigint);
-- Visible rows: (status='active' OR user_id=42) AND tenant_id=current_tenant
-- Use RESTRICTIVE for guard-rail policies (tenant isolation, soft-delete filter)
-- that should AND with everything else.

-- By DEFAULT, the table OWNER bypasses RLS policies.
-- Superusers ALWAYS bypass RLS (can't be changed).
-- To enforce RLS on the owner too:
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
-- Now the owner is subject to policies. Superusers still bypass.

-- BYPASSRLS attribute: a role with BYPASSRLS ignores all RLS policies
CREATE ROLE admin_role LOGIN BYPASSRLS;
-- Don't grant BYPASSRLS to app roles — it defeats the purpose of RLS.
-- Reserve for migration/admin roles that need to see all rows.

-- ── 1. Group roles for team-based access ────────────────────────
CREATE ROLE tenant_app_role;        -- app connections (read/write tenant data)
CREATE ROLE tenant_readonly;        -- analytics (read-only)
CREATE ROLE tenant_migrator;        -- schema migrations (DDL, no BYPASSRLS)

-- ── 2. The app role (non-owner, non-superuser, no BYPASSRLS) ────
-- CRITICAL: the app role must NOT own the tables (owners bypass RLS)
-- and must NOT be a superuser or have BYPASSRLS.
CREATE ROLE app_connection LOGIN PASSWORD '...' NOSUPERUSER NOBYPASSRLS;
GRANT tenant_app_role TO app_connection;
-- app_connection inherits tenant_app_role's privileges.

-- ── 3. Schema and tenant-scoped tables ──────────────────────────
CREATE SCHEMA IF NOT EXISTS tenant_data;

CREATE TABLE tenant_data.orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id BIGINT NOT NULL,                -- the tenant scope column
  customer_id BIGINT NOT NULL,
  amount NUMERIC NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE tenant_data.customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  name TEXT NOT NULL,
  email TEXT
);

-- Index the tenant_id column (RLS policies filter on it → needs an index)
CREATE INDEX orders_tenant_idx ON tenant_data.orders(tenant_id);
CREATE INDEX customers_tenant_idx ON tenant_data.customers(tenant_id);

-- ── 4. Enable RLS on all tenant-scoped tables ───────────────────
ALTER TABLE tenant_data.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE tenant_data.customers ENABLE ROW LEVEL SECURITY;
-- Force RLS on the owner too (in case the migrator role owns the tables):
ALTER TABLE tenant_data.orders FORCE ROW LEVEL SECURITY;
ALTER TABLE tenant_data.customers FORCE ROW LEVEL SECURITY;

-- ── 5. RLS policy: tenant isolation via session variable ────────
-- The app sets app.current_tenant at the start of each request.
-- RLS ensures no query can leak data across tenants.
CREATE POLICY tenant_isolation ON tenant_data.orders
  FOR ALL
  TO tenant_app_role
  USING (tenant_id = current_setting('app.current_tenant', true)::bigint)
  WITH CHECK (tenant_id = current_setting('app.current_tenant', true)::bigint);
-- current_setting('app.current_tenant', true) returns NULL if unset
-- (the `true` arg suppresses the error). NULL::bigint → NULL → no rows match
-- → deny-by-default when the tenant isn't set. Safe.

CREATE POLICY tenant_isolation_customers ON tenant_data.customers
  FOR ALL
  TO tenant_app_role
  USING (tenant_id = current_setting('app.current_tenant', true)::bigint)
  WITH CHECK (tenant_id = current_setting('app.current_tenant', true)::bigint);

-- ── 6. Table-level grants ───────────────────────────────────────
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tenant_data TO tenant_app_role;
GRANT SELECT ON ALL TABLES IN SCHEMA tenant_data TO tenant_readonly;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA tenant_data TO tenant_app_role;

-- ── 7. DEFAULT PRIVILEGES for future tables ─────────────────────
-- The migrator role creates future tables. Set defaults so the app role
-- automatically gets access to new tables.
ALTER DEFAULT PRIVILEGES FOR ROLE tenant_migrator IN SCHEMA tenant_data
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO tenant_app_role;
ALTER DEFAULT PRIVILEGES FOR ROLE tenant_migrator IN SCHEMA tenant_data
  GRANT SELECT ON TABLES TO tenant_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE tenant_migrator IN SCHEMA tenant_data
  GRANT USAGE, SELECT ON SEQUENCES TO tenant_app_role;

-- ── 8. Usage: the app sets the tenant per request ───────────────
-- App code (after authenticating the user, determining their tenant):
--   SET app.current_tenant = '42';
--   SELECT * FROM tenant_data.orders;  -- only sees orders where tenant_id=42
--   INSERT INTO tenant_data.orders (tenant_id, ...) VALUES (42, ...);  -- OK
--   INSERT INTO tenant_data.orders (tenant_id, ...) VALUES (99, ...);  -- ERROR (WITH CHECK fails)
-- Even if the app has a bug (e.g., missing WHERE tenant_id=?), RLS prevents
-- cross-tenant data access. The database enforces it, not the application.

-- ❌ WRONG: granting everything to everyone
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO PUBLIC;
-- ALL PRIVILEGES includes TRUNCATE, REFERENCES, TRIGGER — none of which
-- the app needs. PUBLIC means EVERY role. This is how data gets deleted
-- by a role that should only read.

-- ✅ RIGHT: grant exactly what's needed, to specific roles
GRANT SELECT, INSERT, UPDATE ON orders TO app_role;  -- no DELETE, no TRUNCATE
GRANT SELECT ON orders TO analytics_role;             -- read-only
-- Principle of least privilege: if the app doesn't need DELETE, don't grant it.

-- ❌ WRONG: app connects as superuser
-- Connection string: postgresql://superuser:pass@host/db
-- A single SQL injection → full database compromise.
-- Superuser bypasses RLS, all permission checks, can DROP databases,
-- can read pg_shadow (password hashes), can execute OS commands via
-- COPY TO PROGRAM, can create SECURITY DEFINER functions as any role.

-- ✅ RIGHT: app connects as a dedicated role with minimal privileges
CREATE ROLE app_role LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
GRANT CONNECT ON DATABASE mydb TO app_role;
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO app_role;
-- If compromised: the attacker can read/insert/update app tables.
-- They CANNOT drop the database, read password hashes, bypass RLS,
-- or escalate to other roles. Blast radius is limited.

-- PUBLIC is a pseudo-role that represents ALL roles.
-- PostgreSQL grants some access to PUBLIC by default:
-- - CONNECT on databases
-- - USAGE on public schema (pre-PG 15)
-- - EXECUTE on all functions

-- Revoke unwanted PUBLIC grants:
REVOKE ALL ON DATABASE mydb FROM PUBLIC;          -- restrict who can connect
GRANT CONNECT ON DATABASE mydb TO app_role;       -- re-grant to specific roles

-- PG 15+: public schema no longer grants CREATE to PUBLIC by default.
-- Pre-PG 15: revoke CREATE to prevent users from creating objects in public:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Functions are EXECUTE-able by PUBLIC by default. For SECURITY DEFINER
-- functions (which run as the owner), revoke PUBLIC and grant explicitly:
REVOKE EXECUTE ON FUNCTION get_my_salary(BIGINT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION get_my_salary(BIGINT) TO authenticated_users;

-- List privileges on a table (\dp in psql)
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'orders'
ORDER BY grantee, privilege_type;

-- List all roles and their memberships
SELECT r.rolname AS role, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid      -- the group role
JOIN pg_roles m ON m.oid = am.member;     -- the member role

-- Check if a role has a specific privilege
SELECT has_table_privilege('app_role', 'orders', 'SELECT');  -- true/false

-- List RLS policies on a table
SELECT polname, polcmd, polqual, polwithcheck
FROM pg_policy
WHERE polrelid = 'orders'::regclass;

-- DROP ROLE fails if the role owns objects.
-- Reassign ownership first, then drop:
REASSIGN OWNED BY alice TO bob;    -- transfer alice's objects to bob
DROP OWNED BY alice;               -- drop alice's remaining privileges/objects
DROP ROLE alice;                   -- now safe to drop

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant', true)::bigint);

-- The app connects as the table owner (a single "app" role that owns all tables)
-- and sets app.current_tenant per request.
-- Connection: postgresql://app_owner:pass@host/db