PostgreSQL查询单个用户直接权限及关联角色权限的SQL实现问题

PostgreSQL 中逻辑运算符的优先级为 AND 高于 OR，原有写法没有给条件加正确的分组括号，导致判断逻辑完全偏离预期：

• 第一部分被拼接为独立判断：ps.user_id = 1 AND ps.permission_id =7，且没有校验该记录的active状态
• 第二部分成为无边界的独立判断：只要ps.role_id落在子查询返回的角色ID列表中，无论这条记录属于哪个用户、是否为激活状态，都会被纳入结果。比如样例中set_id=17的记录根本没有绑定任何用户，也会被错误返回。
  同时原写法的校验存在遗漏：直接权限记录、用户角色绑定记录、角色权限绑定记录三类条目，都需要校验active = true，避免把已失效的关联关系查出来。

我们需要严格圈定两类有效结果，所有判断的基础前提是关联记录为激活状态：

1. 目标用户直接分配的指定权限记录
2. 目标用户绑定的角色记录，且该角色确实存在对指定权限的有效绑定

对应修正后的SQL如下：

SELECT *
FROM permission_set ps
WHERE
  ps.active = true
  AND (
    -- 匹配用户直接拥有的指定权限
    (ps.user_id = 1 AND ps.permission_id = 7)
    -- 匹配用户绑定的、拥有指定权限的角色
    OR (
      ps.user_id = 1
      AND ps.role_id IS NOT NULL
      -- 校验对应角色确实绑定了目标权限
      AND EXISTS (
        SELECT 1
        FROM permission_set role_perm
        WHERE
          role_perm.active = true
          AND role_perm.role_id = ps.role_id
          AND role_perm.permission_id = 7
      )
    )
  );

拿提供的样例数据验证，用户ID=1、查询权限ID=7时，结果会正确返回set_id=14（用户直接绑定权限7）、set_id=16（用户绑定角色1，角色1存在绑定权限7的有效记录），不会出现原本误返回的、属于其他用户或未绑定用户的无关记录。

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