68 lines
2.1 KiB
SQL
68 lines
2.1 KiB
SQL
-- =====================================================
|
|
-- 快速修复策略冲突
|
|
-- 解决 "policy already exists" 错误
|
|
-- =====================================================
|
|
|
|
-- 清理 user_roles 表的所有可能策略
|
|
DO $$
|
|
DECLARE
|
|
r RECORD;
|
|
BEGIN
|
|
-- 删除 user_roles 表的所有策略
|
|
FOR r IN
|
|
SELECT policyname
|
|
FROM pg_policies
|
|
WHERE schemaname = 'public'
|
|
AND tablename = 'user_roles'
|
|
LOOP
|
|
EXECUTE format('DROP POLICY IF EXISTS %I ON public.user_roles', r.policyname);
|
|
END LOOP;
|
|
|
|
RAISE NOTICE '✅ 已清理 user_roles 表的所有策略';
|
|
END $$;
|
|
|
|
-- 清理所有消息相关表的策略（如果需要）
|
|
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 $$;
|
|
|
|
-- 验证策略清理结果
|
|
DO $$
|
|
DECLARE
|
|
policy_count INTEGER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO policy_count
|
|
FROM pg_policies
|
|
WHERE schemaname = 'public'
|
|
AND tablename IN ('user_roles', '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');
|
|
|
|
IF policy_count = 0 THEN
|
|
RAISE NOTICE '🎉 策略清理完成: 所有冲突策略已删除';
|
|
ELSE
|
|
RAISE NOTICE '⚠️ 仍有 % 个策略存在，可能需要手动检查', policy_count;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 完成消息
|
|
SELECT
|
|
'✅ 策略冲突已解决' as status,
|
|
'现在可以重新运行您的SQL脚本' as message;
|