306 lines
10 KiB
PL/PgSQL
306 lines
10 KiB
PL/PgSQL
-- =====================================================
|
|
-- 消息系统权限模式管理器
|
|
-- 在开发模式和生产模式之间快速切换
|
|
-- =====================================================
|
|
|
|
-- 创建权限模式管理函数
|
|
CREATE OR REPLACE FUNCTION public.get_permission_mode()
|
|
RETURNS TABLE (
|
|
mode TEXT,
|
|
description TEXT,
|
|
dev_policies INTEGER,
|
|
normal_policies INTEGER,
|
|
dev_functions INTEGER
|
|
)
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_dev_policies INTEGER;
|
|
v_normal_policies INTEGER;
|
|
v_dev_functions INTEGER;
|
|
v_mode TEXT;
|
|
v_description TEXT;
|
|
BEGIN
|
|
-- 统计开发模式策略
|
|
SELECT COUNT(*) INTO v_dev_policies
|
|
FROM pg_policies
|
|
WHERE schemaname = 'public'
|
|
AND (policyname LIKE 'dev_%' OR policyname LIKE 'authenticated_full_%');
|
|
|
|
-- 统计正常模式策略
|
|
SELECT COUNT(*) INTO v_normal_policies
|
|
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')
|
|
AND NOT (policyname LIKE 'dev_%' OR policyname LIKE 'authenticated_full_%');
|
|
|
|
-- 统计开发函数
|
|
SELECT COUNT(*) INTO v_dev_functions
|
|
FROM information_schema.routines
|
|
WHERE routine_schema = 'public'
|
|
AND routine_name LIKE 'dev_%';
|
|
|
|
-- 判断当前模式
|
|
IF v_dev_policies > 0 THEN
|
|
v_mode := '🔓 开发模式';
|
|
v_description := '所有authenticated用户拥有完全权限';
|
|
ELSIF v_normal_policies > 0 THEN
|
|
v_mode := '🔒 生产模式';
|
|
v_description := '基于角色的权限控制';
|
|
ELSE
|
|
v_mode := '❓ 未知模式';
|
|
v_description := '权限策略可能未正确设置';
|
|
END IF;
|
|
|
|
RETURN QUERY SELECT
|
|
v_mode,
|
|
v_description,
|
|
v_dev_policies,
|
|
v_normal_policies,
|
|
v_dev_functions;
|
|
END $$;
|
|
|
|
-- 权限策略详情查看函数
|
|
CREATE OR REPLACE FUNCTION public.list_message_policies()
|
|
RETURNS TABLE (
|
|
table_name TEXT,
|
|
policy_name TEXT,
|
|
policy_type TEXT,
|
|
policy_mode TEXT
|
|
)
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
p.tablename::TEXT,
|
|
p.policyname::TEXT,
|
|
CASE
|
|
WHEN p.cmd = 'r' THEN 'SELECT'
|
|
WHEN p.cmd = 'a' THEN 'INSERT'
|
|
WHEN p.cmd = 'w' THEN 'UPDATE'
|
|
WHEN p.cmd = 'd' THEN 'DELETE'
|
|
WHEN p.cmd = '*' THEN 'ALL'
|
|
ELSE p.cmd::TEXT
|
|
END as policy_type,
|
|
CASE
|
|
WHEN p.policyname LIKE 'dev_%' OR p.policyname LIKE 'authenticated_full_%' THEN '🔓 开发'
|
|
ELSE '🔒 生产'
|
|
END as policy_mode
|
|
FROM pg_policies p
|
|
WHERE p.schemaname = 'public'
|
|
AND p.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')
|
|
ORDER BY p.tablename, p.policyname;
|
|
END $$;
|
|
|
|
-- 快速切换到开发模式
|
|
CREATE OR REPLACE FUNCTION public.switch_to_dev_mode()
|
|
RETURNS TEXT
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
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;
|
|
policy_count INTEGER := 0;
|
|
BEGIN
|
|
-- 清理现有策略
|
|
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);
|
|
|
|
policy_count := policy_count + 1;
|
|
END LOOP;
|
|
|
|
RETURN format('🔓 已切换到开发模式，创建了 %s 个开放策略', policy_count);
|
|
END $$;
|
|
|
|
-- 快速切换到生产模式
|
|
CREATE OR REPLACE FUNCTION public.switch_to_prod_mode()
|
|
RETURNS TEXT
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
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;
|
|
deleted_count INTEGER := 0;
|
|
BEGIN
|
|
-- 删除所有开发模式策略
|
|
FOREACH table_name IN ARRAY table_names LOOP
|
|
FOR r IN
|
|
SELECT policyname
|
|
FROM pg_policies
|
|
WHERE schemaname = 'public'
|
|
AND tablename = table_name
|
|
AND (policyname LIKE 'dev_%' OR policyname LIKE 'authenticated_full_%')
|
|
LOOP
|
|
EXECUTE format('DROP POLICY IF EXISTS %I ON public.%I', r.policyname, table_name);
|
|
deleted_count := deleted_count + 1;
|
|
END LOOP;
|
|
END LOOP;
|
|
|
|
-- 这里应该运行完整的生产权限策略创建
|
|
-- 为了简化，我们只清理开发策略
|
|
-- 实际部署时需要运行 restore_normal_permissions.sql
|
|
|
|
RETURN format('🔒 已清理 %s 个开发策略，请运行 restore_normal_permissions.sql 完成生产模式设置', deleted_count);
|
|
END $$;
|
|
|
|
-- 权限健康检查函数
|
|
CREATE OR REPLACE FUNCTION public.check_permission_health()
|
|
RETURNS TABLE (
|
|
check_item TEXT,
|
|
status TEXT,
|
|
details TEXT,
|
|
recommendation TEXT
|
|
)
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_rls_enabled INTEGER;
|
|
v_auth_functions INTEGER;
|
|
v_role_functions INTEGER;
|
|
v_conflicting_policies INTEGER;
|
|
BEGIN
|
|
-- 检查RLS是否启用
|
|
SELECT COUNT(*) INTO v_rls_enabled
|
|
FROM pg_class c
|
|
JOIN pg_namespace n ON c.relnamespace = n.oid
|
|
WHERE n.nspname = 'public'
|
|
AND c.relname 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')
|
|
AND c.relrowsecurity = true;
|
|
|
|
-- 检查认证相关函数
|
|
SELECT COUNT(*) INTO v_auth_functions
|
|
FROM information_schema.routines
|
|
WHERE routine_schema = 'public'
|
|
AND routine_name IN ('get_user_role', 'is_group_member');
|
|
|
|
-- 检查用户角色函数
|
|
SELECT COUNT(*) INTO v_role_functions
|
|
FROM information_schema.routines
|
|
WHERE routine_schema = 'public'
|
|
AND routine_name LIKE '%role%';
|
|
|
|
-- 检查冲突策略
|
|
SELECT COUNT(*) INTO v_conflicting_policies
|
|
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')
|
|
GROUP BY tablename, cmd
|
|
HAVING COUNT(*) > 3; -- 如果同一表同一操作有超过3个策略可能有问题
|
|
|
|
-- RLS启用检查
|
|
RETURN QUERY SELECT
|
|
'RLS启用状态'::TEXT,
|
|
CASE WHEN v_rls_enabled >= 8 THEN '✅ 正常' ELSE '⚠️ 异常' END,
|
|
format('%s/8 个表启用了RLS', v_rls_enabled),
|
|
CASE WHEN v_rls_enabled < 8 THEN '运行完整部署脚本启用RLS' ELSE '无需操作' END;
|
|
|
|
-- 认证函数检查
|
|
RETURN QUERY SELECT
|
|
'认证函数'::TEXT,
|
|
CASE WHEN v_auth_functions >= 2 THEN '✅ 正常' ELSE '⚠️ 缺失' END,
|
|
format('找到 %s 个认证相关函数', v_auth_functions),
|
|
CASE WHEN v_auth_functions < 2 THEN '运行 supabase_auth_complete_setup.sql' ELSE '无需操作' END;
|
|
|
|
-- 策略冲突检查
|
|
RETURN QUERY SELECT
|
|
'策略冲突'::TEXT,
|
|
CASE WHEN v_conflicting_policies = 0 THEN '✅ 正常' ELSE '⚠️ 可能存在' END,
|
|
format('发现 %s 个潜在冲突', v_conflicting_policies),
|
|
CASE WHEN v_conflicting_policies > 0 THEN '检查并清理重复策略' ELSE '无需操作' END;
|
|
|
|
END $$;
|
|
|
|
-- =============================================================================
|
|
-- 使用示例和说明
|
|
-- =============================================================================
|
|
|
|
/*
|
|
-- 使用示例：
|
|
|
|
-- 1. 查看当前权限模式
|
|
SELECT * FROM public.get_permission_mode();
|
|
|
|
-- 2. 查看所有权限策略
|
|
SELECT * FROM public.list_message_policies();
|
|
|
|
-- 3. 切换到开发模式
|
|
SELECT public.switch_to_dev_mode();
|
|
|
|
-- 4. 切换到生产模式（仅清理，需要手动运行完整脚本）
|
|
SELECT public.switch_to_prod_mode();
|
|
|
|
-- 5. 权限健康检查
|
|
SELECT * FROM public.check_permission_health();
|
|
|
|
-- 6. 快速检查当前状态
|
|
SELECT
|
|
mode,
|
|
description,
|
|
CASE
|
|
WHEN dev_policies > 0 THEN '⚠️ 开发环境权限'
|
|
WHEN normal_policies > 0 THEN '✅ 生产环境权限'
|
|
ELSE '❌ 权限未配置'
|
|
END as security_level
|
|
FROM public.get_permission_mode();
|
|
*/
|
|
|
|
-- 创建权限管理视图（只读）
|
|
CREATE OR REPLACE VIEW public.permission_dashboard AS
|
|
SELECT
|
|
pm.mode,
|
|
pm.description,
|
|
pm.dev_policies,
|
|
pm.normal_policies,
|
|
pm.dev_functions,
|
|
CASE
|
|
WHEN pm.dev_policies > 0 THEN '⚠️ 开发模式 - 权限开放'
|
|
WHEN pm.normal_policies > 0 THEN '✅ 生产模式 - 权限受控'
|
|
ELSE '❌ 未知模式 - 需要检查'
|
|
END as security_status,
|
|
NOW() as checked_at
|
|
FROM public.get_permission_mode() pm;
|
|
|
|
-- 完成提示
|
|
SELECT
|
|
'🛠️ 权限管理器' as component,
|
|
'已创建' as status,
|
|
'get_permission_mode(), switch_to_dev_mode(), switch_to_prod_mode()' as functions,
|
|
'permission_dashboard 视图可查看实时状态' as info;
|