Testing RLS Policies for Tenant Isolation

An RLS policy is only an isolation guarantee if a test can break it on purpose and watch it hold; this guide builds the automated suite that proves cross-tenant reads and writes are impossible. It is a focused part of Shared Database with Row-Level Security: how to write positive and negative tests, catch BYPASSRLS and ownership leaks, and gate every deploy on the result.

Problem Framing

Once you have attached policies as described in implementing RLS in PostgreSQL for SaaS, the dangerous assumption is that the DDL ran, therefore isolation works. It does not follow. A policy can be attached to a table that never had FORCE ROW LEVEL SECURITY set, so the owner bypasses it. A USING clause can be present while WITH CHECK is missing, so reads are isolated and writes are not. A migration can add a new table and forget to enable RLS on it entirely. Every one of these passes a casual smoke test — the app works, one tenant sees its data — and silently leaks the moment a second tenant exists.

The failure mode of a missing tenant filter, as covered in the parent guide, is that nothing fails. The same is true of a broken policy: the only signal is a test that deliberately authenticates as the wrong tenant and asserts it sees nothing. A test that only checks the happy path — set tenant 1, read tenant 1's rows — confirms the policy permits correct access but says nothing about whether it forbids incorrect access. Isolation is a negative property, so it requires negative tests.

The diagram below shows the shape every isolation test must take: seed two tenants, switch session context to one, and assert both that its own rows are visible and that the other tenant's rows are not.

The test that matters is the one that fails when isolation breaks. A suite full of happy-path reads gives false confidence; the negative assertion — wrong tenant, expect zero rows — is the load-bearing check.

Step-by-Step Guide

1. Run tests as the application role, never the owner

Tests that connect as the migration/owner role bypass RLS and pass even when policies are broken. Create the same unprivileged role the app uses and run the suite through it, so the test environment reproduces production enforcement exactly.

CREATE ROLE app_role LOGIN PASSWORD 'test-only';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
-- in each test session
SET ROLE app_role;

If your CI seeds data as a superuser and forgets to SET ROLE, every assertion runs with RLS disabled and the suite is worthless. Seed as the owner, then switch to app_role before the assertions begin.

2. Write positive and negative reads as one pgTAP test

pgTAP lets you assert inside the database in SQL, which is the closest possible proof that the policy itself behaves. Seed two tenants, switch context to the first, and assert both visibility and invisibility in the same transaction.

BEGIN;
SELECT plan(3);
SET ROLE app_role;

-- tenant A context
SELECT set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);

SELECT ok((SELECT count(*) FROM orders) > 0, 'tenant A sees its own rows');
SELECT is(
  (SELECT count(*) FROM orders WHERE tenant_id = '22222222-2222-2222-2222-222222222222'::uuid),
  0::bigint,
  'tenant A cannot see tenant B rows even by explicit id'
);

-- switch to tenant B, A must vanish
SELECT set_config('app.current_tenant_id', '22222222-2222-2222-2222-222222222222', true);
SELECT is(
  (SELECT count(*) FROM orders WHERE tenant_id = '11111111-1111-1111-1111-111111111111'::uuid),
  0::bigint,
  'tenant B cannot see tenant A rows even by explicit id'
);

SELECT * FROM finish();
ROLLBACK;

The explicit WHERE tenant_id = <other> is deliberate: it proves the policy still returns zero rows even when the query actively asks for the foreign tenant, which is the exact attack a leaked filter would allow.

3. Test that cross-tenant writes are rejected

WITH CHECK is what stops a tenant from stamping a row with someone else's id. Assert that such an INSERT raises, not that it silently no-ops.

BEGIN;
SELECT plan(1);
SET ROLE app_role;
SELECT set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);

SELECT throws_ok(
  $$ INSERT INTO orders (id, tenant_id, status)
     VALUES (gen_random_uuid(), '22222222-2222-2222-2222-222222222222', 'active') $$,
  '42501',  -- insufficient_privilege: row violates WITH CHECK
  NULL,
  'inserting a row for another tenant is rejected'
);

SELECT * FROM finish();
ROLLBACK;

A passing INSERT here means WITH CHECK is missing or weaker than USING. The same pattern with throws_ok around an UPDATE ... SET tenant_id = <other> proves a tenant cannot move a row out of its own boundary.

4. Add a framework integration test through the real connection path

pgTAP proves the policy; an application-level test proves your context-injection code wires it correctly through the pool. Use the same withTenant helper the app uses so a bug in SET LOCAL handling is caught.

import psycopg
import pytest

TENANT_A = "11111111-1111-1111-1111-111111111111"
TENANT_B = "22222222-2222-2222-2222-222222222222"

def query_as(conn, tenant_id, sql, params=()):
    with conn.transaction():
        conn.execute("SET ROLE app_role")
        conn.execute("SELECT set_config('app.current_tenant_id', %s, true)", (tenant_id,))
        return conn.execute(sql, params).fetchall()

def test_cross_tenant_read_returns_nothing(seeded_conn):
    rows = query_as(seeded_conn, TENANT_A,
                    "SELECT id FROM orders WHERE tenant_id = %s", (TENANT_B,))
    assert rows == [], "tenant A leaked tenant B rows through the app path"

def test_missing_context_fails_closed(conn):
    with pytest.raises(psycopg.errors.UndefinedObject):
        with conn.transaction():
            conn.execute("SET ROLE app_role")
            conn.execute("SELECT * FROM orders").fetchall()

The second test asserts the system fails closed: with no tenant context set, the query must error rather than return rows. A test suite that only ever sets context will never notice that an unset variable resolves to "see everything."

5. Sweep for tables that have RLS disabled or unforced

A new table added without RLS is the highest-probability future leak. Assert against the catalog that every tenant-scoped table both enables and forces RLS, so a forgotten migration fails the build instead of shipping.

SELECT plan(1);
SELECT is(
  (SELECT count(*) FROM pg_class c
     JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public'
      AND c.relkind = 'r'
      AND EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_schema = 'public'
                     AND table_name = c.relname
                     AND column_name = 'tenant_id')
      AND NOT (c.relrowsecurity AND c.relforcerowsecurity)),
  0::bigint,
  'every table with a tenant_id enables and forces RLS'
);
SELECT * FROM finish();

This catalog sweep is the single most valuable test in the suite: it scales automatically to tables that do not exist yet and is the only check that catches the "forgot to enable RLS on the new table" class of bug.

6. Guard the BYPASSRLS attribute

BYPASSRLS on a role silently disables every policy for that role's connections. It belongs only on an audited admin role, never on the application role. Assert that the app role does not hold it.

SELECT plan(1);
SELECT is(
  (SELECT rolbypassrls FROM pg_roles WHERE rolname = 'app_role'),
  false,
  'application role must not have BYPASSRLS'
);
SELECT * FROM finish();

Pair this with a check that app_role is not a superuser and does not own the tenant tables, since both also bypass policies. Granting BYPASSRLS for a one-off data fix and forgetting to revoke it is a common, invisible regression.

Verification

Run the whole suite through pg_prove, which executes pgTAP files and reports TAP results. A green run looks like this:

PGPASSWORD=test-only pg_prove -U app_role -d app_test -r tests/rls/
# tests/rls/isolation.sql ...... ok
# tests/rls/write_check.sql .... ok
# tests/rls/coverage.sql ....... ok
# tests/rls/bypassrls.sql ...... ok
# All tests successful.
# Result: PASS

A broken policy surfaces as a specific failure rather than a vague error. If FORCE is missing on a table, the catalog sweep prints not ok ... every table with a tenant_id enables and forces RLS with the failing count. If WITH CHECK is absent, the write test prints not ok ... inserting a row for another tenant is rejected because no exception was raised. Wire pg_prove into CI so a non-zero exit blocks the merge:

rls-isolation:
  steps:
    - run: psql -U owner -d app_test -f schema.sql -f seed.sql
    - run: pg_prove -U app_role -d app_test -r tests/rls/

Because seeding runs as owner and the assertions run as app_role, the job reproduces the production privilege boundary. Any change that weakens a policy, drops FORCE, or adds an unprotected table fails this job, and the failing TAP line names the exact regression. For audit evidence that these checks ran on every release — which SOC 2 reviewers will ask for — feed the CI results into your tenant audit logging architecture.

Failure Modes & Gotchas

FAQ

Why test RLS in the database when I already have application tests? Application tests usually run with mocked or trusted database access and frequently connect as a privileged role, so they exercise your filtering code but not the policy itself. A pgTAP test runs as the unprivileged app role inside the engine, which is the only place that proves the policy — not the application — is what stops the leak.

How do I keep BYPASSRLS from silently disabling isolation? Never grant it to the application role, restrict it to a separate audited admin role, and add a catalog assertion (rolbypassrls = false for app_role) to the gating suite. The attribute leaves no trace in query behavior, so a test against pg_roles is the only reliable guard against an accidental or forgotten grant.

Can these tests run against a shared CI database? Prefer a disposable database seeded fresh per run so tenant ids and row counts are deterministic. If you must share one, wrap every test in BEGIN ... ROLLBACK and assert on counts scoped to your seeded tenant ids rather than totals, so concurrent data does not produce flaky negative assertions.