Supabase database functions anyone can call (SECURITY DEFINER)

Why this matters more than RLS now

Supabase and Lovable now create most tables with row level security switched on, so the holes moved elsewhere. In our 2026 study of 1,029 public AI-built apps, 8% had a serious data access problem, and 10 of the 160 apps that take payments (6%) had a database function anyone could call. AI tools write these functions to get around an RLS error, for example to update a plan, give credits or read another user's profile, and mark them SECURITY DEFINER so the error goes away.

What a dangerous function looks like

This function runs as the table owner, ignores RLS and trusts whatever the caller sends. Anyone can make themselves "pro" with one line: supabase.rpc('set_plan', { target: myId, new_plan: 'pro' }).

create function public.set_plan(target uuid, new_plan text)
returns void
language sql
security definer
as $$
  update public.profiles set plan = new_plan where id = target;
$$;

Find every SECURITY DEFINER function

Run this in the Supabase SQL editor. Every row is a function that skips RLS; check each one.

select p.proname as function_name, pg_get_function_identity_arguments(p.oid) as args
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where p.prosecdef and n.nspname = 'public';

Fix 1: use SECURITY INVOKER and let RLS do the work

Most functions do not need owner rights. With SECURITY INVOKER (the default) the function runs as the caller, so your RLS policies still apply.

create or replace function public.get_my_orders()
returns setof public.orders
language sql
security invoker
set search_path = ''
as $$
  select * from public.orders where user_id = auth.uid();
$$;

Fix 2: if it must be SECURITY DEFINER, check the caller

Admin actions and cross-user operations sometimes need owner rights. Then the function itself must check who is calling, set an empty search_path, and only be executable by signed-in users. Plans and credits should only ever change from a verified payment webhook, not from a function the browser can call.

create or replace function public.admin_delete_profile(target uuid)
returns void
language plpgsql
security definer
set search_path = ''
as $$
begin
  if not exists (select 1 from public.admins where user_id = auth.uid()) then
    raise exception 'not allowed';
  end if;
  delete from public.profiles where id = target;
end;
$$;

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

Test it like an attacker

Call the function with only your public key, logged out. If it returns data or changes a row, so can anyone.

curl -X POST "https://<project>.supabase.co/rest/v1/rpc/set_plan" \
  -H "apikey: <your anon or publishable key>" \
  -H "Content-Type: application/json" \
  -d '{"target":"<some user id>","new_plan":"pro"}'

Questions

What does SECURITY DEFINER do in Supabase?

The function runs with the rights of the user who created it (usually postgres), not the caller. That skips row level security on every table it touches.

Why does Supabase warn about a mutable search_path?

A SECURITY DEFINER function without a fixed search_path can be tricked into using an object the caller created. Add set search_path = '' and use schema-qualified names like public.orders.

Does revoking EXECUTE from anon break my app?

Only if logged-out visitors are meant to call that function. Grant it to authenticated for signed-in features, and keep anon access only for functions that return public data.

Can I hide functions from the API completely?

Yes. Put internal functions in a schema that is not exposed through the API (for example a private schema) and call them only from other functions, triggers or server code.