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.

1. Interactive Login (JWT)

Users authenticate using their real PostgreSQL username and password via the get_jwt JSON-RPC method. 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.

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).
• Recommended claim: exp (Unix timestamp) for token expiration.

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).

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

Authentication Method Comparison

See also

• Configuration
• Error Codes