# 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.

- Author: Dil Ahdan (https://dil.dev/)
- Published: 2026-11-05
- Updated: 2026-11-05
- Tags: postgres, RLS, CI, supabase, security
- Canonical: https://dil.dev/notes/rls-isolation-test-disposable-postgres/

## Key takeaways

- owns_project RLS on every table is not enough alone: composite foreign keys must pin each child row back to its take's project so the FK itself enforces tenant boundaries.
- Column grants are the right tool for limiting what the browser can touch: kept, trim_enabled, caption and thumbnail choices only, with transcript times and render jobs staying worker-only.
- Running the real migration against a throwaway Postgres in CI means the isolation proof travels with the schema, not with a developer's memory.
- Cross-tenant reads returning 0 rows, updates touching 0 rows, and inserts raising insufficient_privilege or foreign_key_violation are three distinct failure modes worth asserting separately.
- The service-role worker is not exempt from FK constraints: a cross-project segment insert must still raise foreign_key_violation even with RLS bypassed.

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.

## Quick answers

**What does owns_project RLS actually check?**

It compares the project_id on the row against the project the authenticated user owns, typically via a claim in the JWT. Every table in this migration carries that policy, so a query from the wrong tenant's session simply sees no rows.

**Why use composite foreign keys instead of just a project_id column?**

A plain project_id column can drift if a row is inserted carelessly. A composite FK that references both the take and its project means the database itself rejects any insert that would place a child row under a different project, even when the caller has service-role privileges and RLS is bypassed.

**How does the SQL test impersonate a different tenant?**

It sets request.jwt.claims in the Postgres session to simulate owner B's JWT, then queries rows that belong to owner A. The test asserts that reads return 0 rows, updates touch 0 rows, and inserts raise either insufficient_privilege or foreign_key_violation.

**Why run this in CI rather than just reviewing the migration?**

A code review can miss an interaction between policies, grants and FK constraints. Applying the real migration to a disposable Postgres and running assertions catches the gap automatically on every pull request, before anything reaches a shared environment.

**What can the browser role actually write?**

Column grants restrict the browser to flipping kept and trim_enabled, and writing caption and thumbnail choices. Transcript timestamps, segment status and render job fields are write-protected and only the service-role worker can touch them.

**Where does this test live in the repo?**

The assertions are in apps/web/tests/sql/video-editor-isolation.sql. The Python runner that applies the migration and executes the file is scripts/test-video-editor-sql.py, wired into the Database and operations check in .github/workflows/safety-checks.yml.
