Free read-only SQL script that lists RLS gaps in a Supabase project

[Fael7s]

A common problem in apps built fast, especially with AI tools: one signed-in user can read or change another user's rows, because a policy only checks that the caller is logged in, a view skips Row Level Security, or a user can update their own plan column.

I wrote a single SQL query that reads the database catalog and lists these problems by severity. It has 16 checks, including: tables the API can reach with RLS off or with no policy; policies that let every row through, apply to anon, or treat "signed in" as ownership; views and materialized views that bypass RLS; SECURITY DEFINER functions callable via /rpc or without a fixed search_path; public buckets and storage policies without an owner check; write policies that let users create rows in someone else's name or edit their own role or plan; access decided by user_metadata.

It only reads. It is one SELECT over catalog tables, and I ran it inside a read-only transaction against a Supabase-like test database, where Postgres rejects any write. The test database is published next to the script. Five checks are heuristics and can misfire, so please treat a finding as a lead, not a verdict.

How to run: open the SQL Editor, paste the file, run once. If your Data API exposes schemas other than public, add them at the top.

Script and instructions: https://rowproof.pages.dev/download.html

Feedback on false positives is welcome.

[cekuu35]

Solid scope — the 16-check single-SELECT approach is the right shape for this (zero-install catalog reads are what people actually run). Feedback on the heuristic classes from scanning 100+ public Supabase repos for exactly these patterns:

The highest false-positive-rate class: "signed in treated as ownership." The dominant shape in the wild is for select to authenticated using (true) — which your check flags, correctly — but the same row also catches two legitimate patterns: reference/catalog tables (countries, plan tiers, feature flags) where authenticated-wide reads are intentional, and lookup tables where a second ownership policy narrows the row set. The distinguishing signal worth adding: flag it differently when the table also has an ownership policy on the same cmd (a deliberate two-policy design) vs. when using (true) is the only policy.

Views: the PG15+ distinction matters. security_invoker views (and security_invoker = true functions in PG15+) actually respect RLS — they run as the caller. Only security_definer views bypass it. If your check flags all views the API can reach, every security_invoker view will be a false positive on hosted Supabase where PG17 is the default. The catalog signal: pg_class.relowner + the reloptions, or check has_column_privilege under the invoker assumption.

Column-level grants add a layer the write-policy checks can't see. Postgres supports per-column grants: a policy may allow for update using (auth.uid() = user_id) on the row, while information_schema.column_privileges shows update is only granted on (name, avatar_url) — meaning the plan column is blocked at the grant layer even though the policy looks wide. The inverse misfire: a policy that looks safe (ownership-scoped) with grant update(plan) to authenticated at the column level. Worth adding a column_privileges check for the sensitive-column list (plan, role, is_admin).

The user_metadata class: the heuristic that saves the most debugging is distinguishing metadata as the sole authorization (dangerous) vs. metadata in combination with ownership (usually fine — e.g., auth.uid() = user_id AND (raw_user_meta_data()->>'role') = 'staff'). The sole-authorization shape is the one that dies the first time someone edits their metadata client-side.

One addition for the storage checks: public buckets without an owner check are worth flagging, but the same check should look at storage.objects policies that use bucket_id = X as the only predicate — bucket-scoped without ownership is the storage-side equivalent of the using (true) class and shows up in AI-generated apps.

For calibration data if useful: in the public-repo scans, the roughly-sorted prevalence is permissive to authenticated policies (~25% of repos) > world-readable using (true) with no TO (~15%) > committed credentials (~10%) > SECURITY DEFINER functions without search_path pinning (~8%). The fixed-search_path class is small but the highest severity per instance.

Good call publishing the test database next to it — the reproducibility is what makes false-positive feedback actionable. Following the script's development.