Custom claims: the access-token hook stamps roles into the JWT

The answer. Policies that join membership tables on every row get expensive and hard to read; a claim in the JWT makes them one comparison. The custom access token hook is a Postgres function Auth calls whenever it mints a token: it receives event (with user_id and the draft claims), reads whatever tables it likes, and returns the event with claims added. Our API Worker relies on it for active_org_id and active_org_role. The grants are the part people get wrong: the function must be executable by supabase_auth_admin and by nobody else, and any table it reads needs both a grant and a policy for that role. Then read the claims from the verified token (getClaims(), or auth.jwt() inside policies), because a hook-added claim is not a column on the user record.

The pattern.

create or replace function public.custom_access_token_hook(event jsonb) returns jsonb
language plpgsql stable set search_path = '' as $$
declare claims jsonb := event->'claims'; r text;
begin
  select role into r from public.user_roles where user_id = (event->>'user_id')::uuid;
  claims := jsonb_set(claims, '{app_metadata,user_role}', coalesce(to_jsonb(r), 'null'::jsonb));
  return jsonb_set(event, '{claims}', claims);
end $$;
grant usage on schema public to supabase_auth_admin;
grant execute on function public.custom_access_token_hook to supabase_auth_admin;
revoke execute on function public.custom_access_token_hook from authenticated, anon, public;
grant select on table public.user_roles to supabase_auth_admin;
create policy "auth admin reads roles" on public.user_roles for select to supabase_auth_admin using (true);
-- in a policy:  using ((select auth.jwt()->'app_metadata'->>'user_role') = 'admin')

Watch out.

The function is data; the binding is instance configuration (Dashboard → Authentication → Hooks, or [auth.hook.custom_access_token] locally). A fresh project has the function and no binding, and every authed request 401s.
Claims are minted at sign-in and refresh only. A demotion takes effect at the next refresh; sensitive routes must also check the live table.
A hook that throws blocks every login. Wrap lookups defensively and test it with a real sign-in before deploying.