REVOKE ALL FROM PUBLIC did not do what we thought, and it left an API open
We shipped a Postgres function whose job was to erase an account's audit trail — the one sanctioned way to honour a deletion request against a table that otherwise refuses every UPDATE and DELETE by trigger.
The migration said this, in the code, right above it:
Not granted to authenticated: a signed-in customer,
including an owner, must not be able to erase their own account's history from
the browser.
That comment described an intention. It did not describe the code.
What we actually wrote
create or replace function erase_account_audit(p_account uuid)
returns int language plpgsql security definer as $$ ... $$;
revoke all on function erase_account_audit(uuid) from public;That looks like a lock. It is not one, and the reason is a detail of how Postgres privileges work that is easy to carry the wrong intuition about.
PUBLIC is a role, not "everyone"
REVOKE ... FROM PUBLIC removes the grant held by the special
PUBLIC pseudo-role. It does not touch grants held by any named
role. If anon or authenticated hold their own
EXECUTE, that grant survives untouched.
And in a Supabase project they do. Default privileges in the
public schema hand anon, authenticated
and service_role execute on newly created functions. So the
revoke removed a grant that was doing nothing, and left the three that
mattered.
What that meant
The function is SECURITY DEFINER, so it runs as its owner and
row-level security does not apply. It is in the public schema, so
PostgREST exposes it at /rest/v1/rpc/. Put those together with the
surviving grant and the result is one request:
POST /rest/v1/rpc/erase_account_audit
apikey: <the publishable key that ships in every browser bundle>
{"p_account": "<any account uuid>"}
→ 1
audit rows after: []No session. No token. The publishable key is, by design, in the JavaScript every visitor downloads. Anyone who knew an account id could empty its append-only record — the exact property the trigger existed to guarantee.
The fix is to invert the default
Revoking function by function is the same mistake with a longer list. The next person to add a function inherits the permissive default and nobody notices. So: revoke everything, grant back by name, and change what future functions inherit.
revoke execute on all functions in schema public from anon;
revoke execute on all functions in schema public from authenticated;
alter default privileges in schema public
revoke execute on functions from anon;
alter default privileges in schema public
revoke execute on functions from authenticated;
-- then, explicitly, the ones the app actually calls
grant execute on function ensure_personal_account() to authenticated;
grant execute on function is_member_of(uuid) to authenticated;One caveat worth knowing before you run that: functions referenced by RLS
policies need EXECUTE for the querying role, because
policy expressions are evaluated as the caller. Revoke those and every policy
fails closed and your app goes blank. They are the ones to grant back first.
Trigger functions are the opposite case. Postgres invokes them itself and never consults the caller's privileges, so granting execute on them buys nothing and exposes a definer function to direct RPC.
How we found it
Supabase's own database linter, under
anon_security_definer_function_executable. It had been reporting
this the whole time.
The part worth sitting with is that we wrote the comment describing the protection, believed it, and never tested it. The reproduction took one curl. A comment is a claim about behaviour, and an untested claim about security is a guess with better formatting.
Worth checking in your own project
- Every
SECURITY DEFINERfunction in an exposed schema — who can actuallyEXECUTEit, not who you meant to allow. ALTER DEFAULT PRIVILEGES, so the next function added does not reopen it.- Whether your protective comments have tests. Ours did not.