PostgreSQL策略触发无限递归问题求助：行级权限控制

嘿，这个问题我之前踩过坑！你遇到的infinite recursion detected错误，根源在于你的策略子查询里又访问了employee表——PostgreSQL会对每一次访问employee表的操作都应用行级安全策略，包括你策略里的子查询那次，于是就陷入了无限循环：检查行时要查employee，查employee时又要触发策略检查，周而复始。

解决方案：用SECURITY DEFINER函数封装查询逻辑 #

我们可以把“获取当前用户所属部门ID”的逻辑封装成一个SECURITY DEFINER函数，它会以函数创建者的身份执行（默认跳过行级安全检查，只要创建者有足够权限），这样就能避免递归触发策略。

步骤1：创建获取当前用户部门ID的函数

CREATE OR REPLACE FUNCTION get_current_user_department_id() 
RETURNS bigint AS $$
BEGIN
  RETURN (
    SELECT e.department_id
    FROM account a
    JOIN employee e ON a.employee_id = e.id
    WHERE a.login = CURRENT_USER
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public; -- 固定搜索路径，防止安全注入风险

安全提示：SECURITY DEFINER函数要谨慎使用，建议：

• 只给需要的用户授予函数的执行权限：GRANT EXECUTE ON FUNCTION get_current_user_department_id() TO justuser, operator;
• 函数创建者最好是拥有BYPASSRLS权限的用户（比如超级用户），确保能顺利查询employee表。

步骤2：用函数创建行级安全策略

CREATE POLICY locale_policy ON employee 
TO justuser, operator 
USING (department_id = get_current_user_department_id());

步骤3：确保employee表开启了行级安全

ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

为什么这个方案能解决问题？ #

当PostgreSQL执行策略的USING条件时，会调用get_current_user_department_id()函数，而函数是以创建者身份执行的，不会触发employee表的行级安全策略，也就打破了递归循环。函数返回当前用户的部门ID后，再和employee表的department_id对比，就能实现“仅查看同部门员工”的需求。

额外验证 #

你可以切换到justuser或operator用户，执行查询测试：

SET ROLE justuser;
SELECT * FROM employee; -- 只能看到和当前用户同部门的员工

内容的提问来源于stack exchange，提问作者Powercoder