Episode 1/4 of the mini-series The week Supabase lied to me 4 times. The three following episodes cover a mutation silently swallowed by the SDK [CANONICAL URL EPISODE 2: to fill in after push], an RLS recursion resolved by a JWT hook [CANONICAL URL EPISODE 3: to fill in after push], and a query that stops at exactly 1000 rows without saying so [CANONICAL URL EPISODE 4: to fill in after push].
The Tuesday the security probe spoke
It's 9:12am on a Tuesday in May. The daily drift probe has been running automatically for three weeks — an aclexplode query across all public objects, filtered on anon. I don't open it every morning. That morning, it's waiting for me with a row that has no business being there.
Niran sets a coffee on the corner of my desk without a word. He reads the output over my shoulder. A PII backup table — personal data in plaintext, created two days earlier for a bulk reclassification — shows up in the list with SELECT, INSERT, UPDATE, DELETE granted to anon. Accessible to any unauthenticated curl request. He lets three seconds pass and says: "It's not RLS." Then he goes back to his hoodie.
He's right. It's not an RLS bug. The table itself is open, at the GRANT layer, before RLS even applies.
Three objects, three doors, one mechanism
That week, I realize I'm not dealing with an isolated incident. Three distinct objects, in three different migrations, each open a door nobody thought they'd opened. The backup table first. Then a policy set TO public because the public landing page needs it, which lets a POST {} from anon through with an HTTP 400 NOT NULL response instead of 401 Unauthorized. And finally four SECURITY DEFINER functions written to execute transactional operations with their owner's privileges, all invocable by anon because EXECUTE defaults to TO PUBLIC at CREATE time.
Three objects, three superficially distinct mechanisms, yet one shared root. At every CREATE, Postgres completes the migration with an implicit GRANT the author never wrote. The author believes they're reading what they typed. Postgres reads what the language added.
The first trap, CREATE TABLE AS SELECT
The archetypal case is also the most innocent in appearance. The author wants to freeze the state of a contacts table before a bulk reclassification — a 48-hour safety archive meant to disappear once the migration is validated.
-- supabase/migrations/20260511182000_snapshot_backup_pre_reclassification.sql
CREATE TABLE IF NOT EXISTS public.contacts_backup_pre_reclassement AS
SELECT id, nom, prenom, email, source, statut, notes, created_at
FROM public.contacts
WHERE statut = 'liste_rouge';
-- (migration stops here — no REVOKE, no restricted GRANT block)
What the author believes they wrote is a neutral safety copy, accessible only to admins. What Postgres actually wrote is a table with no active RLS whose default GRANTs give anon SELECT, INSERT, UPDATE, and DELETE. A single curl request against the PostgREST endpoint is enough to list the rows, unauthenticated, in plaintext.
The fix is known, but it requires writing it down. The May 13 patch fits in four lines:
-- supabase/migrations/20260513140000_security_revoke_anon_backup_archive.sql
BEGIN;
REVOKE ALL ON public.contacts_backup_pre_reclassement FROM anon;
REVOKE ALL ON public.contacts_backup_pre_reclassement FROM authenticated;
ALTER TABLE public.contacts_backup_pre_reclassement ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON public.contacts_backup_pre_reclassement TO authenticated;
CREATE POLICY "archive_admin_read"
ON public.contacts_backup_pre_reclassement FOR SELECT TO authenticated
USING (EXISTS (
SELECT 1 FROM public.user_roles ur
WHERE ur.email = auth.email() AND ur.role IN ('super_admin', 'admin')
));
COMMIT;
The form is tedious, repetitive, and that's precisely why it's safe: it writes the invariant into the database instead of leaving it floating in the author's head.
The second trap, CREATE POLICY ... TO public
The second incident is more subtle because the author did write gating. They set a POLICY with a qual meant to filter inserts by a service context. But they set the policy TO public, because the landing page is open to the anonymous browser. The trap is that the GRANT layer resolves before the qual layer. If the default GRANT INSERT TO anon exists on the table, a POST {} from anon reaches the table engine, and it's only the column-level NOT NULL constraint that rejects it — by accident.
-- supabase/migrations/20260513140100_security_revoke_anon_payments_table.sql
BEGIN;
REVOKE INSERT, UPDATE, DELETE ON public.stripe_payments FROM anon;
REVOKE SELECT ON public.stripe_payments FROM anon;
DROP POLICY IF EXISTS stripe_payments_service_insert ON public.stripe_payments;
COMMIT;
-- Note: stripe_payments is written exclusively by the Stripe webhook
-- via service_role — no legitimate anon use justifies this GRANT.
The observable symptom is what you might call a too-precise response. When the system responds HTTP 400 NOT NULL, it's saying in plain text that it tried to insert — which it should have refused at the gate. The correct response to a POST {} from anon on a protected table is 401 Unauthorized. Reading 400 in a log is really reading that the authorization layer let it through.
The third trap, shared root: SECURITY DEFINER inherits EXECUTE TO PUBLIC
This is where the mechanism becomes instructive. A SECURITY DEFINER function, by definition, runs with its owner's privileges, not the caller's. If the owner is postgres, the function bypasses RLS — which is precisely why it was marked SECURITY DEFINER. But Postgres, at CREATE FUNCTION time, adds GRANT EXECUTE TO PUBLIC by default. The function is therefore invocable by anyone, including anon, and its execution happens with owner-level privileges, meaning implicit RLS bypass.
-- supabase/migrations/20260512094000_three_transactional_functions_rpc.sql
-- BEFORE fix: invocable by anon, executed with postgres privileges
CREATE OR REPLACE FUNCTION public.supprimer_brouillon_tx(p_planning_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO ''
AS $function$
BEGIN
-- sensitive operation: atomic deletion of a planning draft
-- seances_cours → cours → plannings in a single transaction
DELETE FROM public.seances_cours WHERE cours_id IN (
SELECT id FROM public.cours WHERE planning_id = p_planning_id
);
DELETE FROM public.cours WHERE planning_id = p_planning_id;
DELETE FROM public.plannings WHERE id = p_planning_id;
RETURN jsonb_build_object('ok', true);
END;
$function$;
The May 13 audit surfaces 86 occurrences of SECURITY DEFINER across the migrations directory. Among them, 4 RPCs had no restriction on anon: supprimer_brouillon_tx, convertir_sd_tx (creates an enrollment record), mark_outbox_sent_tx, mark_outbox_error_tx. The fix is immediate:
-- supabase/migrations/20260513140200_security_revoke_anon_execute_rpc_security_definer.sql
REVOKE EXECUTE ON FUNCTION public.supprimer_brouillon_tx(uuid) FROM anon, public;
REVOKE EXECUTE ON FUNCTION public.convertir_sd_tx(uuid, uuid, date, text, date, numeric, integer, integer) FROM anon, public;
REVOKE EXECUTE ON FUNCTION public.mark_outbox_sent_tx(uuid, text, text) FROM anon, public;
REVOKE EXECUTE ON FUNCTION public.mark_outbox_error_tx(uuid, text) FROM anon, public;
-- resolve_reinscription_token keeps EXECUTE TO anon (legitimate public magic link landing)
The exact function signature must appear, because without it the REVOKE fails silently if the function is overloaded.
The audit that makes all three traps visible
Once you understand the shared root, you can test your own database in seconds. The query below returns 0 rows on a clean base; any row returned is an object exposed to anon or public.
-- Drift audit: objects exposed to anon or public
-- On a secure Supabase database → 0 rows expected
SELECT
'table' AS object_type,
relname AS object,
privilege_type AS privilege
FROM pg_class c
CROSS JOIN aclexplode(c.relacl) acl
WHERE c.relkind = 'r'
AND c.relnamespace = 'public'::regnamespace
AND acl.grantee IN (
(SELECT oid FROM pg_roles WHERE rolname = 'anon'),
(SELECT oid FROM pg_roles WHERE rolname = 'public')
)
UNION ALL
SELECT
'function' AS object_type,
p.proname AS object,
'EXECUTE' AS privilege
FROM pg_proc p
CROSS JOIN aclexplode(p.proacl) acl
WHERE p.pronamespace = 'public'::regnamespace
AND p.prosecdef = true
AND acl.grantee IN (
(SELECT oid FROM pg_roles WHERE rolname = 'anon'),
(SELECT oid FROM pg_roles WHERE rolname = 'public')
)
ORDER BY object_type, object;
This query has been running as a daily drift probe since May 13. Of the 86 SECURITY DEFINER functions scanned in the public schema, 4 were exposed to anon before the fix. Zero after.
What episode 1 teaches us, and what the next ones will show
The lesson fits in two lines. Supabase defaults are not neutral — they add GRANTs the author never wrote. And every migration ends with an explicit REVOKE block, or it lies about its invariants. The lie isn't hostile, it's mechanical: Postgres completes the gesture when the author believes they've finished.
This four-part mini-series starts from the same suspicion, extended to four layers of the Supabase stack. Episode 2 [CANONICAL URL EPISODE 2: to fill in after push] will look at how a mutation can fail without any error surfacing on the application side. Episode 3 [CANONICAL URL EPISODE 3: to fill in after push] will show how an RLS policy that self-references produces infinite recursion resolved by a Custom Access Token Hook. Episode 4 [CANONICAL URL EPISODE 4: to fill in after push] will explain why a from().select() without order() returns exactly 1000 rows without saying so, because of an ORDER BY ctid silently added by PostgREST.
Four lies, one pattern: the invariant the developer thinks they hold is not the one the system executes. Security for the solo developer coding with an AI may lie precisely in this discipline of writing every invariant instead of leaving it implicit.
Episode 1/4 of the mini-series The week Supabase lied to me 4 times, part of the "My ERP with Claude Code" series. DEV.to comments surface Supabase traps from your own stack that I haven't seen — and feed the following episodes.
Top comments (0)