如何用单条PostgreSQL查询根据用户权限筛选工具数据？

需求描述 #

我需要编写一条PostgreSQL查询语句，根据用户权限返回对应的工具数据，对应的Python逻辑如下：

user_permissions = ['admin', 'user-perm1']
if any(perm in ['admin', 'super-user'] for perm in user_permissions):
  # 返回所有工具数据
else:
  # 筛选出perm列值在用户权限列表中的工具数据

请问能否用单条PostgreSQL查询完成这个需求？

解决方案 #

当然可以，利用PostgreSQL的数组操作符就能实现这个逻辑，核心是通过条件判断区分管理员权限和普通权限的返回规则：

SELECT *
FROM tools
WHERE 
  -- 用户拥有admin或super-user权限时，返回所有数据
  ARRAY['admin', 'super-user'] && ARRAY['admin', 'user-perm1']
  OR
  -- 无管理员权限时，仅返回perm列匹配用户权限的记录
  perm = ANY(ARRAY['admin', 'user-perm1']);

如果是在应用中使用参数化查询（推荐，避免SQL注入），可以将用户权限作为参数传入：

SELECT *
FROM tools
WHERE 
  ARRAY['admin', 'super-user'] && :user_permissions
  OR
  perm = ANY(:user_permissions);

逻辑解析 #

• ARRAY['admin', 'super-user'] && :user_permissions：使用数组交集操作符&&检查用户权限数组是否包含管理员权限，只要有一个匹配，该条件为真，此时OR整体成立，返回全表数据。
• 当用户没有管理员权限时，第一个条件为假，会触发第二个条件，仅筛选出perm列存在于用户权限列表中的工具数据。

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