
-- P0-1: 修复 get_current_user_role 硬编码 'admin' 安全漏洞
-- 改为基于 user_roles 表（SSOT）真实判断
CREATE OR REPLACE FUNCTION public.get_current_user_role()
RETURNS text
LANGUAGE plpgsql
STABLE SECURITY DEFINER
SET search_path TO 'public'
AS $$
BEGIN
  IF auth.uid() IS NULL THEN
    RETURN 'anon';
  END IF;
  IF EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = auth.uid() AND role = 'super_admin') THEN
    RETURN 'super_admin';
  END IF;
  IF EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = auth.uid() AND role = 'admin') THEN
    RETURN 'admin';
  END IF;
  RETURN 'user';
END;
$$;

-- 同步修正 _secure 版本：之前从 profiles.role 读取（用户可改，不安全），改走 user_roles
CREATE OR REPLACE FUNCTION public.get_current_user_role_secure()
RETURNS text
LANGUAGE plpgsql
STABLE SECURITY DEFINER
SET search_path TO 'public'
AS $$
BEGIN
  RETURN public.get_current_user_role();
END;
$$;

-- P0-2: 修复 profiles 选择策略中 "(auth.uid() IS NOT NULL AND 1=1)" 导致任意登录用户可读全表的漏洞
DROP POLICY IF EXISTS profiles_secure_select_policy ON public.profiles;

CREATE POLICY profiles_secure_select_policy
ON public.profiles
FOR SELECT
USING (
  auth.uid() = user_id
  OR public.is_admin(auth.uid())
);
