Supabase Custom User Roles: Implementing Fine-grained Access Control

Implementing secure permissions in modern web applications requires moving beyond simple authenticated versus unauthenticated states. When building on PostgreSQL, Supabase gives you the necessary building blocks via Row Level Security (RLS), but mapping user identities to precise operational privileges takes structured configuration. By combining custom claims, dedicated database tables, and auth hooks, you can build a complete Role-Based Access Control (RBAC) system that restricts data exposure precisely at the database layer.

This guide walks through creating custom user roles from scratch, injecting those roles into JSON Web Tokens (JWTs), and writing RLS policies that enforce granular permissions. You'll also see how to avoid performance traps and handle role synchronization cleanly without risking data leaks.

Designing the Role and Permission Schema

Before writing any access control policies, structure how roles and permissions live inside your database. Hardcoding user types into the default auth.users table metadata can get messy as requirements scale. Instead, create dedicated relational tables inside your public schema.

Using native PostgreSQL enums helps enforce strict type safety for your roles and permissions:

create type public.app_permission as enum ('channels.delete', 'messages.delete');
create type public.app_role as enum ('admin', 'moderator');

create table public.user_roles (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users on delete cascade not null,
  role app_role not null,
  unique (user_id, role)
);

create table public.role_permissions (
  id bigint generated by default as identity primary key,
  role app_role not null,
  permission app_permission not null,
  unique (role, permission)
);

Next, seed your role permissions so the application knows what each tier is allowed to execute:

insert into public.role_permissions (role, permission)
values
  ('admin', 'channels.delete'),
  ('admin', 'messages.delete'),
  ('moderator', 'messages.delete');

Lock down these configuration tables immediately using RLS. Regular users shouldn't read or write role mappings directly through the client API.

Injecting Roles into JWTs via Auth Hooks

To evaluate permissions efficiently inside RLS policies without running expensive joins on every single row check, inject the user's role directly into their JWT claims. Supabase allows you to modify outgoing access tokens using custom access token hooks.

Create a PL/pgSQL function that intercepts the token generation event, queries your user_roles table, and appends the role claim:

create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
as $$
declare
  claims jsonb;
  user_role public.app_role;
begin
  select role into user_role 
  from public.user_roles 
  where user_id = (event->>'user_id')::uuid;

  claims := event->'claims';

  if user_role is not null then
    claims := jsonb_set(claims, '{user_role}', to_jsonb(user_role));
  else
    claims := jsonb_set(claims, '{user_role}', 'null'::jsonb);
  end if;

  event := jsonb_set(event, '{claims}', claims);
  return event;
end;
$$;

Grant execution rights strictly to the internal supabase_auth_admin role while revoking access from public, anon, and authenticated users:

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;

Writing RLS Policies with Custom Claims

With the custom claim embedded in the JWT under user_role, your RLS policies can evaluate user permissions instantly. Instead of querying tables inside the policy itself—which can introduce performance bottlenecks—you read the claim straight from the request context using auth.jwt().

First, write a helper authorization function that checks if the JWT claims match a required permission:

create or replace function public.authorize(requested_permission public.app_permission)
returns boolean
language sql
stable
as $$
  select exists (
    select 1
    from public.role_permissions
    where role = (auth.jwt() ->> 'user_role')::public.app_role
      and permission = requested_permission
  );
$$;

Now, apply this helper function directly to your application tables:

alter table public.messages enable row level security;

create policy "Allow moderators and admins to delete messages"
  on public.messages
  for delete
  to authenticated
  using (public.authorize('messages.delete'));

When a user attempts a delete operation on the messages table, PostgreSQL evaluates the JWT claim against the permission mapping instantly, blocking unauthorized requests before execution.

Handling Client-Side State and Token Refresh

When a user's role changes in the database—for instance, an administrator promotes a moderator—the active JWT won't reflect that update immediately because tokens are cached until they expire.

To handle this smoothly on the client side without forcing users to log out and back in, trigger a manual token refresh after role modifications using the Supabase JavaScript Client:

import { createClient } from '@supabase/supabase-js'

const supabase = createClient('YOUR_SUPABASE_URL', 'YOUR_SUPABASE_ANON_KEY')

async function refreshUserSession() {
  const { data, error } = await supabase.auth.refreshSession()
  if (error) {
    console.error('Failed to refresh session:', error)
    return
  }
  console.log('Session refreshed with updated custom claims:', data.session)
}

If your application architecture requires instant propagation across active sockets, combine this token refresh with Supabase Realtime channels to broadcast a refresh signal to specific user IDs.

Common Mistakes to Avoid

When building custom access control layers, developers often run into silent security gaps:

• Trusting client-side state blindly. Never manage user roles solely in frontend local storage or unverified Redux states. If the database RLS policies don't validate the claim, a client can bypass your UI entirely.
• Forgetting to scope admin endpoints. Ensure your backend administrative updates check that the modifying user possesses the proper authorization tier, preventing privilege escalation.
• Over-indexing JWT claims. Keep custom claims light. Storing large arrays or entire user profiles inside the JWT bloats header sizes and degrades network performance.

Frequently Asked Questions

Can I assign multiple roles to a single user?

The schema demonstrated above uses a unique constraint on (user_id, role), which allows a user to hold multiple roles if configured. You will need to adjust your token hook to pack an array of roles into the JWT claims and update your authorize function to check array containment using the ?| operator.

How do I test my RLS policies locally?

You can use set local role and simulate JWT settings inside your local SQL editor using set_config('request.jwt.claims', '{"user_role": "moderator"}', true). This lets you execute test queries as different user tiers without spinning up separate browser sessions.

Why use database hooks instead of updating user metadata directly?

While Supabase provides a user_metadata field, it can sometimes be modified directly by end-users depending on client settings. Storing critical authorization data in a server-managed table and injecting it via a secure auth hook ensures users cannot tamper with their own roles.

What happens when a custom claim is null?

If a user has no entry in the user_roles table, the hook sets user_role to null. Your RLS policies should treat null claims as unprivileged, denying access to protected resources safely.