55 lines
1.9 KiB
PL/PgSQL
55 lines
1.9 KiB
PL/PgSQL
-- =====================================================
|
|
-- 快速修复RLS递归错误
|
|
-- 立即解决 "infinite recursion detected" 错误
|
|
-- =====================================================
|
|
|
|
-- 删除导致递归的策略
|
|
DROP POLICY IF EXISTS "Group members can view other members" ON public.ak_message_group_members;
|
|
DROP POLICY IF EXISTS "Users can view their own messages" ON public.ak_messages;
|
|
|
|
-- 创建辅助函数避免递归
|
|
CREATE OR REPLACE FUNCTION public.is_group_member(group_id UUID, user_id UUID)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1 FROM public.ak_message_group_members
|
|
WHERE ak_message_group_members.group_id = is_group_member.group_id
|
|
AND ak_message_group_members.user_id = is_group_member.user_id
|
|
AND status = 'active'
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- 重新创建安全的策略
|
|
CREATE POLICY "users_can_view_own_membership" ON public.ak_message_group_members
|
|
FOR SELECT USING (
|
|
-- 用户可以查看自己的成员记录
|
|
user_id = auth.uid()
|
|
OR
|
|
-- 管理员可以查看所有成员
|
|
auth.jwt() ->> 'user_role' = 'admin'
|
|
);
|
|
|
|
CREATE POLICY "users_can_view_relevant_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 = 'broadcast')
|
|
OR
|
|
-- 群组消息（使用函数检查，避免递归）
|
|
(receiver_type = 'group' AND public.is_group_member(receiver_id, auth.uid()))
|
|
OR
|
|
-- 管理员可以查看所有消息
|
|
auth.jwt() ->> 'user_role' = 'admin'
|
|
);
|
|
|
|
-- 完成消息
|
|
SELECT
|
|
'✅ RLS递归问题已修复' as status,
|
|
'现在可以安全访问消息和群组' as message;
|