RPC (Database Function Calls)

PostgREST not only automatically exposes tables/views as REST interfaces, but also supports calling PostgreSQL functions (Stored Functions) via the /rpc/{function_name} endpoint. This lets you push complex business logic down to the database layer, so the frontend can complete multi-table transactions, aggregation calculations, sensitive operations, etc. with a single HTTP call.

When to Use RPC

REST CRUD is suitable for simple reads and writes, but the following scenarios are better suited for RPC:

Scenario	Description	Example
Data aggregation	Statistics, leaderboards, reports	"My total order amount", "Top 10 bestsellers"
Multi-table transaction	An atomic write involving multiple tables in one operation	Deduct inventory while placing an order, write the ledger
Complex validation	Business rules that simple RLS cannot express	"At most 3 created per day", "Remaining limited-time flash sale count"
Sensitive operations	Privileged actions that need to bypass RLS but are restricted	"Reset password", "Issue coupon"
Server-side computation	Computation that should not be carried by the client	Price calculation, encryption

Invocation

PostgREST RPC is invoked via a POST request, with parameters passed as the JSON body:

POST https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/rpc/<function_name>
Authorization: Bearer <Token>
Content-Type: application/json

{ "param1": "value1", "param2": 123 }

The return value is determined by the function definition (scalar, JSON, row set, etc.).

Security Prerequisite: Do Not Treat "Endpoint Reachability" as a Permission Boundary

Cloudbase PostgREST currently does not enforce GRANT EXECUTE checks — all roles (including anon) can invoke any /rpc/{function_name} endpoint at the gateway level.

The actual data isolation fully depends on:

1. SECURITY INVOKER functions: The underlying table's GRANT + RLS still constrain the caller, naturally ensuring security
2. SECURITY DEFINER functions: You must validate the caller's role inside the function body (e.g., current_setting('request.jwt.claims', true)::json->>'role'), otherwise you are exposing the creator's full permissions to everyone

Be sure to write explicit role validation in every SECURITY DEFINER function, otherwise the RPC endpoint becomes a "backdoor" that bypasses RLS. All DEFINER examples below demonstrate this.

Create an RPC Function

Simple Example: Public Calculation Function

Prefer LANGUAGE SQL by default. Use LANGUAGE plpgsql only when you need variables, branches, exception handling, or multiple statements.

CREATE OR REPLACE FUNCTION public.rpc_add_numbers(a int, b int)
RETURNS int
LANGUAGE SQL
SECURITY INVOKER
AS $$
    SELECT a + b;
$$;

Invocation:

curl -X POST 'https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/rpc/rpc_add_numbers' \
  -H "Authorization: Bearer <Publishable Key>" \
  -H "Content-Type: application/json" \
  -d '{"a": 1, "b": 2}'
# Response: 3

Read Current User Identity

CREATE OR REPLACE FUNCTION public.rpc_whoami()
RETURNS json
LANGUAGE SQL
SECURITY INVOKER
AS $$
    SELECT json_build_object(
        'role', current_setting('request.jwt.claims', true)::json->>'role',
        'sub',  current_setting('request.jwt.claims', true)::json->>'sub',
        'aud',  current_setting('request.jwt.claims', true)::json->>'aud'
    );
$$;

Complex Business: Atomic Order Placement + Inventory Deduction

This function needs variables, row locks, branches, exception handling, and multiple write statements. It is procedural logic, so it uses LANGUAGE plpgsql.

CREATE OR REPLACE FUNCTION public.rpc_place_order(
    p_product_id int,
    p_quantity   int
)
RETURNS json
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
    v_product   public.products%ROWTYPE;
    v_buyer_id  text := current_setting('request.jwt.claims', true)::json->>'sub';
    v_order_id  int;
BEGIN
    SELECT * INTO v_product FROM public.products WHERE id = p_product_id FOR UPDATE;
    IF NOT FOUND OR v_product.stock < p_quantity THEN
        RAISE EXCEPTION 'Product does not exist or insufficient stock';
    END IF;

    UPDATE public.products SET stock = stock - p_quantity WHERE id = p_product_id;

    INSERT INTO public.orders (product_id, buyer_id, quantity, total_price)
    VALUES (p_product_id, v_buyer_id, p_quantity, v_product.price * p_quantity)
    RETURNING id INTO v_order_id;

    RETURN json_build_object('order_id', v_order_id, 'status', 'pending');
END;
$$;

The entire flow is completed within a single database transaction — inventory deduction and order creation either both succeed or both roll back, without frontend coordination.

SECURITY INVOKER vs SECURITY DEFINER

Mode	Executed As	RLS in Effect	Use Case
SECURITY INVOKER (default)	Caller	✅ Constrained by caller's RLS	Most scenarios: let data access follow RLS
SECURITY DEFINER	Function creator	❌ Bypasses RLS	Controlled privilege escalation

INVOKER (Default, constrained by RLS)

CREATE OR REPLACE FUNCTION public.rpc_get_my_todos()
RETURNS SETOF public.todos
LANGUAGE SQL
SECURITY INVOKER
AS $$
    SELECT * FROM public.todos;  -- Even without WHERE, only your own rows are returned: RLS filters automatically
$$;

At invocation: user A sees only A's, user B sees only B's, and anonymous anon returns empty.

DEFINER (Bypasses RLS, globally visible)

CREATE OR REPLACE FUNCTION public.rpc_get_all_todo_count()
RETURNS bigint
LANGUAGE SQL
SECURITY DEFINER       -- ⭐ Executed as creator, bypassing RLS
AS $$
    SELECT count(*) FROM public.todos;
$$;

Any role (including anonymous) can call it to get the total row count — but only this number, no specific rows are returned.

DEFINER Security Constraints

SECURITY DEFINER is a double-edged sword. Recommended principles:

1. Actively validate role / user inside the function body to decide which roles can call it

   -- DEFINER function callable only by service_role
   -- This needs IF and RAISE EXCEPTION, so it uses LANGUAGE plpgsql
   CREATE OR REPLACE FUNCTION public.admin_only_action()
   RETURNS json
   LANGUAGE plpgsql
   SECURITY DEFINER
   AS $$
   BEGIN
       IF current_setting('request.jwt.claims', true)::json->>'role' <> 'service_role' THEN
           RAISE EXCEPTION 'Permission denied';
       END IF;
       -- Actual logic ...
       RETURN json_build_object('ok', true);
   END;
   $$;

2. Expose only the minimum necessary data: return desensitized summaries, counts, boolean values, avoiding dumping table rows directly

3. Set search_path explicitly: ALTER FUNCTION xxx SET search_path = public, pg_temp; to prevent malicious schema hijacking

Permission Control

Relationship Between GRANT EXECUTE and Table-Level Permissions

PostgreSQL built-in rules:

-- By default all roles can EXECUTE functions (PUBLIC meaning)
-- To restrict, first REVOKE then GRANT:
REVOKE EXECUTE ON FUNCTION public.rpc_admin_only() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.rpc_admin_only() TO service_role;

Current Behavior of Cloudbase PostgREST

Cloudbase PostgREST currently does not enforce GRANT EXECUTE checks — all roles (including anon) can invoke any /rpc/{function_name} endpoint at the gateway level.

The actual data isolation fully depends on:

1. RLS constraints inside the function (in INVOKER mode)
2. Table-level GRANT (even if the function can be called, without table permission no data can be read)
3. Active role validation inside the function body (DEFINER mode must self-check)

Do not treat "whether the client can call a certain RPC" as a security line of defense; the security line of defense must fall on table-level GRANT + RLS or explicit validation inside the function body.

Recommended Patterns

-- INVOKER function: fully relies on the underlying table's GRANT + RLS
CREATE FUNCTION public.rpc_user_action()
RETURNS json LANGUAGE SQL SECURITY INVOKER
AS $$ ... $$;

-- DEFINER function: explicit validation inside the function
-- This needs IF and RAISE EXCEPTION, so it uses LANGUAGE plpgsql
CREATE FUNCTION public.rpc_privileged_action()
RETURNS json LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
    IF NOT (current_setting('request.jwt.claims', true)::json->>'role' = 'service_role') THEN
        RAISE EXCEPTION 'forbidden';
    END IF;
    -- ... business logic
END;
$$;

Call Error Reference

Symptom	Cause	Troubleshooting
404 Not Found	Function does not exist or gateway metadata not refreshed	Confirm the function is created; after switching schema, wait a few seconds before calling
argument missing	Parameter name misspelled or type mismatch	Match the argname in SQL exactly; JSON numbers auto-convert with int / numeric
permission denied for table	In an INVOKER function, the caller lacks table-level GRANT on the operated table	GRANT to the corresponding role; or switch to DEFINER and self-check
RLS rejection	INVOKER function cannot read data	Check the RLS Policy; or switch the function to DEFINER (with role validation)
Returns empty array / 0	RLS silently filtered, data not visible	This is expected behavior, not an error

SDK Call Example

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

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

const db = app.rdb();

// Call RPC
const { data, error } = await db.rpc('rpc_place_order', {
  p_product_id: 1,
  p_quantity:   2,
});

// Get only the count (head + count)
const { count } = await db.rpc('search_articles', { keyword: 'CloudBase' }, {
  count: 'exact',
  head:  true,
});

// Apply filter / sort / pagination to an RPC returning SETOF table data
const { data: articles } = await db
  .rpc('search_articles', { keyword: 'CloudBase' })
  .eq('status', 'published')
  .order('published_at', { ascending: false })
  .limit(5);

For complete SDK usage, see JS SDK - RPC Calls.

Next Steps

• Architecture and Permission Model — complete database capabilities
• RLS Permission Pattern Library — various RLS patterns
• Tutorial: E-commerce Mini Program — complete example