RBAC in local development supabse instance

select auth.jwt()

CREATE OR REPLACE FUNCTION public.authorize(requested_permission public.app_permission)
RETURNS boolean
LANGUAGE plpgsql
STABLE SECURITY DEFINER -- Important: runs as owner to bypass RLS on lookups
AS $$
declare
  user_role public.app_role;
  has_permission int;
begin
  -- 1. Try to get role from JWT (Fastest - Works in App/Production)
  user_role := (auth.jwt() ->> 'user_role')::public.app_role;

  -- 2. Fallback: If JWT is empty (like in Local Impersonation), check the table
  if user_role is null then
    select role into user_role 
    from public.user_roles 
    where user_id = auth.uid();
  end if;

  -- 3. Check permissions
  select count(*)
  into has_permission
  from public.role_permissions
  where role_permissions.permission = requested_permission
    and role_permissions.role = user_role;

  return has_permission > 0;
end;
$$;