Skip to content
Miryang's Dev Notes

Supabase 역할 기반 접근 제어

어드민 역할 및 권한을 관리하기 위한 테이블 생성

create type public.admin_role as enum ('SUPER', 'MANAGER');


create table public.admin_roles (
  id        bigint generated by default as identity primary key,
  user_id   uuid references public.profiles on delete cascade not null,
  role      admin_role not null,
  unique (user_id, role)
);


create type public.admin_permission as enum ('admins.create', 'admins.read', 'admins.update', 'products.create', 'products.read', 'products.update');


create table public.admin_role_permissions (
  id           bigint generated by default as identity primary key,
  role         admin_role not null,
  permission   admin_permission not null,
  unique (role, permission)
);
insert into public.admin_role_permissions (role, permission)
values
  ('SUPER', 'admins.create'),
  ('SUPER', 'admins.read'),
  ('SUPER', 'admins.update'),
  ('SUPER', 'products.create'),
  ('SUPER', 'products.read'),
  ('SUPER', 'products.update'),
  ('MANAGER', 'products.create'),
  ('MANAGER', 'products.read'),
  ('MANAGER', 'products.update')

어드민 역할을 적용하기 위한 Auth Hook 생성

-- Create the auth hook function
create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
as $$
  declare
    claims jsonb;
    admin_role public.admin_role;
  begin
    select role into admin_role from public.admin_roles where user_id = (event->>'user_id')::uuid;


    claims := event->'claims';


    if admin_role is not null then
      -- Set the claim
      claims := jsonb_set(claims, '{admin_role}', to_jsonb(admin_role));
    else
      claims := jsonb_set(claims, '{admin_role}', 'null');
    end if;


    -- Update the 'claims' object in the original event
    event := jsonb_set(event, '{claims}', claims);


    -- Return the modified or original event
    return event;
  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 all
  on table public.admin_roles
to supabase_auth_admin;


revoke all
  on table public.admin_roles
  from authenticated, anon, public;


create policy "Allow auth admin to read admin roles" ON public.admin_roles
as permissive for select
to supabase_auth_admin
using (true)

Supabase 대시보드 > Auth Hooks에서 Customize Access Token (JWT) Claims hook 추가

RLS 정책 생성

create or replace function public.authorize_admin(
  requested_permission admin_permission
)
returns boolean as $$
declare
  bind_permissions int;
  admin_role public.admin_role;
begin
  select (auth.jwt() ->> 'admin_role')::public.admin_role into admin_role;


  select count(*)
  into bind_permissions
  from public.admin_role_permissions
  where admin_role_permissions.permission = requested_permission
    and admin_role_permissions.role = admin_role;


  return bind_permissions > 0;
end;
$$ language plpgsql stable security definer set search_path = '';
create policy "Allow authorized admin create access" on public.products for insert with check ( (SELECT authorize_admin('products.create')) );
create policy "Allow authorized admin read access" on public.products for select using ( (SELECT authorize_admin('products.read')) );
create policy "Allow authorized admin update access" on public.products for update using ( (SELECT authorize_admin('products.update')) );

참조