You Gave Your SECURITY DEFINER Function a Dedicated Owner Role. It Broke. Here's Why.

The least-privilege pattern for Supabase RPCs is well documented: don't let postgres own your SECURITY DEFINER function — create a dedicated NOLOGIN role, transfer ownership, and the function runs with only the privileges it needs. It's in every hardening guide.

What the guides skip is that the pattern breaks in two specific places, and both fail silently at setup time and loudly at runtime — usually in that order, usually in front of a user. This is the anatomy of both failures and the sequence that works, tested against Supabase's managed Postgres (PG16+).

Failure 1: you can't become the owner

ALTER FUNCTION ... OWNER TO r07_executor requires your session to be able to SET ROLE into the target role. On PostgreSQL 16+, membership in a role is split into separate options:

Catalog column	What it permits
admin_option	grant/revoke the membership, regardless of original grantor
set_option	actually SET ROLE into it (and own objects as it)
inherit_option	inherit the role's privileges implicitly

On hosted Supabase, when postgres creates a role, the platform grants the membership — recorded with grantor supabase_admin — and the row you get is often:

member: postgres | granted: r07_executor | grantor: supabase_admin
admin_option: true | set_option: false | inherit_option: false

Teams look at the supabase_admin grantor, conclude the membership is platform-locked, and open a support ticket. But admin_option = true means you can administer the membership yourself — including re-granting it to yourself with the missing flag:

revoke r07_executor from postgres;
grant r07_executor to postgres with set true;

That second statement is the entire fix for failure 1. WITH SET TRUE is PG16+ syntax; the set_option column in your pg_auth_members row is how you verify it took. If the managed platform strips the change on a maintenance pass, that's the support ticket — a one-flag ask, not an access dispute.

Failure 2: the owner can't touch the tables

This is the one nobody writes down. SECURITY DEFINER functions execute with the owner's privileges. A dedicated owner role with the textbook attributes — NOLOGIN, NOSUPERUSER, NOBYPASSRLS — has, by design, no table privileges at all.

So the ownership transfer succeeds, the deployment pipeline is green, and the first authenticated user to call the RPC gets:

PostgresError: permission denied for table authorization_decisions

The function's definition was fine when postgres owned it (postgres has privileges). The new owner doesn't. Custom roles never inherited Supabase's permissive table defaults — those go to anon, authenticated, and service_role only — so the fix is explicit, minimal grants to the owner role:

grant select, insert on public.authorization_decisions to r07_executor;
-- nothing beyond what the function body actually touches

Grant the minimum the function's code paths need — if it reads two tables and writes one, that's select on two and insert on one. The owner role should not have update if no code path updates.

The auth.uid() subtleties inside SECURITY DEFINER

Two behaviors of auth.uid() inside a SECURITY DEFINER function decide whether your actor-derivation is actually safe:

1. JWT claims stay visible. The claims travel in a session-level GUC (request.jwt.claims), and SECURITY DEFINER changes the role, not the session. auth.uid() keeps returning the caller inside the function. This is why the derive-the-actor-inside-the-RPC pattern works at all.
2. Service-key calls return NULL. The service key's JWT carries no sub claim, so auth.uid() is NULL on that path. If service-role invocations should be impossible, don't leave them to fail-open policy logic — assert explicitly:

create or replace function public.transfer_ownership(p_target uuid)
returns void
language plpgsql
security definer
set search_path = ''
as $fn$
begin
  if auth.uid() is null then
    raise exception 'no human actor: refusing';
  end if;
  -- ...
end $fn$;

And the rule that survives every refactor: never accept an actor id as a parameter alongside the internal auth.uid() derivation. The parameter version is the injection vector; the derived version is the control.

The full sequence, in order

Order matters — each step assumes the previous one:

-- 1. minimum table grants to the owner role (fixes failure 2)
grant select, insert on public.authorization_decisions to r07_executor;

-- 2. membership with SET (fixes failure 1)
grant r07_executor to postgres with set true;

-- 3. ownership transfer
alter function public.transfer_ownership(uuid)
  owner to r07_executor;

-- 4. lock the function surface
revoke execute on function public.transfer_ownership(uuid) from public, anon;
grant execute on function public.transfer_ownership(uuid) to authenticated;

-- 5. pin the search_path (hijack surface: zero)
alter function public.transfer_ownership(uuid)
  set search_path = '';

-- 6. drop the working membership, keep only what ops needs
revoke r07_executor from postgres;
-- if ops needs to become the owner occasionally:
-- grant r07_executor to postgres with set true, inherit false;

Step 6 deserves a note: postgres no longer being a member at all is the strongest posture — but if your operations run through postgres, keep the WITH SET TRUE, INHERIT FALSE variant so the role can be become deliberately but never leaks implicitly.

The catalog check for your existing functions

Whether you ran this pattern last week or last year, this finds the two failure modes in one pass:

select p.proname,
       pg_get_userbyid(p.proowner) as owner,
       has_function_privilege('anon', p.oid, 'EXECUTE') as anon_can_execute,
       has_function_privilege('public', p.oid, 'EXECUTE') as public_can_execute
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public'
  and p.prosecdef  -- SECURITY DEFINER only
order by p.proname;

Any row where owner is postgres is a hardening candidate. Any row where anon_can_execute is true should be a decision, not an accident.

Why October 30 makes this more relevant, not less

Supabase's October 30, 2026 change stops auto-granting anon/authenticated on new tables in existing projects. That doesn't touch custom owner roles (they never had defaults), but it makes the discipline load-bearing for everyone: the explicit-grants inventory you now need for the platform roles is the same inventory your SECURITY DEFINER owners should already have been part of. If you're running an October 30 readiness pass, include function owner roles in the grants sweep — the readiness checker and migration templates here cover that inventory step.

---

Sources: PostgreSQL 16 role-membership options (pg_auth_members docs); Supabase role model (docs); a production thread on supabase/supabase#51472 where both failures appeared together.