Every AI coding agent will happily "audit your RLS" if you ask it to. Mine did. It
found three issues, declared the rest fine, and on the second run found two
different issues and declared the rest fine.
That's the problem. An agent asked to go look enumerates what it happens to
notice, then phrases everything it never checked as an all-clear. The finding
isn't deterministic, so the all-clear is worthless.
So I inverted it: the prompt doesn't ask the model to investigate anything. It
hands the model the exact SQL, tells it not to substitute its own, and leaves it
doing the one part it's genuinely good at — explaining what a row in the result
means. I put it up at defencecore.com/audit with
no signup wall.
Why it's safe to paste into a production database
Every query reads a Postgres catalogue view — pg_tables, pg_policies,
storage.buckets. Those describe the shape of your database: table names, policy
definitions, bucket settings. There is no statement in the prompt that selects
from an application table, and none that writes.
That's a property of the SQL, not a promise you're asking a model to keep. You
can read all of it before you paste it.
The prompt
You are auditing the Row Level Security configuration of my Supabase project.
STEP 1 — Run this query exactly as written. Do not modify it, and do not
substitute your own queries. It reads only Postgres catalogue views
(pg_tables, pg_policies, storage.buckets): it cannot read a single row of my
application data, and it changes nothing.
with findings as (
select 1 as rank, 'CRITICAL' as severity,
'Table has no Row Level Security - readable by anyone with the anon key' as finding,
tablename as object
from pg_tables
where schemaname = 'public' and not rowsecurity
union all
select 1, 'CRITICAL',
'Policy grants read access to everyone (USING true)',
tablename || ' -> ' || policyname
from pg_policies
where schemaname = 'public' and qual = 'true'
union all
select 2, 'HIGH',
'Policy accepts any new row (WITH CHECK true) - records can be reassigned',
tablename || ' -> ' || policyname
from pg_policies
where schemaname = 'public' and with_check = 'true'
union all
select 2, 'HIGH',
'Storage bucket is public - any object URL downloads without auth',
name
from storage.buckets
where public
union all
select 3, 'REVIEW',
'RLS enabled but no policy - table returns nothing to my own app',
t.tablename
from pg_tables t
left join pg_policies p
on p.schemaname = t.schemaname and p.tablename = t.tablename
where t.schemaname = 'public' and t.rowsecurity and p.policyname is null
)
select severity, object, finding from findings order by rank, object;
STEP 2 — Report the results as a table, most severe first, naming the exact
table, policy or bucket. If the query returns no rows, say so plainly instead
of looking for something else to report.
STEP 3 — For each finding, state in one sentence what someone holding my
public anon key could actually do with it.
STEP 4 — Describe the fix for each finding in words. Do NOT run, apply, or
offer to run any statement that modifies my database — no ALTER, no CREATE
POLICY, no DROP, no migration. I will make the changes myself.
STEP 5 — Finally, list which of these findings could silently come back after
a future migration or a future prompt, and explain why a one-time audit cannot
catch that.
Paste it into Claude Code, Cursor, or anything else connected to your project. No
agent handy? Copy the with findings as (…) block straight into the Supabase SQL
editor — the SQL is the audit, the prompt is only the part that reads it back
to you.
What the five checks actually catch
Tables with no RLS. Readable by anyone holding your anon key, which ships in
your frontend bundle by design. This is the one everybody knows about and still
the one that shows up most.
Policies where qual = 'true'. RLS is on, a policy is attached, and it
returns every row to everybody. This passes any audit that only asks is RLS
enabled — including most dashboard checkmarks and most agents.
WITH CHECK (true). A user can reassign a record they own to somebody else.
Almost always added to silence an insert error, not to deliberately grant
anything.
Public storage buckets. Correct for avatars. Wrong for the invoices and
scanned documents that end up in the same bucket because public buckets are where
uploads work on the first try.
RLS on with no policy. Not a breach — but it's why a feature quietly returns
an empty list instead of an error, and it's the usual prelude to somebody
disabling RLS to "fix" it.
Step 5 is the point
The interesting step isn't 1 through 4. It's 5: which of these can come back
silently?
All of them. A migration re-creates a table without its policy. A prompt adds
USING (true) to unblock a broken query. Someone flips a bucket public to debug
an upload at 1am. Your audit output looks identical the next time you run it,
because you don't run it again.
That gap is why I ended up building
Defencecore in the first place. A catalogue snapshot
tells you a table is open; it can't tell you who already read it. That's in your
Supabase logs, and on the lower plans those expire in days —
Defencecore reads them as they arrive and keeps the log
lines plus the incident built from them, so the evidence outlives the retention
window that would have deleted it. Read-only, so it can explain a problem and
never cause one.
Run the prompt first though. It's free, it takes thirty seconds, and roughly
everyone I've handed it to has found at least one REVIEW row they didn't expect.
