Write RLS policies for every guarded table as migrations #809

Summary

This issue writes the RLS policies for every guarded table, as ordinary migrations. Nothing enforces them yet: a policy does nothing until its table is enabled, which the pilot (#825) and the rollout (#878) do. So this work can land without changing behavior.

Terms like guarded table, delegation and carve-out are defined in the glossary on #808.

What to build

1. A migration adding the two helper functions.
2. Policy migrations for every table on Work out which tables need RLS policies, and what they'll cost #832's list, shaped as below. Each guarded table gets a business policy and a worker policy.
3. Indexes: contributor_id on scoreset_contributors, experiment_contributors and experiment_set_contributors, whose primary keys lead with the parent id, and score_calibrations.score_set_id.
4. Remove the modified_by_id clause from the calibration read rule in lib/permissions/score_calibration.py, so Python matches the policy.
5. A policy snapshot, src/mavedb/db/policies.sql, so the current rules are readable in one place. A script dumps them from a replayed database, and CI fails when the committed file differs. See below.

The helpers

CREATE FUNCTION app_user_id() RETURNS integer LANGUAGE sql STABLE PARALLEL SAFE
  AS $$ SELECT nullif(current_setting('app.user_id', true), '')::integer $$;

CREATE FUNCTION app_roles() RETURNS text[] LANGUAGE sql STABLE PARALLEL SAFE
  AS $$ SELECT coalesce(nullif(current_setting('app.active_roles', true), '')::text[], '{}') $$;

They're ordinary functions, not SECURITY DEFINER, so they bypass nothing. Postgres inlines them, so a policy costs the same as with the expressions written out. nullif turns the empty setting a pooled connection keeps after a commit into "nobody", instead of a cast error.

Tables with their own rule

CREATE POLICY scoresets_rule ON scoresets FOR ALL USING (
     private = false
  OR created_by_id = app_user_id()
  OR app_roles() && '{admin,mapper}'
  OR id IN (SELECT sc.scoreset_id FROM scoreset_contributors sc
              JOIN contributors c ON c.id = sc.contributor_id
              JOIN users u ON u.username = c.orcid_id
             WHERE u.id = app_user_id())
) WITH CHECK (/* the same predicate */);

CREATE POLICY scoresets_worker ON scoresets FOR ALL TO mavedb_worker USING (true) WITH CHECK (true);

experiments and experiment_sets are the same, over experiment_contributors and experiment_set_contributors, with {admin,mapper}.

collections uses its members instead of contributors, and {admin}:

CREATE POLICY collections_rule ON collections FOR ALL USING (
     private = false
  OR created_by_id = app_user_id()
  OR app_roles() && '{admin}'
  OR id IN (SELECT cua.collection_id FROM collection_user_associations cua
             WHERE cua.user_id = app_user_id()
               AND cua.contribution_role IN ('admin', 'editor', 'viewer'))
) WITH CHECK (/* the same predicate */);

Contributors are matched by ORCID: contributor rows hold an ORCID, not a user id, so the policy looks up the caller's ORCID from users. Postgres runs that lookup once per query.

Calibrations

score_calibrations carries its own rule instead of delegating, because a public score set can hold a private calibration:

CREATE POLICY score_calibrations_rule ON score_calibrations FOR ALL USING (
     private = false
  OR created_by_id = app_user_id()
  OR app_roles() && '{admin}'
  OR (investigator_provided AND EXISTS (
        SELECT 1 FROM scoresets ss
         WHERE ss.id = score_calibrations.score_set_id
           AND (ss.created_by_id = app_user_id()
                OR ss.id IN (SELECT sc.scoreset_id FROM scoreset_contributors sc
                               JOIN contributors c ON c.id = sc.contributor_id
                               JOIN users u ON u.username = c.orcid_id
                              WHERE u.id = app_user_id()))))
) WITH CHECK (/* the same predicate */);

A calibration is visible if it's public, you created it, it's investigator-provided and you own or contribute to its score set, or you're an active admin.

Tables that delegate

CREATE POLICY variants_rule ON variants FOR ALL
  USING (scoreset_id IN (SELECT id FROM scoresets))
  WITH CHECK (scoreset_id IN (SELECT id FROM scoresets));

Every other table on #832's list gets a row here before implementation starts. alleles and the reference rows fetched for them are not public reference data (#832): each row exists because of an allele, so it is visible only through one. A translation names both its ClinGen IDs, so both alleles must be visible. The collection association tables check both sides, so a caller can't learn that a private collection contains a public score set.

Policy snapshot

Policies live in migrations, so the current rule for a table is spread across the history. The snapshot is the readable copy: generated, never edited by hand.

• A script connects to a database built by alembic upgrade head and writes src/mavedb/db/policies.sql. It holds, sorted by table then name:
  • each table's RLS state, from pg_class.relrowsecurity
  • each policy as a CREATE POLICY statement, from pg_policies (command, roles, permissive or restrictive, USING, WITH CHECK)
  • the helper functions, from pg_get_functiondef
• The output is deterministic, so a re-run on an unchanged schema produces an identical file.
• CI runs the script after the replay job from Squash the migration history into one baseline so it replays from an empty database #877 and fails if the result differs from the committed file. A PR that changes a policy commits the regenerated snapshot, so review sees the whole rule before and after.

Carve-outs

Rules for writing and changing policies

• Only delegate where seeing the parent really means you may see the child. Calibrations are the counter-example.
• A policy that references another guarded table only sees the rows the caller can see. That's fine for ownership and contributor tests, but it inverts under NOT EXISTS or NOT IN, where filtering makes the condition true more often and leaks.
• Policies only widen: Postgres ORs them together. Narrowing needs AS RESTRICTIVE.
• INSERT ... RETURNING requires the new row to pass the policy, and SQLAlchemy relies on it to fetch primary keys.
• No SECURITY DEFINER functions in policy bodies. The two helpers are the only functions they call.
• To change a policy, drop and re-create it in the same migration. Never drop a policy on an enabled table without re-creating it: an enabled table with no policy shows no rows to anyone.
• To read the current policies, read src/mavedb/db/policies.sql, not the migration history.

Acceptance criteria

• Every table on Work out which tables need RLS policies, and what they'll cost #832's list has a business policy and a worker policy, or a recorded carve-out.
• The helpers exist, and every policy reads the principal only through them.
• alembic upgrade head from empty produces every policy, with RLS not yet enabled.
• modified_by_id is gone from the calibration read rule.
• The indexes above exist.
• src/mavedb/db/policies.sql matches the replayed database, and CI fails when a migration changes a policy without regenerating it.