Security & Authentication

Security & Authentication

PgArachne relies entirely on the PostgreSQL permission system. It does not reinvent Access Control Lists (ACLs). PgArachne supports four authentication methods. Methods 1–3 use SET LOCAL ROLE to switch identity; method 4 (direct credentials) connects directly as the user, so no role switch is needed.

This applies to the JSON-RPC and MCP endpoints. The SSE endpoint is the one exception — it authenticates the caller but does not switch role or otherwise check per-channel permissions, since PostgreSQL LISTEN/NOTIFY channels are not database objects with their own GRANTs to enforce. See the SSE page for what that means in practice.

1. Interactive Login (JWT)

Users authenticate using their real PostgreSQL username and password with HTTP Basic authentication at POST /{prefix}/{database}/token. If successful, they receive a short-lived JWT. Subsequent requests carry this token in the Authorization: Bearer <token> header, and PgArachne switches the active role to that user for the duration of each request.

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

The credentials travel in the Authorization header, never in a JSON body, and the login has its own URL so a reverse proxy can rate-limit it without reading request bodies. The response is never cacheable.

Requires JWT_SECRET. When it is not configured, the endpoint returns HTTP 404 (-32601) and clients use direct credentials (method 4) or API tokens (method 2) instead.

2. Service Accounts (API Tokens)

For automated systems or scripts, long-lived API tokens are recommended.

• Tokens are stored in the pgarachne.api_tokens table.
• Each token is mapped to a specific database role.
• Send the token via the Authorization: Bearer <token> header.

Minting API tokens requires pgarachne_admin. Use pgarachne.add_api_token(...) with a role that is a member of pgarachne_admin.

3. External Identity Provider (Bring Your Own JWT)

If users are authenticated outside PgArachne (for example, via an external authentication service), you can mint JWTs there and send them directly to PgArachne.

• Header format: Authorization: Bearer <jwt>.
• Signing: HMAC only (HS256 / HS384 / HS512) using the same JWT_SECRET configured in PgArachne.
• Required claims: db_role (string, non-empty) and db_name (string, must match the :database path segment).
• Required claim: exp (Unix timestamp) for token expiration.

Requires JWT_SECRET; without it, Bearer values are only checked as API tokens.

Minimal payload example:

{
  "db_role": "demo_user",
  "db_name": "my_database",
  "exp": 1767225600
}

Note: asymmetric JWT algorithms (e.g. RS256 / ES256) are not supported.

4. Direct Database Credentials (Basic Auth)

The simplest option: send the PostgreSQL username and password with every request using standard HTTP Basic Authentication. PgArachne opens a dedicated connection pool authenticated directly as that user — SET LOCAL ROLE is not performed.

Authorization: Basic <base64(username:password)>

Example with curl:

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

Key differences from token-based methods:

• No GRANT demo_user TO pgarachne is required — PgArachne does not switch roles.
• PostgreSQL Row-Level Security and GRANT permissions are enforced as usual because the connection runs as the actual user.
• Connection pools are kept per user for efficiency, with a maximum lifetime of 5 minutes. After a password change, old connections expire within that window.
• For idempotency keys to work with direct credentials, grant the user EXECUTE ON FUNCTION pgarachne.save_idempotency_key(text, text).

-- Required for JWT / API-token auth only:
GRANT demo_user TO pgarachne;

Authentication Method Comparison

Which Functions Are Callable

PgArachne has no schema allowlist of its own — what a role may call is decided entirely by PostgreSQL privileges. A function is callable (and listed by capabilities, MCP tools/list and the OpenAPI export) when it takes a single jsonb parameter, the role has EXECUTE on it, and the role has USAGE on its schema. Functions in pg_catalog, information_schema, pgarachne and functions belonging to extensions are left out of the listing to keep it readable, but the same privileges apply to them.

-- New functions are not executable by everyone (run as the role that creates them):
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

-- Keep API functions in a dedicated schema and grant explicitly:
GRANT USAGE ON SCHEMA api TO demo_user;
GRANT EXECUTE ON FUNCTION api.hello_world(jsonb) TO demo_user;

Brute-Force Protection

LOGIN_RATE_LIMIT and LOGIN_RATE_LIMIT_PER_IP apply to every authentication method, on every endpoint (JSON-RPC, MCP, SSE, OpenAPI):

• /token: every attempt counts against the per-(IP, username) and per-IP budgets.
• Direct credentials (Basic Auth): failed attempts count against the per-(IP, username) and per-IP budgets. Successful requests are not counted, so a legitimate client sending credentials on every request is never throttled.
• Bearer tokens (JWT or API token): failed attempts count against the per-IP budget.

Once a budget is used up, further attempts are rejected with HTTP 429 until the window (LOGIN_RATE_WINDOW) has passed — without checking the credentials at all.