309 lines
9.7 KiB
PL/PgSQL
309 lines
9.7 KiB
PL/PgSQL
-- =====================================================
|
||
-- Supabase Auth 角色管理和权限配置
|
||
-- 适用于消息系统的教师/学生角色管理
|
||
-- =====================================================
|
||
|
||
-- 1. 创建用户角色管理表
|
||
CREATE TABLE IF NOT EXISTS public.user_roles (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
role TEXT NOT NULL CHECK (role IN ('admin', 'teacher', 'student')),
|
||
class_id UUID DEFAULT NULL, -- 可选：班级关联
|
||
school_id UUID DEFAULT NULL, -- 可选：学校关联
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
created_by UUID REFERENCES auth.users(id),
|
||
|
||
-- 确保每个用户只有一个主要角色
|
||
UNIQUE(user_id)
|
||
);
|
||
|
||
-- 创建索引优化查询
|
||
CREATE INDEX IF NOT EXISTS idx_user_roles_user_id ON public.user_roles(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_user_roles_role ON public.user_roles(role);
|
||
CREATE INDEX IF NOT EXISTS idx_user_roles_class_id ON public.user_roles(class_id);
|
||
|
||
-- 2. 创建更新时间戳触发器
|
||
CREATE OR REPLACE FUNCTION public.update_user_roles_updated_at()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 删除现有触发器（如果存在）
|
||
DROP TRIGGER IF EXISTS trigger_update_user_roles_updated_at ON public.user_roles;
|
||
|
||
CREATE TRIGGER trigger_update_user_roles_updated_at
|
||
BEFORE UPDATE ON public.user_roles
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION public.update_user_roles_updated_at();
|
||
|
||
-- 3. 自动为新用户分配角色的函数
|
||
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
-- 根据邮箱后缀或其他规则自动分配角色
|
||
DECLARE
|
||
user_role TEXT := 'student'; -- 默认角色
|
||
user_email TEXT := NEW.email;
|
||
BEGIN
|
||
-- 根据邮箱判断角色（可根据实际需求调整）
|
||
IF user_email LIKE '%@teacher.%' OR user_email LIKE '%@edu.%' THEN
|
||
user_role := 'teacher';
|
||
ELSIF user_email LIKE '%@admin.%' THEN
|
||
user_role := 'admin';
|
||
END IF;
|
||
|
||
-- 插入角色记录
|
||
INSERT INTO public.user_roles (user_id, role, created_by)
|
||
VALUES (NEW.id, user_role, NEW.id);
|
||
|
||
-- 更新用户元数据中的角色信息
|
||
UPDATE auth.users
|
||
SET raw_user_meta_data = COALESCE(raw_user_meta_data, '{}'::jsonb) ||
|
||
jsonb_build_object('user_role', user_role)
|
||
WHERE id = NEW.id;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 4. 创建新用户触发器
|
||
-- 删除现有触发器（如果存在）
|
||
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
|
||
|
||
CREATE TRIGGER on_auth_user_created
|
||
AFTER INSERT ON auth.users
|
||
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
|
||
|
||
-- 5. 手动更新用户角色的函数
|
||
-- 删除现有函数（如果存在）
|
||
DROP FUNCTION IF EXISTS public.update_user_role(UUID, TEXT, UUID);
|
||
|
||
CREATE OR REPLACE FUNCTION public.update_user_role(
|
||
target_user_id UUID,
|
||
new_role TEXT,
|
||
operator_user_id UUID DEFAULT NULL
|
||
)
|
||
RETURNS BOOLEAN AS $$
|
||
DECLARE
|
||
operator_role TEXT;
|
||
BEGIN
|
||
-- 检查操作者权限（只有admin可以修改角色）
|
||
IF operator_user_id IS NOT NULL THEN
|
||
SELECT role INTO operator_role
|
||
FROM public.user_roles
|
||
WHERE user_id = operator_user_id;
|
||
|
||
IF operator_role != 'admin' THEN
|
||
RAISE EXCEPTION 'Only admins can update user roles';
|
||
END IF;
|
||
END IF;
|
||
|
||
-- 检查新角色是否有效
|
||
IF new_role NOT IN ('admin', 'teacher', 'student') THEN
|
||
RAISE EXCEPTION 'Invalid role: %', new_role;
|
||
END IF;
|
||
|
||
-- 更新角色表
|
||
UPDATE public.user_roles
|
||
SET role = new_role, updated_at = NOW()
|
||
WHERE user_id = target_user_id;
|
||
|
||
-- 同步更新auth.users的元数据
|
||
UPDATE auth.users
|
||
SET raw_user_meta_data = COALESCE(raw_user_meta_data, '{}'::jsonb) ||
|
||
jsonb_build_object('user_role', new_role)
|
||
WHERE id = target_user_id;
|
||
|
||
RETURN FOUND;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 6. 获取用户角色的函数
|
||
-- 删除现有函数（如果存在）
|
||
DROP FUNCTION IF EXISTS public.get_user_role(UUID);
|
||
|
||
CREATE OR REPLACE FUNCTION public.get_user_role(target_user_id UUID DEFAULT auth.uid())
|
||
RETURNS TEXT AS $$
|
||
DECLARE
|
||
user_role TEXT;
|
||
BEGIN
|
||
SELECT role INTO user_role
|
||
FROM public.user_roles
|
||
WHERE user_roles.user_id = target_user_id;
|
||
|
||
RETURN COALESCE(user_role, 'student');
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 7. RLS 策略 for user_roles 表
|
||
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 用户可以查看自己的角色
|
||
CREATE POLICY "Users can view own role" ON public.user_roles
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
-- 管理员可以查看和管理所有角色
|
||
CREATE POLICY "Admins can manage all roles" ON public.user_roles
|
||
FOR ALL USING (
|
||
EXISTS (
|
||
SELECT 1 FROM public.user_roles ur
|
||
WHERE ur.user_id = auth.uid() AND ur.role = 'admin'
|
||
)
|
||
);
|
||
|
||
-- 教师可以查看同班学生的角色（如果有班级系统）
|
||
CREATE POLICY "Teachers can view students in same class" ON public.user_roles
|
||
FOR SELECT USING (
|
||
EXISTS (
|
||
SELECT 1 FROM public.user_roles teacher_role
|
||
WHERE teacher_role.user_id = auth.uid()
|
||
AND teacher_role.role = 'teacher'
|
||
AND teacher_role.class_id = user_roles.class_id
|
||
)
|
||
);
|
||
|
||
-- 8. 预设测试用户角色
|
||
INSERT INTO public.user_roles (user_id, role, created_by) VALUES
|
||
(
|
||
'7bf7378e-a027-473e-97ac-3460ed3f170a', -- 教师用户ID
|
||
'teacher',
|
||
'7bf7378e-a027-473e-97ac-3460ed3f170a'
|
||
) ON CONFLICT (user_id) DO UPDATE SET
|
||
role = EXCLUDED.role,
|
||
updated_at = NOW();
|
||
|
||
INSERT INTO public.user_roles (user_id, role, created_by) VALUES
|
||
(
|
||
'eed3824b-bba1-4309-8048-19d17367c084', -- 学生用户ID
|
||
'student',
|
||
'eed3824b-bba1-4309-8048-19d17367c084'
|
||
) ON CONFLICT (user_id) DO UPDATE SET
|
||
role = EXCLUDED.role,
|
||
updated_at = NOW();
|
||
|
||
-- 9. 更新这些用户的auth元数据
|
||
UPDATE auth.users
|
||
SET raw_user_meta_data = COALESCE(raw_user_meta_data, '{}'::jsonb) ||
|
||
jsonb_build_object('user_role', 'teacher')
|
||
WHERE id = '7bf7378e-a027-473e-97ac-3460ed3f170a';
|
||
|
||
UPDATE auth.users
|
||
SET raw_user_meta_data = COALESCE(raw_user_meta_data, '{}'::jsonb) ||
|
||
jsonb_build_object('user_role', 'student')
|
||
WHERE id = 'eed3824b-bba1-4309-8048-19d17367c084';
|
||
|
||
-- 10. 创建角色管理视图（方便查询）
|
||
CREATE OR REPLACE VIEW public.user_roles_with_email AS
|
||
SELECT
|
||
ur.id,
|
||
ur.user_id,
|
||
ur.role,
|
||
ur.class_id,
|
||
ur.school_id,
|
||
ur.created_at,
|
||
ur.updated_at,
|
||
au.email,
|
||
au.raw_user_meta_data->>'user_role' as metadata_role,
|
||
CASE
|
||
WHEN ur.role = au.raw_user_meta_data->>'user_role' THEN true
|
||
ELSE false
|
||
END as role_synced
|
||
FROM public.user_roles ur
|
||
LEFT JOIN auth.users au ON au.id = ur.user_id;
|
||
|
||
-- 注意：视图不支持RLS策略，权限控制通过底层表 user_roles 实现
|
||
-- 视图的访问权限由底层表的策略控制
|
||
|
||
-- 11. 批量角色管理函数（仅管理员）
|
||
-- 删除现有函数（如果存在）
|
||
DROP FUNCTION IF EXISTS public.batch_update_user_roles(JSONB, UUID);
|
||
|
||
CREATE OR REPLACE FUNCTION public.batch_update_user_roles(
|
||
role_updates JSONB, -- [{"user_id": "uuid", "role": "teacher"}, ...]
|
||
operator_user_id UUID DEFAULT auth.uid()
|
||
)
|
||
RETURNS TABLE(user_id UUID, old_role TEXT, new_role TEXT, success BOOLEAN) AS $$
|
||
DECLARE
|
||
update_record RECORD;
|
||
operator_role TEXT;
|
||
old_role_val TEXT;
|
||
BEGIN
|
||
-- 检查操作者权限
|
||
SELECT role INTO operator_role
|
||
FROM public.user_roles
|
||
WHERE user_roles.user_id = operator_user_id;
|
||
|
||
IF operator_role != 'admin' THEN
|
||
RAISE EXCEPTION 'Only admins can batch update user roles';
|
||
END IF;
|
||
|
||
-- 处理每个更新请求
|
||
FOR update_record IN
|
||
SELECT * FROM jsonb_to_recordset(role_updates)
|
||
AS x(user_id UUID, role TEXT)
|
||
LOOP
|
||
-- 获取旧角色
|
||
SELECT ur.role INTO old_role_val
|
||
FROM public.user_roles ur
|
||
WHERE ur.user_id = update_record.user_id;
|
||
|
||
-- 尝试更新
|
||
BEGIN
|
||
PERFORM public.update_user_role(
|
||
update_record.user_id,
|
||
update_record.role,
|
||
operator_user_id
|
||
);
|
||
|
||
RETURN QUERY SELECT
|
||
update_record.user_id,
|
||
old_role_val,
|
||
update_record.role,
|
||
true;
|
||
EXCEPTION WHEN OTHERS THEN
|
||
RETURN QUERY SELECT
|
||
update_record.user_id,
|
||
old_role_val,
|
||
update_record.role,
|
||
false;
|
||
END;
|
||
END LOOP;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- =====================================================
|
||
-- 使用说明和示例
|
||
-- =====================================================
|
||
|
||
/*
|
||
-- 示例1：手动更新用户角色（需要管理员权限）
|
||
SELECT public.update_user_role(
|
||
'eed3824b-bba1-4309-8048-19d17367c084', -- 用户ID
|
||
'teacher', -- 新角色
|
||
auth.uid() -- 操作者ID（需要是admin）
|
||
);
|
||
|
||
-- 示例2：查询用户角色
|
||
SELECT public.get_user_role('7bf7378e-a027-473e-97ac-3460ed3f170a');
|
||
|
||
-- 示例3：查看所有用户角色（管理员视图）
|
||
SELECT * FROM public.user_roles_with_email ORDER BY created_at DESC;
|
||
|
||
-- 示例4：批量更新角色
|
||
SELECT * FROM public.batch_update_user_roles(
|
||
'[
|
||
{"user_id": "eed3824b-bba1-4309-8048-19d17367c084", "role": "teacher"},
|
||
{"user_id": "another-user-id", "role": "student"}
|
||
]'::jsonb
|
||
);
|
||
|
||
-- 示例5：检查角色同步状态
|
||
SELECT user_id, email, role, metadata_role, role_synced
|
||
FROM public.user_roles_with_email
|
||
WHERE NOT role_synced;
|
||
*/
|