Repository navigation
Work out which tables need RLS policies, and what they'll cost #832
Description
Summary
Before writing policies (#809), we need the exact list of tables that can reveal private data, and confirmation the policies are fast enough at production size. This is an investigation. Its output is a comment on this issue: each table with a ruling (own rule, delegates to a parent, carries the key, carve-out, or public reference data) and the reason, plus production row counts and query plans.
Terms are defined in the glossary on #808. Work against the schema 2026.3.0 ships, which includes RT and 754.
How to decide whether a table needs a policy
A table needs a policy if any query could return its rows without also joining to the parent that decides visibility. For each table reachable by foreign key from scoresets, experiments or collections, check the real queries in routers/ and lib/, not just the schema.
A table that today is only reached through a join still gets listed. It's one new route away from leaking.
Don't find tables by searching for private or created_by_id. That only finds the ones we already know about.
What to produce
1. The guarded list
Every table with its ruling and the query that justifies it. These need an explicit ruling beyond the foreign-key walk:
scoreset_publication_identifiers,experiment_publication_identifiers,experiment_controlled_keywords,experiment_keywords,scoreset_keywordsscore_calibration_functional_classification_variants,calibration_controlsmapped_variants_clinical_controls,gnomad_variants_mapped_variants,variant_annotation_status- From RT:
alleles,mapping_records,mapping_record_alleles,annotation_event,clinvar_allele_links,gnomad_allele_links,vep_allele_consequences,clinvar_controls,variant_translations gnomad_variants- The job tables:
job_runs,pipelines,job_dependencies. Their routes are admin-only today, andjob_paramscan name a private score set. mondo_termsandurn_redirects. A redirect row can reveal that a temporary URN exists.
alleles get a policy: a row is visible only when the caller can see at least one mapping_record_alleles row linking it. An allele exists only because some score set's mapping produced it, so one produced only by a private score set discloses that someone measured it. The policy is an EXISTS over mapping_record_alleles, whose own policy applies inside the subquery. Retired links count, since they still record why the allele exists.
The reference rows fetched for alleles follow the same rule. gnomad_variants are fetched by our alleles' CAIDs, clinvar_controls rows exist only for ClinVar records that matched one of our alleles, and vep_allele_consequences and variant_translations are stored per allele. A gnomAD ID or ClinVar record names the variant outright, so each of these tables delegates to alleles, through its link table where it has one.
Measure the policy chain on large scans that touch many alleles, such as score-set variant listings.
mapped_variants stays without RLS until #747 drops it. Record it as a temporary carve-out, with the routes that read it.
2. The carve-outs
Confirm these are the only tables left without a policy: users, contributors, scoreset_contributors, experiment_contributors, experiment_set_contributors, users_roles, roles, collection_user_associations, access_keys. Record what each exposes to a direct query as mavedb_api.
3. Views and materialized views
- Plain views: list each with its
security_invokersetting. Every view needsWITH (security_invoker = true), or the api reads it with the owner's rights and sees every row.v_variant_annotationsand RT'sv_current_annotation_eventsare the known ones. - Materialized views: RLS never applies to them. List each with its columns. One may only carry data whose visibility depends on publication; with Enforce publication invariants with database constraints #893's constraint, published means public.
4. Row counts
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;A table that reaches scoresets only through a large table has to carry scoreset_id itself. We expect that to be mapping_records and mapping_record_alleles (#833). Until #747's backfill, estimate mapping_records from mapped_variants' count.
5. Query plans at production size
Run the delegation policy at production row counts, using #809's policy shape. Confirm the plan builds the visible score set ids once and probes them per row, and that the contributor lookup (caller's ORCID from users, through contributors to scoreset_contributors) runs once per query.
Acceptance criteria
- The guarded list is posted here, each table with its ruling and justifying query.
- Tables safe only because no route reads them directly are listed as standing risks.
- Carve-outs are confirmed against the policy bodies, with what each exposes.
- Every view is listed with its
security_invokersetting, and every materialized view is checked against the publication rule. - Production row counts are posted, and the carry-
scoreset_idlist comes from them. - Plans at production size are attached, including the contributor lookup.
- Any foreign key Reconcile schema drift between prod, migrations, and models #829 adds or removes is rechecked against the list.
Baseline measurements to repeat at production size
PG 15.17, 2,000 score sets (10% private), 400k variants, policies written with app_user_id() and app_roles():
| Query | With policy | Without |
|---|---|---|
count(*) over 400k variants |
~21 ms | ~9.5 ms |
| One score set's variants (200 rows) | 0.7 ms | — |
The plan to reproduce, with the visible score set ids hashed once and probed per row:
Parallel Seq Scan on public.variants
Filter: (hashed SubPlan 2)
Seq Scan on public.scoresets (actual rows=1806)
Filter: ((NOT private) OR (created_by_id = (NULLIF(current_setting('app.user_id', true), ''))::integer) OR ... OR (hashed SubPlan 1))
Index Scan using users_pkey on users u
Index Scan using contributors_orcid_id_key on contributors c
Index Scan using scoreset_contributors_contributor_id_idx on scoreset_contributors sc
The shape to confirm is absent, delegating through a large table:
Bitmap Index Scan on mv_ca_idx (actual rows=16)
Seq Scan on v (actual rows=360000) ← hash built regardless
Execution Time: 647 ms
Activity
[-]Step 0: enumerate the guarded set by FK reachability and measure delegation cost[/-][+]Work out which tables need RLS policies, and what the policies will cost[/+][-]Work out which tables need RLS policies, and what the policies will cost[/-][+]Work out which tables need RLS policies, and what they'll cost[/+]bencap commentedon Oct 21, 2026
Findings
Every table in the 2026.3.0 schema now has a ruling, and the policy shapes were measured at production size. Four results change #809:
IN (SELECT id FROM …)is only safe onvariants. Below it, in the mapping and allele chain, it produces plans that run past a two-minute timeout. A correlatedEXISTSthere keeps current-data reads within a small multiple of the unpoliced time.alleles.first_published_atfast path.Measured on PG 15 against a local restore of the 2026.3.0 release dump, which has 3,336 score sets and 11.0M variants. Three score sets were reverse-translated locally for the fan-out numbers. Policies were created in transactions that were rolled back, and timed for an anonymous caller and for a signed-in user who owns and contributes to private score sets.
1. Rulings
Guarded tables fall into three classes, and three more groups sit outside RLS.
scoresets,experiments,experiment_sets,collections,score_calibrationsscore_set_idvariantsscoreset_id IN (SELECT id FROM scoresets)mapping_records,mapping_record_alleles,projection_groupsEXISTS (SELECT 1 FROM scoresets s WHERE s.id = <t>.score_set_id)target_genesscoreset_id IN (SELECT id FROM scoresets)target_gene_external_identifiers, which replaces the three offset tables (decision 2)target_genesscore_calibration_functional_classifications,score_calibration_publication_identifiers,calibration_controlsscore_calibrationsscore_calibration_functional_classification_variants(1.4M rows)score_calibration_functional_classifications(1.5k rows, so no carried key is needed)experiment_controlled_keywordsscoreset_meta_analysis_sources,collection_score_sets,collection_experimentsurn_redirectsnew_urnnames. It's read anonymously before auth, and rows are written only at publishpipelinescreated_by_user_id = app_user_id() OR app_roles() && '{admin}'. Owner routes keepINSERTandDELETEjob_runs,job_dependenciespipelines. A run with no pipeline, an ad hoc run, is admin-only: 7,240 of 15,603 runs locallyallelesfirst_published_at IS NOT NULL OR EXISTS (SELECT 1 FROM mapping_record_alleles m WHERE m.allele_id = alleles.id). Retired links countvep_allele_consequences,clinvar_allele_links,gnomad_allele_linksEXISTS (SELECT 1 FROM alleles a WHERE a.id = <t>.allele_id)clinvar_controls,gnomad_variantsEXISTSover their link tablevariant_translationsEXISTSonalleles.clingen_allele_idfor both sidespublication_identifiersEXISTSover its four citation tables, keyed by(db_name, identifier)target_sequencesEXISTSovertarget_genes.target_sequence_idannotation_event,variant_annotation_status,v_current_annotation_events,target_gene_mappings,mapped_variantsand its two link tables until #747 drops them, the legacykeywordstables, and six unmodelled tables that #829 drops:mavedb_ldh_records,mavedb_ldh_records_audit,variants_to_sync,vep_functional_consequence_archive,mavedb_staging_mapped_clinvar_allele_ids_1,mavedb_staging_mapped_variants_clinical_controlslicenses,controlled_keywords,acmg_classifications,mondo_terms,genome_identifiers,taxonomiesusers(its sensitive column,email, is returned only to the user and to admins);contributorsand the three*_contributorstables,users_roles,roles,collection_user_associations(all read by the parent policies);access_keys(looked up before the caller is known)doi_identifiers,sra_identifiers,ensembl_identifiers,refseq_identifiers,uniprot_identifiers, and the three offset tables (decision 2)Three routes filter in Python after an unfiltered query:
/clingen-alleles/{caid}/measurementsfind_variants_by_vrs_identifierThey're correct today, and the
allelespolicy is what protects them in SQL.2. Views
v_variant_annotationsandv_current_annotation_eventsneedWITH (security_invoker = true).published_variants_materialized_viewfilters onpublished_date IS NOT NULL. That's safe because no record is both private and published (Enforce publication invariants with database constraints #893).3. Cost
Two plan problems rule out #809's
INshape belowvariants:alleles,INcan't be hashed. The 887k visible ids don't fit inwork_mem, so Postgres scans a materialized list for every row.mapping_recordsandmapping_record_alleles, the planner misprices lookups. It charges the hashed subplan's startup cost to every index lookup, so it scans and hashes the whole 8.5M-row table instead.EXISTSagainstscoresetsis a primary-key lookup, and the planner prices it correctly.Times in ms, warm; "none" is without policies.
count(*)variantsv_variant_annotationsfor one score setv_variant_annotations, all columnscount(*)allelesscoreset_contributors.contributor_idindex Write RLS policies for every guarded table as migrations #809 adds.first_published_atfast path. It's untested. So is the ClinVar fan-out of new alleles, because CAR submission and the annotation jobs weren't run locally.4. Decisions
1. Allele visibility:
alleles.first_published_at. A database trigger sets it when a link is written for a published score set, and at publish for the alleles already linked. The policy'sEXISTSthen runs only for alleles with no public link, about 15% of them.first_published_atassumes publication is never reversed (Enforce publication invariants with database constraints #893); a change that lets published data become private, such as unpublishing or an embargo, revisits it";EXISTS, which costs 3–10× and grows with history.2. Identifier tables.
Publications are a shared entity that the product renders, so they stay shared. Citations reference them by
(db_name, identifier). The api records a citation withINSERT … ON CONFLICT DO NOTHING, with no conflict target and noRETURNING, then the citation row, and never reads the publication row: Reference publications by their identifier so citations can be written under RLS #945.DOI, SRA, Ensembl, RefSeq and UniProt identifiers are record attributes: every other column is a constant, always null, or derived from the identifier.
target_gene_external_identifiers (target_gene_id, db_name, identifier, offset), which matches the singleexternal_identifierslist the api already returns.That's Store DOI, raw read and target gene identifiers on the rows that use them #944.
What I verified on PG 15 for a caller who can't see the existing row:
ON CONFLICT DO NOTHINGis a silent no-op;ON CONFLICT (col)andINSERT … RETURNINGboth fail the read policy.Those last two failures are why the current find-or-create can't work under a policy, even for new rows.
3. No api grant on tables the api never reads (agreed, for #827). A later route that needs one adds the grant and a policy then.
4. Rules for worker code, for #809's rules section. The worker sees every row, which is safe only while its output reaches people through policied tables:
5. Follow-ups
job_runsits owncreated_by_user_id, so ad hoc runs can be shown to the user who started them.Recommended policy text, as measured
scoresets_ruleis #809's text unchanged.alleles_rulegains thefirst_published_at IS NOT NULL ORprefix once decision 1 lands.Row counts (n_live_tup)