Role topology and idempotent bootstrap script

[bencap]

Create the role topology specified in #826. The script lives in mavedb-api, because the grants track the schema they apply to, and both docker-compose-local.yml and tests/conftest.py need to run it.

A script, not a migration

Roles are cluster objects, and CREATE ROLE needs privileges the migration role should not have. A fresh RDS instance, or a restore into a new cluster, has no roles no matter how many migrations ran. Run as master.

Two parts

Part	When	Where it can run
1	before any schema exists	docker-entrypoint-initdb.d, or by hand on a new cluster
2	after alembic upgrade head	deploy step, or by hand

Part 1 — roles, membership, privileges

• Create the six roles from Split database ownership from request serving #826 if and only if they are absent, and never set or reset a password.
• Grant exactly the memberships Split database ownership from request serving #826 specifies, and nothing else. mavedb_worker must be INHERIT (the default).
• Abort the run if mavedb_api or mavedb_owner is a member of mavedb_system, or if mavedb_worker cannot use it.
• Grant CREATE, USAGE on schema public to mavedb_owner and mavedb_definer; USAGE alone to mavedb_api and mavedb_system.
• Set default privileges FOR ROLE mavedb_owner.
• Make mavedb_migrate create objects as mavedb_owner.

alembic/env.py sets the connection URL and nothing else, so nothing currently establishes an owner role for DDL. The per-role default privileges cover alembic and the 8 manual migrations without touching either, which is why it is preferred to a SET ROLE in env.py. Whichever is chosen, this issue is not done until a migration run produces objects owned by mavedb_owner.

Part 2 — elevated objects and ownership transfer

• Create the elevated objects owned by mavedb_definer, via SET ROLE so ownership lands without a transient grant. REVOKE EXECUTE ... FROM PUBLIC on each: PostgreSQL grants EXECUTE to PUBLIC on every new function by default, so omitting this makes the bypass callable by everyone. Then grant app_resolve_access_key to mavedb_api only and app_refresh_mat_views to mavedb_worker only. SETOF on the access-key function is load-bearing — a non-SETOF composite return yields one all-NULL row on a miss.

• Move ownership off mavedb_api in two steps, in this order. REASSIGN OWNED BY mavedb_api TO mavedb_owner, then re-own every materialized view to mavedb_definer. REASSIGN OWNED sweeps materialized views along with the tables, and the definer-owned refresh wrapper then fails:

  t  -> mavedb_owner
  mv -> mavedb_owner
  SELECT app_refresh_mat_views();
  ERROR:  must be owner of materialized view mv
  

  The MV step must come after the reassign, or the reassign undoes it. Definer ownership is also required because an MV created or refreshed by a non-system role populates from filtered rows, silently.

• Grant DML on existing tables and sequences to mavedb_api and mavedb_system. Default privileges only reach objects created after they are set, so this is what covers everything already in the database.

• Grant SET ON PARAMETER session_replication_role. The target role is an open question. Under FORCE, mavedb_owner cannot insert a private row (new row violates row-level security policy), so the only role that can load a full dump is one that can SET ROLE mavedb_system — and under that role a parameter grant made to mavedb_owner does not apply (permission denied to set parameter). deployment/scripts/dump-public-data.sh emits that statement and seeds private fixtures. Resolve this together with the dump and restore procedure, not in isolation.

Why mavedb_definer and not mavedb_system

mavedb_system would need CREATE ON SCHEMA public to own the elevated objects, and mavedb_worker inherits mavedb_system. A transient grant-create-revoke leaves mavedb_system holding CREATE if anything raises between the statements, and no boot check inspects schema privileges — #824's C7 checks function ownership. A standing NOLOGIN role never opens that window. The cost is one role, not one credential.

Applying it

REASSIGN OWNED BY takes ACCESS EXCLUSIVE per object and will queue behind a long query on variants, blocking everything behind it. Set lock_timeout and retry rather than waiting.

There is no clean reverse. Rollback is REASSIGN OWNED BY mavedb_owner TO mavedb_api plus dropping the new roles, so verify before the window closes: the api can read and write, migrations can DDL and produce owner-owned objects, and the worker can read a private row and refresh a materialized view.

Acceptance criteria

• Part 1 is idempotent: run 1 on a cluster with zero mavedb% roles creates all six; runs 2–4 exit 0 with no changes.
• Part 1 completes on a cluster with no application schema.
• Part 2 completes against a migrated schema, and is also idempotent.
• Creates mavedb_api when absent, leaves it untouched when present, and never sets or resets a password.
• The three in-script assertions fail the run when the topology is wrong — verified by granting mavedb_system to mavedb_owner and by setting mavedb_worker NOINHERIT; both must abort with an actionable message.
• Post-apply: pg_has_role('mavedb_api','mavedb_system','MEMBER') is false, and the same for mavedb_owner. MEMBER, not USAGE — a NOINHERIT member reports USAGE false while retaining the ability to SET ROLE.
• Post-apply: pg_has_role('mavedb_worker','mavedb_system','USAGE') is true, and mavedb_worker is INHERIT. USAGE, not MEMBER — policy role matching uses USAGE semantics.
• A test asserts that a table created by a migration run is owned by mavedb_owner and readable by mavedb_api.
• A test asserts the serving role can reach every table in pg_tables.
• A test asserts every materialized view is owned by mavedb_definer after a full bootstrap, and that app_refresh_mat_views() succeeds when called as mavedb_worker. Run the ownership transfer first — the failure only appears after REASSIGN OWNED.
• A test asserts mavedb_worker cannot CREATE in schema public and cannot SET ROLE to mavedb_owner or mavedb_definer. Run it on a real connection as the worker: SET ROLE is unrestricted inside a superuser session.
• mavedb_definer owns exactly the allowlisted objects and the materialized views, and nothing else.
• Every elevated function has EXECUTE revoked from PUBLIC and granted to exactly one role.
• Applied to staging, then prod, with lock_timeout set. Rollback verified on staging before the prod window.
• The session_replication_role grant target is resolved together with the dump and restore procedure.