PostgreSQL多对多表INSERT场景行级安全(RLS)策略配置问题

需要实现user_group表INSERT操作的RLS（行级安全）策略，满足以下要求：

• 插入数据前校验操作用户归属待插入用户组对应的租户
• 业务规则：单用户可归属多租户，同租户下用户可归属多用户组；用户的租户归属关系通过user_role表查询，即用户在某租户下存在角色分配记录，视为归属该租户。

原尝试的策略代码无法满足校验要求，代码如下：

CREATE POLICY insert_user_group_policy ON user_group FOR INSERT TO public 
WITH CHECK((SELECT user_role.id FROM user_role WHERE user_role.tenant_id = current_setting('app.tenant_id')::int) = user_id);

ALTER TABLE user_group ENABLE ROW LEVEL SECURITY;

• 子查询未关联当前操作用户维度，只要user_role表存在当前租户的任意记录就会放行，完全没有校验操作人身份
• 子查询可能返回多条结果，触发subquery returned more than 1 row运行时报错
• 未校验待插入的user_group记录本身的租户ID和当前会话租户一致，存在跨租户插入数据的风险
• 未匹配user_role记录的user_id和当前操作用户ID，无法确认操作人是否真的在当前租户下有角色

以下代码基于通用字段约定编写，可根据实际表结构调整字段名：

• 当前会话上下文通过app.tenant_id存操作租户ID，app.current_user_id存当前登录用户ID，均为int类型
• user_role表核心字段：tenant_id（租户ID）、user_id（用户ID），存储用户在对应租户下的角色分配关系
• user_group表核心字段：tenant_id（所属租户ID）、user_id（入组用户ID）、group_id（用户组ID）

-- 开启user_group表的行级安全
ALTER TABLE user_group ENABLE ROW LEVEL SECURITY;

-- 创建INSERT操作校验策略
CREATE POLICY insert_user_group_policy ON user_group 
FOR INSERT 
TO public 
WITH CHECK (
  -- 校验插入记录所属租户和当前会话租户一致
  tenant_id = current_setting('app.tenant_id')::int
  -- 校验当前操作用户在当前租户下存在有效角色分配
  AND EXISTS (
    SELECT 1 
    FROM user_role 
    WHERE user_role.tenant_id = current_setting('app.tenant_id')::int
      AND user_role.user_id = current_setting('app.current_user_id')::int
  )
);

可选扩展校验 #

如果业务要求操作用户只能为同租户下的其他用户分配用户组，可在WITH CHECK中追加以下条件，校验待入组的用户也属于当前租户：

-- 校验被加入用户组的用户归属当前租户
AND EXISTS (
  SELECT 1
  FROM user_role ur
  WHERE ur.tenant_id = current_setting('app.tenant_id')::int
    AND ur.user_id = user_group.user_id
)

注意：调用current_setting读取会话变量前，需确保变量已在当前会话中完成初始化，否则会抛出变量不存在的错误，建议在连接建立后通过set_config统一设置租户ID、当前用户ID这类全局上下文参数。

内容的提问来源于stack exchange，提问作者Mr. Jin