238 lines
7.7 KiB
PL/PgSQL
238 lines
7.7 KiB
PL/PgSQL
-- =====================================================
|
|
-- 快速开放消息系统所有权限（开发模式）
|
|
-- 适用于开发/测试环境的一键权限开放
|
|
-- ⚠️ 生产环境严禁使用！
|
|
-- =====================================================
|
|
|
|
-- 确保所有消息表启用RLS
|
|
ALTER TABLE IF EXISTS public.ak_message_types ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE IF EXISTS public.ak_messages ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE IF EXISTS public.ak_message_recipients ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE IF EXISTS public.ak_message_groups ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE IF EXISTS public.ak_message_group_members ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE IF EXISTS public.ak_message_templates ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE IF EXISTS public.ak_user_message_preferences ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE IF EXISTS public.ak_message_stats ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- =============================================================================
|
|
-- 一键清理所有限制性策略并创建开放策略
|
|
-- =============================================================================
|
|
|
|
DO $$
|
|
DECLARE
|
|
table_names TEXT[] := ARRAY[
|
|
'ak_message_types', 'ak_messages', 'ak_message_recipients',
|
|
'ak_message_groups', 'ak_message_group_members', 'ak_message_templates',
|
|
'ak_user_message_preferences', 'ak_message_stats'
|
|
];
|
|
table_name TEXT;
|
|
r RECORD;
|
|
BEGIN
|
|
RAISE NOTICE '🔓 开始设置开发权限模式...';
|
|
|
|
-- 清理现有策略
|
|
FOREACH table_name IN ARRAY table_names LOOP
|
|
-- 删除该表的所有策略
|
|
FOR r IN
|
|
SELECT policyname
|
|
FROM pg_policies
|
|
WHERE schemaname = 'public' AND tablename = table_name
|
|
LOOP
|
|
EXECUTE format('DROP POLICY IF EXISTS %I ON public.%I', r.policyname, table_name);
|
|
END LOOP;
|
|
|
|
-- 创建开放策略
|
|
EXECUTE format('
|
|
CREATE POLICY "dev_open_access_%s" ON public.%I
|
|
FOR ALL USING (auth.role() = ''authenticated'')
|
|
WITH CHECK (auth.role() = ''authenticated'')
|
|
', replace(table_name, 'ak_', ''), table_name);
|
|
|
|
RAISE NOTICE '✅ 已为表 % 设置开放权限', table_name;
|
|
END LOOP;
|
|
|
|
RAISE NOTICE '🎉 开发权限模式设置完成！';
|
|
RAISE NOTICE '⚠️ 所有已认证用户现在可以完全操作消息系统';
|
|
RAISE NOTICE '📝 记住：这仅适用于开发/测试环境！';
|
|
END $$;
|
|
|
|
-- =============================================================================
|
|
-- 创建开发便捷函数
|
|
-- =============================================================================
|
|
|
|
-- 快速发送消息
|
|
CREATE OR REPLACE FUNCTION public.dev_send_message(
|
|
p_title TEXT,
|
|
p_content TEXT,
|
|
p_to_user_id UUID,
|
|
p_type_name TEXT DEFAULT 'notification'
|
|
)
|
|
RETURNS UUID
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_message_id UUID;
|
|
v_type_id UUID;
|
|
v_sender_id UUID := auth.uid();
|
|
BEGIN
|
|
-- 获取或创建消息类型
|
|
SELECT id INTO v_type_id
|
|
FROM public.ak_message_types
|
|
WHERE type_name = p_type_name;
|
|
|
|
IF v_type_id IS NULL THEN
|
|
INSERT INTO public.ak_message_types (type_name, display_name, is_active)
|
|
VALUES (p_type_name, initcap(p_type_name), true)
|
|
RETURNING id INTO v_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 (
|
|
v_type_id, 'user', v_sender_id, 'user', p_to_user_id,
|
|
p_title, p_content, 'sent', v_sender_id
|
|
) RETURNING id INTO v_message_id;
|
|
|
|
-- 创建接收记录
|
|
INSERT INTO public.ak_message_recipients (
|
|
message_id, user_id, status, created_by
|
|
) VALUES (
|
|
v_message_id, p_to_user_id, 'unread', v_sender_id
|
|
);
|
|
|
|
RETURN v_message_id;
|
|
END $$;
|
|
|
|
-- 快速创建群组
|
|
CREATE OR REPLACE FUNCTION public.dev_create_group(
|
|
p_name TEXT,
|
|
p_description TEXT DEFAULT '',
|
|
p_is_public BOOLEAN DEFAULT true
|
|
)
|
|
RETURNS UUID
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_group_id UUID;
|
|
v_creator_id UUID := auth.uid();
|
|
BEGIN
|
|
INSERT INTO public.ak_message_groups (
|
|
name, description, is_public, status, created_by
|
|
) VALUES (
|
|
p_name, p_description, p_is_public, 'active', v_creator_id
|
|
) RETURNING id INTO v_group_id;
|
|
|
|
-- 添加创建者为群主
|
|
INSERT INTO public.ak_message_group_members (
|
|
group_id, user_id, role, status, joined_at, created_by
|
|
) VALUES (
|
|
v_group_id, v_creator_id, 'owner', 'active', NOW(), v_creator_id
|
|
);
|
|
|
|
RETURN v_group_id;
|
|
END $$;
|
|
|
|
-- 快速添加群组成员
|
|
CREATE OR REPLACE FUNCTION public.dev_add_to_group(
|
|
p_group_id UUID,
|
|
p_user_id UUID,
|
|
p_role TEXT DEFAULT 'member'
|
|
)
|
|
RETURNS BOOLEAN
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
INSERT INTO public.ak_message_group_members (
|
|
group_id, user_id, role, status, joined_at, created_by
|
|
) VALUES (
|
|
p_group_id, p_user_id, p_role, 'active', NOW(), auth.uid()
|
|
) ON CONFLICT (group_id, user_id) DO UPDATE SET
|
|
status = 'active',
|
|
role = EXCLUDED.role,
|
|
joined_at = NOW();
|
|
|
|
RETURN true;
|
|
END $$;
|
|
|
|
-- 开发数据查看函数
|
|
CREATE OR REPLACE FUNCTION public.dev_show_my_messages()
|
|
RETURNS TABLE (
|
|
message_id UUID,
|
|
title TEXT,
|
|
content TEXT,
|
|
sender_name TEXT,
|
|
created_at TIMESTAMPTZ,
|
|
status TEXT
|
|
)
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
m.id,
|
|
m.title,
|
|
m.content,
|
|
COALESCE('User-' || m.sender_id::TEXT, 'System') as sender_name,
|
|
m.created_at,
|
|
COALESCE(mr.status, m.status) as message_status
|
|
FROM public.ak_messages m
|
|
LEFT JOIN public.ak_message_recipients mr ON m.id = mr.message_id AND mr.user_id = auth.uid()
|
|
WHERE m.receiver_id = auth.uid() OR mr.user_id = auth.uid()
|
|
ORDER BY m.created_at DESC;
|
|
END $$;
|
|
|
|
-- =============================================================================
|
|
-- 验证开发模式设置
|
|
-- =============================================================================
|
|
|
|
DO $$
|
|
DECLARE
|
|
policy_count INTEGER;
|
|
function_count INTEGER;
|
|
BEGIN
|
|
-- 检查策略
|
|
SELECT COUNT(*) INTO policy_count
|
|
FROM pg_policies
|
|
WHERE schemaname = 'public'
|
|
AND policyname LIKE 'dev_open_access_%';
|
|
|
|
-- 检查函数
|
|
SELECT COUNT(*) INTO function_count
|
|
FROM information_schema.routines
|
|
WHERE routine_schema = 'public'
|
|
AND routine_name LIKE 'dev_%';
|
|
|
|
RAISE NOTICE '📊 开发模式统计:';
|
|
RAISE NOTICE ' - 开放策略: % 个', policy_count;
|
|
RAISE NOTICE ' - 便捷函数: % 个', function_count;
|
|
|
|
IF policy_count >= 8 THEN
|
|
RAISE NOTICE '🎯 开发权限模式已成功启用！';
|
|
RAISE NOTICE '';
|
|
RAISE NOTICE '🛠️ 可用的开发函数：';
|
|
RAISE NOTICE ' • dev_send_message(标题, 内容, 接收者ID, 类型)';
|
|
RAISE NOTICE ' • dev_create_group(群组名, 描述, 是否公开)';
|
|
RAISE NOTICE ' • dev_add_to_group(群组ID, 用户ID, 角色)';
|
|
RAISE NOTICE ' • dev_show_my_messages() - 查看我的消息';
|
|
RAISE NOTICE '';
|
|
RAISE NOTICE '📝 使用示例：';
|
|
RAISE NOTICE ' SELECT dev_send_message(''测试'', ''内容'', ''user-id'');';
|
|
RAISE NOTICE ' SELECT * FROM dev_show_my_messages();';
|
|
ELSE
|
|
RAISE NOTICE '⚠️ 策略设置可能不完整，请检查';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 完成提示
|
|
SELECT
|
|
'🔓 开发权限模式' as mode,
|
|
'已启用' as status,
|
|
'所有authenticated用户可操作消息系统' as description,
|
|
'⚠️ 仅用于开发/测试环境' as warning;
|