6 · The five isolation proofs

The question this chapter answers: how do I prove — not hope — that tenants cannot reach each other's rows?

A proof has three parts: a stated identity (role plus user id), one statement, and an expected outcome. Run as written against TaskHarbor from Chapter 3, each proof below prints its verdict. Together they cover the four commands plus the case that matters most in practice: access that used to exist.

Proof 1 — read isolation

Bo, owner of Borealis, counts the rows he can see:

begin;
set local role authenticated;
set local request.jwt.claim.sub = 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4';   -- bo

select count(*) as visible_tasks,
       count(*) filter (
         where project_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2'
       ) as aster_tasks
from public.tasks;
-- Expected: visible_tasks = 1, aster_tasks = 0
rollback;

Two numbers, both required. visible_tasks = 1 proves bo can still work; aster_tasks = 0 proves the boundary. A test that only checked "zero" would pass just as loudly on a table where RLS denied everything.

Proof 2 — the insert fence

Eve, an Aster member, tries to plant a row in Borealis:

begin;
set local role authenticated;
set local request.jwt.claim.sub = 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5';   -- eve

insert into public.tasks (project_id, title)
values ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b3', 'smuggled');
-- Expected: ERROR 42501, new row violates row-level security policy for table "tasks"
rollback;

This denial is loud by nature — a with check rejection raises rather than filters. Its silent twin is worth knowing: the same insert with a forgotten with-check would succeed and create a row eve can never read back.

Proof 3 — updates cannot cross the line silently

Bo attempts to rename an Aster task:

begin;
set local role authenticated;
set local request.jwt.claim.sub = 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4';   -- bo

update public.tasks
set title = 'hijacked'
where project_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2'
returning id;
-- Expected: zero rows returned (UPDATE 0), no error
rollback;

The using clause filtered Aster's rows out of sight before where ever evaluated, so this fails silently. Assert on the row count — an error-based test would never fire here. The thrown variant belongs to the writer's own rows: if bo edits his task into an Aster project, TaskHarbor's explicit with check raises 42501. Both directions deserve a test in your suite.

Proof 4 — deletes stay home

Same identity, destructive intent:

begin;
set local role authenticated;
set local request.jwt.claim.sub = 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4';   -- bo

delete from public.tasks
where project_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2'
returning 1;
-- Expected: zero rows returned, no error
rollback;

Proof 5 — revocation takes effect immediately

Membership is the boundary, so removing a member must close access without any deploy or token refresh:

begin;

delete from public.members
where workspace_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'
  and user_id      = 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5';

set local role authenticated;
set local request.jwt.claim.sub = 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5';   -- eve

select count(*) as eves_visible_tasks from public.tasks;
-- Expected: 0  (was 2 — Aster's two tasks — while she was a member)
rollback;

Note what this proof implies: policies read live membership, not a copy baked into the token. If your model stores tenancy only inside JWT claims, revocation waits for token refresh — a drift of its own.

Wiring the proofs into pgTAP

Hand-run proofs decay. The Supabase CLI runs pgTAP files placed under supabase/tests/; first make sure the extension exists (once, in a migration):

create extension if not exists pgtap with schema extensions;

Then the whole chapter as one test file — fixtures first, five assertions after:

-- supabase/tests/isolation.test.sql
begin;
select plan(5);

-- Fixtures: Chapter 3's seed, exactly — the counts asserted below are its counts.
insert into public.workspaces (id, name) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'Aster Labs'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2', 'Borealis Design');
insert into public.members (workspace_id, user_id, role) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3', 'owner'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2', 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4', 'owner'),
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5', 'member');
insert into public.projects (id, workspace_id, name) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2', 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'Brand refresh'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b3', 'b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2', 'Studio handbook');
insert into public.tasks (project_id, title) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2', 'Draft moodboard'),
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2', 'Review typography'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b3', 'Outline chapter 1');

-- P1: bo reads his own task, never Aster's.
set local role authenticated;
set local request.jwt.claim.sub = 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4';
select is(
  (select count(*) from public.tasks
    where project_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2'),
  0::bigint, 'P1: bo cannot read Aster tasks');

-- P2: eve cannot write into Borealis.
set local request.jwt.claim.sub = 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5';
select throws_ok(
  $$insert into public.tasks (project_id, title)
    values ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b3', 'smuggled')$$,
  '42501', null, 'P2: cross-tenant insert rejected');

-- P3: bo silently updates nothing in Aster.
set local request.jwt.claim.sub = 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4';
select is_empty(
  $$update public.tasks set title = 'hijacked'
    where project_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2' returning 1$$,
  'P3: cross-tenant update matches nothing');

-- P4: bo deletes nothing in Aster.
select is_empty(
  $$delete from public.tasks
    where project_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2' returning 1$$,
  'P4: cross-tenant delete matches nothing');

-- P5: removing eve closes her access at once.
reset role;   -- back to the session owner to administer membership
delete from public.members
 where workspace_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'
   and user_id      = 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5';
set local role authenticated;
set local request.jwt.claim.sub = 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5';
select is(
  (select count(*) from public.tasks),
  0::bigint, 'P5: revoked member sees nothing');

select * from finish();
rollback;

Run it locally:

supabase test db

Every assertion follows the docs' advice: switch identity per case, match the assertion style to the way each denial happens (throws_ok for rejections, empty results for filtered operations).

One honesty rule the proofs encode: they seed known data first, so a passing zero genuinely means "filtered," never "empty table." A bare request returning an empty array over the API cannot distinguish those two cases on its own.

Putting them in CI

The tests are only structural drift insurance if something runs them when nobody is looking. A minimal workflow:

# .github/workflows/rls-proofs.yml
name: rls-proofs
on: [pull_request]
jobs:
  prove-isolation:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v1
        with:
          version: latest
      - run: supabase start
      - run: supabase db reset     # applies migrations + seed.sql
      - run: supabase test db      # runs supabase/tests/*.sql

Now Pattern-2 drift — a migration that rebuilds a table and loses its policies — fails a pull request instead of surfacing in production. That single property moves isolation from "something we did" to "something we enforce."

Check on your project

1. Adapt Proof 1 to your schema tonight and run it for two real roles across your tenant boundary. Record both numbers.
2. Add the negative-write proof (Proof 2) for your most sensitive table. If it does not raise 42501, stop everything and read the policy again.
3. Create supabase/tests/isolation.test.sql with at least P1 and P5 for your domain, and get supabase test db green locally.
4. Wire the CI workflow above, then deliberately break a policy on a branch and watch the pipeline catch it. That red build is the sound of drift being resisted.