Split database ownership from request serving

[bencap]

Summary

The api connects to its own database as mavedb_api, which owns every table. One credential does DDL, request serving, and data migration. This issue splits those responsibilities across distinct roles.

The api can currently drop its own tables, alembic upgrade head from empty does not reproduce prod, and the dump procedures encode role assumptions that live only in the dump generation files.

Target topology

CREATE ROLE mavedb_owner   NOLOGIN;
CREATE ROLE mavedb_system  NOLOGIN;
CREATE ROLE mavedb_definer NOLOGIN;
CREATE ROLE mavedb_migrate LOGIN PASSWORD '...';
CREATE ROLE mavedb_worker  LOGIN PASSWORD '...';
-- mavedb_api already exists; it loses ownership and gains nothing

GRANT mavedb_owner   TO mavedb_migrate;  -- DDL,  via SET ROLE mavedb_owner
GRANT mavedb_system  TO mavedb_migrate;  -- data, via SET ROLE mavedb_system
GRANT mavedb_definer TO mavedb_migrate;  -- creates the elevated objects
GRANT mavedb_system  TO mavedb_worker;   -- jobs see everything
GRANT mavedb_system  TO mavedb_definer;  -- elevated objects see everything
-- mavedb_owner is a member of NOTHING
-- mavedb_api   is a member of NOTHING

GRANT CREATE, USAGE ON SCHEMA public TO mavedb_owner, mavedb_definer;
GRANT USAGE ON SCHEMA public TO mavedb_api;

Constraints

mavedb_api is a member of nothing. This is what makes any failure degrade in the direction of less permission rather than more.

mavedb_owner is a member of nothing. A SECURITY DEFINER function runs as its owner, so one owned by a mavedb_system member returns every row to whoever may execute it, including mavedb_api. Since mavedb_owner owns every object by default, system membership there would elevate every function in the database. Migrations get data access from an explicit SET ROLE mavedb_system instead.

mavedb_definer owns the three elevated objects: app_resolve_access_key(), app_refresh_mat_views(), and the materialized views. The bypass surface is then an enumerable allowlist rather than everything the table owner happens to own.

mavedb_worker is INHERIT (the default). Policy role matching uses USAGE semantics, so a NOINHERIT member matches no TO mavedb_system policy and silently sees public rows only.

Never GRANT mavedb_worker TO mavedb_owner. It is the reflex when a materialized-view ownership transfer fails, and it makes mavedb_owner a transitive member of mavedb_system, reopening the bypass above.

Credentials

Two new credentials are required: mavedb_migrate for database migrations and mavedb_worker for the worker.