Give the worker an explicit application principal

[bencap]

Why the worker needs its own principal

Both worker compose targets pass the same DB_USERNAME as the api (prod-worker/docker-compose.yml:12). Once that role is demoted to a non-owner with RLS applied, the worker can no longer use it — it would only reach public data.

Scope

• mavedb_worker as a login role, INHERIT, a member of mavedb_system, with DML inherited through that membership and no ownership. Created in Role topology and idempotent bootstrap script #827's bootstrap script.
• A distinct credential in both staging-worker and prod-worker compose targets, and in the local stack.
• app_refresh_mat_views(), a SECURITY DEFINER function owned by mavedb_definer, wrapping REFRESH MATERIALIZED VIEW. Refresh requires ownership; there is no grantable REFRESH privilege in PG 15. This is the only ownership-dependent operation in the worker.
• The worker's boot assertion from Startup enforcement assertion for RLS liveness #824: positively assert it can read a known-private canary row, using pg_has_role(..., 'USAGE').

The refresh wrapper must be owned by mavedb_definer, not mavedb_owner

mavedb_owner is a member of nothing, so a function owned by it runs with filtered visibility. The failure is silent: the materialized view refreshes successfully and is populated from public rows only. Measured 1 of 2 rows, no error. A silently-truncated published_variants_materialized_view feeds unauthenticated statistics routes.

The materialized views themselves must also be owned by mavedb_definer for the same reason — an MV created while SET ROLE mavedb_owner captures filtered rows at creation time.

Verified: REFRESH MATERIALIZED VIEW CONCURRENTLY works inside a SECURITY DEFINER plpgsql function called by the worker within an explicit transaction — the shape job_manager.db actually produces — and the worker retains no CREATE on schema public.

refresh_all_mat_views(db) in db/view.py becomes a call to that function rather than issuing REFRESH directly.

Acceptance criteria

• mavedb_worker exists, is INHERIT, is a mavedb_system member, and owns nothing.
• Both worker compose targets and the local stack use the worker credential, not mavedb_api's.
• REFRESH MATERIALIZED VIEW runs through a SECURITY DEFINER wrapper owned by mavedb_definer.
• Materialized views are owned by mavedb_definer.
• A test asserts the refreshed MV contains rows derived from a private score set's published variants — the negative case here is a full view, not an error.
• A test asserts the worker can read a private score set and its calibrations.
• A test asserts the worker cannot perform DDL and cannot SET ROLE to mavedb_owner or mavedb_definer, run on a real connection as the worker rather than via SET ROLE from a superuser session.
• Worker boot fails if the private canary row is not visible.