Setting Supabase Users, Custom Claims and RBAC¶

Using this as a starting point:

Base functions¶

CREATE FUNCTION update_updated_at_column() RETURNS trigger
    LANGUAGE plpgsql
AS
$$
BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
END;
$$;

Create tables to track user roles and permissions¶

Create table `users`¶

CREATE TABLE public.users
(
    id                 uuid NOT NULL
        PRIMARY KEY        REFERENCES auth.users,
    name               TEXT,
    avatar_url         TEXT,
    metadata           jsonb,
    updated_at         TIMESTAMP WITH TIME ZONE,
    updated_by         uuid,
    email              TEXT,
    deleted_at         TIMESTAMP WITH TIME ZONE,
    banned_until       TIMESTAMP WITH TIME ZONE,
    email_confirmed_at TIMESTAMP WITH TIME ZONE
);

CREATE TRIGGER users_bu_set_updated_at
    BEFORE UPDATE
    ON public.users
    FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();

Create functions to handle new/updated users¶

CREATE FUNCTION handle_new_user() RETURNS trigger
    SECURITY DEFINER
    SET search_path = ''
    LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO public.users (id,
                              name,
                              avatar_url,
                              email,
                              deleted_at,
                              banned_until,
                              email_confirmed_at)
    VALUES (new.id,
            new.raw_user_meta_data ->> 'full_name',
            new.raw_user_meta_data ->> 'avatar_url',
            new.email,
            new.deleted_at,
            new.banned_until,
            new.email_confirmed_at);
    RETURN new;
END;
$$;

CREATE FUNCTION handle_auth_user_update() RETURNS trigger
    SECURITY DEFINER
    SET search_path = ''
    LANGUAGE plpgsql
AS
$$
BEGIN
    UPDATE public.users
    SET email              = new.email,
        deleted_at         = new.deleted_at,
        banned_until       = new.banned_until,
        email_confirmed_at = new.email_confirmed_at
    WHERE id = new.id;
    RETURN new;
END;
$$;

Create triggers for `auth.users`¶

CREATE TRIGGER on_auth_user_created
    AFTER INSERT
    ON auth.users
    FOR EACH ROW
EXECUTE PROCEDURE public.handle_new_user();

CREATE TRIGGER on_auth_user_updated
    AFTER UPDATE
        OF email, deleted_at, banned_until, email_confirmed_at
    ON auth.users
    FOR EACH ROW
EXECUTE PROCEDURE public.handle_auth_user_update();

Create table `roles`¶

CREATE TABLE public.roles
(
    name  TEXT                         NOT NULL
        PRIMARY KEY,
    color TEXT DEFAULT 'primary'::TEXT NOT NULL
        CONSTRAINT valid_color
            CHECK (color = ANY
                   (ARRAY ['primary'::TEXT, 'red'::TEXT, 'orange'::TEXT, 'amber'::TEXT, 'yellow'::TEXT, 'lime'::TEXT, 'green'::TEXT, 'emerald'::TEXT, 'teal'::TEXT, 'cyan'::TEXT, 'sky'::TEXT, 'blue'::TEXT, 'indigo'::TEXT, 'violet'::TEXT, 'purple'::TEXT, 'fuchsia'::TEXT, 'pink'::TEXT, 'rose'::TEXT]))
);

COMMENT ON TABLE public.roles IS 'Application roles';

Create table `permissions`¶

CREATE TABLE public.permissions
(
    action   TEXT NOT NULL,
    resource TEXT NOT NULL,
    PRIMARY KEY (action, resource)
);

COMMENT ON TABLE public.permissions IS 'Application permissions in the format "resource:action"';

Create table `role_permissions`¶

CREATE TABLE public.role_permissions
(
    role     TEXT NOT NULL
        REFERENCES roles
            ON UPDATE CASCADE ON DELETE CASCADE,
    action   TEXT NOT NULL,
    resource TEXT NOT NULL,
    PRIMARY KEY (role, action, resource),
    FOREIGN KEY (action, resource) REFERENCES permissions
        ON DELETE CASCADE
);

COMMENT ON TABLE public.role_permissions IS 'Permissions assigned to each role';

Create table `user_roles`¶

CREATE TABLE public.user_roles
(
    user_id uuid NOT NULL
        REFERENCES users
            ON DELETE CASCADE,
    role    TEXT NOT NULL
        REFERENCES roles
            ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (user_id, role)
);

COMMENT ON TABLE public.user_roles IS 'Roles assigned to each user';

Create table `user_permissions` (for additional permissions)¶

CREATE TABLE public.user_permissions
(
    user_id  uuid NOT NULL
        REFERENCES users
            ON DELETE CASCADE,
    action   TEXT NOT NULL,
    resource TEXT NOT NULL,
    PRIMARY KEY (user_id, action, resource),
    FOREIGN KEY (action, resource) REFERENCES permissions
        ON DELETE CASCADE
);

COMMENT ON TABLE public.user_permissions IS 'Custom permissions assigned directly to each user';

Create RBAC functions¶

CREATE OR REPLACE FUNCTION public.has_permission(requested_permission text) RETURNS boolean
    STABLE
    SECURITY DEFINER
    SET search_path = ''
    LANGUAGE plpgsql
AS
$$
DECLARE
    bind_permissions INT;
    user_roles       jsonb;
    user_permissions jsonb;
BEGIN
    -- Fetch user roles and custom permissions
    SELECT (auth.jwt() ->> 'user_roles')::jsonb INTO user_roles;
    SELECT (auth.jwt() ->> 'user_permissions')::jsonb INTO user_permissions;

    -- Check for matching permission in roles
    SELECT count(*)
    INTO bind_permissions
    FROM public.role_permissions rp
    WHERE rp.action || ':' || rp.resource = requested_permission
      AND rp.role = ANY (array(SELECT jsonb_array_elements_text(user_roles)));

    -- Check for custom permissions
    IF bind_permissions = 0 THEN
        SELECT count(*)
        INTO bind_permissions
        FROM jsonb_array_elements_text(user_permissions) AS perm
        WHERE perm = requested_permission;
    END IF;

    RETURN bind_permissions > 0;
END;
$$;

CREATE OR REPLACE FUNCTION public.has_role(requested_role text) RETURNS boolean
    STABLE
    SECURITY DEFINER
    SET search_path = ''
    LANGUAGE plpgsql
AS
$$
DECLARE
    user_roles jsonb;
BEGIN
    -- Fetch the roles from the JWT claims
    SELECT (auth.jwt() ->> 'user_roles')::jsonb INTO user_roles;

    -- Check if the requested role is in the list of user roles
    RETURN requested_role = ANY (array(SELECT jsonb_array_elements_text(user_roles)));
END;
$$;

Add policies¶

CREATE POLICY "Public profiles are viewable by everyone." ON public.users
    AS PERMISSIVE
    FOR SELECT    USING TRUE;

CREATE POLICY "Users can insert their own profile." ON public.users
    AS PERMISSIVE
    FOR INSERT    WITH CHECK ((SELECT auth.uid() AS uid) = id);

CREATE POLICY "Users can update own profile." ON public.users
    AS PERMISSIVE
    FOR UPDATE    USING ((SELECT auth.uid() AS uid) = id);

CREATE POLICY "Allow UPDATE to role 'admin'" ON public.users
    AS PERMISSIVE
    FOR UPDATE    TO authenticated
    USING (SELECT has_role('admin'::TEXT) AS has_role);

CREATE POLICY "Allow SELECT with permission 'rbac:read'" ON public.roles
    AS PERMISSIVE
    FOR SELECT    TO authenticated
    USING (SELECT has_permission('rbac:read'::TEXT) AS has_permission);

CREATE POLICY "Allow ALL to role 'admin'" ON public.roles
    AS PERMISSIVE
    FOR ALL    TO authenticated
    USING (SELECT has_role('admin'::TEXT) AS has_role);

CREATE POLICY "Allow SELECT with permission 'rbac:read'" ON public.permissions
    AS PERMISSIVE
    FOR SELECT    TO authenticated
    USING (SELECT has_permission('rbac:read'::TEXT) AS has_permission);

CREATE POLICY "Allow ALL to role 'admin'" ON public.permissions
    AS PERMISSIVE
    FOR ALL    TO authenticated
    USING (SELECT has_role('admin'::TEXT) AS has_role);

CREATE POLICY "Allow auth admin to read role permissions" ON public.role_permissions
    AS PERMISSIVE
    FOR SELECT    TO supabase_auth_admin
    USING TRUE;

CREATE POLICY "Allow SELECT with permission 'rbac:read'" ON public.role_permissions
    AS PERMISSIVE
    FOR SELECT    TO authenticated
    USING (SELECT has_permission('rbac:read'::TEXT) AS has_permission);

CREATE POLICY "Allow ALL to role 'admin'" ON public.role_permissions
    AS PERMISSIVE
    FOR ALL    TO authenticated
    USING (SELECT has_role('admin'::TEXT) AS has_role);

CREATE POLICY "Allow auth admin to read user roles" ON public.user_roles
    AS PERMISSIVE
    FOR SELECT    TO supabase_auth_admin
    USING TRUE;

CREATE POLICY "Allow SELECT with permission 'rbac:read'" ON public.user_roles
    AS PERMISSIVE
    FOR SELECT    TO authenticated
    USING (SELECT has_permission('rbac:read'::TEXT) AS has_permission);

CREATE POLICY "Allow ALL to role 'admin'" ON public.user_roles
    AS PERMISSIVE
    FOR ALL    TO authenticated
    USING (SELECT has_role('admin'::TEXT) AS has_role);

CREATE POLICY "Allow auth admin to read user permissions" ON public.user_permissions
    AS PERMISSIVE
    FOR SELECT    TO supabase_auth_admin
    USING TRUE;

CREATE POLICY "Allow SELECT with permission 'rbac:read'" ON public.user_permissions
    AS PERMISSIVE
    FOR SELECT    TO authenticated
    USING (SELECT has_permission('rbac:read'::TEXT) AS has_permission);

CREATE POLICY "Allow ALL to role 'admin'" ON public.user_permissions
    AS PERMISSIVE
    FOR ALL    TO authenticated
    USING (SELECT has_role('admin'::TEXT) AS has_role);

Access Token Hook¶

CREATE OR REPLACE FUNCTION custom_access_token_hook(event jsonb) RETURNS jsonb
    STABLE
    SET search_path = ""
    LANGUAGE plpgsql
AS
$$
DECLARE
    claims               jsonb;
    user_roles           jsonb DEFAULT '[]'::jsonb;
    role_permissions     jsonb DEFAULT '[]'::jsonb;
    custom_permissions   jsonb DEFAULT '[]'::jsonb;
    combined_permissions jsonb DEFAULT '[]'::jsonb;
BEGIN
    -- Fetch all roles for the user
    SELECT coalesce(jsonb_agg(role), '[]'::jsonb)
    INTO user_roles
    FROM public.user_roles
    WHERE user_id = (event ->> 'user_id')::uuid;

    -- If no roles are found, assign the default role 'user'
    IF user_roles = '[]'::jsonb THEN
        user_roles := jsonb_build_array('user');
    END IF;

    -- Fetch all permissions associated with the user's roles
    SELECT coalesce(jsonb_agg(concat(rp.resource, ':', rp.action)), '[]'::jsonb)
    INTO role_permissions
    FROM public.role_permissions rp
    WHERE rp.role = ANY (array(SELECT jsonb_array_elements_text(coalesce(user_roles, '[]'::jsonb))));

    -- Fetch all custom permissions for the user
    SELECT coalesce(jsonb_agg(concat(up.resource, ':', up.action)), '[]'::jsonb)
    INTO custom_permissions
    FROM public.user_permissions up
    WHERE up.user_id = (event ->> 'user_id')::uuid;

    -- Combine role permissions and custom permissions and remove duplicates
    combined_permissions := (SELECT jsonb_agg(DISTINCT permission)
                             FROM jsonb_array_elements_text(role_permissions || custom_permissions) AS permission);

    claims := event -> 'claims';

    -- Add user roles and permissions to claims
    claims := jsonb_set(claims, '{user_roles}', coalesce(user_roles, '[]'::jsonb));
    claims := jsonb_set(claims, '{user_permissions}', coalesce(combined_permissions, '[]'::jsonb));

    -- Update event with new claims
    event := jsonb_set(event, '{claims}', claims);

    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.user_roles TO supabase_auth_admin;

REVOKE ALL ON TABLE public.user_roles FROM anon, public;

GRANT ALL ON TABLE public.user_permissions TO supabase_auth_admin;

REVOKE ALL ON TABLE public.user_permissions FROM anon, public;

CREATE POLICY "Allow auth admin to read user roles" ON public.user_roles
AS PERMISSIVE FOR SELECT TO supabase_auth_admin
USING (TRUE)

Enable the hook#¶

In the dashboard, navigate to Authentication > Hooks (Beta) and select the appropriate PostgreSQL function from the dropdown menu.

Setting Supabase Users, Custom Claims and RBAC¶

Base functions¶

Create tables to track user roles and permissions¶

Create table users¶

Create functions to handle new/updated users¶

Create triggers for auth.users¶

Create table roles¶

Create table permissions¶

Create table role_permissions¶

Create table user_roles¶

Create table user_permissions (for additional permissions)¶

Create RBAC functions¶

Add policies¶

Access Token Hook¶

Enable the hook#¶

Create table `users`¶

Create triggers for `auth.users`¶

Create table `roles`¶

Create table `permissions`¶

Create table `role_permissions`¶

Create table `user_roles`¶

Create table `user_permissions` (for additional permissions)¶