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.
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.
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.
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.
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:
createpolicy"users can read orders"onpublic.ordersforselectusing(auth.role()='authenticated');-- or: to authenticated using (auth.uid() is not null)
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:
selectschemaname,tablename,policyname,qualfrompg_policieswhereschemaname='public'andcmdin('SELECT','ALL')andqual~*'auth\.(role\(\)( AS \w+\))? = ''authenticated''|uid\(\)( AS \w+\))? IS NOT NULL)';
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.
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.
We're a place where coders share, stay up-to-date and grow their careers.
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.
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.