Two weeks into RLS. My current workflow for checking a policy is: one psql tab as the postgres role to alter the policy, one tab where I paste a JWT claims blob and pretend to be user A, one tab for user B. Then I run the same select in both and eyeball it.
It works but it feels like something a person in 1998 would do. Is there a real workflow, or is this genuinely how everyone does it?
One session, one transaction, rolled back:
begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"user-a"}';
select count(*) from documents; -- expect 12
set local request.jwt.claims = '{"sub":"user-b"}';
select count(*) from documents; -- expect 0
rollback;
set local is scoped to the transaction so nothing leaks, and you can switch identity mid-transaction as many times as you like. Save it as a .sql file, run it with psql -f after every policy change. Three tabs becomes one command.
The fact that you can change the claims mid-transaction is what I was missing entirely. I assumed identity was fixed at connection time.
Add reset role; before the rollback if you keep going in the same session interactively - otherwise the next thing you type runs as authenticated and you get very confused about why you cannot alter your own table.
Small quality-of-life thing: put your seeded test users' claims into psql variables in ~/.psqlrc so you type :userA instead of pasting JSON. Sounds trivial, but the pasting is what makes people stop testing after the third policy.
Two weeks in, yes, this is normal and it does get better. RLS is one of the few things where the ceremony is front-loaded - the first five policies feel like a slog and then you have a template and it is thirty seconds each.
three terminal tabs and vibes is a testing strategy. it is not a good one but it is one.
@arrayformula_al · 8mo ago · 3 replies
Then get it out of your hands entirely. The test that matters is one sentence: user B cannot see user A's row. Write it once per table as an integration test, seed two users, run it in CI on every migration.
pgTAP if you like your assertions in SQL, or plain test-runner code hitting your real client if you would rather test the path the app uses. I prefer the latter, because the bug is usually not in the policy - it is in the one query that goes around it.
Reply
Report
@hemline_hank · 8mo ago
Wrote four of these against the real client this afternoon. The third one failed, which was alarming for about ninety seconds until I found the endpoint that builds its own query and skips the wrapper.
Reply
Report
@wren_oyelaran · 8mo ago
That is the whole argument for integration-level tests over pgTAP in one anecdote. The policy was right; the code path around it was not.
Reply
Report