Testing row-level security for what should fail

A too-permissive policy does not throw. It returns rows.

Row-level security in Postgres is the right answer for multi-tenant data. It puts the access rule next to the data instead of in application code, so a forgotten WHERE user_id = ... cannot leak another tenant's rows.

It also has a failure mode that is easy to miss, and it is the same shape as most of the bugs I find genuinely dangerous: getting it wrong does not produce an error.

The asymmetry

Consider two mistakes.

Too restrictive. Your policy is stricter than intended. The app breaks immediately, loudly, in development. Someone's own data does not appear. You fix it in minutes.

Too permissive. Your policy allows more than intended. Nothing breaks. Every test passes, because every test asks "can this user see their own data?" and the answer is still yes. The bug only manifests as a second tenant's rows appearing in a response nobody is inspecting.

Test what should fail

The fix is not clever, just unusual: write assertions about what a user cannot reach. For every table with a policy, you want a test that authenticates as user A, requests user B's row by primary key, and asserts the result is empty.

-- as user A, asking for a row that belongs to user B
select count(*) from flows where id = '<user_b_flow_id>';
-- expected: 0, not an error

Note the expectation. RLS does not raise a permission error on a filtered read — it returns zero rows, exactly as if the row did not exist. If your test asserts on an exception you will never catch the regression.

Four things worth checking specifically

• Is RLS actually enabled on the table? Writing a policy on a table without ENABLE ROW LEVEL SECURITY creates a policy that does nothing. There is no warning.
• Does the policy cover every command? A policy scoped to SELECT leaves UPDATE and DELETE unprotected. Verify each separately — read isolation and write isolation are different claims.
• What role does your server connect as? Table owners and superusers bypass RLS entirely by default. A policy that works perfectly from the client can be completely inert from the backend.
• New tables have no policy. Adding a table is adding an unprotected surface unless you remember. This is worth a checklist item in your migration process rather than a thing you hope to recall.

The general principle

This is the same class of problem as a build that runs nothing (the build that succeeded at nothing) or a flow that completes without doing anything (the flow that ran and did nothing). In each case the system reports success and the success is real — it just is not the thing you cared about.

Where a mistake is silent, the test has to assert on the absence, because nothing else will.