PostgreSQL Database

CloudBase provides a PostgreSQL database service. It is built on open-source PostgREST, supports complete SQL capabilities, and uses a two-layer permission model of table-level GRANT + row-level RLS Policy so clients can access the database directly and securely.

Before reading this page, we recommend learning about PG mode overview to understand the role of PostgreSQL in a CloudBase environment.

Other database types

CloudBase also provides document database and MySQL database. Choose the appropriate database type based on your application requirements.

Use AI to manage relational databases and SQL operations

PG Mode: PostgreSQL-Centered

CloudBase PostgreSQL is not just an additional database option. It represents a PostgreSQL-centered environment model, also called PG mode. In this model, PostgreSQL stores business data and also acts as unified infrastructure for accounts, permissions, and cloud storage metadata:

• Business data: stored directly in PostgreSQL tables. Clients read and write through RESTful APIs automatically exposed by PostgREST.
• Permission model: expressed with SQL. Table-level GRANT and row-level RLS Policy are both enforced, and each request carries a JWT that the database uses for authorization.
• Account system: user data is stored in the auth schema and can be queried or joined with business tables by SQL.
• Cloud storage: file metadata is stored in the storage schema. Permissions are also expressed with RLS and share the same permission language as business data.

This means you only need one expression language, SQL, to model data, design permissions, and work across domains. Later PostgreSQL pages such as quick start, permissions, and RPC assume this PG mode context.

Capabilities

Capability	Description
Complete SQL	Tables, views, foreign keys, indexes, transactions, triggers, stored procedures, CTEs, window functions, partitions, and more
PostgREST RESTful API	Tables, views, and RPC functions are automatically exposed as REST APIs, with filtering, sorting, pagination, relation queries, and nested writes
Two-layer permissions	GRANT at the table level and RLS Policy at the row level
Three access roles	anon, authenticated, and service_role
Extension ecosystem	pgvector + vectorscale, tencentdb_ai, zhparser / pg_jieba for Chinese tokenization, TimescaleDB, Apache AGE, PL/V8, and more
Authentication integration	auth schema and JWT claims can be read directly in SQL
Cloud storage integration	storage schema stores object metadata and can be joined with business tables

How to Enable

When you create a CloudBase PostgreSQL environment, the PostgreSQL database is enabled automatically. You do not need to create or initialize the database instance manually.

Three Ways to Access the Database

1. Client SDK (Direct Access + RLS)

This is the most common method. Frontends use the CloudBase SDK to read and write the database directly, while RLS handles authorization:

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

// Choose one authentication method:
// (1) Use a Publishable Key (frontend-visible): pass accessKey so requests without explicit login use the anon role
const app = cloudbase.init({ env: '<env-id>', accessKey: '<Publishable Key>' });

// (2) Or omit accessKey and explicitly sign in with auth.signInAnonymously() / signInWithPassword()
//     const app = cloudbase.init({ env: '<env-id>' });
//     await app.auth.signInAnonymously();

const db = app.rdb();

// Query. RLS automatically filters by the signed-in user.
const { data } = await db
  .from('todos')
  .select('id, title, is_completed')
  .eq('is_completed', false);

// Insert. owner_id is filled by the database default value from the JWT sub claim.
await db.from('todos').insert({ title: 'Write documentation' });

SDK requests must carry a JWT: one of Publishable Key, access_token, or API Key. If none is provided, the gateway rejects the request.

CloudBase provides multiple SDKs for operating PostgreSQL databases:

SDK Type	Platform
Mini Program ClientSDK	Mini Program
JS SDK	Web browser
Node SDK	Node.js environment
HTTP API	General

tip

After obtaining the db instance with the Mini Program ClientSDK, the PostgreSQL operation syntax is consistent with the Web JS SDK. For syntax details, see the JS SDK.

2. REST API (PostgREST Specification)

Use this method when no SDK is available, such as other backend services, low-code platforms, or third-party systems.

Base endpoints:

REST:  https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/<table>
Auth:  https://<envId>.api.tcloudbasegateway.com/auth/v1

Authorization header: Authorization: Bearer <Publishable Key | access_token | API Key>

Examples:

# List products anonymously using a Publishable Key
curl "https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/products?select=id,name,price&order=price.desc" \
  -H "Authorization: Bearer <Publishable Key>"

# Place an order after sign-in using an access_token
curl -X POST "https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/orders" \
  -H "Authorization: Bearer <access_token>" \
  -H "Prefer: return=representation" \
  -H "Content-Type: application/json" \
  -d '{"product_id":1,"quantity":2,"total_price":199.00}'

For details, see HTTP API - PostgREST RESTful API.

3. Cloud API ExecutePGSql (Control Plane)

The cloud API ExecutePGSql lets administrators execute arbitrary SQL. It is commonly used for CI/CD automation, database schema migrations, and automated table creation.

Basic information:

Field	Value
API name	ExecutePGSql
Endpoint	tcb.tencentcloudapi.com
Version	2018-06-08
Region	Required. Use the region where the environment is located, such as ap-shanghai
Signature method	TC3-HMAC-SHA256

Request body:

{
  "EnvId": "<envId>",
  "Sql":   "<SQL to execute>",
  "Role":  "<optional database role, commonly anon / authenticated / service_role>"
}

The Role parameter is optional:

• Omitted: SQL is executed as the system super administrator role cloudbase_admin, with SUPERUSER and BYPASSRLS permissions. It can execute any DDL, including CREATE TABLE, and bypass all RLS policies.
• Specified: SQL is executed as the specified role, commonly anon, authenticated, or service_role. This is useful for simulating a role in the control plane to debug RLS policies. In this mode, GRANT and RLS both apply.

About DDL statements: Some DDL statements, such as CREATE, ALTER, DROP, GRANT, REVOKE, TRUNCATE, and COMMENT, may return InternalError when executed directly through ExecutePGSql. If this happens, wrap the DDL in an anonymous code block and retry. The anonymous code block uses BEGIN ... END and dynamic EXECUTE, so it explicitly uses LANGUAGE plpgsql; normal function examples still prefer LANGUAGE SQL by default:

-- DDL that may fail when executed directly
CREATE TABLE public.products (id serial PRIMARY KEY, name text);

-- If it fails, retry with a DO block
DO LANGUAGE plpgsql $$
BEGIN
  EXECUTE 'CREATE TABLE public.products (id serial PRIMARY KEY, name text)';
END
$$;

Single quotes ' inside EXECUTE '...' must be escaped as ''.

Python example using Tencent Cloud SDK:

pip install tencentcloud-sdk-python

import json, re
from tencentcloud.common import credential
from tencentcloud.common.profile.client_profile import ClientProfile
from tencentcloud.common.profile.http_profile import HttpProfile
from tencentcloud.tcb.v20180608 import tcb_client, models

SECRET_ID  = "<your SecretId>"
SECRET_KEY = "<your SecretKey>"
ENV_ID     = "<your envId>"
REGION     = "ap-shanghai"

_DDL_KEYWORDS = re.compile(
    r"^\s*(CREATE|DROP|ALTER|GRANT|REVOKE|TRUNCATE|COMMENT|"
    r"SET|RESET|LOCK|REINDEX|CLUSTER|VACUUM|ANALYZE)\b",
    re.IGNORECASE,
)

def _wrap_ddl(sql: str) -> str:
    return f"DO LANGUAGE plpgsql $$ BEGIN EXECUTE '{sql.replace(chr(39), chr(39)*2)}'; END $$"

def execute_sql(sql: str, role: str = None, retry_with_wrap: bool = True) -> dict:
    cred = credential.Credential(SECRET_ID, SECRET_KEY)
    http_profile = HttpProfile()
    http_profile.endpoint = "tcb.tencentcloudapi.com"
    client_profile = ClientProfile()
    client_profile.httpProfile = http_profile
    client = tcb_client.TcbClient(cred, REGION, client_profile)

    body = {"EnvId": ENV_ID, "Sql": sql}
    if role:
        body["Role"] = role

    try:
        req = models.ExecutePGSqlRequest()
        req.from_json_string(json.dumps(body))
        return json.loads(client.ExecutePGSql(req).to_json_string())
    except Exception as e:
        # Some DDL statements can fail when executed directly. Retry with a DO block.
        if retry_with_wrap and _DDL_KEYWORDS.match(sql.strip()):
            return execute_sql(_wrap_ddl(sql), role=role, retry_with_wrap=False)
        raise

# Execute DML directly
print(execute_sql("SELECT 1"))

# Retry DDL automatically with a DO block when needed
execute_sql("""
    CREATE TABLE public.products (
        id    serial       PRIMARY KEY,
        name  text         NOT NULL,
        price numeric(10,2) NOT NULL
    )
""")
execute_sql("ALTER TABLE public.products ENABLE ROW LEVEL SECURITY")
execute_sql("GRANT SELECT ON public.products TO anon")

Limitations

• Each API call can execute only one SQL statement. Do not concatenate multiple statements with semicolons. For batch execution, split the SQL and call the API statement by statement.
• When DDL fails, wrap it with DO LANGUAGE plpgsql $$ BEGIN EXECUTE '...'; END $$. Escape single quotes. This anonymous code block depends on BEGIN and dynamic EXECUTE, so it requires LANGUAGE plpgsql.
• The API may occasionally return InternalError because of transient backend issues. Use exponential backoff retries.
• This is an administrator API with high privileges. Use it only on the backend. Do not expose SecretKey.

Two-Layer Permission Model

Database permissions have two layers. A request succeeds only when both layers pass:

Request -> JWT parsing -> Layer 1: table-level GRANT (which operations are allowed)
                     -> Layer 2: RLS Policy (which rows are accessible) -> Response

Layer 1: GRANT

-- Example: anonymous users can read, signed-in users can read and write, admins have all permissions
GRANT SELECT ON public.orders TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.orders TO authenticated;
GRANT ALL ON public.orders TO service_role;

-- If you use a serial primary key, also grant sequence permissions
GRANT USAGE, SELECT ON SEQUENCE public.orders_id_seq TO authenticated;

Layer 2: RLS Policy

ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;

-- Anonymous users can see only public orders
CREATE POLICY orders_anon_select ON public.orders
  FOR SELECT TO anon
  USING (is_public = true);

-- Signed-in users can see only their own orders
CREATE POLICY orders_auth_select ON public.orders
  FOR SELECT TO authenticated
  USING (buyer_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

service_role has BYPASSRLS and automatically bypasses RLS Policy. As long as the corresponding permission is granted at the GRANT layer, no policy is required for service_role.

Four Common RLS Patterns

The following four patterns cover most application scenarios and can be reused directly. The names follow the traditional CloudBase storage permission patterns to keep the mental model familiar.

Pattern	Typical scenario	Permission effect
READONLY	Blog posts, product catalog	Everyone can read; only the creator can write
PRIVATE	Private notes, personal settings	Only the creator can read and write; users are fully isolated
ADMINWRITE	Announcements, system configuration	Everyone can read; only admins can write
ADMINONLY	Audit logs, backend configuration	Only admins can read and write

Advanced scenarios, such as multi-tenant SaaS collaboration, social visibility, or visibility inherited from a post, can be expressed with subqueries, EXISTS, or OR conditions.

For details, see RLS policy patterns and Basic permission management.

Query Expressions (PostgREST Convention)

The REST API supports rich query parameters:

# Filtering
?category=eq.electronics
?price=gte.1000&price=lte.5000
?name=like.*iPhone*
?status=in.(pending,paid)

# Sorting
?order=price.desc
?order=category.asc,price.desc

# Pagination
?limit=10&offset=0

# Column selection
?select=id,name,price

# Relation query, inferred from foreign keys
?select=*,products(name,price)

Common Error Handling

xxx.rdb is not a function

Cause: The CloudBase SDK version is too old and does not support PostgreSQL database operations.

Solution: Update the SDK to the latest version.

Generating default gateway base url failed: env not found

Cause: The WeChat base library version is too low and does not support CloudBase PostgreSQL database features.

Solution: Update the WeChat base library to 3.8.9 or later.

Set the minimum base library version in WeChat DevTools:

1. Open Project Details.
2. In Local Settings, set Debug Base Library to 3.8.9 or later.
3. In Basic Information, set Minimum Base Library Version to 3.8.9 or later.

Note

After updating the base library version, make sure your Mini Program handles compatibility for older WeChat versions, or set the minimum version requirement in the Mini Program admin console.

Best Practices and Common Pitfalls

Recommended practices:

1. Use DEFAULT to bind ownership fields to the JWT sub; do not let the frontend pass them.
2. Configure both USING and WITH CHECK for UPDATE policies.
3. Lock permissions with both GRANT and RLS. If a role should not write, block it at the GRANT layer.
4. When using serial or bigserial primary keys, remember to grant SEQUENCE permissions.
5. When cleaning test data, delete child rows before parent rows because of foreign keys.

Common pitfalls:

1. RLS is enabled but no policy is created, so all non-service_role requests are rejected.
2. A policy exists but GRANT is missing, so table-level permission fails.
3. API Key is exposed on the frontend, allowing service_role to bypass RLS and expose all data.
4. UPDATE policy defines only USING but not WITH CHECK, so users can change ownership fields and take over data.
5. Anonymous users are granted too many permissions. Unless there is a strong requirement, give anon only SELECT.

Detailed Documentation

• Basic permission management
• RLS policy patterns
• Database functions (RPC)
• Foreign key management
• Index management
• HTTP API - PostgREST RESTful API
• Web SDK - PostgreSQL Database
• Node.js SDK - PostgreSQL Database

Framework Quickstarts

• Use PG database in React
• Use PG database in Vue
• Use PG database in Mini Program

Next Steps

• Quick start - Run secure multi-user data access in five minutes
• Hands-on tutorial: e-commerce Mini Program - Data modeling, permission design, and REST API in one workflow
• PG: Authentication - JWT, three roles, and key management
• PG mode cloud storage - File permissions based on RLS
• Basic permission management
• Index management