Move read enforcement into Postgres row-level security #808

Summary

Today MaveDB checks read permissions in Python, on the entity a request names. It doesn't check the entities a response happens to contain, so a private score set can leak through a variant, a calibration or a collection listing. This epic moves read enforcement into Postgres row-level security (RLS), so no query can return a row the caller isn't allowed to see, whatever route, script or join produced it.

Two incidents motivated it. In #822, 24 permission checks in the collections router did nothing, the test suite stayed green, and four tests asserted the leaked behavior. In #807, private calibrations leaked through public score sets.

RLS here contains bugs in our own code. It doesn't defend against someone who can run arbitrary SQL as the api, since the caller's identity is a setting the connection sets for itself. SQL injection is prevented where queries are built.

New to this work? Read the glossary below first. The child issues use its terms without redefining them.

Four rules

1. RLS decides which rows a caller can see and write.
2. Python decides which actions are allowed, and every 401, 403 and error message.
3. Anything that must hold across rows the caller can't see is a database constraint, not a Python check.
4. A fixed set of CI and startup checks catches the known ways around RLS.

Glossary

The basics

• RLS (row-level security). A Postgres feature that attaches a filter to a table. Every query against that table, from anywhere, only sees the rows the filter allows.
• Policy. The filter itself: a SQL condition on a table, such as "not private, or created by the caller". A row is visible if any of the table's policies allows it.
• Enabled. ENABLE ROW LEVEL SECURITY turns a table's policies on. Until then they exist but do nothing.
• Principal. Who the database thinks is asking. The api sends two settings at the start of every transaction, app.user_id and app.active_roles, and the policies read them. An anonymous request sends empty values.
• GUC. Postgres's name for a setting like app.user_id.
• app_user_id() and app_roles(). Two small SQL functions the policies call to read the principal. They turn an empty setting into "nobody", so a connection with no principal sees public rows instead of erroring (Write RLS policies for every guarded table as migrations #809).

Which tables get policies

• Guarded table. Any table whose rows can reveal private data. Work out which tables need RLS policies, and what they'll cost #832 produces the list.
• Rule-carrying table. A guarded table with its own visibility rule: score sets, experiments, experiment sets, calibrations and collections.
• Delegation. A child table borrowing its parent's rule instead of repeating it. variants is visible when its score set is, written as scoreset_id IN (SELECT id FROM scoresets). The inner query is itself filtered, so the rule lives in one place.
• Carrying the key. When the path to the parent runs through a huge table, delegation is too slow, so the child stores the parent's id directly (Add score_set_id to the allele-centric mapping tables before they ship #833).
• Carve-out. A table deliberately left without a policy, usually because the policies themselves read it: users, contributors, roles, users_roles, the contributor and collection-membership tables, and access_keys.

The database roles

• mavedb_api. The login the api uses. It owns nothing and belongs to no other role, so it only sees what the policies allow.
• mavedb_worker. The login the worker uses. Every guarded table has a policy naming it that shows it every row.
• mavedb_owner. Owns every table. It can't log in, and RLS doesn't apply to it.
• mavedb_migrate. The login for migrations and deploy steps. It runs as mavedb_owner, so migrations create owner-owned tables and see every row.
• Bootstrap script. Creates the roles and grants, and moves table ownership (Split database roles into owner, migrate, worker and api #827).

How we know it works

• Canary. A test that seeds private data, calls every GET route as someone who shouldn't see it, and fails if the data appears in any response (Canary test and route completeness tripwire for permission leaks #810).
• Differential test. A test that checks the Python read rule and the policy agree, across a matrix of users and entity states (Run tests and local development under the production database roles #834).
• RLS checks. Catalog and behavior checks that fail CI and stop the api or worker from starting when RLS can be bypassed or isn't enforcing (Fail CI and startup when RLS can be bypassed or isn't enforcing #824).
• Policy-gap counter. While the Python read filters are still in place, it counts each time a filter drops a row the policy returned. It must stay at zero (Count where the Python read filters disagree with RLS #835).
• Soak. At least one release cycle in production with RLS and the Python filters both running, watching the counter. The filters are deleted after it (Delete the Python read filters #811).

Branch names used in these issues

• RT. The reverse-translation and allele-centric mapping work, on feature/bencap/allele-centric-mapping-and-storage. It adds mapping_records, alleles and related tables.
• 754. Calibration controls, on feature/bencap/754-calibration-controls. It adds calibration_controls.
• 2026.3.0. The release that merges RT, 754 and release-2026.3.0. The RLS work targets the schema after it.

Decisions

1. Four roles. Owner, migrate, worker and api. Only mavedb_migrate can become the owner; the api and worker belong to nothing (Split database roles into owner, migrate, worker and api #827).
2. The owner isn't subject to RLS. No FORCE. Migrations, backfills and restores see every row. The bypasses this allows are caught by the RLS checks (Fail CI and startup when RLS can be bypassed or isn't enforcing #824).
3. Worker access is a policy. Each guarded table has a second policy that shows mavedb_worker every row (Write RLS policies for every guarded table as migrations #809).
4. Policies never raise. They read the principal through app_user_id() and app_roles() (Write RLS policies for every guarded table as migrations #809).
5. The principal belongs to the request, and each transaction carries a copy (Tell Postgres who is asking, on every transaction in a request #823).
6. Children delegate to their parent, inline. Calibrations carry their own rule, because a public score set can hold a private calibration. Collection associations check both sides. mapping_records carries scoreset_id (Write RLS policies for every guarded table as migrations #809, Add score_set_id to the allele-centric mapping tables before they ship #833).
7. One policy per table covers reads and writes: FOR ALL USING (visible) WITH CHECK (visible) (Write RLS policies for every guarded table as migrations #809).
8. Policies are plain SQL in migrations (Write RLS policies for every guarded table as migrations #809).
9. Role lists are {admin, mapper} for score sets, experiments and experiment sets, and {admin} for calibrations and collections. app.active_roles only ever holds roles the user has (Tell Postgres who is asking, on every transaction in a request #823).
10. Invariants over hidden rows are constraints (Enforce publication invariants with database constraints #893, Handle rows hidden by RLS on the write path #879).
11. Named lookups become get-or-404, then check the action (Look up named entities with one get-or-404 helper, then check the action #816).

Order of work

1. Make migrations replayable from an empty database (Squash the migration history into one baseline so it replays from an empty database #877).
2. Roles (Split database roles into owner, migrate, worker and api #827), schema drift (Reconcile schema drift between prod, migrations, and models #829) in parallel. Add scoreset_id to RT's mapping tables before 2026.3.0 ships (Add score_set_id to the allele-centric mapping tables before they ship #833).
3. Independent, any time: the canary (Canary test and route completeness tripwire for permission leaks #810), publication constraints (Enforce publication invariants with database constraints #893), the principal (Tell Postgres who is asking, on every transaction in a request #823), error mapping (Map RLS permission errors to client responses without leaking row data #880), the policy-gap helper (Count where the Python read filters disagree with RLS #835).
4. Work out which tables need policies (Work out which tables need RLS policies, and what they'll cost #832), then write them (Write RLS policies for every guarded table as migrations #809).
5. RLS checks (Fail CI and startup when RLS can be bypassed or isn't enforcing #824) and tests under the real roles (Run tests and local development under the production database roles #834).
6. Write-path fixes (Handle rows hidden by RLS on the write path #879), contributor propagation (Propagate score set and experiment contributors to parent records #564), dumps and exports (VariantEffect/mavedb-deployment#35).
7. Pilot on score_calibrations (Pilot: enable RLS on score_calibrations in staging #825).
8. Enable on every guarded table, staging then production (Enable RLS on every guarded table #878).
9. Soak in production for at least one release cycle (Count where the Python read filters disagree with RLS #835).
10. Next cycle: delete the Python read filters (Delete the Python read filters #811). Look up named entities with one get-or-404 helper, then check the action #816 can land any time after Write RLS policies for every guarded table as migrations #809.

Done when

• Every table on Work out which tables need RLS policies, and what they'll cost #832's list has RLS enabled, or is a recorded carve-out.
• The RLS checks run in CI and at startup, and pass in production.
• The canary covers every GET route and passes.
• The differential test passes over the full user × entity-state matrix.
• No permitted action ends in a silent zero-row write.
• Local development and CI run as mavedb_api, with policies present.
• The policy-gap counter has read zero in production for a full release cycle.