366 lines
13 KiB
PL/PgSQL
366 lines
13 KiB
PL/PgSQL
-- =============================================================================
|
||
-- 简化版 Supabase 消息系统权限策略
|
||
-- 不依赖 user_type 或 role 列，仅使用JWT中的信息
|
||
-- =============================================================================
|
||
|
||
-- 清理所有现有策略
|
||
DO $$
|
||
DECLARE
|
||
r RECORD;
|
||
BEGIN
|
||
-- 删除所有消息相关表的策略
|
||
FOR r IN
|
||
SELECT schemaname, tablename, policyname
|
||
FROM pg_policies
|
||
WHERE schemaname = 'public'
|
||
AND tablename IN ('ak_messages', 'ak_message_recipients', 'ak_message_groups',
|
||
'ak_message_group_members', 'ak_message_templates',
|
||
'ak_user_message_preferences', 'ak_message_stats', 'ak_message_types')
|
||
LOOP
|
||
EXECUTE format('DROP POLICY IF EXISTS %I ON %I.%I',
|
||
r.policyname, r.schemaname, r.tablename);
|
||
END LOOP;
|
||
|
||
RAISE NOTICE '🧹 已清理所有现有权限策略';
|
||
END $$;
|
||
|
||
-- 重新启用行级安全策略
|
||
ALTER TABLE public.ak_message_types ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ak_messages ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ak_message_recipients ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ak_message_groups ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ak_message_group_members ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ak_message_templates ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ak_user_message_preferences ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ak_message_stats ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- =============================================================================
|
||
-- 1. 消息类型权限策略 (ak_message_types)
|
||
-- =============================================================================
|
||
|
||
-- 所有认证用户可以查看消息类型
|
||
CREATE POLICY "authenticated_can_view_message_types" ON public.ak_message_types
|
||
FOR SELECT USING (auth.role() = 'authenticated');
|
||
|
||
-- 管理员可以修改消息类型
|
||
CREATE POLICY "admins_can_modify_message_types" ON public.ak_message_types
|
||
FOR ALL USING (
|
||
auth.jwt() ->> 'user_role' = 'admin'
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- 2. 消息主表权限策略 (ak_messages)
|
||
-- =============================================================================
|
||
|
||
-- 用户可以查看自己相关的消息
|
||
CREATE POLICY "users_can_view_own_messages" ON public.ak_messages
|
||
FOR SELECT USING (
|
||
-- 用户是发送者
|
||
(sender_type = 'user' AND sender_id = auth.uid())
|
||
OR
|
||
-- 用户是接收者（单用户消息）
|
||
(receiver_type = 'user' AND receiver_id = auth.uid())
|
||
OR
|
||
-- 群组消息（检查是否为群组成员）
|
||
(receiver_type = 'group' AND receiver_id IN (
|
||
SELECT group_id FROM public.ak_message_group_members
|
||
WHERE user_id = auth.uid() AND status = 'active'
|
||
))
|
||
OR
|
||
-- 广播消息所有人可见
|
||
(receiver_type = 'broadcast')
|
||
);
|
||
|
||
-- 用户可以发送消息
|
||
CREATE POLICY "users_can_send_messages" ON public.ak_messages
|
||
FOR INSERT WITH CHECK (
|
||
-- 普通用户只能以自己的身份发送
|
||
(sender_type = 'user' AND sender_id = auth.uid())
|
||
OR
|
||
-- 管理员可以发送系统消息
|
||
(sender_type = 'system' AND auth.jwt() ->> 'user_role' = 'admin')
|
||
);
|
||
|
||
-- 用户可以更新自己发送的消息
|
||
CREATE POLICY "users_can_update_own_messages" ON public.ak_messages
|
||
FOR UPDATE USING (
|
||
sender_type = 'user' AND sender_id = auth.uid()
|
||
);
|
||
|
||
-- 用户可以删除自己发送的消息
|
||
CREATE POLICY "users_can_delete_own_messages" ON public.ak_messages
|
||
FOR DELETE USING (
|
||
sender_type = 'user' AND sender_id = auth.uid()
|
||
);
|
||
|
||
-- 教师额外权限：可以查看更多消息（基于JWT角色）
|
||
CREATE POLICY "teachers_can_view_more_messages" ON public.ak_messages
|
||
FOR SELECT USING (
|
||
auth.jwt() ->> 'user_role' = 'teacher'
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- 3. 消息接收记录权限策略 (ak_message_recipients)
|
||
-- =============================================================================
|
||
|
||
-- 用户只能查看自己的接收记录
|
||
CREATE POLICY "users_can_view_own_recipients" ON public.ak_message_recipients
|
||
FOR SELECT USING (
|
||
recipient_type = 'user' AND recipient_id = auth.uid()
|
||
);
|
||
|
||
-- 系统可以创建接收记录
|
||
CREATE POLICY "system_can_create_recipients" ON public.ak_message_recipients
|
||
FOR INSERT WITH CHECK (
|
||
-- 消息发送者可以创建接收记录
|
||
message_id IN (
|
||
SELECT id FROM public.ak_messages
|
||
WHERE sender_id = auth.uid() OR sender_type = 'system'
|
||
)
|
||
OR
|
||
-- 管理员可以创建任何接收记录
|
||
auth.jwt() ->> 'user_role' = 'admin'
|
||
);
|
||
|
||
-- 用户可以更新自己的接收记录
|
||
CREATE POLICY "users_can_update_own_recipients" ON public.ak_message_recipients
|
||
FOR UPDATE USING (
|
||
recipient_type = 'user' AND recipient_id = auth.uid()
|
||
);
|
||
|
||
-- 教师可以查看更多接收记录（用于教学管理）
|
||
CREATE POLICY "teachers_can_view_student_recipients" ON public.ak_message_recipients
|
||
FOR SELECT USING (
|
||
auth.jwt() ->> 'user_role' = 'teacher'
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- 4. 消息群组权限策略 (ak_message_groups)
|
||
-- =============================================================================
|
||
|
||
-- 用户可以查看自己参与的群组
|
||
CREATE POLICY "users_can_view_own_groups" ON public.ak_message_groups
|
||
FOR SELECT USING (
|
||
-- 群组所有者
|
||
owner_id = auth.uid()
|
||
OR
|
||
-- 群组成员
|
||
id IN (
|
||
SELECT group_id FROM public.ak_message_group_members
|
||
WHERE user_id = auth.uid() AND status = 'active'
|
||
)
|
||
OR
|
||
-- 公开群组
|
||
is_public = true
|
||
);
|
||
|
||
-- 认证用户可以创建群组
|
||
CREATE POLICY "authenticated_can_create_groups" ON public.ak_message_groups
|
||
FOR INSERT WITH CHECK (
|
||
owner_id = auth.uid()
|
||
);
|
||
|
||
-- 群组所有者可以更新群组
|
||
CREATE POLICY "owners_can_update_groups" ON public.ak_message_groups
|
||
FOR UPDATE USING (
|
||
owner_id = auth.uid()
|
||
);
|
||
|
||
-- 群组所有者可以删除群组
|
||
CREATE POLICY "owners_can_delete_groups" ON public.ak_message_groups
|
||
FOR DELETE USING (
|
||
owner_id = auth.uid()
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- 5. 群组成员权限策略 (ak_message_group_members)
|
||
-- =============================================================================
|
||
|
||
-- 群组成员可以查看同组成员
|
||
CREATE POLICY "members_can_view_group_members" ON public.ak_message_group_members
|
||
FOR SELECT USING (
|
||
-- 查看自己的成员记录
|
||
user_id = auth.uid()
|
||
OR
|
||
-- 查看同组其他成员
|
||
group_id IN (
|
||
SELECT group_id FROM public.ak_message_group_members
|
||
WHERE user_id = auth.uid() AND status = 'active'
|
||
)
|
||
);
|
||
|
||
-- 群组所有者可以管理成员
|
||
CREATE POLICY "owners_can_manage_members" ON public.ak_message_group_members
|
||
FOR ALL USING (
|
||
group_id IN (
|
||
SELECT id FROM public.ak_message_groups
|
||
WHERE owner_id = auth.uid()
|
||
)
|
||
OR
|
||
-- 用户可以管理自己的成员记录
|
||
user_id = auth.uid()
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- 6. 消息模板权限策略 (ak_message_templates)
|
||
-- =============================================================================
|
||
|
||
-- 用户可以查看活跃的模板
|
||
CREATE POLICY "users_can_view_active_templates" ON public.ak_message_templates
|
||
FOR SELECT USING (
|
||
is_active = true AND (
|
||
-- 公共模板
|
||
is_system = true
|
||
OR
|
||
-- 自己创建的模板
|
||
created_by = auth.uid()
|
||
OR
|
||
-- 管理员可以查看所有
|
||
auth.jwt() ->> 'user_role' = 'admin'
|
||
)
|
||
);
|
||
|
||
-- 认证用户可以创建模板
|
||
CREATE POLICY "authenticated_can_create_templates" ON public.ak_message_templates
|
||
FOR INSERT WITH CHECK (
|
||
created_by = auth.uid()
|
||
);
|
||
|
||
-- 用户可以更新自己的模板
|
||
CREATE POLICY "users_can_update_own_templates" ON public.ak_message_templates
|
||
FOR UPDATE USING (
|
||
created_by = auth.uid() OR auth.jwt() ->> 'user_role' = 'admin'
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- 7. 用户偏好设置权限策略 (ak_user_message_preferences)
|
||
-- =============================================================================
|
||
|
||
-- 用户只能管理自己的偏好设置
|
||
CREATE POLICY "users_can_manage_own_preferences" ON public.ak_user_message_preferences
|
||
FOR ALL USING (
|
||
user_id = auth.uid()
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- 8. 消息统计权限策略 (ak_message_stats)
|
||
-- =============================================================================
|
||
|
||
-- 管理员和教师可以查看统计数据
|
||
CREATE POLICY "authorized_can_view_stats" ON public.ak_message_stats
|
||
FOR SELECT USING (
|
||
auth.jwt() ->> 'user_role' IN ('admin', 'teacher')
|
||
);
|
||
|
||
-- 只有管理员可以修改统计数据
|
||
CREATE POLICY "admins_can_modify_stats" ON public.ak_message_stats
|
||
FOR ALL USING (
|
||
auth.jwt() ->> 'user_role' = 'admin'
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- 9. 安全辅助函数
|
||
-- =============================================================================
|
||
|
||
-- 检查用户是否为群组成员
|
||
CREATE OR REPLACE FUNCTION public.is_group_member_simple(group_uuid UUID, user_uuid UUID)
|
||
RETURNS BOOLEAN AS $$
|
||
BEGIN
|
||
RETURN EXISTS (
|
||
SELECT 1 FROM public.ak_message_group_members
|
||
WHERE group_id = group_uuid
|
||
AND user_id = user_uuid
|
||
AND status = 'active'
|
||
);
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 检查用户是否可以访问消息
|
||
CREATE OR REPLACE FUNCTION public.can_access_message_simple(message_uuid UUID, user_uuid UUID)
|
||
RETURNS BOOLEAN AS $$
|
||
DECLARE
|
||
msg_record RECORD;
|
||
BEGIN
|
||
SELECT sender_type, sender_id, receiver_type, receiver_id
|
||
INTO msg_record
|
||
FROM public.ak_messages
|
||
WHERE id = message_uuid;
|
||
|
||
IF NOT FOUND THEN
|
||
RETURN FALSE;
|
||
END IF;
|
||
|
||
-- 检查访问权限
|
||
RETURN (
|
||
-- 发送者
|
||
(msg_record.sender_type = 'user' AND msg_record.sender_id = user_uuid)
|
||
OR
|
||
-- 接收者
|
||
(msg_record.receiver_type = 'user' AND msg_record.receiver_id = user_uuid)
|
||
OR
|
||
-- 群组成员
|
||
(msg_record.receiver_type = 'group' AND public.is_group_member_simple(msg_record.receiver_id, user_uuid))
|
||
OR
|
||
-- 广播消息
|
||
(msg_record.receiver_type = 'broadcast')
|
||
);
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- =============================================================================
|
||
-- 10. 测试权限策略
|
||
-- =============================================================================
|
||
|
||
-- 创建测试函数
|
||
CREATE OR REPLACE FUNCTION public.test_permissions_simple()
|
||
RETURNS TABLE(
|
||
test_name TEXT,
|
||
result TEXT
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
'Policy Count Check'::TEXT,
|
||
CASE
|
||
WHEN (SELECT COUNT(*) FROM pg_policies WHERE schemaname = 'public') > 0
|
||
THEN '✅ 权限策略已创建'
|
||
ELSE '❌ 没有找到权限策略'
|
||
END::TEXT;
|
||
|
||
RETURN QUERY
|
||
SELECT
|
||
'RLS Status Check'::TEXT,
|
||
CASE
|
||
WHEN (SELECT COUNT(*) FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'ak_%' AND rowsecurity = true) > 0
|
||
THEN '✅ RLS已启用'
|
||
ELSE '❌ RLS未启用'
|
||
END::TEXT;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 运行测试
|
||
SELECT * FROM public.test_permissions_simple();
|
||
|
||
-- 输出完成信息
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '';
|
||
RAISE NOTICE '=============================================================';
|
||
RAISE NOTICE '✅ 简化版权限策略创建完成！';
|
||
RAISE NOTICE '=============================================================';
|
||
RAISE NOTICE '🔧 特点：';
|
||
RAISE NOTICE ' • 不依赖任何特定的用户表列';
|
||
RAISE NOTICE ' • 仅使用JWT中的用户角色信息';
|
||
RAISE NOTICE ' • 支持基本的消息权限控制';
|
||
RAISE NOTICE ' • 教师和管理员有额外权限';
|
||
RAISE NOTICE '=============================================================';
|
||
RAISE NOTICE '📋 JWT 要求：';
|
||
RAISE NOTICE ' • user_role: admin | teacher | student';
|
||
RAISE NOTICE ' • 确保JWT包含正确的角色信息';
|
||
RAISE NOTICE '=============================================================';
|
||
RAISE NOTICE '🎯 测试用户：';
|
||
RAISE NOTICE ' • 教师: 7bf7378e-a027-473e-97ac-3460ed3f170a';
|
||
RAISE NOTICE ' • 学生: eed3824b-bba1-4309-8048-19d17367c084';
|
||
RAISE NOTICE '=============================================================';
|
||
END $$;
|