Supabase: Public Can Execute SECURITY DEFINER Function

The warning means anon can call a SECURITY DEFINER function through /rest/v1/rpc/<name>, and revoking from anon alone won't clear it, because Postgres grants EXECUTE on every new function to PUBLIC. Revoke from public, anon, authenticated in the migration that creates the function, then grant back only the roles that should call it.

Supabase's linter added two WARN-level lints for this in April 2026, one per API role: 0028_anon_security_definer_function_executable (Public Can Execute SECURITY DEFINER Function) and 0029_authenticated_security_definer_function_executable (Signed-In Users Can Execute SECURITY DEFINER Function). Fix them together: a revoke aimed at one role leaves the other firing.

What does "Public Can Execute SECURITY DEFINER Function" mean?

It means a function in an API-exposed schema runs with its owner's privileges, and anon holds EXECUTE on it. The lint's SQL checks prosecdef and has_function_privilege('anon', p.oid, 'EXECUTE') in every schema listed in pgrst.db_schemas (public by default), minus Supabase's internal schemas.

"Public" means "without signing in". Supabase's database functions guide warns that a definer function created by a migration is owned by postgres, which bypasses RLS, and that any role can call it by default. Anyone with your publishable key can call supabase.rpc('get_order_total', …) and read data RLS would hide. 0029 runs the same check against authenticated, which under open signup means anyone with an email address.

Why does the warning come back after I revoked from anon?

Because anon still gets EXECUTE through PUBLIC. Postgres grants EXECUTE on every new function to PUBLIC, which every role belongs to, and the REVOKE docs are explicit that revoking from one role doesn't stop it using a privilege PUBLIC still has. Separately, Supabase's Securing your API page says that on existing projects, functions created in public also receive EXECUTE for anon, authenticated and service_role directly.

The usual first attempt:

-- Security Advisor: "Public Can Execute SECURITY DEFINER Function".
-- Revoked from anon so logged-out visitors can't call it.
create or replace function public.get_order_total(p_order_id uuid)
returns numeric
language sql
security definer
as $$
  select total from public.orders where id = p_order_id;
$$;

revoke execute on function public.get_order_total(uuid) from anon;

That revoke only touches anon's own grant. PUBLIC still holds EXECUTE, so has_function_privilege('anon', …) stays true and 0028 fires again; authenticated was never touched, so 0029 fires too. The body also returns the total for any order id, with no ownership check.

The scan above is real GuardLayer output for this file. It catches a different defect, the unpinned search_path (lint 0011, covered in the mutable search_path fix). GuardLayer has no rule for EXECUTE grants on functions, so it won't flag the incomplete revoke. As Supabase's guide puts it, "Pinning the search_path doesn't change who can call the function."

"[B]y default, execute privilege is granted to PUBLIC for newly created functions … you must revoke the default PUBLIC privileges and then grant execute privilege selectively." — PostgreSQL docs, Writing SECURITY DEFINER Functions Safely

Triage: which findings are real

List every definer function in public with its owner, return type and effective callers, then sort each row into one of four buckets:

select p.oid::regprocedure                                       as function,
       pg_get_userbyid(p.proowner)                               as owner,
       p.prorettype::regtype                                     as returns,
       p.proacl,                          -- null = default ACL: PUBLIC can execute
       has_function_privilege('public', p.oid, 'EXECUTE')        as public_exec,
       has_function_privilege('anon', p.oid, 'EXECUTE')          as anon_exec,
       has_function_privilege('authenticated', p.oid, 'EXECUTE') as authed_exec
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where p.prosecdef and n.nspname = 'public'
order by p.prorettype = 'trigger'::regtype, p.proname;

1. It returns trigger. Usually handle_new_user. Supabase's managing user data guide creates it as a definer function in public with no revoke, so any project that copied it verbatim gets both warnings. It isn't reachable as an RPC: PostgREST's schema cache drops functions returning trigger (t.oid <> 'trigger'::regtype in SchemaCache.hs). Revoke anyway to clear the noise.

2. It doesn't need to bypass RLS. If the tables it reads have policies for the caller, switch it with alter function … security invoker. Both lints ignore invoker functions. The reporter of splinter#172 cut 44 warnings to 33 this way.

3. It must bypass RLS, and only your server calls it. Cron jobs, webhooks, admin scripts. Revoke from public, anon and authenticated and grant service_role explicitly, or move the function to a schema the Data API doesn't expose, which the lints don't check.

4. It must bypass RLS, and users call it. Revoke from all three roles, grant authenticated back, and compare auth.uid() to the row inside the body, as in the RPC-only RLS design. 0029 keeps firing, and the splinter docs treat that case as intentional: validate the input, then suppress. All 33 functions left in #172 were this case, per the reporter's audit.

The dangerous findings take caller-chosen arguments and write where the caller can't. One variant posted on 18 Oct 2023 in the programmatically create users thread defines public.create_user(email text, password text) as SECURITY DEFINER, inserting into auth.users with email_confirmed_at set to now(). With default grants, any caller could POST /rest/v1/rpc/create_user and run that insert as the function owner. Whether postgres can still write to auth.users today is untested here; the grant is the bug either way.

The fix

For the sample, assuming orders has an owner-scoped SELECT policy (bucket 2):

create or replace function public.get_order_total(p_order_id uuid)
returns numeric
language sql
security invoker
set search_path = ''
as $$
  select o.total from public.orders o where o.id = p_order_id;
$$;

revoke execute on function public.get_order_total(uuid) from public, anon, authenticated;
grant execute on function public.get_order_total(uuid) to authenticated;

A server-only definer function, and the signup trigger:

revoke execute on function public.rebuild_leaderboard() from public, anon, authenticated;
grant execute on function public.rebuild_leaderboard() to service_role;

revoke execute on function public.handle_new_user() from public, anon, authenticated;

CREATE TRIGGER requires EXECUTE on the function, and in Postgres's trigger.c that is the only check: ExecCallTriggerFunc, which fires the trigger, has none. Tested on plain Postgres, not on Supabase: apply the revoke on a branch or local stack and sign up a test user. If that fails with Database error saving new user, grant EXECUTE on the function to supabase_auth_admin, the role Supabase Auth connects as.

Future functions, and October 30

Put the revoke in the same migration as every create function; Supabase's documented default-privileges fix doesn't remove the PUBLIC grant. Its "Revoke default privileges" snippet ends with alter default privileges for role postgres in schema public revoke execute on functions from public;, and the ALTER DEFAULT PRIVILEGES docs say that per-schema form "has no effect, unless it is undoing a matching GRANT". In discussion #45329 a user reproduced it on a hosted project: with the snippet applied, anon could still execute a new function. A maintainer replied that this point "is not handled on purpose as that is the PostgreSQL default".

The same thread announces the platform change: on October 30, 2026, the behaviour you get by unticking "Automatically expose new tables and functions" is enforced on every existing project (how the Data API grant change works). It only changes objects created afterwards, and PUBLIC keeps EXECUTE regardless, so today's warnings will still be there on October 31.

A 60-second self-check

# Migrations that create definer functions
grep -rliE "security[[:space:]]+definer" supabase/migrations/

# One-line function revokes that never name PUBLIC (half-fixes)
grep -rniE "revoke (execute|all)[^;]*on function" supabase/migrations/ \
  | grep -viE "from[^;]*public"

Each file from the first command needs a revoke naming public; each line from the second is a half-fix. Then rerun the triage query: public_exec should be false on every row.

FAQ

Is "Public Can Execute SECURITY DEFINER Function" always a vulnerability? No. It's real when the function touches data RLS would block, using arguments the caller chooses. The splinter docs list two false positives: a deliberate public endpoint (a contact form, an anonymous vote) and a low-privilege owner.

Why is handle_new_user flagged? It's SECURITY DEFINER in public with the default grant, and the lint doesn't filter on return type. It can't be called over RPC: PostgREST skips trigger functions, and PL/pgSQL refuses to run one outside a trigger. Revoke anyway, then test a signup.

Does CREATE OR REPLACE undo my revoke? No. Replacing a function keeps its ownership and permissions. Dropping and recreating it doesn't: the new function starts with default privileges, PUBLIC included.

Can GuardLayer detect this? Partly. It flags SECURITY DEFINER functions in committed SQL that don't pin search_path. It doesn't check EXECUTE grants on functions, so a missing or anon-only revoke passes. The triage query covers that half.