Supabase had granted anon TRUNCATE on 96 tables. I never wrote that line, and every check I own was green.

Read on: the blind spot a non-homologous model caught · 繁體中文版

Do this first, it takes fifteen seconds

Paste this into your Supabase SQL editor:

SELECT grantee, privilege_type, count(*) AS n
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND grantee IN ('anon', 'authenticated')
  AND privilege_type IN ('TRUNCATE', 'TRIGGER')
GROUP BY 1, 2 ORDER BY 1, 2;

If it comes back non-empty, welcome. Mine came back with anon holding TRUNCATE on 96 tables, authenticated on 109.

I had not written a single one of those grants.

How I found it

I was building a podcast feature and idly checked the privileges on my articles table — I only wanted to confirm anon could read published posts. What I saw in anon's list was INSERT, UPDATE, DELETE, TRUNCATE.

The first three didn't worry me much. They're governed by RLS, my policies are fail-closed, and anon has no write policy at all. Ugly, but unreachable.

TRUNCATE is a different animal. PostgreSQL's row-level security applies to exactly four verbs: SELECT, INSERT, UPDATE, DELETE. TRUNCATE is not one of them. It empties an entire table in one statement and it answers only to table-level privileges. The whole RLS wall I spent a year building sits above this one. TRIGGER is the same — whoever holds it can attach a trigger, which is a path to executing code as the table owner.

Where the grants came from

Supabase's public schema ships with a default ACL entry (pg_default_acl) that says: every table created in this schema from now on automatically grants arwdDxtm to anon and authenticated. The D in that string is TRUNCATE. The t is TRIGGER.

So nobody slipped. The platform made the decision before I wrote my first line of SQL. And revoking on existing tables isn't enough — the next table you create grows it back.

Let me be honest about severity before this reads like a scare piece: this is not "your database gets wiped tomorrow." PostgREST has no TRUNCATE verb and no create-trigger endpoint, so a public anon key can't reach it over the API. You need an actual database connection. What it is, is a hole in the foundation of your defence in depth: you believe the worst case is bounded by RLS, and two verbs are not governed by RLS at all.

Why every check I own was green

I'm not without defences. I have a pre-commit checker whose whole job is verifying that every migration creating a table also writes its three GRANT lines. It has a self-test. It has a blindness signal. It was green every single time.

It was green correctly. What it checks is what I wrote: it reads migration files as text and confirms the GRANT lines are present. And the entirety of this hole is what I never wrote. Reading your declarations cannot, in principle, see past their edge.

The sentence I ended up writing on the wall:

A checker reads your declarations. The nastiest defects live in what you never declared — platform defaults, upstream package behaviour, the difference between your laptop and production.

That's the same family as an antivirus reporting "no malware detected" while the file sat right there on the disk: the tool answered the question it was asked, and the question had a blind side.

The more embarrassing half: I made the same mistake again while fixing it

I wrote the repair migration the same day. A loop over every table in public, revoking TRUNCATE and TRIGGER from anon and authenticated, with a self-verification at the end: count the residue, roll back unless it's zero.

It ran. Verification reported 0 rows of residue. Green.

The next day I looked again with an independent query: seven views still held TRUNCATE.

The reason is small and horrible. My revoke loop scanned relkind IN ('r','p') — ordinary and partitioned tables, the list my head produces when it hears "table". My verification query joined pg_tables, a system view that also lists only ordinary tables. The fix missed views, and the verification was structurally incapable of seeing views. They shared one blind spot, so the verification could never falsify the fix.

Notice how fine the trap is: both the fix and the verification really did query the live database. "Go ask the real world" sounds like the cure, and it is not sufficient — when I asked the world, the scope of the question was still typed from memory. The query was real. The range was invented.

The three rules I took out of it

1. Derive scope from a query, never from memory. Before acting on "all the tables", ask the database what actually exists:

SELECT DISTINCT relkind FROM pg_class WHERE relnamespace = 'public'::regnamespace;

Generate the repair loop from that output. Don't type ('r','p') because that is what "table" feels like.

2. The acceptance query may not contain a single word the invariant doesn't contain. My invariant was "anon must hold no TRUNCATE." That sentence contains no concept of kind of relation — so the acceptance query had no business joining pg_tables at all. Asking information_schema.role_table_grants directly (it naturally includes views) is the correct answer. Every extra predicate in an acceptance query is an assumption smuggled in from memory.

3. Every "too little" check has a "too much" twin — ask for it while you're there. My green checker guarded against missing grants causing a 403 in the frontend. Its dual — is anybody holding too much? — had never been assigned to any checker in over a year. That pairing exists nearly everywhere: guard against missed sends and the twin is double sends; guard against running late and the twin is running early. Asking costs almost nothing at the moment you write the first one.

The fix, copy-pasteable

-- 1. Revoke on what exists (including views! check your own relkinds first)
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN
    SELECT c.relname FROM pg_class c
    WHERE c.relnamespace = 'public'::regnamespace
      AND c.relkind IN ('r', 'p', 'v', 'm')
  LOOP
    BEGIN
      EXECUTE format('REVOKE TRUNCATE, TRIGGER ON public.%I FROM anon, authenticated', r.relname);
    EXCEPTION WHEN OTHERS THEN NULL;  -- extension-owned objects you cannot touch
    END;
  END LOOP;
END $$;

-- 2. Stop new tables from growing it back
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
  REVOKE TRUNCATE, TRIGGER ON TABLES FROM anon, authenticated;

-- 3. Acceptance: zero joins, zero extra predicates — exactly as wide as the invariant
SELECT grantee, privilege_type, count(*)
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND grantee IN ('anon', 'authenticated')
  AND privilege_type IN ('TRUNCATE', 'TRIGGER')
GROUP BY 1, 2;
-- expected: empty

Two footnotes. First, pg_default_acl usually holds two entries — one owned by postgres, one by supabase_admin. You cannot alter the second (I get permission denied), but in my project supabase_admin owns zero user objects, so that dirty ACL has never actually applied. Check yours with SELECT relname, pg_get_userbyid(relowner) FROM pg_class WHERE relnamespace='public'::regnamespace. Second, cleaning once is not staying clean — I turned the acceptance query into a scheduled check that runs before every deploy, because the only thing that catches a privilege which regrows is asking again on a timer.

What it cost me to learn

This hole existed in my project for over a year. During that year I wrote dozens of governance rules, a dozen checkers, thousands of tests. All green. All honest. All inspecting things I had written.

It was found because I glanced at something while building an unrelated feature. That is part of the lesson: for the class of defect that lives in what you never wrote, there is no checker you can build by reading your own source. Only two moments catch it — the moment you adopt a platform, when you enumerate the decisions it made on your behalf, and a scheduled question asked of the live system rather than of your files.

96 is 0 now.

---

Originally published on my blog: Supabase had granted anon TRUNCATE on 96 tables. I never wrote that line, and every check I own was green.

I keep a running index of every pothole I've hit building a real production system solo — symptom on the left, what to grep in your own repo on the right: coffeeshooters.com/potholes

And if your team is shipping AI-written code faster than anyone can read it, that's the thing I do for a living: coffeeshooters.com/code-audit