# security definer functions and RLS: what to expose

> A security definer function runs with its owner's rights and skips RLS. How to write one safely, keep it out of the Data API, and still call it from RLS policies.

Source: https://supahost.app/kb/security-definer-functions · Updated 2026-11-11

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](/kb/infinite-recursion-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

```sql
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:

```sql
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:

```sql
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;
```

```ts
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`:

```sql
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](/docs/database/row-level-security)
- [RLS performance best practices](/kb/rls-performance)
- [How to revoke execute on a Postgres function](/kb/revoke-function-execute)
- [Calling database functions (RPC)](/docs/api/rpc)