When RLS can't express it: security definer done right

Deciding where an operation lives

Three places, in order of preference. Every step down costs you a guarantee.

Prefer up the list. A policy applies to every query anyone ever writes, including the one added in six months by someone who never read this page.

The worked example: ownership transfer

Role hierarchies deliberately make ownership transfer impossible through policies. Three properties put it out of reach:

1. It writes three rows that must agree, and a per-row check can't see the whole picture.
2. It demotes the caller, which no sensible WITH CHECK would permit.
3. It must be all-or-nothing — a half-applied transfer leaves an organization with two owners, or none.

So it becomes a function. And the moment it does, RLS stops running. The order that follows is not stylistic:

authenticate → authorise → validate → act

create or replace function public.transfer_ownership(org_id uuid, new_owner uuid)
returns void
language plpgsql security definer
set search_path = public          -- not optional
as $$
declare
  caller uuid := auth.uid();
begin
  -- 1. authenticate
  if caller is null then
    raise exception 'not authenticated';
  end if;

  -- 2. authorise -- RLS is NOT consulted in here
  if not exists (
    select 1 from organization_members
    where organization_id = org_id and user_id = caller and role = 'owner'
  ) then
    raise exception 'only the owner may transfer ownership';
  end if;

  -- 3. validate the target
  if not exists (
    select 1 from organization_members
    where organization_id = org_id and user_id = new_owner
  ) then
    raise exception 'the new owner must already be a member';
  end if;

  -- 4. act -- one body is one transaction; demote before promoting
  update organization_members set role = 'admin'
   where organization_id = org_id and user_id = caller;

  update organization_members set role = 'owner'
   where organization_id = org_id and user_id = new_owner;

  update organizations set owner_id = new_owner where id = org_id;
end;
$$;

Why each step is there

Authenticate. A definer function runs as its owner. An unauthenticated caller who reaches the body is executing as a privileged role with a null auth.uid() — so every where user_id = auth.uid() silently matches nothing and every if not exists takes the wrong branch.

Authorise. This is the step that gets dropped, because the function's name sounds like it implies the check. Note that it correlates org_id — a check of "is the caller an owner" without the organization would let the owner of any tenant seize any other. That is the role-hierarchy bug, relocated somewhere no policy is watching.

Validate. Promoting a non-member would let an owner add a stranger to the organization as a side effect of an action called something else. Every argument is attacker-supplied, including the ones that look structural.

Act. A function body is one transaction, so the three writes commit together or not at all.

The two mistakes that turn a helper into an escalation

set search_path is not optional

Without it, a definer function resolves unqualified names using the caller's search_path. A caller who can create objects in a schema earlier on that path can shadow a table or function your body references, and have their version run with the owner's privileges. Pin it on every definer function you write.

The EXECUTE grant is the access control

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

Constraints outrank code

Policies protect you from your users. Constraints protect you from your own server-side code, which runs with RLS switched off. If an invariant must hold no matter what, it belongs in the schema:

-- at most one owner per org, enforced even against the service role
create unique index one_owner_per_org
  on organization_members (organization_id)
  where role = 'owner';

This also dictates the write order in the function above: the partial unique index means promoting the new owner before demoting the old one would collide.

When it really is the service role

Some work has no user behind it — Stripe webhooks, nightly jobs, migrations. That's what the service role is for, and it bypasses RLS entirely.

• The key never reaches the browser. Not in NEXT_PUBLIC_*, not in a client component, not in an edge function that echoes its environment.
• Every route using it authenticates and authorises first, in application code, because nothing below is going to. A service-role handler that trusts its input is an open database with a URL.
• Scope it to the narrowest possible endpoint. One route that does one thing beats a general-purpose admin endpoint that takes a table name.

What to test

Once an operation moves into a function, the assertions that matter are the ones proving it restated everything it gave up: an admin can't call it, a member can't, an outsider can't, the owner of a different organization can't, an anonymous caller can't reach the body at all, a failed attempt leaves no partial state, and even the service role can't violate the constraint.

Then delete the authorisation block and re-run. On the function above that fails five separate assertions, including a member promoting themselves and one tenant's owner seizing another. Five failures from deleting six lines is a fair measure of how much a definer function is carrying.