Row-Level Security Test Pattern for Postgres: Catch Leaks Pre-Prod

The architecture has three pieces. A deterministic two-tenant fixture, a test runner that switches Postgres role per assertion, and a schema-level invariant check that runs against pg_catalog. They live alongside the application code in version control, run via supabase test db and a Node test runner, and execute on every pull request.

Deterministic two-tenant fixture

Start with the fixture. Two tenants, each with a user, each with seeded rows in every table the application protects. The fixture is loaded inside a transaction that gets rolled back after the test, so each run starts from the same known state.

-- supabase/tests/fixtures/two_tenants.sql
-- Loaded at the top of every RLS test file.

insert into auth.users (id, email, raw_user_meta_data)
values
  ('11111111-1111-1111-1111-111111111111', 'a@test.local', '{"tenant_id":"aaaa1111-aaaa-1111-aaaa-111111111111"}'),
  ('22222222-2222-2222-2222-222222222222', 'b@test.local', '{"tenant_id":"bbbb2222-bbbb-2222-bbbb-222222222222"}');

insert into public.tenants (id, name) values
  ('aaaa1111-aaaa-1111-aaaa-111111111111', 'Tenant A'),
  ('bbbb2222-bbbb-2222-bbbb-222222222222', 'Tenant B');

insert into public.projects (id, tenant_id, name) values
  ('a1a1a1a1-0000-0000-0000-000000000001', 'aaaa1111-aaaa-1111-aaaa-111111111111', 'Project A1'),
  ('a1a1a1a1-0000-0000-0000-000000000002', 'aaaa1111-aaaa-1111-aaaa-111111111111', 'Project A2'),
  ('b2b2b2b2-0000-0000-0000-000000000001', 'bbbb2222-bbbb-2222-bbbb-222222222222', 'Project B1');

Two tenants is the minimum that lets you assert isolation. Tenant A has multiple rows so the "I see my own rows" direction is non-trivial; tenant B's row exists so the "I cannot see the other tenant's row" direction has a target to fail on. The UUIDs are fixed values so assertions can reference them by literal.

Per-assertion role switching in pgTAP

The test runner switches the authenticated session role per assertion. In pgTAP this is a set_config('request.jwt.claims', ...) plus set local role authenticated inside the transaction. The role switch is the whole point — without it, the test runs as the superuser that loaded the fixtures, which the Postgres docs note bypasses RLS entirely. Skip the role switch and you are back to writing service-role tests that prove nothing.

-- supabase/tests/rls/projects_isolation.sql
begin;
select plan(4);

\i fixtures/two_tenants.sql

-- Helper that sets the authenticated session to a given user id.
create or replace function test_authenticate_as(user_id uuid) returns void as $
begin
  perform set_config(
    'request.jwt.claims',
    json_build_object(
      'sub', user_id::text,
      'role', 'authenticated'
    )::text,
    true
  );
  set local role authenticated;
end;
$ language plpgsql;

-- Assertion 1: tenant A can see tenant A's projects.
select test_authenticate_as('11111111-1111-1111-1111-111111111111');
select results_eq(
  'select id from public.projects order by id',
  $values ('a1a1a1a1-0000-0000-0000-000000000001'::uuid),
          ('a1a1a1a1-0000-0000-0000-000000000002'::uuid)$,
  'tenant A sees only tenant A projects'
);

-- Assertion 2: tenant A cannot see tenant B's projects.
select is_empty(
  $select id from public.projects
    where tenant_id = 'bbbb2222-bbbb-2222-bbbb-222222222222'$,
  'tenant A cannot read tenant B rows'
);

-- Assertion 3: tenant A cannot UPDATE tenant B's row.
select is(
  (with attempt as (
     update public.projects set name = 'pwned'
     where id = 'b2b2b2b2-0000-0000-0000-000000000001'
     returning 1
   ) select count(*) from attempt),
  0::bigint,
  'tenant A cannot update tenant B rows'
);

-- Assertion 4: tenant A cannot DELETE tenant B's row.
select is(
  (with attempt as (
     delete from public.projects
     where id = 'b2b2b2b2-0000-0000-0000-000000000001'
     returning 1
   ) select count(*) from attempt),
  0::bigint,
  'tenant A cannot delete tenant B rows'
);

select * from finish();
rollback;

The shape of every assertion is the same: switch into a tenant's role, run the operation that should be blocked, assert the result set is empty or the row count is zero. The transaction-and-rollback wrapper at the top and bottom means the next test starts from the same fixture state — no test order coupling, no leftover data.

Schema-level invariant check against pg_catalog

The schema-level invariant check runs as its own pgTAP test and catches the "new table without a policy" failure mode by querying pg_catalog directly. The Postgres documentation is explicit that the default-deny rule applies only when RLS has been enabled — so the invariant has two parts: every application-schema table must have rowsecurity = true, and every such table must have at least one policy.

-- supabase/tests/rls/schema_invariants.sql
begin;
select plan(2);

-- Every application-schema table has RLS enabled.
select is(
  (select count(*)::bigint
   from pg_tables
   where schemaname = 'public'
     and rowsecurity = false
     and tablename not in ('schema_migrations')), -- allowlist for known-safe tables
  0::bigint,
  'every public table has RLS enabled'
);

-- Every application-schema table has at least one policy.
select is(
  (select count(*)::bigint
   from pg_tables t
   where t.schemaname = 'public'
     and t.tablename not in ('schema_migrations')
     and not exists (
       select 1 from pg_policies p
       where p.schemaname = t.schemaname
         and p.tablename = t.tablename
     )),
  0::bigint,
  'every public table has at least one policy defined'
);

select * from finish();
rollback;

That second assertion is the one that catches the most production bugs. RLS-enabled with no policy is the default-deny state — safe but unusable. RLS-disabled with no policy is the wide-open state — dangerous and the most common new-table mistake. The pg_catalog query distinguishes the two and fails the build on the wrong one.

Vitest layer covering the JWT roundtrip

The TypeScript layer covers what pgTAP cannot: the end-to-end check that the auth layer issues the right JWT, the client library passes it through, and the policy fires the same way it does in production. Using vitest and the Supabase JS client, this looks roughly like:

// tests/rls/projects.test.ts
import { createClient } from "@supabase/supabase-js";
import { beforeAll, describe, expect, it } from "vitest";

const url = process.env.SUPABASE_URL!;
const anonKey = process.env.SUPABASE_ANON_KEY!;

async function signedInClient(email: string, password: string) {
  const client = createClient(url, anonKey);
  const { data, error } = await client.auth.signInWithPassword({ email, password });
  if (error) throw error;
  return client;
}

describe("projects RLS — tenant isolation", () => {
  let tenantA: Awaited<ReturnType<typeof signedInClient>>;
  let tenantB: Awaited<ReturnType<typeof signedInClient>>;

  beforeAll(async () => {
    tenantA = await signedInClient("a@test.local", "test-password");
    tenantB = await signedInClient("b@test.local", "test-password");
  });

  it("tenant A sees only tenant A projects", async () => {
    const { data, error } = await tenantA.from("projects").select("id");
    expect(error).toBeNull();
    expect(data?.map((r) => r.id).sort()).toEqual([
      "a1a1a1a1-0000-0000-0000-000000000001",
      "a1a1a1a1-0000-0000-0000-000000000002",
    ]);
  });

  it("tenant A cannot read tenant B rows by id", async () => {
    const { data, error } = await tenantA
      .from("projects")
      .select("id")
      .eq("id", "b2b2b2b2-0000-0000-0000-000000000001");
    expect(error).toBeNull();
    expect(data).toEqual([]); // RLS hides the row; no error, just empty
  });

  it("tenant A cannot update tenant B rows", async () => {
    const { data, error } = await tenantA
      .from("projects")
      .update({ name: "pwned" })
      .eq("id", "b2b2b2b2-0000-0000-0000-000000000001")
      .select();
    expect(error).toBeNull(); // RLS returns success-with-zero-rows, not an error
    expect(data).toEqual([]);
  });
});

A subtle point in that third test: under RLS, a write that touches no rows returns success with an empty data set rather than a permissions error. A test that asserts error is non-null will fail even though the policy is doing its job. Assert against the returned row set, not the error shape.

CI gate that blocks merge

The CI integration is one job that runs supabase test db followed by npx vitest run tests/rls. The pgTAP suite runs first because it is faster and catches the schema invariants in seconds; the vitest suite runs second because it is slower but covers the JWT roundtrip. Both fail the pull request build on a single assertion failure. Gating merge on the build means a policy regression cannot land without an explicit human override.