A wrong RLS policy doesn't throw an error, it just returns the wrong rows, so it ships untested and you find out when data leaks. rlsautotest generates the pgTAP tests and the seed data straight from your policies, so you can catch it. Free and open-source.
The problem: RLS fails quietly
Row-Level Security is what keeps one tenant's data from another's.
When it's wrong, it doesn't error. It returns the wrong rows.
- Too strict → empty results → your app looks broken.
- Too loose → rows the user shouldn't see → your app leaks.
Neither raises an exception. You find out from a confused user, or a security report.
And "it works in the SQL editor" proves nothing. The SQL editor bypasses RLS, so your policies were never exercised.
Why nobody tests it
Supabase's own docs admit writing pgTAP tests for RLS is "inaccessible to most web developers."
To test one policy by hand you have to:
- create a few users (owner, other user, anon, role-holder)
- insert rows owned by different users
- become each identity (set JWT claims + role)
- run SELECT / INSERT / UPDATE / DELETE as each
- assert exactly which rows each can and can't touch
Per table. Per policy.
And the seed data matters as much as the test. Assert "the owner sees their row" against an empty table and it passes while proving nothing.
Most teams take one look and skip it. So the actual security boundary ships untested.
What a real RLS test checks
From each identity's point of view:
owner → sees and changes their own row
other user / tenant → can't see it at all
anon **→ blocked
**role-holder → exactly the access the role grants
All against a row really owned by the identity under test.
And "denied" has two flavors a good test keeps apart:
row-level filtering → zero rows
missing grant → permission error
That's a lot of careful setup, for every policy.
Generate it instead
I got tired of doing this by hand, so I built rlsautotest **(open-source, Apache-2.0).
**Repo: https://github.com/unitautogen/rlsautotest#readme
Not Supabase-only. It runs on any Postgres (Neon, RDS, your own server). I've tested it most on Supabase, so the examples here are Supabase-flavored.
Point it at a disposable copy of your DB. It reads your policies from the catalog and generates the tests and the seed data:
bashpip install rlsautotest
# quick check: who can touch what, as an HTML report
rlsautotest --db-url "$DATABASE_URL" --schema public --html rls-report.html
# or emit a native pgTAP suite to commit + run in CI
rlsautotest --db-url "$DATABASE_URL" --schema public --emit supabase/
The report reads like a permissions table:
notes SELECT INSERT UPDATE DELETE
service_role ✓ ✓ ✓ ✓
authenticated, authorized ✓ ✓ ✓ ✓
authenticated, not authorized · · · ·
anon · · · ·
You're hunting for one thing: a ✓ where a · should be. An anon or unauthorized user who can act. That's a hole, and it jumps out.
Two things make it trustworthy:
Real seed data. It works backward from each policy to a row actually owned by the identity, so green means real isolation, not an empty-table pass.
No false greens. Anything it can't verify soundly, it marks instead of faking.
And --report exits non-zero on a leak or an RLS-off table, so it drops straight into CI.
The bug that passes code review
The kind of thing this catches that humans miss:
A table has two permissive UPDATE policies. Each one's WITH CHECK limits the value a row can become (one status per role). But the check for who may write sits only in USING, not in WITH CHECK.
Here's the trap:
Postgres OR-combines every permissive policy's WITH CHECK, independent of which USING matched.
So the effective check becomes the union of all of them, with the "who" gone. Any identity that can touch the row can write any value any policy allows. A role can set a status its own policy forbids.
Each policy looks correct alone. The leak only exists in the combination. rlsautotest enumerates the value space per identity and shows you exactly which forbidden value slips through.
What it won't do (on purpose)
It proves your DB enforces what your policies declare, not what you intended. A wrong-but-consistent policy is confirmed, greenly.
A command with no policy shows as blocked and isn't asserted unless you opt in.
A policy behind an opaque function is reported, not faked.
A tester that emits confident checkmarks it can't back up is worse than no tester. So when it can't prove something, it says so.
Try it
Got policies you're not 100% sure about? Point it at a throwaway copy and read the report. One command. Worst case, you confirm you're fine.
Repo **+ **docs: https://github.com/unitautogen/rlsautotest#readme
Top comments (14)
The zero-rows versus permission-error split is a distinction most write-ups skip, glad you kept them apart. Does the generator also walk views and security definer paths? The leaks I've hit lived there, not in the table policies.
Straight answer: not yet. It walks base tables and their policies today, not views or SECURITY DEFINER paths. Both are exactly where rights get swapped for the owner's: a view without security_invoker runs as its owner and skips the base table's RLS, and a SECURITY DEFINER function runs as the definer unless it re-checks auth. A tool that stops at the table catches neither, which is why that's where I want to take it next. If you've got a sanitized example that bit you, I'd love it as a test case.
Here's a clean one to seed: a base table with per-user RLS, then a reporting view over it for some dashboard, created without security_invoker. The view runs as its owner, so PostgREST serves it straight to anon and every user's email comes back, and the base table's RLS never gets consulted. The tell you could assert on is that exact pair: a view whose base table has relrowsecurity and policies, but the view itself isn't security_invoker. Green base table, wide-open view.
Ok. I will work around your description to create the scenario. This should be mostly be fixed by next week. Will keep you posted. MEanwhile if you want to track it in github, i would encourage you to log enhancement over there. Here is the repo link- github.com/unitautogen/rlsautotest
@vollos - One ask if you are ok to answer. Do you face issues around unit testing your functions and triggers? How do you ensure proper code coverage and track the metrics?
Glad the view case is worth chasing. On your question, honest answer: coverage metrics aren't really my beat. I spend more time reviewing other people's code for security than maintaining a big suite of my own, so I'm not the one with a coverage regime to model. From the review side though, functions and triggers are the least-tested surface I see in AI-built Supabase apps, usually zero tests at all. And where there is coverage, line coverage misses the case that bites: whether the function ever ran as an identity that shouldn't be allowed. A SECURITY DEFINER function can be 100% line-covered and never once exercised against the wrong caller. So the thing I'd track is coverage by identity, not by lines.
@vollos - The requirement you mentioned for views and security definer paths has been implemented in rlsautotest version 0.3.0.
you may update it via
pip install --upgrade rlsautotest
Let me know if you face any issues.
Thanks.
Nice, that's a fast turnaround for both paths. Does the report call out the base-table-vs-view mismatch explicitly now, or does it fold views into the same per-table pass/fail matrix?
Its called out separately under a new section in the report called "Bypass Surfaces" with the name of the object, type of the object(view, materialised views or functions), severity of the bypass and the reason (why) to be listed in the section.
On a separate note, I am creating a pgTAP unit test generator for testing triggers, functions with branch and line coverage. Based on your suggestion yesterday, i am also going to add the security feature in which based on RLS , function coverage would be provided per DB role. Would that be something that you would be interested in testing with your client databases given your line of work? Do let me know.
Thanks.
Bypass Surfaces as its own section makes sense, glad it's not folded into the same matrix as the table checks. On testing against client data though, that's not something I can do, those engagements aren't mine to route a third-party tool through, sanitized or not. Happy to keep poking at it on my own test schemas if that's useful instead.
Totally fair on the client data . Your own test schemas can work great for this.
Give me about a week to get things ready and I'll ping you with a build you can run locally, nothing leaves your machine.
Appreciate the identity-coverage nudge.
Did you get a chance to test the new rlsautotest v 0.3.0? Do let me know if you have any queries or suggestions.
Thanks.
Silent failures in RLS are definitely one of the biggest foot guns when migrating from a traditional backend to Supabase. I usually tackle this by writing custom PL/pgSQL functions that explicitly assert the expected row counts and throw exceptions if the policy logic falls short during CI. Another approach that has saved me a lot of debugging time is using the Supabase CLI to run local migrations with seed data specifically designed to trigger edge cases in the policies. Have you found a specific testing library or framework that integrates better with Jest or Vitest for mocking the auth context during these RLS tests?
Honestly, I haven't found a Jest/Vitest library I'd trust here, because there's nothing to mock. auth.uid()/auth.jwt() just read request.jwt.claims and the current role, so the only faithful way to impersonate is on a real session: SET LOCAL ROLE authenticated + set_config('request.jwt.claims', '{"sub":...}', true), query, assert, roll back. Mock it in JS and you're testing the mock, not the policy. From Vitest that's a pg client, one transaction per test, set role and claims, assert, rollback. It's also why I emit pgTAP: the assertion runs in the same session as the policy, so nothing gets faked.
Your instincts are already right, by the way. The throwing PL/pgSQL asserts are hand-rolled pgTAP, and those edge-case seeds are the part that quietly rots when a policy changes.