security definer functions and RLS: what to expose

A security definer function runs with the privileges of the role that owns it, not the role that calls it. In Supabase projects that owner is normally postgres, which isn't subject to RLS. That makes these functions useful for RLS helpers and admin actions, and dangerous if the wrong people can call them.

When you need one

• RLS helper lookups. A policy on bookings needs to check team_members, which has its own RLS. Querying it inside the policy is slow, or recursive if team_members' policy checks itself. A security definer helper reads it directly. (See infinite recursion detected in policy.)
• Controlled writes. Users may cancel their own booking but not edit it. A function cancel_booking(id) that checks ownership and updates one column is simpler than a column-level policy.

If neither applies, use the default security invoker and let RLS do its job.

The rules

1. Put it in a schema the Data API doesn't expose. Anything in public that a role can execute is callable at /rest/v1/rpc/<name>.
2. Set an empty search_path and fully qualify every table and function. Otherwise a caller could create an object that shadows one you use.
3. Revoke PUBLIC execute and grant only to the roles that need it.
4. Check the caller inside the function using auth.uid(). The function itself bypasses RLS, so it has to enforce the rules.
5. Mark lookup helpers stable, so Postgres can evaluate them once per statement in a policy.

Example: a team membership helper

create schema if not exists private;

create or replace function private.my_team_ids()
returns setof uuid
language sql
stable
security definer
set search_path = ''
as $$
  select team_id
  from public.team_members
  where user_id = (select auth.uid());
$$;

revoke execute on function private.my_team_ids() from public, anon;

-- policies run as the calling role, which needs these to use the helper
grant usage on schema private to authenticated;
grant execute on function private.my_team_ids() to authenticated;

Use it in a policy:

create policy "Team members read team bookings"
on public.bookings
for select
to authenticated
using ( team_id in (select private.my_team_ids()) );

authenticated can execute the helper, but because private isn't an exposed schema, no one can call it over HTTP.

Example: an action exposed on purpose

When you do want an RPC endpoint, keep it in public but make it narrow:

create or replace function public.cancel_booking(booking_id bigint)
returns void
language plpgsql
security definer
set search_path = ''
as $$
begin
  update public.bookings
  set status = 'cancelled', cancelled_at = now()
  where id = booking_id
    and user_id = (select auth.uid())
    and status = 'confirmed';

  if not found then
    raise exception 'Booking not found or not cancellable' using errcode = 'P0002';
  end if;
end;
$$;

revoke execute on function public.cancel_booking(bigint) from public, anon;
grant execute on function public.cancel_booking(bigint) to authenticated;

const { error } = await supabase.rpc('cancel_booking', { booking_id: 42 })

Views have the same problem

A view runs with its owner's rights by default, so a view over an RLS-protected table can leak every row. On Postgres 15 and later (SupaHost runs 17), create views with security_invoker:

create view public.upcoming_bookings
with (security_invoker = true) as
select * from public.bookings where starts_at > now();

On SupaHost

• The exposed schemas are fixed to public, storage, graphql_public and pgmq_public. Any other schema, like private, can't be reached through the Data API, and that can't be changed by accident.
• The MCP server's get_advisors tool reports functions in public without a fixed search_path (function_search_path_mutable), along with tables missing RLS.

Related

• Row Level Security
• RLS performance best practices
• How to revoke execute on a Postgres function
• Calling database functions (RPC)