46 lines
1.2 KiB
PL/PgSQL
46 lines
1.2 KiB
PL/PgSQL
-- Admin-only function to change any user's role.
|
|
-- Uses auth.uid() for the admin check (not a parameter).
|
|
CREATE OR REPLACE FUNCTION admin_set_user_role(
|
|
p_target_user_id UUID,
|
|
p_new_role TEXT
|
|
)
|
|
RETURNS JSON
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
v_old_role TEXT;
|
|
BEGIN
|
|
-- Only admins may call this
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
|
|
) THEN
|
|
RAISE EXCEPTION 'Unauthorized: admin access required';
|
|
END IF;
|
|
|
|
-- Validate role
|
|
IF p_new_role NOT IN ('user', 'provider', 'admin') THEN
|
|
RAISE EXCEPTION 'Invalid role: must be user, provider, or admin';
|
|
END IF;
|
|
|
|
-- Prevent self-demotion (optional safeguard)
|
|
IF p_target_user_id = auth.uid() AND p_new_role != 'admin' THEN
|
|
RAISE EXCEPTION 'Cannot demote yourself from admin';
|
|
END IF;
|
|
|
|
SELECT role INTO v_old_role FROM profiles WHERE id = p_target_user_id;
|
|
|
|
UPDATE profiles
|
|
SET role = p_new_role, updated_at = NOW()
|
|
WHERE id = p_target_user_id;
|
|
|
|
RETURN json_build_object(
|
|
'success', true,
|
|
'old_role', v_old_role,
|
|
'new_role', p_new_role
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
GRANT EXECUTE ON FUNCTION admin_set_user_role(UUID, TEXT) TO authenticated; |