-- db/bootstrap/00_roles.sql
--
-- Cluster-level role provisioning. Run ONCE by a superuser / rds_superuser / azure_pg_admin BEFORE
-- the first `goose up`. Roles are cluster-global, so this is intentionally NOT a goose migration
-- (goose tracks per-database state). Idempotent via DO guards.
--
-- Three-role split (docs/STACK_DECISIONS.md §2 + the EEO segregation boundary):
--   * missionmatch_migrator   — owns tables, runs DDL, BYPASSRLS (so goose creates/alters freely).
--   * missionmatch_app        — the application runtime role: NOBYPASSRLS + non-owner, so RLS is
--                               ALWAYS enforced. Has NO privilege on the `eeo` schema (segregation).
--   * missionmatch_compliance — the ONLY role that may read/write EEO demographics (adverse-impact
--                               reporting). NOBYPASSRLS. Runs on its own pool, separate from matching.
--
-- Passwords are placeholders; set real secrets out-of-band (Secrets Manager / Key Vault) and
-- `ALTER ROLE ... PASSWORD`. On managed PG, grant rds_superuser/azure_pg_admin instead of SUPERUSER.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'missionmatch_migrator') THEN
        CREATE ROLE missionmatch_migrator LOGIN BYPASSRLS PASSWORD '[REDACTED, rotated 29 Jul 2026]_MIGRATOR';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'missionmatch_app') THEN
        CREATE ROLE missionmatch_app LOGIN NOBYPASSRLS PASSWORD '[REDACTED, rotated 29 Jul 2026]_APP';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'missionmatch_compliance') THEN
        CREATE ROLE missionmatch_compliance LOGIN NOBYPASSRLS PASSWORD '[REDACTED, rotated 29 Jul 2026]_COMPLIANCE';
    END IF;
END
$$;

-- The migrator owns the schema objects; the other roles only use what they are explicitly granted.
-- (Run goose connected as missionmatch_migrator so it owns every object it creates.)
GRANT USAGE ON SCHEMA public TO missionmatch_app, missionmatch_compliance;