Postgres Row Level Security实现trips表仅创建者访客可查询方案咨询

实现方案 #

符合SQL最佳实践的做法是直接在RLS策略的using子句中使用EXISTS关联trip_guests表做逐行校验，不需要额外冗余字段，也不需要自定义标量函数。

首先建议先修复字段类型不匹配的问题（可选但强烈推荐，避免隐式转换导致的性能损耗和异常）：

-- 修正trip_guests.trip_id的类型，和trips.id的INT类型对齐
ALTER TABLE trip_guests 
ALTER COLUMN trip_id TYPE INT USING trip_id::integer;

然后配置RLS策略即可：

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

-- 创建select权限策略
CREATE POLICY "Allow trip creator and guests to select trips"
ON trips FOR SELECT
USING (
  -- 条件1：当前用户是行程创建者
  admin_id = auth.uid()
  -- 条件2：当前用户是该行程的受邀访客
  OR EXISTS (
    SELECT 1 FROM trip_guests
    WHERE trip_guests.trip_id = trips.id
    AND trip_guests.email = auth.email()
  )
);

-- 若还未给已认证用户授予trips表的查询权限，需补充执行
GRANT SELECT ON trips TO authenticated;

原理解释 #

• EXISTS子查询会针对trips表的每一行做校验，判断当前行的行程ID是否在trip_guests表中存在和当前登录用户邮箱匹配的记录，天然支持返回所有符合权限的行程，不会出现之前仅返回单条记录的问题
• 该写法完全符合第三范式要求，不需要在trips表中冗余存储访客数组字段，数据一致性由外键保证
• trip_guests表的(email, trip_id)联合主键自带索引，关联查询性能有保障

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