-- Runtime / migration / admin credential separation for the JPA persistence platform.
-- Design §36; enforced at startup by PostgreSqlRuntimeRoleVerifier + DatabaseRolePolicy.
--
-- The separation is what makes "Flyway owns schema change" enforceable rather than aspirational.
-- If the application's own credential cannot execute DDL, then no code path, no library, and no
-- injected statement can alter the schema at runtime — regardless of what the application intended.
--
-- Run as a superuser once per database. Replace the placeholder passwords with values from the
-- deployment's secret store; they are intentionally not committed.

-- 1. The schema the application owns. Owned by the migration role, not the runtime role.
create schema if not exists app authorization app_migration;

-- 2. Roles.
--    app_migration : owns the schema, applies Flyway migrations. DDL.
--    app_runtime   : the application's credential. DML only, no DDL, no CREATE.
--    app_admin     : J4 operations — COPY, backfill, maintenance. Never used by request paths.
create role app_migration login password 'REPLACE_FROM_SECRET_STORE';
create role app_runtime   login password 'REPLACE_FROM_SECRET_STORE';
create role app_admin     login password 'REPLACE_FROM_SECRET_STORE';

-- 3. Revoke the PUBLIC grants that make the checks in DatabaseRolePolicy necessary.
--    Before PostgreSQL 15, PUBLIC held CREATE on the public schema — which is how an unprivileged
--    role ends up able to plant an object that shadows a real one through search_path.
revoke all on database current_database() from public;
revoke create on schema public from public;

-- 4. Runtime: read and write rows in the application schema. Nothing else.
grant connect on database current_database() to app_runtime;
grant usage on schema app to app_runtime;
grant select, insert, update, delete on all tables in schema app to app_runtime;
grant usage, select on all sequences in schema app to app_runtime;

-- Tables created by future migrations must inherit the same grants, or the first deployment after
-- a new table silently fails at runtime with a permission error.
alter default privileges for role app_migration in schema app
    grant select, insert, update, delete on tables to app_runtime;
alter default privileges for role app_migration in schema app
    grant usage, select on sequences to app_runtime;

-- 5. Explicitly deny the two privileges the startup verifier checks for.
revoke create on schema app from app_runtime;
revoke create on database current_database() from app_runtime;

-- 6. Admin: bulk operations under an audited identity, still without schema ownership.
grant connect on database current_database() to app_admin;
grant usage on schema app to app_admin;
grant select, insert, update, delete on all tables in schema app to app_admin;
alter default privileges for role app_migration in schema app
    grant select, insert, update, delete on tables to app_admin;

-- 7. Pin the runtime search_path so an unqualified name cannot resolve anywhere unexpected.
alter role app_runtime set search_path = app, pg_catalog;
alter role app_admin   set search_path = app, pg_catalog;