My /rpc function or view bypasses RLS

Your table has RLS on and owner-only policies, and a direct select returns only the caller's rows. Then a database function called through /rpc, or a view exposed through the Data API, returns everyone's rows, sometimes to a signed-out request. The cause is usually the same: the function is SECURITY DEFINER, or the view was created without security_invoker, so it reads the table as its owner instead of as the caller. Supabase puts it plainly: a function created in the Dashboard or by a migration "is owned by postgres, which bypasses Row Level Security. Any role can call it by default, including anon" (Supabase: Database functions).

The fix

1. List the SECURITY DEFINER functions the API can reach (Verified: run in the SQL editor or psql as the database owner. It's read-only):

-- SECURITY DEFINER functions in exposed schemas: owner, search_path, who can call them
select n.nspname as schema,
       p.proname || '(' || pg_get_function_identity_arguments(p.oid) || ')' as function,
       pg_get_userbyid(p.proowner) as owner,
       coalesce((select c from unnest(p.proconfig) c where c like 'search_path=%'), 'NOT SET') as search_path,
       has_function_privilege('anon', p.oid, 'execute') as anon_can_call,
       has_function_privilege('authenticated', p.oid, 'execute') as signed_in_can_call
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where p.prosecdef
  and n.nspname in ('public')  -- add every schema under "Exposed schemas" in your API settings
  and not exists (select 1 from pg_depend d where d.objid = p.oid and d.deptype = 'e')
order by 1, 2;

prosecdef is true for definer functions, and proconfig holds settings attached with set, such as search_path (PostgreSQL: pg_proc). The query skips functions that belong to extensions. Read each row: a function with anon_can_call = t can be called by anyone with your publishable key, and signed_in_can_call = t by anyone who can sign up. search_path = NOT SET is the problem lint 0011 flags (step 7). In our test it listed our two leaky functions as NOT SET and callable by both roles. After the fix it showed the one function we kept as definer with search_path="" and anon_can_call = f (Verified). A row here isn't automatically a leak; step 3 or 4 decides what to do with it.

2. List the views that read tables as their owner (Verified, read-only):

-- Views in exposed schemas without security_invoker
select n.nspname as schema,
       c.relname as view,
       pg_get_userbyid(c.relowner) as owner,
       has_table_privilege('anon', c.oid, 'select') as anon_can_select,
       has_table_privilege('authenticated', c.oid, 'select') as signed_in_can_select
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'v'
  and n.nspname in ('public')  -- same list as above
  and not exists (
    select 1 from unnest(coalesce(c.reloptions, '{}')) o
    where lower(o) in ('security_invoker=true', 'security_invoker=on', 'security_invoker=1', 'security_invoker=yes')
  )
  and not exists (select 1 from pg_depend d where d.objid = c.oid and d.deptype = 'e')
order by 1, 2;

Postgres stores the option as you typed it (security_invoker=true or security_invoker=on in our test), which is why the query checks several spellings (Verified). In our test it listed our notes_feed view before the fix and returned 0 rows after (Verified). Every view it lists reads its tables with the owner's rights and the owner's RLS, so if the owner is postgres, your policies don't apply through it.

3. Switch functions to SECURITY INVOKER where you can (Verified). Most functions that just read or write the caller's own data don't need definer rights:

alter function public.search_notes(text) security invoker;

Supabase says to "Prefer security invoker, which is also the default" (Supabase: Database functions). After this, the function runs as the caller and the table's policies apply. In our test, the same /rpc/search_notes call that had returned all three notes to a signed-out request returned [], and each account got only its own notes (Verified). The caller still needs the table grants and policies a direct query would need.

4. If a function must stay SECURITY DEFINER, lock it down (Verified). Pin the search path, qualify every name, check the caller inside, and narrow who can call it:

create or replace function public.get_note(note_id bigint)
returns setof public.notes
language sql
security definer
set search_path = ''
as $$
  select n.*
  from public.notes n
  where n.id = note_id
    and n.user_id = (select auth.uid())
$$;
revoke execute on function public.get_note(bigint) from public, anon;
grant execute on function public.get_note(bigint) to authenticated;

Each line comes from the docs. "When you use security definer, you must set the search_path. With an empty search path, search_path = '', name the schema for every relation in the function body." Then "Check ownership in the function body. An execute grant controls which roles can call the function. It doesn't limit the rows a permitted role gets back", and "Revoke it from public and from each role holding a direct grant, such as anon" (Supabase: Database functions). PostgreSQL's own advice for definer functions is the same: set a search path that excludes schemas untrusted users can write to, and revoke the default PUBLIC execute privilege before granting it selectively (PostgreSQL: CREATE FUNCTION).

In our test, after this, a signed-out call failed with 42501 "permission denied for function get_note", the second account asking for the first account's note got [], and the owner still got it (Verified). Both revokes matter: when we revoked only from anon, anon could still call the function through the PUBLIC grant (Verified). create or replace keeps the existing grants, and the whole block re-ran cleanly a second time (Verified).

Why the search path matters: without it, an unqualified name is looked up on the caller's path. In our test, run in psql as the authenticated role, a temporary table named notes made an unpinned definer function count the caller's 999 fake rows instead of the 3 real ones, while the pinned, schema-qualified version still read public.notes (Verified, psql only; we didn't try this over the API). Supabase: "Without a pinned search_path, a caller can point an unqualified name at their own object and run it with the function owner's privileges" (Supabase: Row Level Security).

5. Or move it out of the API's reach (Verified). A definer function only your policies or other functions call doesn't need to sit in an exposed schema. Supabase: a security definer function in an exposed schema "is callable over the Data API with the creator's privileges. Never create one in a schema listed under "Exposed schemas" in your API settings" (Supabase: Row Level Security), and its RLS guide puts such helpers in a private schema.

create schema if not exists private;
alter function public.get_note(bigint) set schema private;

In our test, once the function was in a schema PostgREST didn't expose, /rpc/get_note returned 404 PGRST202 for both a signed-out request and a signed-in account (Verified). Update any callers to private.get_note. If a policy calls it, the calling role needs grant usage on schema private to authenticated, as in Supabase's example (Docs-only: we didn't call it from a policy). Moving it doesn't replace step 4: a definer function called from a policy still needs its search path pinned.

6. Make views run as the caller (Verified on Postgres 17):

alter view public.notes_feed set (security_invoker = true);

Supabase: "In Postgres 15 and above, make a view obey the RLS policies of its underlying tables when invoked by anon and authenticated by setting security_invoker = true" (Supabase: Row Level Security). PostgreSQL: with it set, "the policies and permissions of the invoking user are used instead, as if the base relations had been referenced directly from the query using the view" (PostgreSQL: CREATE VIEW). In our test, /notes_feed went from all three notes for every caller to [] signed out and each account's own notes (Verified). On Postgres 14 and older, Supabase says to revoke access from anon and authenticated or move the view to an unexposed schema instead (Docs-only: we tested on Postgres 17).

7. Stop new functions being callable by everyone, then re-run Security Advisor. Supabase gives this to restrict functions created later (Supabase: Database functions):

alter default privileges revoke execute on functions from public;
alter default privileges in schema public revoke execute on functions from anon, authenticated;

In our test, after the first line a newly created function couldn't be called by anon or authenticated until we granted it (Verified). The second line removes Supabase's own default grants, which our local database didn't have (Docs-only). After this, grant execute on every new function to the roles that call it, including helpers your policies call. The related Advisor lints are 0010 security_definer_view, 0011 function_search_path_mutable, 0028 anon_security_definer_function_executable and 0029 authenticated_security_definer_function_executable. (Docs-only: we ran the queries above, not the hosted Advisor.)

Tested October 26, 2026 on a throwaway local Postgres 17 with PostgREST (exposing public) and Supabase Auth (GoTrue), with two real accounts (A and B) and a signed-out request. The notes table had RLS on with owner-only policies, and a direct select returned only the caller's notes. Before the fix (two definer functions with no search path, a default view, all granted to anon and authenticated): signed out, A and B each got all three notes from /rpc/search_notes and /notes_feed, and signed out and B both read A's note through /rpc/get_note. After the fix: signed out got [] from the invoker function and the view and 42501 from get_note, B got only B's note everywhere and [] for A's note, and A still got A's two notes.

Why it happens

• Definer functions run as their owner. "Postgres runs a function either as the user calling it (invoker) or as its creator (definer)" (Supabase: Database functions). A definer function owned by postgres reads tables as postgres, and the caller's policies never run. In our test, B got A's notes from a definer function while a direct select gave B only B's own (Verified).
• Views default to the owner's rights. "Views bypass RLS by default because they are usually created with the postgres user. This is a feature of Postgres, which automatically creates views with security definer" (Supabase: Row Level Security). PostgreSQL applies "the row-level security policies of the view owner" unless security_invoker is set (PostgreSQL: CREATE VIEW).
• Everyone can call a new function. PostgreSQL grants EXECUTE on functions to PUBLIC by default (PostgreSQL: Privileges), and Supabase's linter docs add that Supabase "additionally grants default privileges for new functions to anon, authenticated, service_role" (Supabase splinter: 0028).
• /rpc doesn't need you to publish anything. The same doc: "/rest/v1/rpc accepts any function name the role has EXECUTE on" (Supabase splinter: 0029). A helper written for an admin script is callable the moment it lands in public.

Definer isn't always wrong. Supabase's own fix for a recursive policy is a definer function, but it lives in a private schema, pins search_path, filters on (select auth.uid()) and revokes execute from public (Supabase: Row Level Security). We use the same pattern in infinite recursion in a policy. The trouble starts when one sits in public with none of that. If a missing grant is what pushed you toward definer, fix the grant instead: 42501 permission denied.

What not to do

• Don't make a function security definer just to get past an RLS error. Inside it, the caller's policies don't apply at all.
• Don't rely on granting execute only to authenticated. It controls who can call, not which rows come back. Supabase: granting to authenticated "still returns any user's row to every signed-in caller" (Supabase: Database functions).
• Don't revoke from anon and stop there. The PUBLIC grant still lets anon call it (Verified).
• Don't trust a user id passed as a function argument. Check auth.uid() inside the function.
• Don't think pinning search_path fixes access. "Pinning the search_path doesn't change who can call the function" (Supabase: Database functions).
• Don't hide a leaky function by leaving it out of your client code. Anyone with the publishable key can call it by name (see is the publishable key safe?).

AI prompt

My Supabase project uses RLS. Find database functions and views that bypass it.
Run these read-only queries and show me the results:
select n.nspname, p.proname, pg_get_function_identity_arguments(p.oid), p.proconfig,
  has_function_privilege('anon', p.oid, 'execute') as anon,
  has_function_privilege('authenticated', p.oid, 'execute') as signed_in
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where p.prosecdef and n.nspname = 'public';
select c.relname, c.reloptions from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'v' and n.nspname = 'public';
Also grep my migrations for "security definer" and "create view".
For each function, tell me whether it needs definer rights. If not, propose
alter function ... security invoker. If it does, propose: set search_path = '',
schema-qualified names, a check against (select auth.uid()) inside, revoke execute
from public and anon, grant only to the roles that call it, or a move to a private schema.
For each view without security_invoker, propose alter view ... set (security_invoker = true).
Do not use the service_role key in client code. Show the SQL first and wait for my OK.

How to verify (two accounts)

1. Re-run the queries from steps 1 and 2. Step 2 should return 0 rows. Every function left in step 1 should show search_path="", and anon_can_call = f unless signed-out visitors really should call it.
2. Signed out (publishable key only), call each function with supabase.rpc('name', args) and select from each view. Expect [], public data only, or a 42501 "permission denied for function" error.
3. Sign in as user A and create a row. Sign in as user B and call the same functions and views, passing A's row id where a function takes one. B should get only B's rows and nothing of A's.
4. As A, confirm the app still works: A's own rows come back from every function and view.

Related

Our Supabase RLS guide · RLS is on, but my table still leaks · Is my Supabase storage bucket public? · 42501 permission denied · Supabase self-check

Your turn

Did step 1 list a definer function you didn't know was callable? Tell us what it did (search, a dashboard count, an admin helper). We'll add the patterns we see most.