SECURITY DEFINER Functions Are PUBLIC EXECUTE

The short version

PostgreSQL grants EXECUTE to the PUBLIC role by default the moment CREATE FUNCTION runs. SECURITY DEFINER is the modifier that makes a function run with the privileges of the role that owns it rather than the role that called it. Create a write function with that modifier and leave it without an explicit REVOKE, and it becomes directly callable from the anon (unauthenticated) or authenticated role through Supabase’s PostgREST layer.

• Even with no GRANT written anywhere, EXECUTE for PUBLIC is there from the start
• RLS policies on the table are irrelevant, because a SECURITY DEFINER function runs as the owner
• Closing it means writing REVOKE ALL ... FROM PUBLIC explicitly and re-GRANTing only to the roles that need it

What it looks like

We created a function to push records for asynchronous delivery into a table called sync_outbox. Its definition pins search_path. search_path is the schema search path deciding which schema an unqualified object name resolves from inside a function, and pinning it to public is standard practice to stop a caller rewriting it so that a fake same-named object outside public resolves first.

create table if not exists public.sync_outbox (
  id            uuid primary key default gen_random_uuid(),
  kind          text not null,
  ref_id        uuid,
  payload       jsonb not null default '{}'::jsonb,
  status        text not null default 'pending'
                check (status in ('pending','sent','failed','dead')),
  attempts      int  not null default 0,
  -- (omitted)
);

alter table public.sync_outbox enable row level security;
create policy sync_outbox_staff_read on public.sync_outbox
  for select using (public.is_staff());

create or replace function public.enqueue_sync_outbox(p_kind text, p_ref_id uuid, p_payload jsonb)
returns uuid language sql security definer set search_path = public as $$
  insert into public.sync_outbox (kind, ref_id, payload)
  values (p_kind, p_ref_id, coalesce(p_payload, '{}'::jsonb))
  returning id;
$$;

The table’s RLS says only “allow SELECT for the staff role”. There is no policy anywhere permitting INSERT for anon. The function was written on the premise that only the app’s backend (a cron worker) calls it. Pinning search_path was handled, but that guards a different kind of attack and does nothing for the problem here.

But this function is SECURITY DEFINER, and EXECUTE for PUBLIC is granted by default the instant CREATE FUNCTION runs. Supabase’s PostgREST automatically exposes functions in the public schema at /rest/v1/rpc/<name>. It connects as anon or authenticated depending on the JWT the caller sends, and both roles inherit PUBLIC’s privileges, so any function not explicitly revoked can be called directly by anyone.

That is what the review flagged. The table’s RLS correctly narrowed reads to staff, and the function’s execute privilege had never been narrowed at all.

Why

Three facts stack up.

① SECURITY DEFINER runs with the function owner’s privileges

Not the calling role’s privileges but those of the role that owns the function. That much is as specified.

② CREATE FUNCTION grants EXECUTE to PUBLIC by default

Even with no GRANT ever written, the function is callable by anyone the moment it is created. This is the opposite of tables, whose GRANT is closed by default (nobody can touch them without explicit permission): functions are open by default.

③ PostgREST automatically exposes public schema functions as RPCs

anon / authenticated are the connection roles Supabase provides, and they inherit PUBLIC’s privileges as they are. With not a single GRANT on the table, one forgotten REVOKE on a SECURITY DEFINER function leaves a write path open that bypasses RLS entirely.

table GRANT        → closed by default → nobody touches it without explicit permission
function EXECUTE   → open by default   → anyone calls it unless explicitly revoked

This asymmetry is the cause of the oversight. Look only at the table and conclude “RLS protects this”, and the hole on the function side goes unseen.

Fixing it

Strip the default PUBLIC privilege and re-allow only the roles that need it.

-- Close the SECURITY DEFINER write function to direct calls from anon/authenticated
-- (via PostgREST). Strip the default PUBLIC EXECUTE, allow only the backend (service_role).
revoke all on function public.enqueue_sync_outbox(text, uuid, jsonb) from public;
grant execute on function public.enqueue_sync_outbox(text, uuid, jsonb) to service_role;

REVOKE ALL ... FROM PUBLIC cuts off every inheritance source including anon / authenticated, and GRANT EXECUTE returns it to service_role alone, which actually needs to call it. The function definition itself is unchanged. All that happened is an open door was closed, with no effect on the intended calling path from the backend.

Checking it

Joining pg_proc and pg_roles gives you a function’s owner and its execute privileges.

select p.proname, r.rolname as owner, p.proacl
from pg_proc p
join pg_roles r on r.oid = p.proowner
where p.proname = 'enqueue_sync_outbox';

Look for EXECUTE for PUBLIC remaining in proacl (notation like =X/<owner>). Unless this check is run on every new SECURITY DEFINER write function, the same path opens once per function whose revoke was forgotten.

Making it not recur

This fix went in as an addition of revoke / grant to the existing migration file rather than recreating the function. Unless writing REVOKE ALL ... FROM PUBLIC paired with every SECURITY DEFINER function becomes the rule, the next write function added opens the same path.

• Write REVOKE / GRANT directly beneath the creation SQL for any SECURITY DEFINER write function, so that adding only one of them is structurally impossible
• Include the function’s EXECUTE privilege in the same review item as the table’s RLS review (on the premise of the asymmetry: tables closed by default, functions open by default)
• Verify whether something is anonymously writable by actually querying pg_proc.proacl, not by reading the wording of a policy

However carefully the RLS policies are written, it means nothing if another door — the function’s execute privilege — stands open. Another case shaped as an overlooked SECURITY DEFINER is in The function owner and the migration role. That one is about an intended bypass ceasing to work; this one about an unintended bypass working. Both start from the same property: SECURITY DEFINER behaviour is decided by who created the function, not by who called it.