DIL.DEV
LOADING0%
DIL.DEV
← ALL NOTES

Proving Row-Level Security With a Disposable Postgres in CI

Five new tables for a video editor, each with owns_project RLS and composite FKs. A throwaway Postgres in CI proves tenant isolation before any code ships.

postgresRLSCIsupabasesecurity

A terminal showing a SQL test run with green pass indicators against a disposable Postgres instance.

Migration 0195 for the ActionMode video editor adds five tables. Before any of it touched a shared environment, a throwaway Postgres in CI ran the real migration, switched JWT claims to impersonate a second tenant, and confirmed that every cross-tenant read, update and insert failed in the expected way. The test lives alongside the migration and runs on every pull request.

The Five Tables and What They Hold

The migration creates:

  • video_recordings: the top-level take, owned by a project.
  • video_segments: individual clips cut from a recording.
  • video_captions: transcript lines attached to a segment.
  • video_thumbnails: candidate thumbnails for a segment.
  • video_render_jobs: async export work queued by the worker.

Each table carries an owns_project RLS policy. That policy compares the row's project_id against the projects the authenticated user owns, using a claim from the JWT. A session with the wrong tenant's claims sees no rows at all.

Why RLS Alone Is Not Enough

RLS handles reads and updates well. Inserts are trickier.

If a child table only has a project_id column and an RLS insert policy, a determined caller could still attempt to insert a segment under a recording that belongs to a different project, as long as they supply a matching project_id. The policy might pass because the project_id they supplied is one they own, even though the recording is not.

Composite foreign keys close that gap. Every child table references both the parent row and its project_id together. The database checks that the combination exists in the parent. If a segment insert names a recording from project A but supplies project B's ID, the FK raises foreign_key_violation immediately, before any application logic runs.

This matters even for the service-role worker. Service role bypasses RLS, but it does not bypass FK constraints. The test asserts this explicitly: a cross-project segment insert as service_role must still fail.

Column Grants: Keeping the Browser in Its Lane

Not every column should be writable from the browser. The grant structure reflects what each caller actually needs:

  • Browser (authenticated role): can flip kept and trim_enabled on segments, and write caption text and thumbnail selections.
  • Worker (service role): owns transcript timestamps, segment status transitions and all render job fields.

Column-level grants enforce this at the database layer. Even if application code had a bug that tried to write a transcript timestamp from the browser, Postgres would reject it. There is no relying on the API layer to filter the payload correctly.

What the SQL Test Actually Asserts

The test file is apps/web/tests/sql/video-editor-isolation.sql. It runs three classes of assertion against owner B trying to reach owner A's data:

Cross-tenant reads:

  • SELECT on A's recordings, segments, captions and thumbnails returns 0 rows when the session carries B's JWT claims.

Cross-tenant updates:

  • UPDATE statements targeting A's rows touch 0 rows. The RLS policy filters them out silently, which is the correct behaviour.

Cross-tenant inserts:

  • Inserting a segment under A's recording raises insufficient_privilege or foreign_key_violation, depending on which constraint fires first.
  • Inserting as service_role with a mismatched project raises foreign_key_violation because the FK check runs regardless of RLS.

Asserting all three separately matters. A policy that blocks reads might still allow an insert if the insert policy is misconfigured. Checking each failure mode independently means a regression in one does not hide behind a passing check in another.

How the Runner Works

scripts/test-video-editor-sql.py does the following:

  1. Spins up a disposable Postgres (no shared state, no existing data).
  2. Applies migration 0195 in full, exactly as it would run in production.
  3. Seeds the minimum rows needed: two projects, two owners, one recording per project.
  4. Executes video-editor-isolation.sql and collects results.
  5. Fails the step if any assertion does not match the expected outcome.

The runner is wired into the Database and operations check in .github/workflows/safety-checks.yml. It runs on every pull request that touches the apps/web/tests/sql/ or supabase/migrations/ paths.

Using the real migration rather than a hand-crafted schema is the important part. If the migration has a typo in a policy name or a missing grant, the test catches it because it is running the same SQL that production would run.

Setting JWT Claims in a Test Session

Supabase RLS policies that read request.jwt.claims rely on a Postgres session variable. In a test, you set it directly:

sql
SELECT set_config(
  'request.jwt.claims',
  '{"sub": "owner-b-uuid", "role": "authenticated"}',
  true
);

From that point in the session, any RLS policy that checks auth.uid() or reads claims from the JWT sees owner B's identity. The test switches between owner A and owner B by resetting this variable, so the same file can assert both directions of isolation.

This is not a Supabase-specific trick. It is standard Postgres session configuration. Any test runner that can execute SQL can do it.

Fitting Into the Broader PR Sequence

This migration is PR 2 of 6 for the video editor. The isolation proof being in CI from PR 2 means every subsequent PR that touches the schema has to keep the test green. If PR 4 adds a column and forgets to update the grant, the test fails. The isolation guarantee does not drift silently as the feature grows.

ActionMode is public on dil.dev if you want to see the broader context.

What I'd do differently

The test seeds the minimum rows to make assertions pass. That is fine for isolation checks, but it means the test does not catch policies that accidentally allow reads on empty tables (a policy that returns true when there are no rows to filter). I'd add an explicit assertion that a table with rows belonging to owner A returns exactly the right count when queried as owner A, so the test proves the policy is actually being evaluated rather than just not blocking an empty result set.

I'd also separate the service-role FK assertion into its own test step with a clearer label. Right now it lives in the same file as the JWT-based assertions, which makes the failure message slightly ambiguous when something goes wrong. Two clearly named steps would make a CI failure easier to diagnose at a glance.

NOTES BY EMAIL

Get the next note in your inbox.

One email per note. No spam. Reply “stop” any time and you are off the list.