Skip to main content
Supported Regions:上海

Quick Start

Before reading, we recommend first reviewing the PG Mode Overview to understand the relationship between PostgreSQL, authentication, cloud storage, and the permission model.

This article uses a minimal todo app as an example to demonstrate the complete flow in a CloudBase PostgreSQL database — from create table → enable RLS → authorize → write policy → SDK call — helping you get a secure multi-user data access chain working in 5–10 minutes.

Prerequisites​

Obtaining Keys

Publishable Key / API Key can be created in the console (or via the CreateApiKey YUNAPI).

Step 1: Create a Table​

Create a todos table. You can use the SQL Editor (Console → Database → SQL Editor) to execute directly:

CREATE TABLE public.todos (
  id            bigserial    PRIMARY KEY,
  title         varchar(255) NOT NULL,
  is_completed  boolean      NOT NULL DEFAULT false,
  -- ⭐ owner_id is automatically read from the JWT as the current user ID
  owner_id      varchar(64)  NOT NULL
                 DEFAULT (current_setting('request.jwt.claims', true)::json->>'sub'),
  created_at    timestamptz  NOT NULL DEFAULT now()
);

CREATE INDEX idx_todos_owner_id ON public.todos(owner_id);

Key point: The default value of owner_id is bound to the JWT sub. The frontend does not need to pass owner_id, preventing identity forgery at the source.

Step 2: Enable RLS​

ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;

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

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

Table-level GRANT controls what kinds of operations each role can perform on the table:

-- Anonymous users: no permissions granted (unauthenticated users cannot operate personal todos)

-- Authenticated users: can read and write their own todos
GRANT SELECT, INSERT, UPDATE, DELETE ON public.todos TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.todos_id_seq TO authenticated;

-- Admin: full permissions
GRANT ALL ON public.todos TO service_role;
GRANT USAGE, SELECT ON SEQUENCE public.todos_id_seq TO service_role;

Step 4: Create RLS Policies​

Row-level Policies control which specific rows each role can operate on:

-- SELECT: authenticated users can only read their own todos
CREATE POLICY todos_select_own ON public.todos
  FOR SELECT TO authenticated
  USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

-- INSERT: authenticated users can only write records where owner_id = themselves
CREATE POLICY todos_insert_own ON public.todos
  FOR INSERT TO authenticated
  WITH CHECK (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));

-- UPDATE: authenticated users can only update their own records, and cannot change owner_id to someone else
CREATE POLICY todos_update_own ON public.todos
  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: authenticated users can only delete their own records
CREATE POLICY todos_delete_own ON public.todos
  FOR DELETE TO authenticated
  USING (owner_id = (current_setting('request.jwt.claims', true)::json->>'sub'));
Automated Deployment via Cloud API

The SQL above can be executed via the cloud API ExecutePGSql (suitable for CI/CD). Note that some DDL statements under the API path need to be wrapped with DO LANGUAGE plpgsql $$ BEGIN EXECUTE '...'; END $$ and retried. This anonymous code block depends on BEGIN and dynamic EXECUTE. See Architecture and Permission Model - ExecutePGSql for details.

Step 5: Read and Write Data via SDK​

Install​

npm install @cloudbase/js-sdk

Usage​

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

// You can also pass accessKey: '<Publishable Key>', so that unauthenticated calls use the anon identity
const app = cloudbase.init({ env: '<CloudBase environment ID>' });
const auth = app.auth;

// 1. Sign in (anonymous / password / third-party all work; anonymous shown here)
//    After anonymous sign-in, the JWT has role=anon but carries a real sub, which can be used as the owner_id attribution field
await auth.signInAnonymously();

// 2. Access the PG database
const db = app.rdb();

// Query: RLS filters automatically, returning only the current user's todos
const { data, error } = await db
  .from('todos')
  .select('*')
  .eq('is_completed', false);

// Insert: owner_id is auto-filled by the database default value as the JWT sub
await db.from('todos').insert({ title: 'Write a document' });

// Update: RLS only allows updating your own
await db.from('todos').update({ is_completed: true }).eq('id', 1);

// Delete: RLS only allows deleting your own
await db.from('todos').delete().eq('id', 1);
SDK Usage Notes

Step 6: Use the REST API Directly (Optional)​

For scenarios without an SDK, you can call the PostgREST RESTful API directly:

# Sign in
curl -X POST "https://<envId>.api.tcloudbasegateway.com/auth/v1/signin/anonymously" \
  -H "Content-Type: application/json" \
  -d '{}'
# Response: { "access_token": "eyJhbG...", ... }

# Query
curl "https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/todos?select=*&is_completed=eq.false" \
  -H "Authorization: Bearer <access_token>"

# Insert (owner_id omitted, auto-filled by DEFAULT)
curl -X POST "https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/todos" \
  -H "Authorization: Bearer <access_token>" \
  -H "Prefer: return=representation" \
  -H "Content-Type: application/json" \
  -d '{"title":"Write a document"}'

Verify RLS Is Working​

  • Unauthenticated access (no Authorization header, or called as anon) → no permission, returns an empty array or 403
  • Access after sign-in → only returns your own data
  • Forge owner_id (frontend maliciously passes "owner_id": "other-user") → rejected by WITH CHECK
  • Try to read another user's todos (e.g., ?owner_id=eq.other-user) → even with the filter, the USING policy still only returns your own data

Common Pitfalls​

  1. Enabled RLS but wrote no Policy → all non-service_role requests are denied
  2. Wrote a Policy but forgot GRANT → table-level permission check fails
  3. UPDATE only wrote USING, not WITH CHECK → user can change owner_id to someone else and steal data
  4. Used API Key in frontend code → equivalent to fully exposing all data (service_role BYPASSRLS)
  5. Used serial / bigserial primary key but forgot to grant SEQUENCE permission → INSERT reports a permission error
  6. Some statements error directly when DDL goes through ExecutePGSql → on failure, wrap with DO LANGUAGE plpgsql $$ BEGIN EXECUTE '...'; END $$ and retry

Next Steps​