RLS 策略模式库

本文汇总 PG 模式云存储常用的 8 类 RLS 权限模式，可直接复制到你的项目中使用。命名与 PostgreSQL 数据库 - RLS 权限模式库 完全对齐——同一份心智模型，业务表与云存储通用。

通用前提

以下模板均假设：

已创建对应的 Bucket（参见快速体验）
storage.objects / storage.buckets 已默认开启 RLS（初始化脚本已完成）
不需要再 GRANT ... ON storage.objects——三角色对该表默认即 ALL，权限完全由 RLS 决定
使用 auth.uid() 读取当前用户 ID，见身份认证

1. PUBLIC — 完全公开

适用场景：站点静态资源、公开素材、CDN 分发的图床

CREATE POLICY public_select ON storage.objects
  FOR SELECT TO anon, authenticated
  USING (bucket_id = 'public-assets');

CREATE POLICY public_insert ON storage.objects
  FOR INSERT TO anon, authenticated
  WITH CHECK (bucket_id = 'public-assets');

操作	`anon`	`authenticated`	`service_role`
SELECT	✅	✅	✅
INSERT	✅	✅	✅
UPDATE	❌	❌	✅
DELETE	❌	❌	✅

实际生产中通常不允许匿名 INSERT，至少要求登录。

2. READONLY — 所有人可读，仅上传者可写

适用场景：用户发布的图片 / 视频 UGC，所有人可见但仅作者可改

-- 所有人可读
CREATE POLICY readonly_select ON storage.objects
  FOR SELECT TO anon, authenticated
  USING (bucket_id = 'ugc');

-- 登录用户可上传，且 owner_id 必须是自己（由 Storage API 注入）
CREATE POLICY readonly_insert ON storage.objects
  FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'ugc' AND owner_id = auth.uid());

-- 仅 owner 可改
CREATE POLICY readonly_update ON storage.objects
  FOR UPDATE TO authenticated
  USING (bucket_id = 'ugc' AND owner_id = auth.uid())
  WITH CHECK (bucket_id = 'ugc' AND owner_id = auth.uid());

-- 仅 owner 可删
CREATE POLICY readonly_delete ON storage.objects
  FOR DELETE TO authenticated
  USING (bucket_id = 'ugc' AND owner_id = auth.uid());

操作	`anon`	`authenticated`（owner）	`authenticated`（他人）	`service_role`
SELECT	✅	✅	✅	✅
INSERT	❌	✅	✅（只能上传自己的）	✅
UPDATE	❌	✅	❌	✅
DELETE	❌	✅	❌	✅

3. PRIVATE — 仅上传者可读写

适用场景：用户私密文件（简历、私人备份、个人云盘）

CREATE POLICY private_select ON storage.objects
  FOR SELECT TO authenticated
  USING (bucket_id = 'private' AND owner_id = auth.uid());

CREATE POLICY private_insert ON storage.objects
  FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'private' AND owner_id = auth.uid());

CREATE POLICY private_update ON storage.objects
  FOR UPDATE TO authenticated
  USING (bucket_id = 'private' AND owner_id = auth.uid())
  WITH CHECK (bucket_id = 'private' AND owner_id = auth.uid());

CREATE POLICY private_delete ON storage.objects
  FOR DELETE TO authenticated
  USING (bucket_id = 'private' AND owner_id = auth.uid());

操作	`anon`	`authenticated`（owner）	`authenticated`（他人）	`service_role`
全部	❌	✅	❌	✅

4. ADMINWRITE — 所有人可读，仅管理员可写

适用场景：官方素材库、字典图标、运营投放图

CREATE POLICY adminwrite_select ON storage.objects
  FOR SELECT TO anon, authenticated
  USING (bucket_id = 'official');

-- 不写 INSERT/UPDATE/DELETE 策略 → 默认拒绝匿名 / 普通用户
-- service_role 自带 BYPASSRLS，可由后端 API Key 写入

操作	`anon`	`authenticated`	`service_role`
SELECT	✅	✅	✅
INSERT/UPDATE/DELETE	❌	❌	✅

5. ADMINONLY — 仅管理员可读写

适用场景：敏感凭证、备份归档、内部资料

-- 不写任何策略 → 默认全拒绝
-- 仅 service_role（BYPASSRLS）可读写，必须由后端用 API Key 调用

操作	`anon`	`authenticated`	`service_role`
全部	❌	❌	✅

6. 路径前缀隔离（每用户一个文件夹）

适用场景：用户头像、个人云盘、IM 附件——所有用户共用一个 Bucket，但物理路径按 <uid>/ 切分

约定对象 key 格式：<uid>/<filename> 或更深层 <uid>/<sub-dir>/...。

CREATE POLICY user_folder_select ON storage.objects
  FOR SELECT TO authenticated
  USING (
    bucket_id = 'user-files'
    AND (storage.foldername(name))[1] = auth.uid()
  );

CREATE POLICY user_folder_insert ON storage.objects
  FOR INSERT TO authenticated
  WITH CHECK (
    bucket_id = 'user-files'
    AND (storage.foldername(name))[1] = auth.uid()
  );

CREATE POLICY user_folder_update ON storage.objects
  FOR UPDATE TO authenticated
  USING (
    bucket_id = 'user-files'
    AND (storage.foldername(name))[1] = auth.uid()
  )
  WITH CHECK (
    bucket_id = 'user-files'
    AND (storage.foldername(name))[1] = auth.uid()
  );

CREATE POLICY user_folder_delete ON storage.objects
  FOR DELETE TO authenticated
  USING (
    bucket_id = 'user-files'
    AND (storage.foldername(name))[1] = auth.uid()
  );

性能提示

path_tokens 是生成列，可建索引；如对此模式有高并发查询需求：

CREATE INDEX idx_objects_user_folder
  ON storage.objects (bucket_id, (path_tokens[1]));

也可写成 path_tokens[1] = auth.uid()，与 (storage.foldername(name))[1] 等价但对索引更友好。

7. 业务表 JOIN（团队 / 多租户协作）

适用场景：团队网盘、多租户 SaaS——文件归属由业务表 team_members 决定

约定对象 key：<team_id>/<filename>。

-- 业务表
CREATE TABLE public.team_members (
  team_id text NOT NULL,
  user_id text NOT NULL,
  role    text NOT NULL DEFAULT 'member',  -- member / admin
  PRIMARY KEY (team_id, user_id)
);

-- 团队成员可读
CREATE POLICY team_files_select ON storage.objects
  FOR SELECT TO authenticated
  USING (
    bucket_id = 'team-files'
    AND EXISTS (
      SELECT 1 FROM public.team_members tm
      WHERE tm.team_id = (storage.foldername(storage.objects.name))[1]
        AND tm.user_id = auth.uid()
    )
  );

-- 团队成员可上传到自己所在的团队目录
CREATE POLICY team_files_insert ON storage.objects
  FOR INSERT TO authenticated
  WITH CHECK (
    bucket_id = 'team-files'
    AND EXISTS (
      SELECT 1 FROM public.team_members tm
      WHERE tm.team_id = (storage.foldername(name))[1]
        AND tm.user_id = auth.uid()
    )
  );

-- 仅团队管理员可删
CREATE POLICY team_files_delete ON storage.objects
  FOR DELETE TO authenticated
  USING (
    bucket_id = 'team-files'
    AND EXISTS (
      SELECT 1 FROM public.team_members tm
      WHERE tm.team_id = (storage.foldername(storage.objects.name))[1]
        AND tm.user_id = auth.uid()
        AND tm.role   = 'admin'
    )
  );

跨 schema GRANT

Policy 中 EXISTS (SELECT 1 FROM public.team_members ...) 是以当前角色身份执行子查询，因此需要保证 authenticated 角色对 public.team_members 至少有 SELECT 权限：

GRANT SELECT ON public.team_members TO authenticated;

并为 team_members 自身的 RLS 策略允许"用户读自己所在记录"。

8. 元数据驱动（短期分享链接 / 内容标签）

适用场景：临时分享、根据 user_metadata 中的标签控制可见性

-- 文件 user_metadata 形如 {"share_token": "xyz", "expires_at": "2025-06-01T00:00:00Z"}

CREATE POLICY shared_by_token ON storage.objects
  FOR SELECT TO anon, authenticated
  USING (
    bucket_id = 'share'
    AND user_metadata ? 'share_token'
    AND current_setting('request.headers', true)::json->>'x-share-token'
        = user_metadata->>'share_token'
    AND (user_metadata->>'expires_at')::timestamptz > now()
  );

这里读取了 HTTP 请求头 x-share-token——request.headers 是 PostgREST 注入的 GUC，在 PG 模式网关下同样生效。

抽象 Policy 表达

如果同一份判断逻辑会在多条策略里重复使用，建议封装为函数：

-- 判断当前用户是否为对象 owner
CREATE OR REPLACE FUNCTION storage.is_owner(obj_name text, obj_owner_id text)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
  SELECT obj_owner_id = auth.uid();
$$;

-- 判断当前用户是否在某团队中
CREATE OR REPLACE FUNCTION public.is_team_member(p_team_id text)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.team_members
    WHERE team_id = p_team_id AND user_id = auth.uid()
  );
$$;

-- 在 Policy 中使用
CREATE POLICY team_files_select ON storage.objects
  FOR SELECT TO authenticated
  USING (
    bucket_id = 'team-files'
    AND public.is_team_member((storage.foldername(name))[1])
  );

选型决策表

业务场景	推荐模式
落地页静态资源、字体、图标	1. PUBLIC
朋友圈照片、UGC 图片墙	2. READONLY
私人笔记附件、简历	3. PRIVATE
官方素材库、活动 banner	4. ADMINWRITE
后台备份、敏感凭证	5. ADMINONLY
用户头像、个人云盘	6. 路径前缀隔离
团队网盘、多租户 SaaS	7. 业务表 JOIN
临时分享链接、标签过滤	8. 元数据驱动
文章封面、订单附件等"跟随业务对象"	7. 业务表 JOIN（变体）

下一步

快速体验 — 跑通 pattern 6 的端到端示例
权限管理 — storage schema、辅助函数、RLS-only 模型
PostgreSQL 数据库 - RLS 权限模式库 — 业务表侧对应模板
常见问题

1. PUBLIC — 完全公开​

2. READONLY — 所有人可读，仅上传者可写​

3. PRIVATE — 仅上传者可读写​

4. ADMINWRITE — 所有人可读，仅管理员可写​

5. ADMINONLY — 仅管理员可读写​

6. 路径前缀隔离（每用户一个文件夹）​

7. 业务表 JOIN（团队 / 多租户协作）​

8. 元数据驱动（短期分享链接 / 内容标签）​

抽象 Policy 表达​

选型决策表​

下一步​