JSON-RPC

6 min read

POST /{prefix}/{database}/jsonrpc calls a PostgreSQL function and returns its result as a JSON-RPC 2.0 response. This page is the reference; for a first working example see the Quick Start.

1. Request

POST /db/my_database/jsonrpc
Authorization: Basic ...            (or Bearer <jwt / api token>)
Content-Type: application/json

{"jsonrpc": "2.0", "method": "api.hello_world", "params": {"name": "Alice"}, "id": 1}

Field	Description
`method`	Required. `schema.function`, at most 256 characters. Quoted identifiers (`"my schema"."my fn"`) are allowed. The schema is mandatory: `hello_world` alone is rejected with 400. `capabilities` is a built-in method (section 5).
`params`	Any JSON value, passed to the function unchanged as its `jsonb` argument. Missing or `null` becomes `{}`. Objects are the convention; arrays and scalars also work.
`id`	Any JSON value; it is copied into the response. If it is missing the response contains `"id": null` (there are no fire-and-forget notifications).
`jsonrpc`	Conventionally `"2.0"`; it is not validated on input, the response always carries `"2.0"`.
`idempotencyKey`	Optional extension (section 7). At most 256 characters.

Batch requests (a JSON array of calls) are not supported and return 400 — send one call per HTTP request.

2. Response

HTTP 200  {"jsonrpc":"2.0","result":{"message":"Hello, Alice!"},"id":1}

HTTP 404  {"jsonrpc":"2.0","error":{"code":-32601,"message":"Function does not exist"},"id":1}

Unlike plain JSON-RPC over a socket, errors also set a meaningful HTTP status, so clients and proxies can react without parsing the body. The error.code is 0 unless a specific JSON-RPC code applies (-32601 unknown function, -32001 permission denied, -32000 duplicate request). The full list is on the Error Codes page.

3. Writing a function

A method is a PostgreSQL function that takes exactly one argument of type jsonb and returns json or jsonb. Functions with other signatures are neither listed by capabilities nor callable.

CREATE OR REPLACE FUNCTION api.find_user(payload jsonb DEFAULT '{}'::jsonb)
RETURNS json LANGUAGE sql STABLE AS $$
    SELECT row_to_json(u) FROM (
        SELECT id, name FROM app.users WHERE id = (payload->>'id')::int
    ) u;
$$;

GRANT USAGE ON SCHEMA api TO web_user;
GRANT EXECUTE ON FUNCTION api.find_user(jsonb) TO web_user;

Return value. The JSON result is sent as result. A SQL NULL (as above, when no row matches) becomes "result": null. The function must return valid JSON: a text return type fails with 500 unless the text happens to be JSON — use to_json('hi'::text) for a string result.
Errors inside the function. RAISE EXCEPTION or any SQL error aborts the call with HTTP 500 and the generic message Function call failed; the real PostgreSQL message is only written to the server log, so schema details do not leak. Report expected business errors (not found, validation) in the return value instead, for example {"ok": false, "reason": "..."}.
Transactions. Each call runs in one transaction that is committed when the function returns and rolled back on any error.
Permissions. The call runs as the authenticated PostgreSQL role, so GRANT, REVOKE and Row-Level Security decide what a caller can do. A role needs USAGE on the schema and EXECUTE on the function. PostgreSQL grants EXECUTE on new functions to everyone by default, so revoke that (ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC) and keep exposed functions in a dedicated schema — see Security.

4. Describing a function for clients and AI agents

The first line of the function’s comment is its description; a JSON object after --- PARAMS --- describes the parameters (JSON Schema properties). Both appear in capabilities, the OpenAPI spec and MCP tools/list:

COMMENT ON FUNCTION api.find_user(jsonb) IS 'Looks up a user by id.
--- PARAMS ---
{"id": {"type": "integer", "description": "User id"}}';

A comment with malformed JSON after the marker is ignored (the function falls back to a generic parameter description) and never breaks discovery.

5. Built-in methods

`capabilities`

Lists the functions the authenticated role may call (EXECUTE and schema USAGE); system schemas and extension functions are hidden. Each entry has method, description, parameters, http_method, endpoint and kind (rpc, or file for file functions).

{"jsonrpc":"2.0","method":"capabilities","id":1}

Logging in for a JWT is not a JSON-RPC method: use POST /{prefix}/{database}/token with HTTP Basic credentials (section 6). The old method get_jwt, which took the password in params, was removed and answers 404 / -32601 with a pointer to the new endpoint.

6. Authentication

Every request needs an Authorization header; a request without one is rejected with 401 before any database connection is opened.

Header	What happens
`Basic <base64(user:password)>`	A connection pool authenticated as that PostgreSQL user. Simplest; no tokens. Send it over HTTPS only.
`Bearer <jwt>`	Token from `POST /{prefix}/{database}/token` (or your own identity provider). PgArachne switches to the token’s role with `SET LOCAL ROLE`; the service role needs `GRANT user TO pgarachne`.
`Bearer <api token>`	Long-lived token minted with `pgarachne.add_api_token(...)`, bound to a role. Good for services.

Getting a JWT: `POST /{prefix}/{database}/token`

Exchanges a PostgreSQL login and password, sent with HTTP Basic authentication, for a JWT. The credentials travel in the Authorization header — never in a JSON body — and the endpoint has its own URL, so a reverse proxy can apply stricter limits to logins without reading request bodies. It requires JWT_SECRET; otherwise it returns 404. Every attempt counts against the login rate limit and the response is never cacheable.

curl -X POST http://localhost:8080/db/my_database/token -u web_user:password
→ {"token":"eyJhbGciOi...","token_type":"Bearer","expires_in":28800}

curl http://localhost:8080/db/my_database/jsonrpc -H "Authorization: Bearer eyJhbGciOi..." \
  -H "Content-Type: application/json" -d '{"jsonrpc":"2.0","method":"capabilities","id":1}'

Failed attempts are rate limited (HTTP 429). All details, token lifetimes and an external identity provider are on the Security & Authentication page.

7. Idempotency

Add "idempotencyKey": "order-42" to make a call safe to retry. The first call with a key runs normally; a repeated key is rejected before the function runs with HTTP 409 and -32000 “This request has already been processed”. The key is stored in the same transaction as the call, so a call that failed (and rolled back) can be retried with the same key. Keys are namespaced per role.

With JWT or API token authentication this works out of the box.
With Basic authentication the user itself writes the key, so it needs USAGE on schema pgarachne and INSERT on pgarachne.requests (otherwise the call fails with 500 Idempotency check failed).
Old keys are not removed automatically — schedule pgarachne.cleanup_idempotency_keys() (see Idempotency Key Cleanup).

8. Limits

Request body: MAX_REQUEST_BYTES (default 2 MiB), otherwise 413.
method and idempotencyKey: 256 characters each.
Authentication failures: LOGIN_RATE_LIMIT / LOGIN_RATE_LIMIT_PER_IP per LOGIN_RATE_WINDOW (see Configuration).

9. Calling it

curl -X POST http://localhost:8080/db/my_database/jsonrpc \
  -u web_user:password -H "Content-Type: application/json" \
  -d '{"jsonrpc":"2.0","method":"api.find_user","params":{"id":1},"id":1}'

From a web page use fetch() with the same header and body; a complete, copy-and-paste page (including SSE and file download) is in the Quick Start.

← Configuration MCP →

JSON-RPC

JSON-RPC

1. Request

2. Response

3. Writing a function

4. Describing a function for clients and AI agents

5. Built-in methods

capabilities

6. Authentication

Getting a JWT: POST /{prefix}/{database}/token

7. Idempotency

8. Limits

9. Calling it

`capabilities`

Getting a JWT: `POST /{prefix}/{database}/token`