Supabase Local¶

CREATE USER "mage" REPLICATION BYPASSRLS;

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

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

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 permissions for the user_roles and user_permissions tables

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 OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb) RETURNS jsonb
    LANGUAGE plpgsql STABLE
    AS $$
DECLARE
    claims               jsonb;
    user_roles           jsonb;
    role_permissions     jsonb;
    custom_permissions   jsonb;
    combined_permissions jsonb;
BEGIN
    -- Fetch all roles for the user
    SELECT jsonb_agg(role)
    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 IS NULL THEN
        user_roles := jsonb_build_array('user');
    END IF;

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

    -- Fetch all custom permissions for the user
    SELECT jsonb_agg(concat(up.resource, ':', up.action))
    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(coalesce(role_permissions, '[]'::jsonb) ||
                                                            coalesce(custom_permissions, '[]'::jsonb)) AS permission);

    claims := event -> 'claims';

    IF user_roles IS NOT NULL THEN
        claims := jsonb_set(claims, '{user_roles}', user_roles);
    ELSE
        claims := jsonb_set(claims, '{user_roles}', '[]'::jsonb); -- Default to empty array
    END IF;

    claims := jsonb_set(claims, '{user_permissions}', combined_permissions);

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

    RETURN event;
END;
$$;