Supabase仅追加表行级安全：订阅分配追踪策略问询

核心需求 #

要实现的目标是：仅允许当前持有某订阅的用户，查看该订阅的所有分配历史记录（用于追踪重新分配过程），同时规避原思路中IN子查询的低效问题。

优化后的策略写法 #

推荐用EXISTS子查询替代IN，EXISTS在多数场景下性能更优（找到匹配项即终止查询，无需遍历全部结果）。以下是两种可行方案：

方案1：仅允许当前订阅所有者查看对应订阅的所有历史记录 #

CREATE POLICY "Current subscription owners can view full assignment history"
ON assignments
FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM assignments latest_assign
    WHERE latest_assign.subscription_id = assignments.subscription_id
    ORDER BY latest_assign.created_at DESC
    LIMIT 1
    AND latest_assign.user_id = auth.uid()
  )
);

方案2：允许用户查看自己的分配记录 + 当前持有的订阅的所有历史记录 #

如果需要兼顾“用户能看到自己被分配过的所有记录”，可以调整为：

CREATE POLICY "Users can view their own assignments and current subscriptions' history"
ON assignments
FOR SELECT
USING (
  -- 查看自己作为被分配者的记录
  user_id = auth.uid()
  -- 查看自己当前持有的订阅的所有历史
  OR EXISTS (
    SELECT 1
    FROM assignments latest_assign
    WHERE latest_assign.subscription_id = assignments.subscription_id
    AND latest_assign.created_at = (
      SELECT MAX(created_at)
      FROM assignments
      WHERE subscription_id = assignments.subscription_id
    )
    AND latest_assign.user_id = auth.uid()
  )
);

性能优化建议 #

为让子查询快速执行，必须创建复合索引：

-- 针对subscription_id和created_at的索引，加速最新记录查找
CREATE INDEX idx_assignments_subscription_created ON assignments (subscription_id, created_at DESC);

该索引可让数据库直接定位到每个subscription_id的最新记录，避免全表扫描。

你的思路验证 #

你提出的“先匹配user_id == auth.uid()的行，再对关联的subscription_id执行ORDER BY created_at, LIMIT 1”的思路是可行的，本质就是方案2的逻辑——先放行用户自己的记录，再校验该记录所属订阅的最新所有者是否为当前用户，结合索引后性能会远优于原IN子查询的写法。

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