Supported Regions:上海

Hands-on Tutorial: E-commerce Mini Program

Before reading, we recommend understanding the PG Mode Overview to clarify the relationship between PostgreSQL, authentication, Cloud Storage, and the permission model.

This article uses an e-commerce Mini Program as a complete real-world scenario, walking you through the end-to-end chain of data modeling, permission design, and REST API calls for a CloudBase PostgreSQL database. The capabilities covered:

Multi-table modeling with foreign key constraints
Combined use of the two-layer permission model (GRANT + RLS)
Differentiated access for different roles (anon / authenticated / service_role)
Full usage of the PostgREST REST API (filtering, sorting, pagination, Prefer)
Typical business rules such as soft unlisting based on is_active, JWT-based order-placement identity binding, and "orders cannot be modified"

💡 After reading this article, you can apply the same approach to blogs, private notes, SaaS, social apps, and more.

Scenario Overview

Build an e-commerce app similar to Shopify / Youzan / a Mini Program store, which needs:

Feature	Description
🏪 Product browsing	Everyone (including anonymous users) can browse listed products
🔒 Hide unlisted products	Products with `is_active=false` are invisible to regular users
🛒 Buyer places an order	Logged-in users can create orders; the buyer identity is bound automatically
👁️ Private orders	Each buyer can only see their own orders
🚫 Orders cannot be modified	Buyers cannot modify or cancel after placing an order; only admins can change the status
👨‍💼 Full admin rights	Admins can manage products and process all order status transitions

Data Model

┌────────────────────────┐       ┌─────────────────────────┐
│       products          │       │        orders            │
├────────────────────────┤       ├─────────────────────────┤
│ id          (serial PK) │◄──┐   │ id           (serial PK) │
│ name        (text)       │   │   │ product_id   (int FK)    │───┘
│ description (text)       │   │   │ buyer_id     (text, JWT) │
│ price       (numeric)    │   └──┤ quantity     (int)       │
│ stock       (int)        │       │ total_price  (numeric)   │
│ category    (text)       │       │ status       (text)      │
│ is_active   (bool)       │       │ address      (jsonb)     │
│ created_at  (timestamptz)│       │ created_at   (timestamptz)│
└────────────────────────┘       └─────────────────────────┘

Prerequisites

A CloudBase environment with PG mode created
The Publishable Key and API Key obtained (see PG: Authentication - Credentials and JWT)
The environment ID, noted as <envId>, e.g. pg-test-3gxmdbdb580ecfd1
API endpoints:
- REST: https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/v1
- Auth: https://<envId>.api.tcloudbasegateway.com/auth/v1

SQL Execution Order

The SQL below must be executed strictly in the following order, otherwise it will fail due to dependencies:

建表 (CREATE TABLE)
  → 启用 RLS (ALTER TABLE ... ENABLE ROW LEVEL SECURITY)
    → 授权 (GRANT)
      → 创建策略 (CREATE POLICY)

When cleaning up, do it in reverse: delete Policy first → then disable RLS → finally drop the table (DROP TABLE ... CASCADE automatically cleans up the Policy).

🔧 SQL can be executed line by line directly through the SQL Editor in the console, or deployed automatically via the cloud API ExecutePGSql (some DDL under the API path needs to be wrapped for retry with DO LANGUAGE plpgsql $$ BEGIN EXECUTE '...'; END $$; this is an anonymous procedural code block. See Architecture and Permission Model - ExecutePGSql).

Step 1: Create Tables

-- ═══════════════════════════════════════════════════
-- 商品表
-- ═══════════════════════════════════════════════════
CREATE TABLE public.products (
    id          serial        PRIMARY KEY,
    name        text          NOT NULL,
    description text,
    price       numeric(10,2) NOT NULL,
    stock       int           DEFAULT 0,
    category    text,
    is_active   boolean       DEFAULT true,      -- true=上架, false=下架
    created_at  timestamptz   DEFAULT now()
);

-- ═══════════════════════════════════════════════════
-- 订单表
-- ═══════════════════════════════════════════════════
CREATE TABLE public.orders (
    id           serial        PRIMARY KEY,
    product_id   int           NOT NULL REFERENCES public.products(id),
    -- ⭐ buyer_id 自动从 JWT 中获取当前用户的 sub（auth.users.id 类型为 varchar(64)）
    buyer_id     varchar(64)   NOT NULL
                   DEFAULT (current_setting('request.jwt.claims', true)::json->>'sub'),
    quantity     int           NOT NULL DEFAULT 1,
    total_price  numeric(10,2) NOT NULL,
    status       text          NOT NULL DEFAULT 'pending',  -- pending → paid → shipped → completed
    address      jsonb,                                     -- 收货地址（结构化 JSON）
    created_at   timestamptz   DEFAULT now(),
    updated_at   timestamptz   DEFAULT now()
);

CREATE INDEX idx_orders_buyer_id ON public.orders(buyer_id);

Key design: buyer_id uses DEFAULT (current_setting('request.jwt.claims', true)::json->>'sub') to automatically get the current user ID from the JWT, no need to pass it from the frontend, eliminating the possibility of identity forgery.

Step 2: Enable RLS

ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orders   ENABLE ROW LEVEL SECURITY;

After RLS is enabled, if there is no Policy, all non-service_role users cannot access any data (default deny). Continue to the next step.

Step 3: Grant Table-level Permissions (GRANT)

-- ═══════════════════════════════════════════════════
-- products（商品表）— 所有人可读，仅管理员可写
-- ═══════════════════════════════════════════════════

-- 匿名用户：只能查看
GRANT SELECT ON public.products TO anon;

-- 已认证用户：也只能查看（普通用户不能操作商品）
GRANT SELECT ON public.products TO authenticated;

-- 管理员：全部权限（BYPASSRLS 自动绕过 RLS）
GRANT ALL ON public.products TO service_role;
GRANT USAGE, SELECT ON SEQUENCE public.products_id_seq TO service_role;


-- ═══════════════════════════════════════════════════
-- orders（订单表）— 买家可查可下单，不可改不可删
-- ═══════════════════════════════════════════════════

-- 已认证用户：可查看 + 可下单，但不能修改和删除
GRANT SELECT, INSERT ON public.orders TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.orders_id_seq TO authenticated;

-- 管理员：全部权限
GRANT ALL ON public.orders TO service_role;
GRANT USAGE, SELECT ON SEQUENCE public.orders_id_seq TO service_role;

-- 匿名用户：不授予 orders 任何权限（未登录不能操作订单）

💡 When using a serial / bigserial primary key, you must also grant USAGE on the corresponding SEQUENCE, otherwise INSERT will fail because it cannot get the next auto-increment value.

Step 4: Create RLS Policies

-- ═══════════════════════════════════════════════════
-- products 的 RLS Policy
-- ═══════════════════════════════════════════════════

-- 所有人只能看到上架商品（is_active = true）
CREATE POLICY products_select
    ON public.products
    FOR SELECT
    USING (is_active = true);

-- ⚠️ 不需要创建 INSERT/UPDATE/DELETE 的 Policy
-- 因为 anon 和 authenticated 在表级权限上就没有写权限
-- service_role 拥有 BYPASSRLS，自动绕过所有 Policy


-- ═══════════════════════════════════════════════════
-- orders 的 RLS Policy
-- ═══════════════════════════════════════════════════

-- SELECT: 买家只能看到自己的订单
CREATE POLICY orders_select
    ON public.orders
    FOR SELECT
    TO authenticated
    USING (
        buyer_id = (current_setting('request.jwt.claims', true)::json->>'sub')
    );

-- INSERT: 买家下单时，buyer_id 必须是自己
CREATE POLICY orders_insert
    ON public.orders
    FOR INSERT
    TO authenticated
    WITH CHECK (
        buyer_id = (current_setting('request.jwt.claims', true)::json->>'sub')
    );

-- ⚠️ 故意不给 authenticated 创建 UPDATE/DELETE Policy
-- 配合表级权限（也没授予 UPDATE/DELETE），实现"订单不可改"的业务规则

Semantics of TO <role> in a Policy

The TO <role> clause of an RLS Policy is used to restrict the policy to apply only to the specified role:

Writing FOR SELECT TO authenticated USING(...) — this Policy only takes effect for the authenticated role; it is completely absent for anon
Not writing the TO clause (FOR SELECT USING(...)) — applies to all roles (except service_role, which automatically bypasses via BYPASSRLS)

For any (role, operation) combination that has no matching Policy, the default is deny — this is a core feature of RLS, and also why "enabling RLS but writing no Policy" causes all non-service_role requests to be rejected.

So in this example, the orders table has no Policy for anon + anon also has no permission at the GRANT layer → double lock, anonymous users absolutely cannot access orders.

Permission Effect Overview

Products table

Operation	`anon` (guest)	`authenticated` (buyer)	`service_role` (admin)
SELECT (listed products)	✅	✅	✅
SELECT (unlisted products)	❌	❌	✅
INSERT	❌	❌	✅
UPDATE	❌	❌	✅
DELETE	❌	❌	✅

Orders table

Operation	`anon` (guest)	`authenticated` (own orders)	`authenticated` (others' orders)	`service_role` (admin)
SELECT	❌	✅	❌	✅
INSERT	❌	✅	❌ (cannot impersonate)	✅
UPDATE	❌	❌	❌	✅
DELETE	❌	❌	❌	✅

REST API Practice

All requests pass the Token via HTTP Header:

Authorization: Bearer <Token>
Content-Type:  application/json

The source of the Token depends on the role:

┌───────────────────────────────────────────────────────┐
│                 前端（小程序 / Web）                    │
│                                                        │
│  游客模式：Publishable Key 作为 Token                  │
│           → role=anon, 无 sub                          │
│                                                        │
│  登录模式：调用 /auth/v1/signin 获取 access_token       │
│           → role=authenticated, 有 sub                 │
└───────────────────────────────────────────────────────┘

┌───────────────────────────────────────────────────────┐
│                 后端（云函数 / 服务端）                  │
│                                                        │
│  管理模式：API Key 作为 Token                           │
│           → role=service_role, BYPASSRLS               │
└───────────────────────────────────────────────────────┘

Scenario 1: Product browsing (guest)

Guests can browse products without logging in, using the Publishable Key:

# 浏览所有上架商品（按价格降序）
GET /v1/rdb/rest/products?select=id,name,price,category&order=price.desc
Authorization: Bearer <Publishable Key>

Response:

[
  { "id": 1, "name": "iPhone 15 Pro",  "price": "8999.00", "category": "电子产品" },
  { "id": 2, "name": "MacBook Air M3", "price": "9499.00", "category": "电子产品" }
]

⚠️ Unlisted products (is_active=false) will not appear in the results — this is filtered automatically by RLS, no frontend handling needed.

# 按价格区间过滤
GET /v1/rdb/rest/products?select=name,price&price=gte.5000&price=lte.10000

# 按分类浏览（URL 中的中文可直接写或按 URL 编码 %E7%94%B5%E5%AD%90%E4%BA%A7%E5%93%81）
GET /v1/rdb/rest/products?select=name,price&category=eq.电子产品

Scenario 2: User places an order (logged-in user)

Step 1: Log in

POST /auth/v1/signin
Content-Type: application/json

{ "username": "buyer-zhang", "password": "MyPassword@1234" }

Response:

{ "access_token": "eyJhbG...", "token_type": "Bearer", "expires_in": 7200, ... }

Step 2: Create an order

POST /v1/rdb/rest/orders
Authorization: Bearer <access_token>
Prefer: return=representation
Content-Type: application/json

{
  "product_id":  1,
  "quantity":    2,
  "total_price": 17998.00,
  "address": { "name": "张三", "phone": "138****0000", "street": "深圳市南山区" }
}

Prefer: return=representation makes PostgREST return the complete row data after writing, which is convenient for updating the frontend UI.

Response:

[
  {
    "id": 1,
    "product_id": 1,
    "buyer_id": "user-uuid-zhang",
    "quantity": 2,
    "total_price": "17998.00",
    "status": "pending",
    "address": { "name": "张三", "phone": "138****0000", "street": "深圳市南山区" },
    "created_at": "2024-01-15T10:30:00Z"
  }
]

💡 Security guarantee: Even if the frontend maliciously passes "buyer_id": "other-user-id", the RLS WITH CHECK policy will reject this request (HTTP 403 / 409), because buyer_id must equal the sub in the JWT.

Scenario 3: Order query (logged-in user)

GET /v1/rdb/rest/orders?select=id,product_id,quantity,total_price,status,created_at
Authorization: Bearer <access_token>

RLS filters automatically, returning only the current user's own orders:

[
  {
    "id": 1,
    "product_id": 1,
    "quantity": 2,
    "total_price": "17998.00",
    "status": "pending",
    "created_at": "2024-01-15T10:30:00Z"
  }
]

⚠️ Even without any filter condition, only your own orders are returned; even if you try ?buyer_id=eq.other-user-id, only an empty array is returned — the RLS policy takes priority over user-provided filter conditions.

Scenario 4: Admin operations (backend)

Admins (backend cloud functions / ops tools) use the API Key, bypassing all RLS:

# 查看所有订单（包括所有买家的）
GET /v1/rdb/rest/orders?select=id,buyer_id,status,total_price
Authorization: Bearer <API Key>

# 更新订单状态：pending → paid
PATCH /v1/rdb/rest/orders?buyer_id=eq.user-uuid-zhang&status=eq.pending
Authorization: Bearer <API Key>
Prefer: return=representation
Content-Type: application/json

{ "status": "paid" }

# 下架商品（注意 URL 中空格用 %20 编码）
PATCH /v1/rdb/rest/products?name=eq.iPhone%2015%20Pro
Authorization: Bearer <API Key>
Content-Type: application/json

{ "is_active": false }

API Key Security

The API Key has superuser privileges with BYPASSRLS. It is strictly forbidden to appear in frontend code, Mini Programs, or Apps. It should only be used in backend environments such as cloud functions or CloudRun, and injected via environment variables.

PostgREST Query Parameters Reference

The REST API strictly follows the PostgREST specification; common parameters:

Filtering

Operator	Description	Example
`eq`	equal	`?category=eq.电子产品`
`neq`	not equal	`?status=neq.completed`
`gt` / `gte`	greater than / greater than or equal	`?price=gte.1000`
`lt` / `lte`	less than / less than or equal	`?price=lte.5000`
`like` / `ilike`	fuzzy / case-insensitive fuzzy	`?name=like.iPhone`
`in`	IN query	`?status=in.(pending,paid)`
`is`	IS NULL / NOT NULL	`?deleted_at=is.null`

Sorting

?order=price.desc                    # 单字段倒序
?order=category.asc,price.desc        # 多字段排序

Pagination

?limit=10&offset=0                    # 第一页
?limit=10&offset=10                   # 第二页

Column Selection

?select=id,name,price                 # 只返回指定列，减少传输量

# 订单携带关联的商品信息
GET /v1/rdb/rest/orders?select=id,quantity,products(name,price)

Prefer Header

Prefer	Effect
`return=representation`	Return the full row after writing
`return=minimal`	No body returned after writing (default)
`count=exact`	Return the total row count (response header `Content-Range`)

Combined Usage

GET /v1/rdb/rest/products?select=name,price,category
                           &category=eq.电子产品
                           &price=gte.5000
                           &order=price.desc
                           &limit=10
                           &offset=0

Equivalent SDK Code

For the same business logic, using @cloudbase/js-sdk:

import cloudbase from '@cloudbase/js-sdk';

const app = cloudbase.init({ env: '<envId>' });
const auth = app.auth;
const db = app.rdb();

// 游客浏览商品（使用匿名登录获取 anon 身份）
await auth.signInAnonymously();
const { data: products } = await db
  .from('products')
  .select('id, name, price, category')
  .order('price', { ascending: false });

// 登录后下单
await auth.signInWithPassword({ username: 'buyer-zhang', password: 'MyPassword@1234' });
const { data: newOrder } = await db.from('orders').insert({
  product_id:  1,
  quantity:    2,
  total_price: 17998.00,
  address: { name: '张三', phone: '138****0000' }
});

// 查询我的订单
const { data: myOrders } = await db.from('orders').select('*');

Clean Up Test Data

-- 先删子表，再删父表（CASCADE 会自动删除关联的 Policy）
DROP TABLE IF EXISTS public.orders   CASCADE;
DROP TABLE IF EXISTS public.products CASCADE;

Best Practices and Common Pitfalls

✅ Recommended Practices

Bind user identity automatically with DEFAULT — the ownership field prevents forgery from the source
Set both USING and WITH CHECK in the UPDATE Policy — the former controls "which rows can be changed", the latter controls "whether the changed value is valid"
GRANT + RLS double lock — if you don't want a role to write, don't grant it at the GRANT layer
Don't forget to grant SEQUENCE permission when using a serial primary key
Delete the child table before the parent table when cleaning up (or use CASCADE)

⚠️ Common Pitfalls

Pitfall	Symptom	Solution
RLS enabled but no Policy written	All non-`service_role` requests rejected	Create a Policy for each role that needs access
Policy written but no GRANT	Even if the Policy allows, still returns a permission error	Check the table-level GRANT
API Key exposed in the frontend	All data fully exposed	Use only in the backend, inject via environment variables
UPDATE only has `USING` without `WITH CHECK`	User can change the ownership field to someone else	Set both in UPDATE
serial primary key without SEQUENCE permission	INSERT reports `permission denied for sequence`	`GRANT USAGE, SELECT ON SEQUENCE`
DDL not wrapped in `DO $$` going directly through `ExecutePGSql`	Some DDL errors	On failure, wrap with `DO LANGUAGE plpgsql $$ BEGIN EXECUTE '...'; END $$` for retry
Too many permissions granted to anonymous users	Anonymous users can write data	`anon` should generally only get `SELECT`

Next Steps

Quick Start — 5-minute Hello World
Architecture and Permission Model — database capabilities, access methods, extensions
PG: Authentication — includes three roles, JWT, Key management
PG Mode Cloud Storage — file permissions based on RLS
Basic Permission Management — advanced RLS topics

Scenario Overview​

Data Model​

Prerequisites​

SQL Execution Order​

Step 1: Create Tables​

Step 2: Enable RLS​

Step 3: Grant Table-level Permissions (GRANT)​

Step 4: Create RLS Policies​

Permission Effect Overview​

Products table​

Orders table​

REST API Practice​

Scenario 1: Product browsing (guest)​

Scenario 2: User places an order (logged-in user)​

Step 1: Log in​

Step 2: Create an order​

Scenario 3: Order query (logged-in user)​

Scenario 4: Admin operations (backend)​

PostgREST Query Parameters Reference​

Filtering​

Sorting​

Pagination​

Column Selection​

Related Query (based on foreign key)​

Prefer Header​

Combined Usage​

Equivalent SDK Code​

Clean Up Test Data​

Best Practices and Common Pitfalls​

✅ Recommended Practices​

⚠️ Common Pitfalls​

Next Steps​

Scenario Overview

Data Model

Prerequisites

SQL Execution Order

Step 1: Create Tables

Step 2: Enable RLS

Step 3: Grant Table-level Permissions (GRANT)

Step 4: Create RLS Policies

Permission Effect Overview

Products table

Orders table

REST API Practice

Scenario 1: Product browsing (guest)

Scenario 2: User places an order (logged-in user)

Step 1: Log in

Step 2: Create an order

Scenario 3: Order query (logged-in user)

Scenario 4: Admin operations (backend)

PostgREST Query Parameters Reference

Filtering

Sorting

Pagination

Column Selection

Related Query (based on foreign key)

Prefer Header

Combined Usage

Equivalent SDK Code

Clean Up Test Data

Best Practices and Common Pitfalls

✅ Recommended Practices

⚠️ Common Pitfalls

Next Steps