DEV Community

Five Supabase RLS Mistakes a Read-Only Query Can Catch

Trung Thanh Tran on November 10, 2026

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

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.