Step 0: enumerate the guarded set by FK reachability and measure delegation cost

[bencap]

To decide how to implement RLS, we must know what it needs to protect. This issue enumerates the guarded set.

Scope

1. Enumerate by the invariant, not by columns

Any row a query can reach must either carry on itself everything needed to decide visibility, or be guaranteed to join to the row that does.

For each table FK-reachable from scoresets, experiments, or collections, answer: can this row be returned by any query that does not join to its gating ancestor? If yes, it needs its own policy.

Do this against actual query shapes in routers/ and lib/, not against the schema alone. A table only ever reached by a join from a guarded table is safe today, but one new route away from not being safe.

Do not use WHERE column_name IN ('private','created_by_id','modified_by_id') as the instrument. It returns the tables already known and produces a false all-clear.

Tables already known to need a policy and not derivable from a column scan: collection_score_sets and collection_experiments. Both hold a foreign key to a rule-carrying entity, and collections is a carve-out so they cannot delegate to their own parent. Without policies, an association row survives an invisible target and CollectionScoreSetAssociation.score_set — a non-optional scalar — resolves to None.

2. Confirm the carve-out set

These carry no policy, because every parent policy reads them and a policy on them reads the parent back:

users, contributors, scoreset_contributors, experiment_contributors, experiment_set_contributors, users_roles, roles, collections, collection_user_associations.

Confirm no other table is read by a policy body, and record the resulting disclosure: mavedb_api can read the contributor list of any private score set, the membership of any private collection, and any user's role assignments by direct query.

3. Enumerate the relkinds RLS does not reach

Two other relkinds carry the same data and are invisible to a pg_policy audit:

• Plain views. db/view.py emits bare CREATE VIEW, and PG 15 defaults security_invoker to false, so RLS on base tables is evaluated as the view owner. The concrete instance is v_variant_annotations (models/variant_annotation_view.py): a plain view joining Variant/ScoreSet/MappedVariant with no privacy predicate at all, exposing every variant of every score set including private ones, with HGVS and mapping payload. List every plain view and its owner. The fix is WITH (security_invoker = true) as a default in db/view.py's CreateView compiler rather than a lint, plus catalog test C8 in Startup enforcement assertion for RLS liveness #824.
• Materialized views. RLS never applies to reads. published_variants_materialized_view is safe only because its definition filters published_date IS NOT NULL, which assumes published implies not-private — true for score sets by convention rather than constraint, and false for calibrations, whose rule is orthogonal to publication. The rule to enforce is not "test the filter" but: no materialized view may carry a column governed by a rule other than publication. List every MV and every column against that rule.

4. Measure cardinalities

The delegation rule is "delegate to the nearest ancestor whose visible set is small and bounded." That needs numbers:

SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

Anything large that sits between a subtree table and scoresets puts that table on the must-carry-scoreset_id list. The expectation is that only mapped_variants qualifies, because target_genes and score_calibrations are a few rows per score set. Confirm it by measurement.

5. Confirm the plan shape on production cardinalities

Re-run the winning form against production row counts and confirm the plan still contains a hashed SubPlan rather than a per-row subquery. The invariant being tested is that policy cost stays bounded by the visible row count of scoresets.

Re-measure the contributor disjunct in its real shape. Contributor-ship is ORCID-keyed, so it is two hops — scoreset_contributors joined to contributors, with the caller's ORCID resolved from users — not the single-hop form the baseline below used. Confirm the ORCID resolution hoists to an InitPlan and that the added join stays within noise.

Acceptance criteria

• A written list of every guarded table, each with the query shape that justifies it, recorded in this issue.
• The carve-out set is confirmed against policy bodies, with the disclosure recorded.
• A written list of every plain view with its owner and security_invoker setting, and every materialized view checked against the publication rule.
• Cardinalities captured from production, and the must-carry-scoreset_id list derived from them rather than assumed.
• The winning policy form re-measured at production cardinalities with the two-hop contributor disjunct live, with the plan output attached.
• Any table where the answer is "safe only because no route selects it independently" is listed explicitly as a standing risk rather than silently omitted.
• Reconcile schema drift between prod, migrations, and models #829's triage query is corrected or removed, since it cannot answer this question.

---

Baseline measurements to re-run against production

Synthetic bed on PG 15.17: 2,000 score sets (10% private) / 400k variants / 400k mapped variants. Every form returned exactly 360,000 of 400,000 — correctness never differentiated between them, only cost did.

Policy form	inner set	400k scan	40-row lookup
none (baseline)	—	12 ms	~0.5 ms
scoreset_id IN (SELECT id FROM scoresets)	1,800	15 ms	1.2 ms
materialised private/owner_id on the row	—	11 ms	~0.5 ms
app_can_read_scoreset(scoreset_id) — function, 1 hop	—	614 ms	0.4 ms
variant_id IN (SELECT id FROM variants)	360,000	738 ms	688 ms
app_can_read_variant(variant_id) — function, 2 hops	—	5,364 ms	0.9 ms

The table above omits the contributor check, and the figures below used a single-hop contributor association that does not exist in the schema. On the same bed: as a SECURITY DEFINER function the 400k scan costs 31.7 ms against a 17.7 ms baseline, because SECURITY DEFINER defaults to PARALLEL UNSAFE and drops the plan from parallel to serial. As an inline semi-join it is 18.9 ms. #809 specifies the inline form.

The plan shape to reproduce — visible score-set ids hashed once, then probed per row:

Parallel Seq Scan on mv
  Filter: (hashed SubPlan 1)
    Seq Scan on ss (actual rows=1800)
      Filter: (NOT private) OR (owner_id = current_setting('app.user_id')::integer)
Execution Time: 14.7 ms

The shape to confirm is absent — the two-hop form, where the index scan works perfectly and is then wasted:

Bitmap Index Scan on mv_ca_idx (actual rows=16)
Seq Scan on v (actual rows=360000)      ← hash built regardless
Execution Time: 647 ms