检查同表时Row Level Security策略失效问题排查

你的memberships_insert策略存在逻辑歧义：在WITH CHECK的子查询中，organization_id未明确指定为待插入行的组织ID，数据库会将其解析为子查询内memberships表的organization_id，导致条件等价于memberships.organization_id = memberships.organization_id（恒成立），但实际业务逻辑需要验证当前用户是否是目标组织的成员。

同时，原memberships_select策略仅允许用户查看自己的成员记录，若需要组织成员能查看整个组织的成员列表，也需要调整。

1. 修复memberships插入策略 #

替换原memberships_insert策略，用NEW.organization_id明确引用待插入行的组织ID：

DROP POLICY IF EXISTS memberships_insert ON public.memberships;

CREATE POLICY memberships_insert ON public.memberships FOR INSERT TO user_role
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.organization_id = NEW.organization_id
    AND memberships.user_id = current_setting('app.sub')::uuid
  )
);

2. （可选）优化memberships查询策略 #

若需要组织成员能查看所在组织的所有成员记录，替换原memberships_select策略：

DROP POLICY IF EXISTS memberships_select ON public.memberships;

CREATE POLICY memberships_select ON public.memberships FOR SELECT TO user_role
USING (
  -- 允许查看自己的成员记录
  "user_id" = current_setting('app.sub')::uuid
  -- 允许查看自己所在组织的所有成员记录
  OR EXISTS (
    SELECT 1 FROM public.memberships m
    WHERE m.organization_id = memberships.organization_id
    AND m.user_id = current_setting('app.sub')::uuid
  )
);

重新执行alice添加bob为contoso成员的语句：

BEGIN;
  SET LOCAL ROLE user_role;
  SELECT set_config('app.sub', (SELECT "id" FROM public.users WHERE "username" = 'alice')::text, true);
  INSERT INTO public.memberships ("organization_id", "user_id")
  VALUES (
    (SELECT "id" FROM public.organizations WHERE "name" = 'contoso'),
    (SELECT "id" FROM public.users WHERE "username" = 'bob')
  ) RETURNING *;
COMMIT;

此时语句可正常执行，bob会被成功添加为contoso的成员。

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