A read-only SQL check for the 3 Supabase mistakes AI app builders leave behind

If you built your app with Lovable, Bolt, Cursor or another AI builder on top of Supabase, the database layer is where things quietly go wrong. Nothing errors, the app "works", and the data is readable by anyone who opens DevTools.

Here's a single read-only query you can paste into Supabase → SQL Editor → Run. It changes nothing. Every row it returns is a real problem.

select * from (
  -- 1. Tables without Row Level Security
  select 'CRITICAL: table without RLS' as problem, tablename::text as "table", null::text as policy,
         'alter table public.' || tablename || ' enable row level security; -- then add per-user policies' as fix
  from pg_tables
  where schemaname = 'public' and not rowsecurity

  union all
  -- 2. Policies that are just "true"
  select 'HIGH: policy open to everyone (' || cmd || ')', tablename::text, policyname::text,
         'replace true with a real condition, e.g. (auth.uid() = user_id)'
  from pg_policies
  where schemaname = 'public' and (qual = 'true' or with_check = 'true')

  union all
  -- 3. Write policies without a SELECT policy
  select 'HIGH: can write but cannot read', tablename::text, null,
         'create policy "read own" on public.' || tablename || ' for select using (auth.uid() = user_id);'
  from pg_policies
  where schemaname = 'public'
  group by tablename
  having bool_or(cmd in ('INSERT', 'UPDATE', 'DELETE')) and not bool_or(cmd in ('SELECT', 'ALL'))
) checks
order by problem;

1. Tables without RLS

Your Supabase anon key is public because it ships in your frontend JavaScript. That's fine when RLS is on. With RLS off, anyone holding that key can read and modify the whole table through the REST API.

This isn't hypothetical. It's the pattern behind CVE-2025-48757: in a scan of 1,645 Lovable-built apps, 170 had exposed databases.

2. Policies that are just true

These usually come from a debugging session: "the insert fails, make the error go away". RLS looks enabled in the dashboard, but a using (true) policy lets everyone through. For truly public read-only data (a product catalog, say) a SELECT … using (true) can be intentional. On anything else, or on write policies, it almost never is.

3. Write policies without a SELECT policy (the confusing one)

Symptoms:

• Inserts fail with 42501 new row violates row-level security policy for table … even though your INSERT policy is correct, sometimes even when it's literally with check (true).
• "It works in the SQL Editor but not from the app."
• Deletes or updates return success but affect 0 rows.

Why: supabase-js runs INSERT … RETURNING by default, so Postgres has to let you see the row you just inserted, and that is governed by the SELECT policy. With no SELECT policy, the insert is rejected. Filtered DELETE/UPDATE can only touch rows that are visible through SELECT, so they silently match nothing. The SQL Editor query has no RETURNING, which is why it works there.

I found this exact root cause in three separate threads in the Supabase GitHub discussions in September alone (#50483, #50265, #49801). A variant is a SELECT policy that queries its own table and can't see the row created in the same statement. Use a direct column check such as auth.uid() = user_id.

What to do with the results

• No rows: 🎉 these three are covered. This check doesn't cover everything: policies can still be logically wrong, and service-role keys can still leak into the frontend.
• Rows you're not sure how to fix: each row includes a suggested fix. Test in a branch or a staging project first, never straight in production.

The query is also in this repo, alongside a worked example of rescuing a broken AI-built app (9 bugs, a test for each, before/after): citafacil-rescue.

I fix apps built with AI tools that work in preview but break in production. If you ran this and something came up, feel free to comment with the table names (never data).

Comments

[Cenk KURTOĞLU]

Solid read-only check — I run a variation of this before every launch. One gap worth closing: it only catches tables that already exist, but on Supabase every new table is born with RLS disabled. The moment your AI builder regenerates a schema (or adds chat_messages during a refactor), you're silently exposed again until someone re-runs the check.

Two cheap additions:

1. Close the future blindspot at the source: anything the AI can create should default to locked. (Enable RLS on every flagged table, then forbid grant/DDL from the anon path — the pattern holds.)

2. Add the fourth mistake alongside tables: open functions. A SECURITY DEFINER function with grant execute to anon on a table with RLS off is the same leak served through the REST/pg layer the anon key exposes publicly.

I package this as a free 60-point read-only pre-launch check (runs in ~30s against your DB, uploads nothing) if anyone wants the full list: github.com/cekuu35/supabase-rls-leak-demo