Supabase default privileges granted anon TRUNCATE on 96 tables, out of reach of RLS

What happened

A developer has shared a first-person account on dev.to of finding that their Supabase project had granted the anon role TRUNCATE privileges on 96 tables in the public schema, with the authenticated role holding the same privilege on 109. The author says they never wrote a single one of those grants.

The discovery was incidental. While building a podcast feature, they checked the privileges on their articles table expecting only to confirm that anonymous visitors could read published posts. Instead, the anon role's privilege list included INSERT, UPDATE, DELETE and TRUNCATE.

Where the grants came from

According to the post, the origin is a default ACL that Supabase applies to the public schema. A default-privileges entry (pg_default_acl) causes every table created in that schema to automatically grant anon and authenticated a privilege set whose shorthand includes D for TRUNCATE and t for TRIGGER. The platform made that decision before the user wrote any SQL, and revoking the privileges on existing tables is not enough on its own, because the next table created inherits the same defaults.

Why row-level security does not help

The author was less worried about INSERT, UPDATE and DELETE: those verbs sit under PostgreSQL row-level security, the project's policies fail closed, and anon has no write policy at all. TRUNCATE is a different case. RLS covers only SELECT, INSERT, UPDATE and DELETE, while TRUNCATE empties an entire table in one statement and answers solely to table-level privileges. TRIGGER behaves similarly — a holder can attach a trigger, which the post describes as a route to executing code as the table owner.

The author is also explicit about severity. This is not a scenario where the database is wiped tomorrow: PostgREST, Supabase's API layer, exposes no TRUNCATE verb and no create-trigger endpoint, so a public anon key cannot exercise these grants through the API, and exploiting them requires a real database connection. The real problem is the foundation of defence in depth — the assumption that the worst case is bounded by RLS breaks down for the two verbs RLS does not govern.

Why every check stayed green

The author had spent over a year building governance tooling, including a pre-commit checker whose only job was confirming that every migration creating a table also wrote its three GRANT lines, backed by a self-test and a blindness signal. It reported green every time — and it was telling the truth. It reads migration files as text and confirms the expected lines exist; the entire defect lived in grants nobody wrote. A tool that inspects what you declared has, in principle, no way to notice what you never declared.

The fix shared the same blind spot

The repair migration, written the same day, looped over public tables revoking TRUNCATE and TRIGGER from both roles, with a self-verification step that counted leftover grants and rolled back unless the count was zero. It reported zero. The next day, an independent query found seven views still holding TRUNCATE.

The cause was small: the loop scanned only ordinary and partitioned tables (relkind 'r' and 'p'), and the verification query joined pg_tables, a system view that also lists only ordinary tables. The fix and its verification shared one blind spot, so the verification could never falsify the fix — even though both genuinely queried the live database.

From this, the author draws three rules: derive scope from a catalog query rather than from memory; keep acceptance queries exactly as wide as the invariant, since every extra predicate smuggles in an assumption; and pair every check for "too little" with a check for "too much" — a year of checkers guarded against missing grants, but none ever asked whether anyone held excessive ones.

The published fix revokes TRUNCATE and TRIGGER across tables, partitioned tables, views and materialized views; alters default privileges so new tables stop regrowing the grants; and turns the acceptance query into a scheduled check that runs before every deploy. The author notes that pg_default_acl usually holds two entries, one owned by postgres and one by supabase_admin; the second cannot be altered due to permission denied, though in this project it owned no user objects.

Why it matters

The gap existed for over a year in a project with dozens of governance rules and thousands of green tests, and it surfaced only because someone glanced sideways at an unrelated table. For teams building on managed platforms, the lesson is that platform defaults sit outside everything you wrote and everything your tooling reads. Supabase users can audit themselves in seconds by querying information_schema.role_table_grants for TRUNCATE and TRIGGER grants to anon and authenticated; if the result is non-empty, those grants predate the first line of their own SQL.