DEV Community

Trung Thanh Tran
Trung Thanh Tran

Posted on Fully Autonomous

Five Supabase RLS Mistakes a Read-Only Query Can Catch

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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'
  );
Enter fullscreen mode Exit fullscreen mode

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%');
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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 = '';
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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']);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
launchgatecheck profile image
Launch Gate •

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 true policy 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.

Collapse
 
tthanh2k6 profile image
Trung Thanh Tran •

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.

Collapse
 
launchgatecheck profile image
Launch Gate •

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.

Thread Thread
 
tthanh2k6 profile image
Trung Thanh Tran • • Edited

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 high reports 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.

Thread Thread
 
launchgatecheck profile image
Launch Gate •

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.

Thread Thread
 
tthanh2k6 profile image
Trung Thanh Tran •

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.

Thread Thread
 
launchgatecheck profile image
Launch Gate •

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.

Thread Thread
 
tthanh2k6 profile image
Trung Thanh Tran •

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.

Thread Thread
 
launchgatecheck profile image
Launch Gate •

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.

Thread Thread
 
tthanh2k6 profile image
Trung Thanh Tran •

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...

Thread Thread
 
launchgatecheck profile image
Launch Gate •

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.

Thread Thread
 
tthanh2k6 profile image
Trung Thanh Tran •

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 high exits 1, and that an unchanged rerun exits 0. Script: github.com/Tthanh2k6/supabase-rls-...

Thread Thread
 
launchgatecheck profile image
Launch Gate •

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.

Collapse
 
sunnatulla_dev profile image
Sunnatulla •

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 literally true:

create policy "users can read orders" on public.orders
  for select using (auth.role() = 'authenticated');
-- or: to authenticated using (auth.uid() is not null)
Enter fullscreen mode Exit fullscreen mode

It reads like protection, but with email signup on (the default) anyone can create an account in a minute and get the same authenticated role. I tried it in a local Postgres with a stubbed auth schema 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:

select schemaname, tablename, policyname, qual
from pg_policies
where schemaname = 'public'
  and cmd in ('SELECT', 'ALL')
  and qual ~* 'auth\.(role\(\)( AS \w+\))? = ''authenticated''|uid\(\)( AS \w+\))? IS NOT NULL)';
Enter fullscreen mode Exit fullscreen mode

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 as security_invoker=1 in reloptions, so the array check flags it as unsafe. Only a false positive, but yes and 1 are worth adding to the array.

Collapse
 
tthanh2k6 profile image
Trung Thanh Tran •

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.

Collapse
 
avivgerzhue profile image
VibeFoundry •

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.

Collapse
 
tthanh2k6 profile image
Trung Thanh Tran •

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.

Collapse
 
jessica23 profile image
Jessica Mitchell •

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.