0
Challenge · 60RLS at Scale

Testing policies: impersonation & pgTAP

Testingused in ~70% of apps

You paste your app's exact SELECT into the SQL Editor to double-check the data before you ship. It looks perfect. What you did not notice is which role ran it: the SQL Editor connects as the table owner, and an owner is exempt from the very policies you are trying to verify. You just proved nothing about your RLS, in either direction — and you shipped anyway.

Core idea. Testing a policy means proving a specific Postgres role, wearing a specific user's identity, gets a specific outcome — and neither clicking through your app nor querying in the SQL Editor forces you to become the attacker. A real test (1) impersonates the exact role and, where ownership matters, the exact user's `sub` claim, (2) asserts on the shape of the result — an empty set, a thrown error code — instead of eyeballing rows, and (3) always runs inside a transaction it rolls back. `pgTAP`, run through `supabase test db`, is exactly that pattern formalized so it runs in CI on every pull request, not just in your head the night before launch.
Playground, this isn't a screenshot
BEGINimpersonating bobalways ROLLBACK
select * from posts where id = 2;
The pattern: every case above runs inside one transaction that is always rolled back — pass or fail, the fixture is untouched. That is what lets a test insert, update and delete freely without ever leaving residue for the next test, and it is exactly what supabase test db does around every pgTAP file.
↑ play a little first, then dig in