PostgreSQL行级安全（RLS）配置：实现自身及同团队用户数据查询

问题根源 #

你之前的策略失效是因为：子查询访问users_teams时会触发同一张表的RLS规则，形成递归限制，导致子查询返回空结果，最终只有user_id = auth.uid()的条件生效，无法获取同团队的其他用户关联记录。

一、修复users_teams表的RLS策略 #

1. 创建安全定义器函数 #

该函数绕过RLS限制，确保能正确获取当前用户所属的所有团队ID：

CREATE OR REPLACE FUNCTION get_current_user_teams()
RETURNS SETOF uuid AS $$
SELECT team_id FROM users_teams WHERE user_id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER;

-- 限制函数仅允许认证用户调用
REVOKE ALL ON FUNCTION get_current_user_teams() FROM public;
GRANT EXECUTE ON FUNCTION get_current_user_teams() TO authenticated;

2. 启用RLS并创建策略 #

-- 启用表的RLS
ALTER TABLE users_teams ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_team_access_policy ON users_teams
TO authenticated
USING (
    -- 允许查看自身的团队关联记录
    user_id = auth.uid()
    -- 允许查看同团队所有用户的团队关联记录
    OR team_id IN (SELECT get_current_user_teams())
);

二、配置users表的RLS策略 #

允许用户查看自身数据以及同团队其他用户的数据：

-- 启用表的RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_access_policy ON users
TO authenticated
USING (
    -- 允许查看自身信息
    id = auth.uid()
    -- 允许查看同团队用户信息：验证双方是否共享至少一个团队
    OR EXISTS (
        SELECT 1 FROM users_teams ut_current
        JOIN users_teams ut_target ON ut_current.team_id = ut_target.team_id
        WHERE ut_current.user_id = auth.uid()
        AND ut_target.user_id = users.id
    )
);

三、表结构优化建议 #

• 改用复合主键：users_teams表的主键建议改为(user_id, team_id)，避免同一用户重复加入同一团队，同时提升关联查询性能：

ALTER TABLE users_teams DROP CONSTRAINT users_teams_pkey;
ALTER TABLE users_teams ADD PRIMARY KEY (user_id, team_id);

• 添加索引优化：为users_teams的team_id字段添加索引，加速团队用户关联查询：

CREATE INDEX idx_users_teams_team_id ON users_teams(team_id);

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