270 lines
10 KiB
PL/PgSQL
270 lines
10 KiB
PL/PgSQL
-- =====================================================
|
||
-- 开放所有消息系统权限给已认证用户
|
||
-- 适用于开发/测试环境，允许所有登录用户操作消息系统
|
||
-- ⚠️ 生产环境请谨慎使用！
|
||
-- =====================================================
|
||
|
||
-- 清理现有的限制性策略
|
||
DO $$
|
||
DECLARE
|
||
r RECORD;
|
||
BEGIN
|
||
RAISE NOTICE '🧹 清理现有限制性策略';
|
||
|
||
-- 删除所有消息相关表的策略
|
||
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);
|
||
RAISE NOTICE ' 删除策略: %.%', r.tablename, r.policyname;
|
||
END LOOP;
|
||
|
||
RAISE NOTICE '✅ 策略清理完成';
|
||
END $$;
|
||
|
||
-- 确保RLS仍然启用（但策略将非常宽松）
|
||
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. 消息类型权限策略 - 所有已认证用户可以完全操作
|
||
CREATE POLICY "authenticated_full_access_message_types" ON public.ak_message_types
|
||
FOR ALL USING (auth.role() = 'authenticated')
|
||
WITH CHECK (auth.role() = 'authenticated');
|
||
|
||
-- 2. 消息主表权限策略 - 所有已认证用户可以完全操作
|
||
CREATE POLICY "authenticated_full_access_messages" ON public.ak_messages
|
||
FOR ALL USING (auth.role() = 'authenticated')
|
||
WITH CHECK (auth.role() = 'authenticated');
|
||
|
||
-- 3. 消息接收者权限策略 - 所有已认证用户可以完全操作
|
||
CREATE POLICY "authenticated_full_access_recipients" ON public.ak_message_recipients
|
||
FOR ALL USING (auth.role() = 'authenticated')
|
||
WITH CHECK (auth.role() = 'authenticated');
|
||
|
||
-- 4. 消息群组权限策略 - 所有已认证用户可以完全操作
|
||
CREATE POLICY "authenticated_full_access_groups" ON public.ak_message_groups
|
||
FOR ALL USING (auth.role() = 'authenticated')
|
||
WITH CHECK (auth.role() = 'authenticated');
|
||
|
||
-- 5. 群组成员权限策略 - 所有已认证用户可以完全操作
|
||
CREATE POLICY "authenticated_full_access_group_members" ON public.ak_message_group_members
|
||
FOR ALL USING (auth.role() = 'authenticated')
|
||
WITH CHECK (auth.role() = 'authenticated');
|
||
|
||
-- 6. 消息模板权限策略 - 所有已认证用户可以完全操作
|
||
CREATE POLICY "authenticated_full_access_templates" ON public.ak_message_templates
|
||
FOR ALL USING (auth.role() = 'authenticated')
|
||
WITH CHECK (auth.role() = 'authenticated');
|
||
|
||
-- 7. 用户偏好权限策略 - 所有已认证用户可以完全操作
|
||
CREATE POLICY "authenticated_full_access_preferences" ON public.ak_user_message_preferences
|
||
FOR ALL USING (auth.role() = 'authenticated')
|
||
WITH CHECK (auth.role() = 'authenticated');
|
||
|
||
-- 8. 消息统计权限策略 - 所有已认证用户可以完全操作
|
||
CREATE POLICY "authenticated_full_access_stats" ON public.ak_message_stats
|
||
FOR ALL USING (auth.role() = 'authenticated')
|
||
WITH CHECK (auth.role() = 'authenticated');
|
||
|
||
-- =============================================================================
|
||
-- 创建便捷的操作函数（可选）
|
||
-- =============================================================================
|
||
|
||
-- 快速发送消息函数（简化版）
|
||
CREATE OR REPLACE FUNCTION public.send_message_simple(
|
||
message_type_name TEXT,
|
||
receiver_type TEXT,
|
||
receiver_id UUID,
|
||
title TEXT,
|
||
content TEXT,
|
||
sender_id UUID DEFAULT auth.uid()
|
||
)
|
||
RETURNS UUID AS $$
|
||
DECLARE
|
||
new_message_id UUID;
|
||
type_id UUID;
|
||
BEGIN
|
||
-- 获取消息类型ID
|
||
SELECT id INTO type_id FROM public.ak_message_types
|
||
WHERE type_name = message_type_name LIMIT 1;
|
||
|
||
IF type_id IS NULL THEN
|
||
-- 如果类型不存在，创建一个
|
||
INSERT INTO public.ak_message_types (type_name, display_name, is_active)
|
||
VALUES (message_type_name, message_type_name, true)
|
||
RETURNING id INTO type_id;
|
||
END IF;
|
||
|
||
-- 插入消息
|
||
INSERT INTO public.ak_messages (
|
||
message_type_id, sender_type, sender_id, receiver_type, receiver_id,
|
||
title, content, status, created_by
|
||
) VALUES (
|
||
type_id, 'user', sender_id, receiver_type, receiver_id,
|
||
title, content, 'sent', sender_id
|
||
) RETURNING id INTO new_message_id;
|
||
|
||
-- 如果是单用户消息，自动创建接收记录
|
||
IF receiver_type = 'user' THEN
|
||
INSERT INTO public.ak_message_recipients (
|
||
message_id, user_id, status, created_by
|
||
) VALUES (
|
||
new_message_id, receiver_id, 'unread', sender_id
|
||
);
|
||
END IF;
|
||
|
||
RETURN new_message_id;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 快速创建群组函数
|
||
CREATE OR REPLACE FUNCTION public.create_group_simple(
|
||
group_name TEXT,
|
||
description TEXT DEFAULT '',
|
||
is_public BOOLEAN DEFAULT true,
|
||
creator_id UUID DEFAULT auth.uid()
|
||
)
|
||
RETURNS UUID AS $$
|
||
DECLARE
|
||
new_group_id UUID;
|
||
BEGIN
|
||
-- 创建群组
|
||
INSERT INTO public.ak_message_groups (
|
||
name, description, is_public, status, created_by
|
||
) VALUES (
|
||
group_name, description, is_public, 'active', creator_id
|
||
) RETURNING id INTO new_group_id;
|
||
|
||
-- 添加创建者为群组成员
|
||
INSERT INTO public.ak_message_group_members (
|
||
group_id, user_id, role, status, joined_at, created_by
|
||
) VALUES (
|
||
new_group_id, creator_id, 'owner', 'active', NOW(), creator_id
|
||
);
|
||
|
||
RETURN new_group_id;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 快速加入群组函数
|
||
CREATE OR REPLACE FUNCTION public.join_group_simple(
|
||
group_id UUID,
|
||
user_id UUID DEFAULT auth.uid()
|
||
)
|
||
RETURNS BOOLEAN AS $$
|
||
BEGIN
|
||
-- 直接加入群组（无需审批）
|
||
INSERT INTO public.ak_message_group_members (
|
||
group_id, user_id, role, status, joined_at, created_by
|
||
) VALUES (
|
||
group_id, user_id, 'member', 'active', NOW(), user_id
|
||
) ON CONFLICT (group_id, user_id) DO UPDATE SET
|
||
status = 'active',
|
||
joined_at = NOW();
|
||
|
||
RETURN true;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- =============================================================================
|
||
-- 验证和测试
|
||
-- =============================================================================
|
||
|
||
-- 验证策略创建结果
|
||
DO $$
|
||
DECLARE
|
||
policy_count INTEGER;
|
||
function_count INTEGER;
|
||
BEGIN
|
||
RAISE NOTICE '🔍 验证开放权限策略';
|
||
|
||
-- 检查策略数量
|
||
SELECT COUNT(*) INTO policy_count
|
||
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');
|
||
|
||
-- 检查便捷函数
|
||
SELECT COUNT(*) INTO function_count
|
||
FROM information_schema.routines
|
||
WHERE routine_schema = 'public'
|
||
AND routine_name IN ('send_message_simple', 'create_group_simple', 'join_group_simple');
|
||
|
||
RAISE NOTICE '📊 开放权限统计:';
|
||
RAISE NOTICE ' - 开放策略数量: %', policy_count;
|
||
RAISE NOTICE ' - 便捷函数数量: %', function_count;
|
||
|
||
IF policy_count >= 8 THEN
|
||
RAISE NOTICE '🎉 所有已认证用户现在可以完全操作消息系统！';
|
||
RAISE NOTICE '⚠️ 注意：这是非常宽松的权限设置，仅适用于开发/测试环境';
|
||
ELSE
|
||
RAISE NOTICE '⚠️ 策略创建可能不完整，请检查';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- =============================================================================
|
||
-- 使用示例和说明
|
||
-- =============================================================================
|
||
|
||
/*
|
||
-- 使用示例：
|
||
|
||
-- 1. 发送简单消息
|
||
SELECT public.send_message_simple(
|
||
'notification', -- 消息类型
|
||
'user', -- 接收者类型
|
||
'eed3824b-bba1-4309-8048-19d17367c084', -- 接收者ID
|
||
'测试消息', -- 标题
|
||
'这是一条测试消息内容' -- 内容
|
||
);
|
||
|
||
-- 2. 创建群组
|
||
SELECT public.create_group_simple(
|
||
'测试群组', -- 群组名称
|
||
'这是一个测试群组', -- 描述
|
||
true -- 是否公开
|
||
);
|
||
|
||
-- 3. 加入群组
|
||
SELECT public.join_group_simple('group-id-here');
|
||
|
||
-- 4. 直接查询所有数据（现在允许）
|
||
SELECT * FROM public.ak_messages ORDER BY created_at DESC LIMIT 10;
|
||
SELECT * FROM public.ak_message_groups WHERE is_public = true;
|
||
SELECT * FROM public.ak_message_types;
|
||
|
||
-- 5. 直接插入数据（现在允许）
|
||
INSERT INTO public.ak_message_types (type_name, display_name, description)
|
||
VALUES ('custom', '自定义类型', '用户创建的自定义消息类型');
|
||
|
||
-- 6. 直接更新数据（现在允许）
|
||
UPDATE public.ak_messages SET status = 'read' WHERE id = 'message-id-here';
|
||
|
||
-- 7. 直接删除数据（现在允许）
|
||
DELETE FROM public.ak_message_templates WHERE created_by = auth.uid();
|
||
*/
|
||
|
||
-- 完成消息
|
||
SELECT
|
||
'🔓 消息系统权限已全面开放' as status,
|
||
'所有已认证用户现在可以操作所有消息系统功能' as message,
|
||
'⚠️ 仅适用于开发/测试环境' as warning;
|