-- ===================================================================================== -- Veridon Database-per-Service: passwortfreie Rollen- und Schema-Provisionierung -- =====================================================================================

-- Zweck -- Legt fuer die fuenf datenhaltenden Workloads (audit, cmdb, iam, workflow, -- workflow_engine) je einen Owner-/Migrator-/App-Rollensatz sowie das leere, -- ownergefuehrte Anwendungsschema an und KONVERGIERT bestehende Objekte auf den -- exakten Zielzustand (Attribute, Ownership, Mitgliedschaften).

-- Ausfuehrung -- * Einmalig / wiederholbar durch eine bestehende PostgreSQL-Admin-Identitaet. -- Benoetigte Rechte (siehe Runbook): CREATEROLE reicht NICHT allein, weil das Skript -- Schema-Ownership per ALTER SCHEMA ... OWNER TO setzt und PUBLIC-Rechte widerruft. -- Praktisch wird eine Superuser-Identitaet (oder ein Rollen-Admin, der Member der -- Zielrollen ist UND Datenbank-Owner bzw. mit passenden Rechten ausgestattet ist) -- benoetigt. Der heutige Runtime-Login veridon darf dies NICHT ausfuehren. -- * Idempotent und KONVERGENT: ein falsch vorprovisionierter Ausgangszustand -- (z. B. Owner faelschlich LOGIN/SUPERUSER, App als Owner-Member, Schema falschem -- Owner zugeordnet) wird beim erneuten Lauf auf den exakten Zielzustand gebracht. -- * Kompatibel mit PostgreSQL 16 (lokal) und 18.4 (ITU).

-- Bewusst NICHT enthalten -- * KEINE Passwoerter. Username = Rollenname; das Passwort ist ein separat verwaltetes, -- zufaellig generiertes Secret (ITU: zentraler Veridon-Secret-Manager via -- ALTER ROLE ... PASSWORD; lokal: ../local/bootstrap-local-credentials.sql). -- Der Rollenname wird NIEMALS als Passwort verwendet. -- * KEINE Domain-Migrationen; KEINE feingranularen Tabellen-Grants (der Migration-only- -- Runner wendet die Grant-Matrix nach flyway.migrate() an).

-- Rollenmodell (Design 4.2) -- Owner : NOLOGIN, NOSUPERUSER, NOCREATEDB, NOCREATEROLE, NOREPLICATION. -- Migrator : LOGIN NOINHERIT, ausschliesslich Mitglied des eigenen Owners, CONN LIMIT 2. -- App : LOGIN NOINHERIT, KEIN Owner-Mitglied, nur USAGE + (per Default-Priv) DML. -- =====================================================================================

\set ON_ERROR_STOP on

DO provision DECLARE rec RECORD; v_db text := current_database(); BEGIN FOR rec IN SELECT * FROM (VALUES ('audit', 'veridon_audit_owner', 'veridon_audit_migrator', 'veridon_audit_app', 20), ('cmdb', 'veridon_cmdb_owner', 'veridon_cmdb_migrator', 'veridon_cmdb_app', 20), ('iam', 'veridon_iam_owner', 'veridon_iam_migrator', 'veridon_iam_app', 20), ('workflow', 'veridon_workflow_owner', 'veridon_workflow_migrator', 'veridon_workflow_app', 40), ('workflow_engine', 'veridon_workflow_engine_owner', 'veridon_workflow_engine_migrator', 'veridon_workflow_engine_app', 60) ) AS t(schema_name, owner_role, migrator_role, app_role, app_conn_limit) LOOP -- ---- Rollen sicherstellen (CREATE nur falls fehlend) ------------------------ IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = rec.owner_role) THEN EXECUTE format('CREATE ROLE %I NOLOGIN', rec.owner_role); END IF; IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = rec.migrator_role) THEN EXECUTE format('CREATE ROLE %I LOGIN', rec.migrator_role); END IF; IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = rec.app_role) THEN EXECUTE format('CREATE ROLE %I LOGIN', rec.app_role); END IF;

    -- ---- KONVERGENZ: exakte Attribute unbedingt setzen (auch fuer Bestandsrollen) --
    EXECUTE format(
        'ALTER ROLE %I NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION',
        rec.owner_role);
    EXECUTE format(
        'ALTER ROLE %I LOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT 2',
        rec.migrator_role);
    EXECUTE format(
        'ALTER ROLE %I LOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT %s',
        rec.app_role, rec.app_conn_limit);

    -- ---- Mitgliedschaften: Migrator IST Mitglied seines Owners, App NICHT ---------
    EXECUTE format('GRANT %I TO %I', rec.owner_role, rec.migrator_role);

    -- ---- Schema ownergefuehrt (Ownership konvergieren) --------------------------
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I AUTHORIZATION %I',
                   rec.schema_name, rec.owner_role);
    EXECUTE format('ALTER SCHEMA %I OWNER TO %I', rec.schema_name, rec.owner_role);
    EXECUTE format('REVOKE ALL ON SCHEMA %I FROM PUBLIC', rec.schema_name);
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', rec.schema_name, rec.app_role);

    -- ---- Datenbank-CONNECT (environment-agnostisch) ----------------------------
    EXECUTE format('GRANT CONNECT ON DATABASE %I TO %I', v_db, rec.migrator_role);
    EXECUTE format('GRANT CONNECT ON DATABASE %I TO %I', v_db, rec.app_role);

    -- ---- Baseline-Default-Privileges: kuenftige Owner-Objekte -> App-DML ---------
    EXECUTE format(
        'ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA %I '
        'GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO %I',
        rec.owner_role, rec.schema_name, rec.app_role);
    EXECUTE format(
        'ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA %I '
        'GRANT USAGE, SELECT ON SEQUENCES TO %I',
        rec.owner_role, rec.schema_name, rec.app_role);

    -- ---- Rollen-Timeouts + search_path (Design 4.7) -----------------------------
    EXECUTE format('ALTER ROLE %I IN DATABASE %I SET search_path = %I',
                   rec.app_role, v_db, rec.schema_name);
    EXECUTE format('ALTER ROLE %I IN DATABASE %I SET statement_timeout = %L',
                   rec.app_role, v_db, '15s');
    EXECUTE format('ALTER ROLE %I IN DATABASE %I SET lock_timeout = %L',
                   rec.app_role, v_db, '3s');
    EXECUTE format('ALTER ROLE %I IN DATABASE %I SET idle_in_transaction_session_timeout = %L',
                   rec.app_role, v_db, '60s');
    EXECUTE format('ALTER ROLE %I IN DATABASE %I SET search_path = %I',
                   rec.migrator_role, v_db, rec.schema_name);

    RAISE NOTICE 'converged schema % (owner=%, migrator=%, app=%, app_conn_limit=%)',
        rec.schema_name, rec.owner_role, rec.migrator_role, rec.app_role, rec.app_conn_limit;
END LOOP;

END provision;

-- ---- KONVERGENZ: fremde/unerlaubte Owner-Mitgliedschaften defensiv entfernen --------- -- App-Rollen duerfen KEIN Owner-Mitglied sein; Migrator-Rollen NUR ihres eigenen Owners. DO cleanup DECLARE r RECORD; BEGIN FOR r IN SELECT member_role.rolname AS grantee, owner_role.rolname AS granted_owner FROM pg_auth_members m JOIN pg_roles member_role ON member_role.oid = m.member JOIN pg_roles owner_role ON owner_role.oid = m.roleid WHERE owner_role.rolname LIKE 'veridon_%_owner' AND ( member_role.rolname LIKE 'veridon_%_app' OR (member_role.rolname LIKE 'veridon_%_migrator' AND replace(member_role.rolname, '_migrator', '') <> replace(owner_role.rolname, '_owner', '')) ) LOOP EXECUTE format('REVOKE %I FROM %I', r.granted_owner, r.grantee); RAISE NOTICE 'revoked unerlaubte Owner-Mitgliedschaft: % war Member von %', r.grantee, r.granted_owner; END LOOP; END cleanup;

-- Datenbankweite Haertung: kein CREATE fuer PUBLIC im Schema public. REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Read-only Bestaetigung (gibt keine Secretwerte aus): exakter Zielzustand je Rolle. SELECT r.rolname, r.rolcanlogin AS can_login, r.rolinherit AS inherit, r.rolsuper AS superuser, r.rolcreatedb AS createdb, r.rolcreaterole AS createrole, r.rolreplication AS replication, r.rolconnlimit AS conn_limit FROM pg_roles r WHERE r.rolname LIKE 'veridon_%_owner' OR r.rolname LIKE 'veridon_%_migrator' OR r.rolname LIKE 'veridon_%_app' ORDER BY r.rolname;