Why We Test Row-Level Security Against a Real Postgres Database, Not Mocks

Row-level security is one of the few places in a backend where I think mocking your database is close to indefensible. RLS policies aren't application logic you wrote and understand the shape of — they're rules enforced by the database engine itself, evaluated per-row, per-query, based on session context. A mock can't tell you whether a real policy actually does what you think it does, because a mock doesn't run the policy. It runs your assumption about the policy.

When we built the auth and data-access layer for a multi-tenant healthcare platform — Supabase/Postgres underneath a Next.js/TypeScript monorepo, with magic-link/OTP authentication — this wasn't a theoretical concern. Getting row-level security wrong in a healthcare context isn't a bug, it's a data breach. So the test suite (14 tests, and growing) runs against a real, disposable Postgres instance, not a mock, and that decision is the single highest-leverage testing choice in the codebase.

What a mock actually can't catch

Mocking the database for RLS tests usually means mocking the query layer — you assert that your application code called the database with the parameters you expected, and you trust that the policy you wrote does the right thing with them. That trust is exactly the thing under test, and a mock structurally cannot verify it, because:

• Policy interaction effects. Postgres RLS policies combine with AND/OR depending on how they're defined (permissive vs. restrictive), and with multiple policies on a table, the actual enforced behaviour is a function of all of them together. You cannot reason about this correctly by reading one policy in isolation — you have to run the query.
• Session context propagation. RLS policies typically key off session variables (e.g., the authenticated user's ID via auth.uid() in Supabase's convention). Whether that context is actually set correctly, at the right point in the connection lifecycle, for the actual role the application connects as, is an integration concern a mock skips entirely.
• Role and grant subtleties. RLS interacts with the underlying GRANT/role system — a policy can be technically correct and still leak data if the connecting role has table-level privileges that bypass or interact unexpectedly with the policy. This is invisible until you run against the real privilege system.

What the suite actually asserts

The structure that's worked well is to test from the perspective of each role or tenant context that the system supports, asserting both what should be visible and what should be invisible — the second half is the one people skip, and it's the half that actually matters for security:

-- Setup: two tenants, each with their own row
insert into records (id, tenant_id, payload) values
  (1, 'tenant_a', 'alpha-data'),
  (2, 'tenant_b', 'beta-data');

def test_tenant_isolation_positive_and_negative(db):
    # Positive: tenant A can see its own row
    as_tenant_a = db.connect_as(tenant="tenant_a")
    assert as_tenant_a.query("select * from records").row_ids() == [1]

    # Negative: tenant A cannot see tenant B's row, even by direct id lookup
    result = as_tenant_a.query("select * from records where id = 2")
    assert result.row_ids() == []  # not an error — silently filtered, as RLS does

    # Negative: tenant A cannot write into tenant B's rows
    with pytest.raises(PolicyViolation):
        as_tenant_a.execute(
            "update records set payload = 'x' where id = 2"
        )

That silent-filtering behaviour in the second case is itself worth calling out: RLS doesn't throw a permission error for a SELECT that matches no visible rows; it just returns zero rows, indistinguishable from "that row doesn't exist." That's correct and intentional (it avoids leaking existence), but it means a test suite that only checks for "no error" will falsely pass a query that's silently returning the wrong (empty) data for the wrong reason. You have to assert the actual row set, not just the absence of an exception.

Making it reproducible, not just correct

A real-database test suite is only as good as its reproducibility. The parts that made this sustainable rather than flaky:

• A disposable instance per test run, via Docker, seeded fresh each time — not a shared dev database that accumulates state and false confidence.
• Deterministic seed data with intentionally adversarial rows (near-boundary tenant IDs, null edge cases) rather than only the happy-path rows a developer would think to add by hand.
• Migration tooling that runs the same schema and policy definitions the test suite validates against — so the tests can't silently drift from what's actually deployed.

The trade-off, honestly

Real-database tests are slower than mocks and require more infrastructure (a Docker-managed Postgres in CI, migration and seed tooling to keep it deterministic). That cost is real. But for anything where the correctness property you're testing is enforced by the database itself rather than by your application code, a faster test that can't actually catch the failure mode isn't a good trade — it's a false sense of coverage. For RLS specifically, given what's at stake when it's wrong, that trade isn't close.