Our free check reads a snapshot of a Supabase database and, where the fix follows from the schema, writes the migration that closes the hole, plus the lines that undo it. Until this week we had only tested those migrations with unit tests. So we ran them against real schemas.
The setup
- 155 public Next.js + Supabase repositories from our earlier precision samples.
- A database built the way a new Supabase project has it: the
supabase/postgresimage, then GoTrue's own migrations (auth migrate) and one start ofstorage-api. Without those you have noauth.jwt(), nostorage.objects, and, most important, no Supabase default privileges, so every new table looks private when it is not. - Each repository's
supabase/migrationsapplied in order aspostgres, the way the CLI does. 59 of 133 rebuilt cleanly; most of the rest reference a table no migration creates. - Everything ran on a Docker network with no route out, since migrations can call
pg_netorhttp.
For every finding with a migration, on its own copy of the database:
- as the stranger the finding names (anon, or any signed-in user), try what it claims: read the table, add a row, call the function;
- apply the fix as
postgres; - snapshot again and re-run the check: the finding must be gone and nothing new may appear;
- try again as the stranger;
- apply the rollback and compare the snapshot with the first one.
Numbers
| Findings with a migration | 243 (on 34 databases) |
| Applied without error | 243 |
| Gone on re-check, nothing new | 243 |
| Rollback restored the same access | 243 |
| All fixes of a project in one file | 24 projects, 0 errors, 0 findings left |
Before each fix, the stranger really could do what the finding said in 239 of 243 cases: 152 of 152 SECURITY DEFINER functions ran for anon or a signed-in user, 34 of 34 tables without RLS were readable and writable.
What broke
A rollback handed out a right that was never there. Our fix for an open SECURITY DEFINER function revokes EXECUTE from public, anon, authenticated and grants it to service_role. The rollback treated that last grant as a no-op, because Supabase's default privileges give service_role EXECUTE on every new function. One project had revoked it on purpose. After "undo", the service role could run three functions it could not run before. The rollback now takes it back when the snapshot shows it was not held.
Two cards, one line. A function with two overloads produced two findings that quoted the same fact line. They now show the argument list.
Four headlines named the wrong stranger. They said "anyone can change every row" for an UPDATE policy open to anon. Through the Data API, anon could change nothing: Supabase's authenticator role preloads safeupdate, so an UPDATE needs a WHERE, and a WHERE that reads a column makes Postgres apply the SELECT policies too. These tables had no read policy for anon, so the update matched no rows. In three of the four, signed-in users could read the table, so the true headline is "anyone who signs up can change every row". The rule now works out who can change which rows from what they can read.
What this is not
Re-checked is not verified. We did not run anyone's app, and a security invoker fix can break an app that relied on the owner's rights. That is what the rollback lines are for.
The check is free at https://auditai.sh/check?utm_source=devto&utm_medium=social&utm_campaign=fix-loop. It reads names and rules, never a row of your data.
Top comments (1)
Good rollout — the rollback no-op trap is exactly the kind of thing unit tests miss, because it depends on what the database came with, not what the migration wrote. One layer worth adding on top: default privileges make this trap recursive. On a vanilla Supabase instance every new function gets EXECUTE for service_role automatically, so your "should not hold" snapshot check needs to compare against the project's default-privilege baseline, not just its current catalog scan.
I've been doing this on real repos over the past weeks and the same class of bug shows up on the public side too: a project that had deliberately revoked anon/authenticated EXECUTE gets "restored" by a migration generator that treats the public GRANT as the baseline. If your rollback diff ever regenerates, explicitly snapshot pg_catalog.pg_default_acl alongside your schema snapshot — it closes the loop.
I have a free read-only checklist that covers this + the rest of the pre-launch surface if it's useful: github.com/cekuu35/supabase-rls-leak-demo