Supported Regions:上海

RLS Permission Pattern Examples

This article summarizes the four common RLS permission patterns and two advanced scenarios for CloudBase PostgreSQL databases, along with complete SQL templates that can be copied directly into your project. Each pattern includes:

Applicable scenario
Complete SQL for table creation + GRANT + Policy
Permission effect matrix

💡 The names here intentionally align with the READONLY / PRIVATE / ADMINWRITE / ADMINONLY permission patterns in Cloud Storage: one mental model that works for both databases and Cloud Storage.

1. READONLY — Everyone can read, only the creator can write

Applicable scenario: blog posts, product catalogs, public posts, community Q&A

SQL Template

-- 建表（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'));

Permission Effect

Operation	`anon`	`authenticated` (creator)	`authenticated` (non-creator)	`service_role`
SELECT	✅	✅	✅	✅
INSERT	❌	✅	✅ (only own records)	✅
UPDATE	❌	✅ (own only)	❌	✅
DELETE	❌	✅ (own only)	❌	✅

2. PRIVATE — Only the creator can read and write

Applicable scenario: private notes, personal settings, private messages, todo lists

Key Differences from READONLY

The SELECT policy also adds an owner_id check (users are fully isolated from each other)
No permissions are granted to anon

SQL Template

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'));

Permission Effect

Each user can only see their own data; users are fully isolated from each other.

3. ADMINWRITE — Everyone can read, only admins can write

Applicable scenario: bulletin board, system configuration, dictionary tables, global constants

Key Idea

Do not grant any write permission to anon / authenticated — writing is blocked at the table-level permission layer; only service_role (BYPASSRLS) can write.

SQL Template

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);

Permission Effect

Operation	`anon`	`authenticated`	`service_role`
SELECT (published)	✅	✅	✅
SELECT (unpublished)	❌	❌	✅
INSERT / UPDATE / DELETE	❌	❌	✅

4. ADMINONLY — Only admins can read and write

Applicable scenario: system logs, audit records, backend configuration, sensitive data tables

Key Idea

Grant no permissions to anon / authenticated and create no Policy — the strictest double lock: without GRANT you cannot pass the first layer, without a Policy you cannot pass the second layer (but service_role, with BYPASSRLS, automatically passes through).

SQL Template

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 能访问

Permission Effect

Apart from service_role, all other roles are completely unable to access the data.

5. Advanced: Multi-tenant SaaS (team collaboration)

Applicable scenario: Notion / Slack / Linear style apps: teammates share documents within a team, while different teams are fully isolated

Core technique: subquery Policy — use a subquery in the Policy to join the team members table.

SQL Template

-- 团队表
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')
        )
    );

💡 Performance tip: A subquery Policy is executed once per row. Recommendations:

Create indexes on team_members(user_id) and team_documents(team_id)

For large tables, replace the subquery with a function is_team_member(team_id) (SECURITY DEFINER)

Applicable scenario: Twitter / Instagram / Moments style apps: posts can be set public or private

Core technique: conditional visibility — use an OR condition to achieve "public posts visible to everyone, private posts visible only to the author".

SQL Template

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')
        )
    );

Abstracting Policies

The SQL above repeatedly uses current_setting('request.jwt.claims', true)::json->>'sub'. You can wrap it in a function to simplify writing:

-- 辅助函数：返回当前登录用户的 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
$$;

After that, you can use auth.uid() directly in all Policies:

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

Pattern Selection Decision Table

Business description	Which pattern
Everyone can browse, only the author can modify	READONLY
Only visible to the owner (isolated)	PRIVATE
Public information, displayable on the frontend, only modifiable from the backend	ADMINWRITE
Sensitive data, backend access only	ADMINONLY
Team / workspace collaboration	Multi-tenant SaaS
Posts can be public or private	Social app
E-commerce, with business status transitions	See Hands-on Tutorial

Next Steps

Hands-on Tutorial: E-commerce Mini Program — combined usage + REST API practice
Quick Start — 5-minute Hello World
Basic Permission Management — RLS fundamentals and common scenarios
Architecture and Permission Model — full understanding of the two-layer permission model

1. READONLY — Everyone can read, only the creator can write​

SQL Template​

Permission Effect​

2. PRIVATE — Only the creator can read and write​

Key Differences from READONLY​

SQL Template​

Permission Effect​

3. ADMINWRITE — Everyone can read, only admins can write​

Key Idea​

SQL Template​

Permission Effect​

4. ADMINONLY — Only admins can read and write​

Key Idea​

SQL Template​

Permission Effect​

5. Advanced: Multi-tenant SaaS (team collaboration)​

SQL Template​

6. Advanced: Social app (public / private)​

SQL Template​

Abstracting Policies​

Pattern Selection Decision Table​

Next Steps​

1. READONLY — Everyone can read, only the creator can write

SQL Template

Permission Effect

2. PRIVATE — Only the creator can read and write

Key Differences from READONLY

SQL Template

Permission Effect

3. ADMINWRITE — Everyone can read, only admins can write

Key Idea

SQL Template

Permission Effect

4. ADMINONLY — Only admins can read and write

Key Idea

SQL Template

Permission Effect

5. Advanced: Multi-tenant SaaS (team collaboration)

SQL Template

6. Advanced: Social app (public / private)

SQL Template

Abstracting Policies

Pattern Selection Decision Table

Next Steps