RLS 权限模式示例

本文汇总 CloudBase PostgreSQL 数据库中四种常见 RLS 权限模式以及两个进阶场景的完整 SQL 模板，可直接复制到你的项目中使用。每个模式都包含：

• 适用场景
• 完整建表 + GRANT + Policy 的 SQL
• 权限效果矩阵

💡 本文与云存储传统权限模式中的 READONLY / PRIVATE / ADMINWRITE / ADMINONLY 同名，语义刻意对齐：一份心智模型，数据库和云存储通用。

1. READONLY — 所有人可读，仅创建者可写

适用场景：博客文章、产品目录、公共帖子、社区问答

SQL 模板

-- 建表（owner_id 自动绑定 JWT sub）
CREATE TABLE public.posts (
    id         serial      PRIMARY KEY,
    title      text        NOT NULL,
    content    text,
    owner_id   varchar(64) NOT NULL
                 DEFAULT (current_setting('request.jwt.claims', true)::json->>'sub'),
    created_at timestamptz DEFAULT now()
);

ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- 表级权限
GRANT SELECT ON public.posts TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.posts TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.posts_id_seq TO authenticated;
GRANT ALL ON public.posts TO service_role;
GRANT USAGE, SELECT ON SEQUENCE public.posts_id_seq TO service_role;

-- RLS 策略
-- 所有人可读
CREATE POLICY posts_select ON public.posts
    FOR SELECT
    USING (true);

-- 登录用户可创建（但 owner_id 必须是自己）
CREATE POLICY posts_insert ON public.posts
    FOR INSERT TO authenticated
    WITH CHECK (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

-- 仅创建者可更新
CREATE POLICY posts_update ON public.posts
    FOR UPDATE TO authenticated
    USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'))
    WITH CHECK (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

-- 仅创建者可删除
CREATE POLICY posts_delete ON public.posts
    FOR DELETE TO authenticated
    USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

权限效果

操作	anon	authenticated（创建者）	authenticated（非创建者）	service_role
SELECT	✅	✅	✅	✅
INSERT	❌	✅	✅（只能创建自己的）	✅
UPDATE	❌	✅（仅自己的）	❌	✅
DELETE	❌	✅（仅自己的）	❌	✅

---

2. PRIVATE — 仅创建者可读写

适用场景：私人笔记、个人设置、私密消息、待办清单

与 READONLY 的关键区别

• SELECT 策略也加上 owner_id 判断（用户间完全隔离）
• 不授予 anon 任何权限

SQL 模板

CREATE TABLE public.private_notes (
    id         serial      PRIMARY KEY,
    content    text        NOT NULL,
    owner_id   varchar(64) NOT NULL
                 DEFAULT (current_setting('request.jwt.claims', true)::json->>'sub'),
    created_at timestamptz DEFAULT now()
);

ALTER TABLE public.private_notes ENABLE ROW LEVEL SECURITY;

-- 不授予 anon 任何权限
GRANT SELECT, INSERT, UPDATE, DELETE ON public.private_notes TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.private_notes_id_seq TO authenticated;
GRANT ALL ON public.private_notes TO service_role;
GRANT USAGE, SELECT ON SEQUENCE public.private_notes_id_seq TO service_role;

-- SELECT 策略：仅创建者可见
CREATE POLICY notes_select ON public.private_notes
    FOR SELECT TO authenticated
    USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

-- INSERT 策略：owner_id 必须是自己
CREATE POLICY notes_insert ON public.private_notes
    FOR INSERT TO authenticated
    WITH CHECK (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

-- UPDATE 策略：仅创建者可更新且不能改 owner_id
CREATE POLICY notes_update ON public.private_notes
    FOR UPDATE TO authenticated
    USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'))
    WITH CHECK (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

-- DELETE 策略：仅创建者可删
CREATE POLICY notes_delete ON public.private_notes
    FOR DELETE TO authenticated
    USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

权限效果

每个用户只能看到自己的数据，用户之间完全隔离。

---

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

适用场景：公告板、系统配置、字典表、全局常量

关键思路

不授予 anon / authenticated 任何写权限——从表级权限层面就阻止写入；只有 service_role（BYPASSRLS）可以写。

SQL 模板

CREATE TABLE public.announcements (
    id         serial      PRIMARY KEY,
    title      text        NOT NULL,
    content    text,
    published  boolean     DEFAULT true,
    created_at timestamptz DEFAULT now()
);

ALTER TABLE public.announcements ENABLE ROW LEVEL SECURITY;

-- 仅授予读权限
GRANT SELECT ON public.announcements TO anon;
GRANT SELECT ON public.announcements TO authenticated;
-- 不授予 INSERT/UPDATE/DELETE 给 anon/authenticated
GRANT ALL ON public.announcements TO service_role;
GRANT USAGE, SELECT ON SEQUENCE public.announcements_id_seq TO service_role;

-- SELECT 策略：仅显示 published=true 的公告
CREATE POLICY announcements_select ON public.announcements
    FOR SELECT
    USING (published = true);

权限效果

操作	anon	authenticated	service_role
SELECT（已发布）	✅	✅	✅
SELECT（未发布）	❌	❌	✅
INSERT / UPDATE / DELETE	❌	❌	✅

---

4. ADMINONLY — 仅管理员可读写

适用场景：系统日志、审计记录、后台配置、敏感数据表

关键思路

不授予 anon / authenticated 任何权限 + 不创建任何 Policy——最严格的双重锁定：没有 GRANT 过不了第一层，没有 Policy 过不了第二层（但 service_role 具备 BYPASSRLS，自动穿透）。

SQL 模板

CREATE TABLE public.system_config (
    key         text        PRIMARY KEY,
    value       jsonb       NOT NULL,
    updated_at  timestamptz DEFAULT now()
);

ALTER TABLE public.system_config ENABLE ROW LEVEL SECURITY;

-- 仅授予 service_role
GRANT ALL ON public.system_config TO service_role;

-- 不授予 anon / authenticated 任何权限
-- 不创建任何 RLS Policy
-- 结果：只有 service_role 能访问

权限效果

除了 service_role，其他所有角色完全无法访问。

---

5. 进阶：多租户 SaaS（团队协作）

适用场景：Notion / Slack / Linear 类应用：同团队成员共享文档，不同团队完全隔离

核心技巧：子查询 Policy——在 Policy 中使用子查询关联团队成员表。

SQL 模板

-- 团队表
CREATE TABLE public.teams (
    id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    name       text        NOT NULL,
    created_at timestamptz DEFAULT now()
);

-- 团队成员表（多对多关系）
CREATE TABLE public.team_members (
    team_id   uuid NOT NULL REFERENCES public.teams(id) ON DELETE CASCADE,
    user_id   varchar(64) NOT NULL,
    role      text NOT NULL DEFAULT 'member',  -- owner / admin / member
    joined_at timestamptz DEFAULT now(),
    PRIMARY KEY (team_id, user_id)
);

-- 团队文档表
CREATE TABLE public.team_documents (
    id         serial      PRIMARY KEY,
    team_id    uuid        NOT NULL REFERENCES public.teams(id) ON DELETE CASCADE,
    title      text        NOT NULL,
    content    text,
    owner_id   varchar(64) NOT NULL
                 DEFAULT (current_setting('request.jwt.claims', true)::json->>'sub'),
    created_at timestamptz DEFAULT now()
);

CREATE INDEX idx_team_docs_team_id ON public.team_documents(team_id);

ALTER TABLE public.team_documents ENABLE ROW LEVEL SECURITY;

GRANT SELECT, INSERT, UPDATE, DELETE ON public.team_documents TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.team_documents_id_seq TO authenticated;
GRANT ALL ON public.team_documents TO service_role;

-- 用户只能看到自己所属团队的文档
CREATE POLICY team_docs_select ON public.team_documents
    FOR SELECT TO authenticated
    USING (
        team_id IN (
            SELECT tm.team_id FROM public.team_members tm
            WHERE tm.user_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        )
    );

-- 用户只能在自己所属的团队中创建文档
CREATE POLICY team_docs_insert ON public.team_documents
    FOR INSERT TO authenticated
    WITH CHECK (
        owner_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        AND team_id IN (
            SELECT tm.team_id FROM public.team_members tm
            WHERE tm.user_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        )
    );

-- 仅文档所有者可编辑
CREATE POLICY team_docs_update ON public.team_documents
    FOR UPDATE TO authenticated
    USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'))
    WITH CHECK (
        owner_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        AND team_id IN (
            SELECT tm.team_id FROM public.team_members tm
            WHERE tm.user_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        )
    );

-- 团队 admin / owner 可删除本团队任何文档
CREATE POLICY team_docs_delete_admin ON public.team_documents
    FOR DELETE TO authenticated
    USING (
        team_id IN (
            SELECT tm.team_id FROM public.team_members tm
            WHERE tm.user_id = (current_setting('request.jwt.claims', true)::json->>'sub')
              AND tm.role IN ('owner', 'admin')
        )
    );

💡 性能提示：子查询 Policy 会对每条行执行一次。建议：

• 在 team_members(user_id) 和 team_documents(team_id) 上建索引
• 对于大表，可将子查询替换为函数 is_team_member(team_id)（SECURITY DEFINER）

---

6. 进阶：社交应用（公开 / 私密）

适用场景：Twitter / Instagram / 朋友圈类应用：帖子可设为公开或私密

核心技巧：条件可见性——通过 OR 条件实现"公开帖子所有人可见，私密帖子仅作者可见"。

SQL 模板

CREATE TABLE public.social_posts (
    id         serial      PRIMARY KEY,
    content    text        NOT NULL,
    is_public  boolean     DEFAULT true,
    owner_id   varchar(64) NOT NULL
                 DEFAULT (current_setting('request.jwt.claims', true)::json->>'sub'),
    created_at timestamptz DEFAULT now()
);

CREATE TABLE public.post_comments (
    id         serial      PRIMARY KEY,
    post_id    int         NOT NULL REFERENCES public.social_posts(id) ON DELETE CASCADE,
    content    text        NOT NULL,
    owner_id   varchar(64) NOT NULL
                 DEFAULT (current_setting('request.jwt.claims', true)::json->>'sub'),
    created_at timestamptz DEFAULT now()
);

ALTER TABLE public.social_posts  ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.post_comments ENABLE ROW LEVEL SECURITY;

GRANT SELECT ON public.social_posts TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.social_posts TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.social_posts_id_seq TO authenticated;

GRANT SELECT ON public.post_comments TO anon;
GRANT SELECT, INSERT, DELETE ON public.post_comments TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.post_comments_id_seq TO authenticated;

-- ═══════════════════════════════════════════════════
-- social_posts 的 Policy
-- ═══════════════════════════════════════════════════

-- 公开帖子所有人可读 + 私密帖子仅作者可读
CREATE POLICY posts_select ON public.social_posts
    FOR SELECT
    USING (
        is_public = true
        OR owner_id = (current_setting('request.jwt.claims', true)::json->>'sub')
    );

CREATE POLICY posts_insert ON public.social_posts
    FOR INSERT TO authenticated
    WITH CHECK (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

CREATE POLICY posts_update ON public.social_posts
    FOR UPDATE TO authenticated
    USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'))
    WITH CHECK (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

CREATE POLICY posts_delete ON public.social_posts
    FOR DELETE TO authenticated
    USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

-- ═══════════════════════════════════════════════════
-- post_comments 的 Policy
-- ═══════════════════════════════════════════════════

-- 评论跟随帖子可见性：能看到帖子 → 能看到评论
CREATE POLICY comments_select ON public.post_comments
    FOR SELECT
    USING (
        post_id IN (
            SELECT sp.id FROM public.social_posts sp
            WHERE sp.is_public = true
               OR sp.owner_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        )
    );

-- 登录用户可评论可见帖子
CREATE POLICY comments_insert ON public.post_comments
    FOR INSERT TO authenticated
    WITH CHECK (
        owner_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        AND post_id IN (
            SELECT sp.id FROM public.social_posts sp
            WHERE sp.is_public = true
               OR sp.owner_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        )
    );

-- 用户可删除自己的评论；帖子作者可删除自己帖子下的任何评论
CREATE POLICY comments_delete ON public.post_comments
    FOR DELETE TO authenticated
    USING (
        owner_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        OR post_id IN (
            SELECT sp.id FROM public.social_posts sp
            WHERE sp.owner_id = (current_setting('request.jwt.claims', true)::json->>'sub')
        )
    );

---

抽象 Policy 表达

上面的 SQL 反复出现 current_setting('request.jwt.claims', true)::json->>'sub'。可以封装为函数简化书写：

-- 辅助函数：返回当前登录用户的 ID
CREATE OR REPLACE FUNCTION auth.uid() RETURNS text
  LANGUAGE sql STABLE
AS $$
    SELECT current_setting('request.jwt.claims', true)::json->>'sub'
$$;

-- 辅助函数：返回当前角色
CREATE OR REPLACE FUNCTION auth.role() RETURNS text
  LANGUAGE sql STABLE
AS $$
    SELECT current_setting('request.jwt.claims', true)::json->>'role'
$$;

-- 辅助函数：返回完整 claims
CREATE OR REPLACE FUNCTION auth.jwt() RETURNS jsonb
  LANGUAGE sql STABLE
AS $$
    SELECT current_setting('request.jwt.claims', true)::jsonb
$$;

之后即可在所有 Policy 中直接用 auth.uid()：

CREATE POLICY notes_select ON public.private_notes
    FOR SELECT TO authenticated
    USING (owner_id = auth.uid());

---

选型决策表

业务描述	选哪种
所有人可浏览、仅作者可改	READONLY
仅本人可见（隔离）	PRIVATE
公示信息，前端可展示、仅后台可改	ADMINWRITE
敏感数据，仅后台访问	ADMINONLY
团队 / 工作区协作	多租户 SaaS
帖子可公开可私密	社交应用
电商、带业务状态流转	参考 实战教程

下一步

• 实战教程：电商小程序 — 综合运用 + REST API 实操
• 快速体验 — 5 分钟 Hello World
• 基础权限管理 — RLS 基本概念与常见场景
• 架构与权限模型 — 完整理解双层权限模型