596 lines
21 KiB
PL/PgSQL
596 lines
21 KiB
PL/PgSQL
-- =====================================================
|
||
-- 消息系统完整权限策略配置
|
||
-- 配合 supabase_auth_complete_setup.sql 使用
|
||
-- =====================================================
|
||
|
||
-- 清理所有现有权限策略
|
||
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_users_can_view_message_types" ON public.ak_message_types
|
||
FOR SELECT USING (
|
||
auth.role() = 'authenticated'
|
||
);
|
||
|
||
-- 管理员和教师可以管理消息类型
|
||
CREATE POLICY "admins_teachers_can_manage_message_types" ON public.ak_message_types
|
||
FOR ALL USING (
|
||
public.get_user_role(auth.uid()) IN ('admin', 'teacher')
|
||
);
|
||
|
||
-- =====================================================
|
||
-- 2. 消息主表权限策略 (ak_messages)
|
||
-- =====================================================
|
||
|
||
-- 用户可以查看相关的消息
|
||
CREATE POLICY "users_can_view_relevant_messages" ON public.ak_messages
|
||
FOR SELECT USING (
|
||
-- 管理员可以查看所有消息
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 用户是发送者
|
||
(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')
|
||
OR
|
||
-- 教师可以查看学生发送的消息（如果在同一个班级）
|
||
(public.get_user_role(auth.uid()) = 'teacher'
|
||
AND sender_type = 'user'
|
||
AND EXISTS (
|
||
SELECT 1 FROM public.user_roles teacher_role
|
||
JOIN public.user_roles student_role ON teacher_role.class_id = student_role.class_id
|
||
WHERE teacher_role.user_id = auth.uid()
|
||
AND teacher_role.role = 'teacher'
|
||
AND student_role.user_id = sender_id
|
||
AND student_role.role = 'student'
|
||
AND teacher_role.class_id IS NOT NULL
|
||
))
|
||
);
|
||
|
||
-- 用户可以发送消息
|
||
CREATE POLICY "users_can_send_messages" ON public.ak_messages
|
||
FOR INSERT WITH CHECK (
|
||
-- 认证用户可以以自己的身份发送消息
|
||
(auth.role() = 'authenticated' AND sender_type = 'user' AND sender_id = auth.uid())
|
||
OR
|
||
-- 管理员可以发送系统消息
|
||
(public.get_user_role(auth.uid()) = 'admin' AND sender_type = 'system')
|
||
OR
|
||
-- 教师可以发送广播消息（如果有权限）
|
||
(public.get_user_role(auth.uid()) = 'teacher'
|
||
AND receiver_type = 'broadcast'
|
||
AND public.user_has_permission('can_send_broadcasts', auth.uid()))
|
||
);
|
||
|
||
-- 用户可以更新自己发送的消息
|
||
CREATE POLICY "users_can_update_own_messages" ON public.ak_messages
|
||
FOR UPDATE USING (
|
||
-- 管理员可以更新所有消息
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 用户可以更新自己发送的消息
|
||
(sender_type = 'user' AND sender_id = auth.uid())
|
||
OR
|
||
-- 教师可以更新发送给自己班级的消息
|
||
(public.get_user_role(auth.uid()) = 'teacher'
|
||
AND public.user_has_permission('can_moderate', auth.uid())
|
||
AND receiver_type = 'group'
|
||
AND receiver_id IN (
|
||
SELECT group_id FROM public.ak_message_group_members mgm
|
||
JOIN public.user_roles ur ON ur.user_id = mgm.user_id
|
||
WHERE mgm.user_id = auth.uid() AND ur.role = 'teacher'
|
||
))
|
||
);
|
||
|
||
-- 用户可以删除自己发送的消息
|
||
CREATE POLICY "users_can_delete_own_messages" ON public.ak_messages
|
||
FOR DELETE USING (
|
||
-- 管理员可以删除所有消息
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 用户可以删除自己发送的消息
|
||
(sender_type = 'user' AND sender_id = auth.uid())
|
||
OR
|
||
-- 教师可以删除不当消息（如果有权限）
|
||
(public.get_user_role(auth.uid()) = 'teacher'
|
||
AND public.user_has_permission('can_moderate', auth.uid()))
|
||
);
|
||
|
||
-- =====================================================
|
||
-- 3. 消息接收者权限策略 (ak_message_recipients)
|
||
-- =====================================================
|
||
|
||
-- 用户可以查看自己的接收记录
|
||
CREATE POLICY "users_can_view_own_recipients" ON public.ak_message_recipients
|
||
FOR SELECT USING (
|
||
-- 管理员可以查看所有记录
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 用户可以查看自己的接收记录
|
||
user_id = auth.uid()
|
||
OR
|
||
-- 发送者可以查看接收状态
|
||
message_id IN (
|
||
SELECT id FROM public.ak_messages
|
||
WHERE sender_type = 'user' AND sender_id = auth.uid()
|
||
)
|
||
OR
|
||
-- 教师可以查看学生的接收状态（同班级）
|
||
(public.get_user_role(auth.uid()) = 'teacher'
|
||
AND user_id IN (
|
||
SELECT student_role.user_id FROM public.user_roles teacher_role
|
||
JOIN public.user_roles student_role ON teacher_role.class_id = student_role.class_id
|
||
WHERE teacher_role.user_id = auth.uid()
|
||
AND teacher_role.role = 'teacher'
|
||
AND student_role.role = 'student'
|
||
AND teacher_role.class_id IS NOT NULL
|
||
))
|
||
);
|
||
|
||
-- 系统自动创建接收记录
|
||
CREATE POLICY "system_can_create_recipients" ON public.ak_message_recipients
|
||
FOR INSERT WITH CHECK (
|
||
-- 管理员可以创建记录
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 消息发送者可以创建接收记录
|
||
message_id IN (
|
||
SELECT id FROM public.ak_messages
|
||
WHERE sender_type = 'user' AND sender_id = auth.uid()
|
||
)
|
||
OR
|
||
-- 用户可以为自己创建接收记录
|
||
user_id = auth.uid()
|
||
);
|
||
|
||
-- 用户可以更新自己的接收状态
|
||
CREATE POLICY "users_can_update_own_recipients" ON public.ak_message_recipients
|
||
FOR UPDATE USING (
|
||
user_id = auth.uid()
|
||
OR
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
);
|
||
|
||
-- =====================================================
|
||
-- 4. 消息群组权限策略 (ak_message_groups)
|
||
-- =====================================================
|
||
|
||
-- 用户可以查看相关的群组
|
||
CREATE POLICY "users_can_view_relevant_groups" ON public.ak_message_groups
|
||
FOR SELECT USING (
|
||
-- 管理员可以查看所有群组
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 群组创建者可以查看
|
||
created_by = auth.uid()
|
||
OR
|
||
-- 群组成员可以查看
|
||
id IN (
|
||
SELECT group_id FROM public.ak_message_group_members
|
||
WHERE user_id = auth.uid() AND status = 'active'
|
||
)
|
||
OR
|
||
-- 教师可以查看公开群组
|
||
(public.get_user_role(auth.uid()) = 'teacher' AND is_public = true)
|
||
);
|
||
|
||
-- 用户可以创建群组
|
||
CREATE POLICY "users_can_create_groups" ON public.ak_message_groups
|
||
FOR INSERT WITH CHECK (
|
||
-- 认证用户可以创建群组
|
||
auth.role() = 'authenticated' AND created_by = auth.uid()
|
||
AND (
|
||
-- 教师默认可以创建群组
|
||
public.get_user_role(auth.uid()) = 'teacher'
|
||
OR
|
||
-- 学生需要特定权限
|
||
(public.get_user_role(auth.uid()) = 'student'
|
||
AND public.user_has_permission('can_create_groups', auth.uid()))
|
||
OR
|
||
-- 管理员可以创建群组
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
)
|
||
);
|
||
|
||
-- 群组创建者和管理员可以更新群组
|
||
CREATE POLICY "creators_admins_can_update_groups" ON public.ak_message_groups
|
||
FOR UPDATE USING (
|
||
created_by = auth.uid()
|
||
OR
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
);
|
||
|
||
-- 群组创建者和管理员可以删除群组
|
||
CREATE POLICY "creators_admins_can_delete_groups" ON public.ak_message_groups
|
||
FOR DELETE USING (
|
||
created_by = auth.uid()
|
||
OR
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
);
|
||
|
||
-- =====================================================
|
||
-- 5. 群组成员权限策略 (ak_message_group_members)
|
||
-- =====================================================
|
||
|
||
-- 用户可以查看相关的群组成员
|
||
CREATE POLICY "users_can_view_relevant_group_members" ON public.ak_message_group_members
|
||
FOR SELECT USING (
|
||
-- 管理员可以查看所有成员
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 用户可以查看自己的成员关系
|
||
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'
|
||
)
|
||
OR
|
||
-- 群组创建者可以查看所有成员
|
||
group_id IN (
|
||
SELECT id FROM public.ak_message_groups
|
||
WHERE created_by = auth.uid()
|
||
)
|
||
);
|
||
|
||
-- 群组创建者和管理员可以添加成员
|
||
CREATE POLICY "creators_admins_can_add_members" ON public.ak_message_group_members
|
||
FOR INSERT WITH CHECK (
|
||
-- 管理员可以添加成员
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 群组创建者可以添加成员
|
||
group_id IN (
|
||
SELECT id FROM public.ak_message_groups
|
||
WHERE created_by = auth.uid()
|
||
)
|
||
OR
|
||
-- 用户可以申请加入群组（pending状态）
|
||
(user_id = auth.uid() AND status = 'pending')
|
||
);
|
||
|
||
-- 群组创建者、管理员和用户本人可以更新成员状态
|
||
CREATE POLICY "authorized_users_can_update_members" ON public.ak_message_group_members
|
||
FOR UPDATE USING (
|
||
-- 管理员可以更新所有成员
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 群组创建者可以更新成员
|
||
group_id IN (
|
||
SELECT id FROM public.ak_message_groups
|
||
WHERE created_by = auth.uid()
|
||
)
|
||
OR
|
||
-- 用户可以更新自己的成员状态（如退出群组）
|
||
user_id = auth.uid()
|
||
);
|
||
|
||
-- 群组创建者、管理员和用户本人可以删除成员
|
||
CREATE POLICY "authorized_users_can_remove_members" ON public.ak_message_group_members
|
||
FOR DELETE USING (
|
||
-- 管理员可以删除所有成员
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 群组创建者可以删除成员
|
||
group_id IN (
|
||
SELECT id FROM public.ak_message_groups
|
||
WHERE created_by = auth.uid()
|
||
)
|
||
OR
|
||
-- 用户可以删除自己的成员关系
|
||
user_id = auth.uid()
|
||
);
|
||
|
||
-- =====================================================
|
||
-- 6. 消息模板权限策略 (ak_message_templates)
|
||
-- =====================================================
|
||
|
||
-- 用户可以查看相关的消息模板
|
||
CREATE POLICY "users_can_view_relevant_templates" ON public.ak_message_templates
|
||
FOR SELECT USING (
|
||
-- 管理员可以查看所有模板
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 用户可以查看自己创建的模板
|
||
created_by = auth.uid()
|
||
OR
|
||
-- 用户可以查看公开模板
|
||
is_public = true
|
||
OR
|
||
-- 教师可以查看教师模板
|
||
(public.get_user_role(auth.uid()) = 'teacher' AND category = 'teacher')
|
||
);
|
||
|
||
-- 用户可以创建模板
|
||
CREATE POLICY "users_can_create_templates" ON public.ak_message_templates
|
||
FOR INSERT WITH CHECK (
|
||
auth.role() = 'authenticated' AND created_by = auth.uid()
|
||
);
|
||
|
||
-- 用户可以更新自己的模板
|
||
CREATE POLICY "users_can_update_own_templates" ON public.ak_message_templates
|
||
FOR UPDATE USING (
|
||
created_by = auth.uid()
|
||
OR
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
);
|
||
|
||
-- 用户可以删除自己的模板
|
||
CREATE POLICY "users_can_delete_own_templates" ON public.ak_message_templates
|
||
FOR DELETE USING (
|
||
created_by = auth.uid()
|
||
OR
|
||
public.get_user_role(auth.uid()) = '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()
|
||
OR
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
);
|
||
|
||
-- =====================================================
|
||
-- 8. 消息统计权限策略 (ak_message_stats)
|
||
-- =====================================================
|
||
|
||
-- 用户可以查看相关的统计信息
|
||
CREATE POLICY "users_can_view_relevant_stats" ON public.ak_message_stats
|
||
FOR SELECT USING (
|
||
-- 管理员可以查看所有统计
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
OR
|
||
-- 用户可以查看自己相关的统计
|
||
(entity_type = 'user' AND entity_id = auth.uid())
|
||
OR
|
||
-- 群组成员可以查看群组统计
|
||
(entity_type = 'group' AND entity_id IN (
|
||
SELECT group_id FROM public.ak_message_group_members
|
||
WHERE user_id = auth.uid() AND status = 'active'
|
||
))
|
||
OR
|
||
-- 教师可以查看班级统计
|
||
(entity_type = 'class' AND public.get_user_role(auth.uid()) = 'teacher'
|
||
AND entity_id IN (
|
||
SELECT class_id FROM public.user_roles
|
||
WHERE user_id = auth.uid() AND role = 'teacher'
|
||
))
|
||
);
|
||
|
||
-- 系统和管理员可以更新统计
|
||
CREATE POLICY "system_can_update_stats" ON public.ak_message_stats
|
||
FOR ALL USING (
|
||
public.get_user_role(auth.uid()) = 'admin'
|
||
);
|
||
|
||
-- =====================================================
|
||
-- 9. 安全函数和辅助策略
|
||
-- =====================================================
|
||
|
||
-- 创建安全的消息发送函数
|
||
CREATE OR REPLACE FUNCTION public.send_secure_message(
|
||
message_type_id UUID,
|
||
receiver_type TEXT,
|
||
receiver_id UUID,
|
||
title TEXT,
|
||
content TEXT,
|
||
metadata_json JSONB DEFAULT '{}'::jsonb
|
||
)
|
||
RETURNS UUID AS $$
|
||
DECLARE
|
||
new_message_id UUID;
|
||
sender_role TEXT;
|
||
BEGIN
|
||
-- 检查发送者角色
|
||
sender_role := public.get_user_role(auth.uid());
|
||
|
||
-- 权限检查
|
||
IF sender_role IS NULL OR sender_role = 'anonymous' THEN
|
||
RAISE EXCEPTION 'Authentication required to send messages';
|
||
END IF;
|
||
|
||
-- 检查广播权限
|
||
IF receiver_type = 'broadcast' AND NOT public.user_has_permission('can_send_broadcasts', auth.uid()) THEN
|
||
RAISE EXCEPTION 'Permission denied: Cannot send broadcast messages';
|
||
END IF;
|
||
|
||
-- 插入消息
|
||
INSERT INTO public.ak_messages (
|
||
message_type_id, sender_type, sender_id, receiver_type, receiver_id,
|
||
title, content, metadata, created_by
|
||
) VALUES (
|
||
message_type_id, 'user', auth.uid(), receiver_type, receiver_id,
|
||
title, content, metadata_json, auth.uid()
|
||
) RETURNING id INTO new_message_id;
|
||
|
||
RETURN new_message_id;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 创建安全的群组加入函数
|
||
CREATE OR REPLACE FUNCTION public.join_message_group(
|
||
target_group_id UUID,
|
||
join_message TEXT DEFAULT NULL
|
||
)
|
||
RETURNS BOOLEAN AS $$
|
||
DECLARE
|
||
group_info RECORD;
|
||
user_role TEXT;
|
||
BEGIN
|
||
-- 获取群组信息
|
||
SELECT * INTO group_info FROM public.ak_message_groups WHERE id = target_group_id;
|
||
|
||
IF NOT FOUND THEN
|
||
RAISE EXCEPTION 'Group not found';
|
||
END IF;
|
||
|
||
user_role := public.get_user_role(auth.uid());
|
||
|
||
-- 检查加入权限
|
||
IF NOT group_info.is_public AND group_info.created_by != auth.uid()
|
||
AND user_role != 'admin' THEN
|
||
-- 非公开群组需要申请
|
||
INSERT INTO public.ak_message_group_members (group_id, user_id, status, joined_at)
|
||
VALUES (target_group_id, auth.uid(), 'pending', NOW())
|
||
ON CONFLICT (group_id, user_id) DO UPDATE SET
|
||
status = 'pending',
|
||
updated_at = NOW();
|
||
RETURN false; -- 返回false表示需要审批
|
||
ELSE
|
||
-- 公开群组或有权限直接加入
|
||
INSERT INTO public.ak_message_group_members (group_id, user_id, status, joined_at)
|
||
VALUES (target_group_id, auth.uid(), 'active', NOW())
|
||
ON CONFLICT (group_id, user_id) DO UPDATE SET
|
||
status = 'active',
|
||
joined_at = NOW(),
|
||
updated_at = NOW();
|
||
RETURN true; -- 返回true表示成功加入
|
||
END IF;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- =====================================================
|
||
-- 10. 权限验证和测试查询
|
||
-- =====================================================
|
||
|
||
-- 创建权限测试函数
|
||
CREATE OR REPLACE FUNCTION public.test_message_permissions(test_user_id UUID)
|
||
RETURNS TABLE(
|
||
test_name TEXT,
|
||
permission_check TEXT,
|
||
result BOOLEAN,
|
||
details TEXT
|
||
) AS $$
|
||
BEGIN
|
||
-- 测试角色获取
|
||
RETURN QUERY SELECT
|
||
'Role Check'::TEXT,
|
||
'get_user_role'::TEXT,
|
||
(public.get_user_role(test_user_id) IS NOT NULL),
|
||
format('User role: %s', public.get_user_role(test_user_id));
|
||
|
||
-- 测试发送消息权限
|
||
RETURN QUERY SELECT
|
||
'Send Message Permission'::TEXT,
|
||
'user_has_permission'::TEXT,
|
||
public.user_has_permission('send_message', test_user_id),
|
||
format('Can send messages: %s', public.user_has_permission('send_message', test_user_id));
|
||
|
||
-- 测试创建群组权限
|
||
RETURN QUERY SELECT
|
||
'Create Group Permission'::TEXT,
|
||
'user_has_permission'::TEXT,
|
||
public.user_has_permission('create_group', test_user_id),
|
||
format('Can create groups: %s', public.user_has_permission('create_group', test_user_id));
|
||
|
||
-- 测试消息访问权限（检查用户能访问的消息数量）
|
||
RETURN QUERY SELECT
|
||
'Message Access Count'::TEXT,
|
||
'message_access'::TEXT,
|
||
true,
|
||
format('Accessible messages: %s', (
|
||
SELECT COUNT(*) FROM public.ak_messages
|
||
WHERE (sender_type = 'user' AND sender_id = test_user_id)
|
||
OR (receiver_type = 'user' AND receiver_id = test_user_id)
|
||
OR receiver_type = 'broadcast'
|
||
));
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- =====================================================
|
||
-- 完成消息和使用示例
|
||
-- =====================================================
|
||
|
||
/*
|
||
-- 测试权限系统
|
||
SELECT * FROM public.test_message_permissions('7bf7378e-a027-473e-97ac-3460ed3f170a');
|
||
SELECT * FROM public.test_message_permissions('eed3824b-bba1-4309-8048-19d17367c084');
|
||
|
||
-- 发送安全消息示例
|
||
SELECT public.send_secure_message(
|
||
(SELECT id FROM public.ak_message_types WHERE type_name = 'announcement' LIMIT 1),
|
||
'user',
|
||
'eed3824b-bba1-4309-8048-19d17367c084',
|
||
'Test Message',
|
||
'This is a test message',
|
||
'{"priority": "normal"}'::jsonb
|
||
);
|
||
|
||
-- 加入群组示例
|
||
SELECT public.join_message_group(
|
||
(SELECT id FROM public.ak_message_groups LIMIT 1),
|
||
'I would like to join this group'
|
||
);
|
||
|
||
-- 检查用户可访问的消息
|
||
SELECT m.id, m.title, m.created_at
|
||
FROM public.ak_messages m
|
||
WHERE (
|
||
-- 用户相关的消息
|
||
(m.sender_type = 'user' AND m.sender_id = auth.uid())
|
||
OR (m.receiver_type = 'user' AND m.receiver_id = auth.uid())
|
||
OR (m.receiver_type = 'broadcast')
|
||
OR (m.receiver_type = 'group' AND m.receiver_id IN (
|
||
SELECT group_id FROM public.ak_message_group_members
|
||
WHERE user_id = auth.uid() AND status = 'active'
|
||
))
|
||
)
|
||
ORDER BY m.created_at DESC
|
||
LIMIT 10;
|
||
*/
|
||
|
||
-- 完成提示
|
||
SELECT
|
||
'✅ 消息系统权限策略配置完成' as status,
|
||
(SELECT COUNT(*) FROM pg_policies WHERE schemaname = 'public'
|
||
AND tablename LIKE 'ak_%') as total_policies,
|
||
'RLS已启用，权限控制已生效' as message;
|