Repository navigation
Using PGlite to make a Postgres RLS failure reproducible in 2 seconds (no Docker, no credentials) #1075
Replies: 3 comments
❤️
Possibly you could leverage PGlite REPL and directly embed the example in the blog post: https://pglite.dev/repl/ |
|
That is a much better idea than what I have, and I had not connected the REPL to this problem at all. Thank you. The thing that makes it fit is that the demo has no application layer. There is no signup, no UI, no network path — the entire argument is The specific thing I would want a reader to try by hand, because it is the moment the penny drops: -- as the table owner, the policy you just wrote does nothing
select * from notes;
-- now be someone RLS applies to
set local role authenticated;
set local request.jwt.claims = '{"sub":"<user-b-uuid>"}';
select * from notes;Watching the same One thing I will need to check before I claim it works: whether Also worth saying: the reason the repo uses PGlite rather than a mock is that the behaviour under test is |
|
Love this — and yes, embedding pglite-repl so readers run the leak themselves is exactly the direction. "Trust me, this policy leaks" → a REPL block where they hit run and watch Since you write about RLS — the reason I went down the reproducibility rabbit hole is I built a small scanner for it: https://github.com/cekuu35/supabase-rls-monitor . It statically flags the same leak classes ( |
Uh oh!
There was an error while loading. Please reload this page.
Sharing a small thing PGlite made possible that I could not do cleanly before it existed.
I write about Postgres row-level security, and the recurring problem is that RLS arguments are unfalsifiable in a blog post. You can describe a policy that looks correct and leaks, but the reader has no way to check you without standing up a database, which nobody does for a five-minute read.
So I put the whole thing in a repo that runs the actual Postgres row-security machinery in PGlite:
https://github.com/cekuu35/supabase-rls-leak-demo
npm ci && npm run test:ciNo Docker, no cloud project, no credentials, about two seconds. Two branches with byte-for-byte identical test files, differing only by one policy file —
brokengives 4 failed / 1 passed,fixedgives 5 passed. The failing assertion prints the row that leaked across users rather than just a count, so the failure is self-explaining.Why PGlite specifically mattered here
The behaviour being demonstrated is not application logic, it is Postgres itself:
enable row level security, policy evaluation per command, and the fact that a table owner bypasses RLS unless you useforce row level security. A mock or an in-memory fake would have proved nothing — the whole point is that realpg_policysemantics decide the outcome. PGlite runs the real thing, so the test is evidence rather than an illustration.It also let me do something I think is underrated for teaching: the harness switches to a non-owner
authenticatedrole before asserting. That single detail is the difference between a test that passes because RLS works and a test that passes because the owner bypassed it — and it is trivial to get wrong in a real project too.Two small operational notes for anyone doing something similar:
npm ciplus the test command.set local roleplusset local request.jwt.claimsinside an explicit transaction reproduces the Supabase claim-passing pattern faithfully enough that the policies under test are the same ones you would ship.Disclosure so it is not buried: the repo is MIT and free, and I sell related things (a $29 SQL audit kit and the audit as a service) which are linked from its README. The fixture itself has no upsell in it and runs without any of that.
Thanks for PGlite — it turned "trust me, this policy leaks" into something a reader can run.
All reactions