SECURITY DEFINER Functions in Exposed Schemas in Supabase

What goes wrong

A SECURITY DEFINER function runs with its creator’s privileges, often a superuser, and bypasses row level security (RLS) completely. In the public schema of a Supabase project, anyone can call it through /rest/v1/rpc/<function_name>.

How it happens

You need a function that reads across users, such as an admin operation or an aggregation, and mark it SECURITY DEFINER without realizing the API exposes it.

How to find it

List the functions with prosecdef = true outside the system schemas.

SELECT n.nspname AS schema, p.proname AS function_name
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prosecdef = true
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'extensions', 'auth', 'storage', 'pgsodium', 'vault', 'supabase_functions', 'graphql', 'graphql_public', 'realtime', '_realtime', 'pgsodium_masks', 'pgbouncer', 'net', '_analytics');

How to fix it

Move the function to a private schema that the API doesn’t expose, or revoke EXECUTE from PUBLIC, anon and authenticated. Always set a fixed search_path on it too.

-- Option 1: move it to a private schema
ALTER FUNCTION public.<function_name> SET SCHEMA private;

-- Option 2: keep it in public, fix search_path and revoke
ALTER FUNCTION public.<function_name> SET search_path = public;
REVOKE EXECUTE ON FUNCTION public.<function_name> FROM PUBLIC, anon, authenticated;