Security & Authentication

2 min read

Quickstart

Security & Authentication

PgArachne relies entirely on the PostgreSQL permission system. It does not reinvent Access Control Lists (ACLs). All queries are executed under the specific database role of the authenticated user using SET LOCAL ROLE.

1. Interactive Login (JWT)

Users authenticate using their real PostgreSQL username and password via the login function. If successful, they receive a generic JWT. When this token is used, PgArachne switches the active role to that user for the duration of the request.

2. Service Accounts (API Tokens)

For automated systems or scripts, you can use long-lived API tokens.

Tokens are stored in the pgarachne.api_tokens table.
Each token is mapped to a specific database user/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 you authenticate users outside PgArachne (for example in an external auth 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 /api/:database).
Recommended claim: exp (Unix timestamp) for token expiration.

Minimal payload example:

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

Important: asymmetric JWT algorithms (for example RS256/ES256) are not accepted by the current server implementation.

Critical Configuration: Proxy Privileges

Since PgArachne connects as the user defined in DB_USER (e.g., pgarachne) and switches identity to other users, the proxy user must be a member of those target roles.

Run this SQL for every user/role that needs to log in:

-- Allow 'pgarachne' to switch to 'demo_user'
GRANT demo_user TO pgarachne;

Quickstart