119 lines
3.6 KiB
SQL
119 lines
3.6 KiB
SQL
-- =====================================================
|
||
-- 快速修复视图策略错误
|
||
-- 解决 "is not a table" 错误
|
||
-- =====================================================
|
||
|
||
-- 删除可能错误创建在视图上的策略
|
||
DO $$
|
||
DECLARE
|
||
r RECORD;
|
||
BEGIN
|
||
RAISE NOTICE '🧹 检查并删除视图上的错误策略';
|
||
|
||
-- 尝试删除可能错误创建在视图上的策略
|
||
-- 注意：这些命令可能会失败，但不会影响系统
|
||
BEGIN
|
||
DROP POLICY IF EXISTS "Users can view role info with restrictions" ON public.user_roles_with_email;
|
||
RAISE NOTICE ' 删除了视图策略: user_roles_with_email';
|
||
EXCEPTION WHEN OTHERS THEN
|
||
RAISE NOTICE ' 视图策略不存在或已删除: user_roles_with_email';
|
||
END;
|
||
|
||
-- 检查其他可能的视图策略
|
||
FOR r IN
|
||
SELECT schemaname, tablename, policyname
|
||
FROM pg_policies
|
||
WHERE schemaname = 'public'
|
||
AND tablename IN (
|
||
SELECT table_name
|
||
FROM information_schema.views
|
||
WHERE table_schema = 'public'
|
||
)
|
||
LOOP
|
||
BEGIN
|
||
EXECUTE format('DROP POLICY IF EXISTS %I ON %I.%I',
|
||
r.policyname, r.schemaname, r.tablename);
|
||
RAISE NOTICE ' 删除了视图策略: %.%', r.tablename, r.policyname;
|
||
EXCEPTION WHEN OTHERS THEN
|
||
RAISE NOTICE ' 无法删除策略: %.%', r.tablename, r.policyname;
|
||
END;
|
||
END LOOP;
|
||
|
||
RAISE NOTICE '✅ 视图策略清理完成';
|
||
END $$;
|
||
|
||
-- 确保视图存在且正确
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '🔍 检查视图状态';
|
||
|
||
-- 重新创建视图（如果需要）
|
||
DROP VIEW IF EXISTS public.user_roles_with_email CASCADE;
|
||
|
||
CREATE OR REPLACE VIEW public.user_roles_with_email AS
|
||
SELECT
|
||
ur.id,
|
||
ur.user_id,
|
||
ur.role,
|
||
ur.class_id,
|
||
ur.school_id,
|
||
ur.created_at,
|
||
ur.updated_at,
|
||
au.email,
|
||
au.raw_user_meta_data->>'user_role' as metadata_role,
|
||
CASE
|
||
WHEN ur.role = au.raw_user_meta_data->>'user_role' THEN true
|
||
ELSE false
|
||
END as role_synced
|
||
FROM public.user_roles ur
|
||
LEFT JOIN auth.users au ON au.id = ur.user_id;
|
||
|
||
RAISE NOTICE '✅ 视图重建完成';
|
||
END $$;
|
||
|
||
-- 验证系统状态
|
||
DO $$
|
||
DECLARE
|
||
view_count INTEGER;
|
||
table_count INTEGER;
|
||
policy_count INTEGER;
|
||
BEGIN
|
||
RAISE NOTICE '🔍 验证系统状态';
|
||
|
||
-- 检查视图
|
||
SELECT COUNT(*) INTO view_count
|
||
FROM information_schema.views
|
||
WHERE table_schema = 'public'
|
||
AND table_name = 'user_roles_with_email';
|
||
|
||
-- 检查表
|
||
SELECT COUNT(*) INTO table_count
|
||
FROM information_schema.tables
|
||
WHERE table_schema = 'public'
|
||
AND table_name = 'user_roles';
|
||
|
||
-- 检查策略（应该只在表上，不在视图上）
|
||
SELECT COUNT(*) INTO policy_count
|
||
FROM pg_policies
|
||
WHERE schemaname = 'public'
|
||
AND tablename = 'user_roles';
|
||
|
||
RAISE NOTICE '📊 系统状态:';
|
||
RAISE NOTICE ' - user_roles_with_email 视图: %',
|
||
CASE WHEN view_count > 0 THEN '存在' ELSE '不存在' END;
|
||
RAISE NOTICE ' - user_roles 表: %',
|
||
CASE WHEN table_count > 0 THEN '存在' ELSE '不存在' END;
|
||
RAISE NOTICE ' - user_roles 表策略数量: %', policy_count;
|
||
|
||
IF view_count > 0 AND table_count > 0 THEN
|
||
RAISE NOTICE '🎉 系统状态正常！';
|
||
ELSE
|
||
RAISE NOTICE '⚠️ 系统可能需要重新初始化';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 完成消息
|
||
SELECT
|
||
'✅ 视图策略错误已修复' as status,
|
||
'视图不支持RLS策略，权限由底层表控制' as message;
|