PostgreSQL/MySQL用户权限校验SQL问题：排除同Slug禁用权限

环境说明 #

• DBMS：PostgreSQL（兼容MySQL）
• 涉及数据表：permissions、roles、model_permissions、model_roles

需求与现有问题 #

基础需求实现 #

已实现查询用户（如ID=1）所有权限（直接分配+角色继承）的SQL：

select p.*
from permissions p
         join model_permissions mp on mp.permission_id = p.id
         join model_roles mr on mr.model_type = 'users' and mr.model_id = 1
where
    (
       (mp.model_type = 'users' and mp.model_id = 1)
        or
       (mr.role_id = mp.model_id and mp.model_type = 'roles')
    )

group by p.id;

升级需求 #

校验用户是否拥有指定权限，但如果用户存在同slug且allowed=false的直接权限，必须返回空结果。示例：用户ID=1通过角色拥有edit权限（allowed=true），但同时直接分配了edit的禁用权限（对应model_permissions表记录|users | 1 | 4 |），此时查询edit权限应返回空。

原有尝试的缺陷 #

尝试的查询逻辑存在明显漏洞：

select p.*
from permissions p
         join model_permissions mp on mp.permission_id = p.id
         join model_roles mr on mr.model_type = 'users' and mr.model_id = 1
where
     (
        (mp.model_type = 'users' and mp.model_id = 1)
        or
        (mr.role_id = mp.model_id and mp.model_type = 'roles')
     )
  and
    not exists(select *
                 from permissions p2
                 where p2.allowed=false
                 and p2.slug=p.slug
                 )
  and
    p.slug = 'edit'

group by p.id;

问题出在not exists子句：它会查找系统中所有allowed=false且slug匹配的权限，而非当前用户的直接禁用权限。只要系统里存在任意一条slug为edit且allowed=false的权限，这个子句就会过滤掉所有结果，完全忽略权限的归属关系——这就是为什么删除用户的禁用权限后依然返回空的原因。

可行查询的原理解释 #

你找到的可行查询做了两个关键调整：

select p.*
from permissions p
         join model_permissions mp on mp.permission_id = p.id
where
    mp.model_type = 'roles' and mp.model_id = 1
  and
    not exists(select p2.*
                 from permissions p2
                 join model_permissions mp2 on mp2.permission_id = p2.id
                 where p2.allowed=false
                 and p2.slug=p.slug
                 )
  and
    p.slug = 'edit'
group by p.id;

1. 缩小权限来源范围：直接只从角色关联的权限中查询目标slug（edit），避免了直接权限的干扰
2. 关联权限分配表判断禁用：通过join model_permissions mp2，not exists子句现在会检查是否存在有分配记录的allowed=false权限（而非系统中所有的）。不过这个查询存在小瑕疵：它没有明确限定是当前用户的直接禁用权限，如果其他用户有同slug的禁用权限，依然会误判。

更优方案（逻辑严谨且清晰） #

下面的SQL严格遵循需求逻辑，明确区分权限归属，可读性和可维护性更强：

SELECT p.*
FROM permissions p
WHERE p.slug = 'edit'
AND (
    -- 场景1：用户直接拥有该权限且状态为允许
    EXISTS (
        SELECT 1
        FROM model_permissions mp
        WHERE mp.permission_id = p.id
          AND mp.model_type = 'users'
          AND mp.model_id = 1
          AND p.allowed = true
    )
    OR
    -- 场景2：用户通过角色继承该权限且状态为允许
    EXISTS (
        SELECT 1
        FROM model_permissions mp
        JOIN model_roles mr ON mr.role_id = mp.model_id
        WHERE mp.permission_id = p.id
          AND mp.model_type = 'roles'
          AND mr.model_type = 'users'
          AND mr.model_id = 1
          AND p.allowed = true
    )
)
-- 核心规则：排除用户直接拥有同slug的禁用权限的情况
AND NOT EXISTS (
    SELECT 1
    FROM permissions p2
    JOIN model_permissions mp2 ON mp2.permission_id = p2.id
    WHERE p2.slug = p.slug
      AND p2.allowed = false
      AND mp2.model_type = 'users'
      AND mp2.model_id = 1
);

这个查询的逻辑链：

• 先锁定目标slug的权限记录
• 判断用户是否通过直接分配或角色继承拥有该允许权限
• 最后确保用户没有直接分配同slug的禁用权限，完全符合需求

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