Supabase SECURITY DEFINER functions: how they skip RLS and how to lock them down

If the Security Advisor shows “Function Search Path Mutable”, or you find security definer in SQL your AI tool wrote, you have functions that skip your row-level security. Whether that’s a problem depends on who can call them.

Why this happens

A normal Postgres function runs with the rights of whoever calls it. When someone using your public key calls it, RLS applies like any other query.

A function marked security definer runs with the rights of its owner. In Supabase, functions you create in the SQL editor are owned by postgres, which skips RLS. Whatever the function reads or changes, your policies don’t see it.

That’s useful when you need it. A policy that checks a roles table can end up in a loop, and a definer function is the usual way around it. The trouble starts when the function is also reachable by the public.

Supabase’s API exposes functions in the public schema at /rest/v1/rpc/<name>. New functions are executable by default by anon, authenticated and service_role. So a definer function in public is a public endpoint with full database rights, and it only does the checks you wrote into it.

Here’s the kind of function an assistant writes when asked to “let users look up an order”:

create function public.get_order(p_order_id uuid)
returns setof public.orders
language sql
security definer
as $$
  select * from public.orders where id = p_order_id;
$$;

RLS on orders is switched on and correct, and this function ignores it. Anyone who has your public key and any order ID gets that order back. Because the function has no search_path set, it is also open to a second problem: the schemas it searches can be changed by the caller, which can make the function pick up a different object from the one you meant.

How to check your own app

Run this in your own project’s SQL editor. It lists definer functions in public, with who can run them:

select
  n.nspname as schema,
  p.proname as name,
  pg_get_function_identity_arguments(p.oid) as args,
  p.proconfig as settings,
  has_function_privilege('anon', p.oid, 'execute') as anon_can_run,
  has_function_privilege('authenticated', p.oid, 'execute') as signed_in_can_run
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where p.prosecdef
  and n.nspname = 'public';

For each row, read the function body (Database → Functions in the dashboard). Then ask three questions:

1. Does it return or change data that belongs to someone?
2. Does it check who’s calling it, using auth.uid()?
3. Is anon_can_run true when it shouldn’t be?

If settings is empty, no search_path is set. To see what an outsider sees, call the function with only the public key:

curl -X POST "https://YOUR-PROJECT.supabase.co/rest/v1/rpc/get_order" \
  -H "apikey: YOUR_ANON_KEY" \
  -H "Authorization: Bearer YOUR_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"p_order_id": "an-id-from-your-own-data"}'

The JSON keys must match the function’s argument names. Only run this against your own project.

The fix

1. Make the function security invoker if you can

Most functions don’t need to bypass RLS. Remove security definer and the default, security invoker, takes over, so your policies apply:

create or replace function public.get_order(p_order_id uuid)
returns setof public.orders
language sql
security invoker
set search_path = ''
as $$
  select * from public.orders where id = p_order_id;
$$;

Now a caller only gets back a row the orders policies already let them see.

2. For definer functions you keep, do three things

Set an empty search_path and write every object name in full, with its schema. Check the caller inside the function. Keep the function small.

create or replace function public.get_my_order(p_order_id uuid)
returns setof public.orders
language sql
security definer
set search_path = ''
as $$
  select o.*
  from public.orders o
  where o.id = p_order_id
    and o.user_id = (select auth.uid());
$$;

An anonymous caller has no auth.uid(), so they get nothing back.

3. Remove execute from roles that shouldn’t have it

Postgres grants execute to everyone on new functions, and Supabase also grants it to anon explicitly, so revoke both:

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

Include the argument types in the signature, as shown. To stop this happening on future functions, you can change the default privileges for functions in the schema, or make a habit of adding the two lines above to every function you write.

4. Move helper functions out of the exposed schema

Functions used only inside RLS policies or triggers don’t need an API address. Put them in a schema the API doesn’t expose, such as private, and call them from policies as private.function_name(). The list of exposed schemas is in your project’s API settings, and public is there by default. Don’t add your private schema to it.

Policies run as the user making the request, so that role needs permission to use the schema and run the function:

create schema if not exists private;
grant usage on schema private to authenticated;
grant execute on function private.function_name() to authenticated;

5. Test from outside again

Repeat the curl call. You should get a permission error or an empty result. Then confirm a signed-in test user can still do the thing the function was written for.

Mistakes to avoid

• Adding security definer to silence a permission error. The error was telling you a policy blocked something. Fix the policy.
• Trusting an ID passed in as an argument. A function that takes user_id as a parameter lets callers pass someone else’s. Use auth.uid() instead.
• Revoking from anon but not public. Revoke from both, then check with the query above.
• Leaving search_path out of definer functions. Set it, and write schema names in the body.
• Forgetting views. Views run with their creator’s rights by default. See Supabase RLS disabled for the security_invoker option on Postgres 15 and later.