Adding a user role to a Supabase project.

Use the following instructions in a Supabase project to add user_role to jwt token generated at login.

The following steps are required to create and activate the process.

Create A Supabase Role

Create a new role "app_role" in Supabase

 CREATE TYPE app_role AS ENUM ('admin', 'editor', 'user');

Create the user_roles table

The user_roles table is used to connect the logging in user to a user_role. The auth hook uses this table to extract the user_role.

create table public.user_roles (
  id bigint generated by default as identity not null,
  user_id uuid not null,
  role public.app_role not null default 'user',
  constraint user_roles_pkey primary key (id),
  constraint user_roles_user_id_role_key unique (user_id, role),
  constraint user_roles_user_id_fkey foreign KEY (user_id) references auth.users (id) on delete CASCADE
) TABLESPACE pg_default;

Add a Stored Procedure to Supabase

Create a new Stored Procedure in the public schema named "custom_access_token_hook".

    CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
    RETURNS jsonb
    LANGUAGE plpgsql
    AS $$
    declare
    claims jsonb;
    user_role public.app_role;
    begin
    -- Fetch the user role in the user_roles table
    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
      -- Set the claim
      claims := jsonb_set(claims, '{user_role}', to_jsonb(user_role));
    else
      claims := jsonb_set(claims, '{user_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 Permissions to the custom auth hook

Access will have to be granted to the hook for it to function.

GRANT EXECUTE ON FUNCTION public.custom_access_token_hook TO supabase_auth_admin;
GRANT USAGE ON SCHEMA public TO supabase_auth_admin;
REVOKE EXECUTE ON FUNCTION public.custom_access_token_hook FROM authenticated, anon, public;
GRANT ALL ON TABLE public.user_roles TO supabase_auth_admin;
REVOKE ALL ON TABLE public.user_roles FROM authenticated, anon, public;

Modify the docker-compose.yml file

The following items need to be added to the docker-compose.yml file. Add to the bottom of the Auth->Environment section.

GOTRUE_HOOK_CUSTOM_ACCESS_TOKEN_ENABLED: "true"
GOTRUE_HOOK_CUSTOM_ACCESS_TOKEN_URI: "pg-functions://postgres/public/custom_access_token_hook"

Restart docker compose

Docker compose will have to be restart inorder for the hook to work with the user login process.

docker compose down
docker compose up -d

Tags: supabase, fastAPI

← Back home