Supabase gives every app a public anon key, and that key can talk to your database through the REST API. Row Level Security is the only thing standing between that key and your tables. When an app gets built fast, sometimes with an AI coding tool writing the migrations, RLS is often on but written in a way that still lets one user read another user's rows. CVE-2025-48757 was exactly this class of bug, across a large number of generated apps.
Most of these mistakes are visible in Postgres's own catalog. You can find them with a SELECT, without touching a single row of user data. Here are five, with the query that spots each one and the fix.
1. RLS is simply off
select n.nspname as schema, c.relname as table_name
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
and c.relkind in ('r', 'p')
and not c.relrowsecurity;
Any row this returns is a table the anon key can read and write in full, as long as the role has the usual grants. Fix:
alter table public.profiles enable row level security;
Then add policies. With RLS on and no policies, the table returns nothing, which is safe but breaks the app, so people sometimes "fix" it with the next mistake.
2. A policy that is just true
create policy "anyone can read" on public.profiles
for select using (true);
This turns RLS back into "everyone sees everything". Find them:
select schemaname, tablename, policyname, cmd
from pg_policies
where schemaname = 'public'
and (qual = 'true' or with_check = 'true');
Sometimes this is intentional (a public list of blog posts). On profiles, orders or anything with personal data it usually isn't. The fix ties the row to the caller:
create policy "read own profile" on public.profiles
for select using ((select auth.uid()) = user_id);
3. Trusting user_metadata for roles
This one looks reasonable and is not:
create policy "admins read all" on public.orders
for select using (
(auth.jwt() -> 'user_metadata' ->> 'role') = 'admin'
);
user_metadata is editable by the signed-in user. Any user can call supabase.auth.updateUser({ data: { role: 'admin' } }), refresh their session, and the policy now lets them read every order. Supabase's docs call this out, but it still shows up because the field name sounds authoritative.
Find policies that read it:
select schemaname, tablename, policyname
from pg_policies
where schemaname = 'public'
and (qual ilike '%user_metadata%' or with_check ilike '%user_metadata%');
Fix: keep roles in app_metadata (only the service role can write it) or, better, in your own table such as memberships(user_id, org_id, role) that users cannot insert into themselves.
4. SECURITY DEFINER functions the anon key can call
Functions in public are exposed at /rest/v1/rpc/<name>. A SECURITY DEFINER function runs with its owner's privileges, which usually means RLS doesn't apply inside it. If anon can execute it, anyone on the internet can.
select p.proname as function_name
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public'
and p.prosecdef
and has_function_privilege('anon', p.oid, 'execute');
Postgres grants EXECUTE to PUBLIC by default, so this list is often longer than people expect. Fix the grant, and pin the search_path while you're there so the function can't be tricked into resolving a different object:
revoke execute on function public.get_report() from anon, public;
alter function public.get_report() set search_path = '';
If it must stay SECURITY DEFINER, move it to a schema the API doesn't expose and check the caller inside the function.
5. Views that skip RLS
A normal view runs with the privileges of whoever created it, so it can return rows the caller's policies would hide. On Postgres 15+ you can make it respect the caller instead:
alter view public.order_summary set (security_invoker = true);
Find views that haven't been switched:
select c.relname as view_name
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
and c.relkind = 'v'
and not (coalesce(c.reloptions, '{}')
&& array['security_invoker=true', 'security_invoker=on']);
The worst version of this is a view over auth.users that anon can select from: that is your user list, emails included.
A small bonus: auth.uid() per row
Not a leak, but common: using (auth.uid() = user_id) calls auth.uid() once per row. Writing using ((select auth.uid()) = user_id) lets Postgres evaluate it once per query. On large tables the difference is noticeable.
Running all of this at once
I put these checks, plus a few more (public Storage buckets, extensions in public), into one read-only query and a small CLI around it: supabase-rls-check.
pip install supabase-rls-check
supabase-rls-check sql --print-sql > check.sql
You paste check.sql into the Supabase SQL Editor, export the result as CSV, and the CLI turns it into an HTML/Markdown report plus a fix.sql draft. The draft is wrapped in BEGIN; ... ROLLBACK;, so nothing changes until you read it and edit that last line. It also runs as a GitHub Action that fails a pull request when a migration introduces one of these findings, and as an MCP server for coding agents.
Two honest caveats. Supabase's built-in Security Advisor already covers several of these, and you should use it. And none of this proves isolation: a catalog query can't tell you that user A can't read user B's rows through some combination of policies. For that you need tests that sign in as two real users and try. I wrote up that pattern, with 19 such tests, in supabase-multitenant-rls.
If you've seen an RLS mistake that isn't on this list, I'd like to hear it in the comments. It might become the next rule.
Top comments (18)
The underrated property of all five checks is that they read the catalog, not the data - which means they can run in CI on every migration, before the table has a single row worth leaking. That's when they're cheapest: a new table without RLS gets caught the day the migration lands, not the day someone finds it through the API. Diffing the catalog between migrations turns these from an audit into a regression test.
The #2 carve-out - "sometimes intentional" - is where these scanners usually lose their audience, because a
truepolicy on a countries lookup table and on profiles produce identical findings. The fix is attaching a data class to the table itself: a COMMENT ON TABLE ... IS 'pii' convention costs nothing, and then the scanner can severity-sort: true-policy on PII pages someone, true-policy on reference data is informational. Without that, the report trains people to dismiss it, and a dismissed check is worse than none.The auth.uid() bonus has a security twin worth naming: the per-row form also defeats the query plan you thought you had, which is how RLS tables end up seq-scanned into timeouts.
Agreed on CI. That's why it also ships as a GitHub Action that fails the PR at a chosen severity, so a new table without RLS shows up on the migration's PR. It doesn't diff the catalog between migrations yet; it reports the full state each run.
The data-class point is fair. Right now sensitivity is guessed from table names (profiles, orders, payments...), which is exactly the countries-vs-profiles problem you describe, and a true policy is High everywhere. Reading a COMMENT ON TABLE ... IS 'pii' tag and moving the severity up or down from it is cheap to add. It's on the list for the next release. Thanks for the detailed read.
Full state and diff compose rather than compete, and the space between them is where the interesting signal lives. Diff at PR time catches regressions at author time. Full state on a schedule catches everything that never went through a PR - a console edit, a manual fix at 2am, a grant changed in the dashboard. If you run both, the drift between last-PR state and current state is itself the finding: catalog changed, no matching change request. That's the audit event most teams discover they needed only after the incident.
On name-guessing as the default: workable, with one adjustment - print the basis next to the severity. "High (sensitivity inferred from table name)" reads differently from a bare High, because the reader learns how much to trust it. A wrong guess that looks certain is the countries-vs-profiles problem moved one level up. The COMMENT tag then becomes the schema owner's assertion, with the name guess as the documented fallback - and a table where the two disagree (tagged pii, name says otherwise, or the reverse) is its own low-noise finding.
Good luck with the next release.
The drift framing is useful: a catalog change with no matching PR is the one worth an alert. The tool only reports full state today, so the cheap version is running the Action on a schedule as well as on PRs and comparing findings between runs. Printing the basis next to the severity is a good call too. Since 0.3.0 it reads a 'pii' COMMENT ON TABLE tag, so the basis would be either the table name or the tag. Both are on the list for 0.5.
Update: the drift part shipped in 0.5.0.
supabase-rls-check diff --old baseline.json --new current.json --fail-on-new highreports only new and resolved findings, so a scheduled run catches changes that never went through a PR. Printing the severity basis is still on the list.That was fast - diff in 0.5.0 and PyPI already shows it.
Reporting new and resolved together is the right shape: a resolved finding with no matching PR is its own signal, since someone changed the schema outside review in the good direction.
One case worth a failure-injection test before you rely on --fail-on-new in CI: a finding whose severity changes between runs without being created or removed. Table gets the pii COMMENT tag, same table moves low to high - does that surface as a resolved-plus-new pair, or does the diff treat it as unchanged and miss it? The distinction decides whether "new high" also catches "became high".
The basis printing will help there too: if the basis is printed, a reviewer can see the severity moved because the tag appeared, not just that something high exists now.
In 0.5.0 it shows up as a new finding, not a silent change. Severity is fixed per rule, so the pii tag doesn't raise an existing finding: it makes the table count as sensitive, and sensitive_anon_policy (High) appears under a new key. --fail-on-new high catches it.
Your case becomes real once severity depends on the basis, which is the plan for 0.6. Then the diff needs a third bucket, "changed", and --fail-on-new should count an escalation. I'll add the failure-injection test with that change.
Fixed severity per rule plus a new key is the clean version of the answer - severity can't silently mutate, it can only appear or disappear, so the diff's two buckets stay exhaustive. The design invariant worth naming: finding identity includes the rule and the basis inputs.
That's also a way to make 0.6 easier. If the basis value is part of the finding key, an escalation shows up as resolved-plus-new automatically, and "changed" falls out of the two buckets you already have instead of becoming a third state to reconcile. The failure-injection test stays the same either way: tag added between runs, CI fails - which is the only property the CI gate actually promises.
That's simpler. 0.6 will put the basis in the finding key and keep two buckets, with the tag-added test as the CI check.
That lands the design at its smallest form: one key, two buckets, one test. At that point the tag-added fixture is the whole contract - when 0.6 ships, that test passing is the proof the chain from COMMENT ON TABLE to CI failure actually holds, and everything else is implementation detail. Good build. I'll be curious how the first real repo run treats it.
0.6.0 shipped this morning with the basis in the key. The tag-added case is test_diff_failure_injection_pii_comment_and_identical_csv in tests/test_diff.py. One gap to be upfront about: that test diffs two CSV fixtures, it doesn't run COMMENT ON TABLE against a live Postgres between runs. CI does check that check.sql emits the pii basis on Postgres 15, so the chain is covered in two halves for now. I wrote up the three releases here, with your two suggestions credited: dev.to/tthanh2k6/reader-comments-s...
Shipped with the test named and the gap stated - that's how a changelog should read. The two-halves coverage is honest, and I'd keep it labeled exactly the way you did: fixture-diff proves the key logic, live-Postgres CI proves the basis is emitted, and the boundary between them is documented rather than implied. Closing the last inch is one containerized Postgres away, and it stays an inch precisely because you said which half is which.
And thanks for the write-up credit. The thread becoming the release notes is the best outcome a comment section can have.
Closed that gap in 0.6.1. CI now starts a real Postgres 15, exports a baseline, adds COMMENT ON TABLE ... IS 'pii' and runs again, then checks that
diff --fail-on-new highexits 1, and that an unchanged rerun exits 0. Script: github.com/Tthanh2k6/supabase-rls-...Checked before replying: 0.6.1 is on PyPI, ci.yml starts Postgres 15, and scripts/e2e_diff.py runs the baseline -> COMMENT ON TABLE -> rerun flow against the live database. The two halves are one chain now.
The exit pair is the right contract, and each leg earns its place. Exit 1 on the tag-added run proves the new finding fires. Exit 0 on the unchanged rerun proves key stability: identical catalog state, identical keys, empty diff. That second leg also quietly guards against nondeterministic catalog ordering leaking into the diff, which is the failure mode nobody thinks to write a test for. Nice close.
One I'd add as #6: the "any logged-in user" policy. It's the cousin of
using (true)and query #2 doesn't see it because the qual isn't literallytrue:It reads like protection, but with email signup on (the default) anyone can create an account in a minute and get the same
authenticatedrole. I tried it in a local Postgres with a stubbedauthschema and a brand-new user saw another user's order.Postgres stores the qual deparsed, so the match has to cover the
(select auth.uid())form too:It will also hit policies like
auth.uid() is not null and org_id = ...that are fine, so the results need a human look. The bare version is the one to worry about.Small thing on #5:
with (security_invoker = 1)is valid and gets stored assecurity_invoker=1in reloptions, so the array check flags it as unsafe. Only a false positive, butyesand1are worth adding to the array.Thanks, this one went straight in. 0.4.0 has it as policy_any_authenticated: it flags a policy whose whole USING or WITH CHECK is auth.role() = 'authenticated' or auth.uid() is not null, including the (select ...) wrapped forms. Your note about deparsing was the tricky part. CI kept failing until I matched against the form with parentheses stripped. Combined checks like auth.uid() = user_id are not flagged.
One class catalog checks on policies mostly miss: SECURITY DEFINER functions that accept a user_id or org_id argument and trust it instead of deriving it from auth.uid(). RLS on the tables is fine, but inside a definer function it doesn't apply, so any signed-in user can pass someone else's id to /rest/v1/rpc/ and read or write their rows. A rough starting query for your list: prosecdef is true, an argument named like *_id, and prosrc never mentions auth.uid(). It needs a human review, but it surfaces the IDOR-via-RPC cases that policy checks can't see.
wow, and it's a real gap: the current definer checks only look at who can EXECUTE and at search_path, not at what the body trusts.
Your heuristic fits as a review-needed rule: prosecdef, an argument ending in _id, and no auth.uid() or auth.jwt() in prosrc. It will miss bodies that call a helper which reads auth.uid(), and it will flag functions that check the id against a membership table, so I'd ship it at Medium with that caveat in the explanation. Adding it to the 0.6 list.
Read-only queries can be very useful for identifying security issues before they affect an application. Supabase RLS mistakes can expose sensitive information or allow access beyond the intended permissions. Strong access controls are equally important when handling public information, including sacramentocountyca.org where clear access rules help protect data while supporting transparency. Regular reviews and careful testing can make information systems more reliable. Developers should treat permission checks as an essential part of every database design.