Keeping kdb serve safe

Keep the server on 127.0.0.1, use users or a token, put HTTPS in front, and pick strong passwords.

• Category: Users & security
• Keywords: security, safe, secure, KDB_TOKEN, Docker, internet, vps, firewall, --bind, --token, bind address, 0.0.0.0, 127.0.0.1, localhost, access token, login, log in, logout, HTTP Basic, Bearer, cookie, kdb_login, kdb_auth, X-KDB-CSRF, HTTPS, TLS, SSL, certificate, reverse proxy, Caddy, nginx, SSH tunnel, X-Forwarded-Proto, failed logins, too many failed logins, 429, 401, 403, cross-site, CSRF, DNS rebinding, password rules, SCRAM, checklist
• See also: Users and roles, Security functions, kdb serve, kdb user, Logins, tokens and roles over HTTP, PostgreSQL protocol (port 8832), Backup, check, repair and restore

kdb serve gives full access to your data over the network: the HTTP API, the web console and the PostgreSQL port. This page says how to keep strangers out.

The short rules:

1. Keep the default --bind 127.0.0.1. Then only programs on the same computer can connect.
2. Create users with long passwords (see Users and roles). Then every request needs a login.
3. For access from other computers, turn on TLS (--tls-cert: https and sslmode=require), or put a web server with HTTPS in front, or use an SSH tunnel.
4. Keep the data folder and the backups private: whoever can read them can read everything, without a login.

HTTP: Is a login needed on this server?

GET /health

Response:

HTTP 200 (application/json)

{
  "status": "ok",
  "version": "0.2.0-dev",
  "server_time_ms": 1792647852082,
  "auth_required": false,
  "login": false,
  "tls": false,
  "ready": true,
  "standby": false
}

--bind

Syntax:

kdb serve [--bind 127.0.0.1 | --bind <address>] ...

The network address the server listens on. It is used for the HTTP port and the PostgreSQL port.

• Returns: nothing; the start-up lines show the console address.
• Notes:
  • Any address that does not start with 127. needs users or --token, or kdb serve refuses to start. (localhost counts as local.)
  • Your app's backend on the same VPS reaches KDB on 127.0.0.1. You almost never need 0.0.0.0.
  • The Docker image must listen on 0.0.0.0, because a published port cannot reach the container's own 127.0.0.1. So it refuses to start without KDB_TOKEN (-e KDB_TOKEN=<token>). Publish the port only to where it is needed, for example -p 127.0.0.1:9900:9900.
  • Also close ports 9900 (HTTP) and 8832 (PostgreSQL) in the firewall. Open only SSH, 80 and 443.
  • Without a login, KDB still refuses requests that a browser marks as coming from another web site, and requests whose Host is not localhost or 127.x (a trick called DNS rebinding). So a bad web page open in your browser can not reach your local server.

Command line: 0.0.0.0 without users and without a token is refused (shows an error)

kdb serve --bind 0.0.0.0

Output:

error: --bind 0.0.0.0 without --token and without users would let anyone who can reach this computer
read, change and delete every database. Do one of these first:
  1. create an admin user:   kdb user add <name> --role admin
  2. or start with a secret:  --token <long random text>
  3. or keep --bind 127.0.0.1 and put a web server with HTTPS in front of KDB
See docs/tutorial/16-users-passwords-security.md

exit code 1

HTTP: A request from another web site is refused

GET /exec?query=SELECT%201
Sec-Fetch-Site: cross-site

Response:

HTTP 403 (application/json)

{"error": "cross-site requests are not allowed"}

HTTP: A request sent by a page of another web site is refused

GET /exec?query=SELECT%201
Origin: http://evil.example.com

Response:

HTTP 403 (application/json)

{"error": "cross-origin requests are not allowed"}

--token

Syntax:

kdb serve --token <long random text>

A secret that every request must send, as Authorization: Bearer <token>. It gives full rights (like an admin on every database).

• Returns: nothing; the start-up line access says token required.
• Notes:
  • The token is one secret for everybody. Users are better: each program gets its own password and only the role it needs, and you can remove one user without changing the others.
  • With users and a token, both work. PostgreSQL clients can use the token as the password.
  • Other people on the same computer can see command lines (ps, Task Manager). Start the server from a file only you can read (for example a systemd unit), or use users.
  • The web console asks for the token and the server keeps it in an HttpOnly, SameSite=Strict cookie (kdb_auth), so page scripts cannot read it. Changes sent with that cookie also need the X-KDB-CSRF header (CSRF); the console sends it by itself.

Example: Make a strong token or password

SELECT random_token() AS token_for_kdb_serve, random_token(24) AS password_for_a_program;

Result:

(1 row)

Command line: A server with a token

kdb serve --token 3kD9vQx7LmN2pR8sT4wY6zA1bC5eF0gH

Output:

KDB 0.2.0-dev
  console   http://127.0.0.1:9900
  data      ./kdb-data (from KDB_DATA_DIR; one folder per database)
  default   default (used when a request names no database)
  backups   ./kdb-data/_backups/<database name> (from default)
  access    token required
  capacity  32 HTTP threads (default), 500 PostgreSQL connections (default), lock wait 5 s (default)
  lanes     reads using 100 ms of CPU (three times in a row) wait for one of 16 heavy slots, at most 16 HTTP in line (--lanes on)
  reads     32 reads run at once (the CPUs, at least 2; follows the CPU limit), the rest wait in line (--read-slots)
  commits   synced to disk before they are confirmed (--sync-commits always)
  time zone Asia/Katmandu (the computer's); also timers and backup schedules
  readers   read while a long trigger or CALL writes (--reader-yield on); idle open transaction rolled back after 30 s (default)
  memory    26.95 GB for the whole process (85% of the machine's memory); table data 512.0 MB of it (--memory)
  cpu       32 CPUs (the machine's); 16 query threads (half of the CPUs, follows the limit), limits checked every 10 s
  limits    statement timeout 1 min, result memory 1.00 GB, 5000000 groups, 32 statements per user
  safety    writes allowed; DROP TABLE kept 7 days (UNDROP TABLE); audit log on (SHOW AUDIT)
  logs      slow queries >= 1000 ms to ./kdb-data/_logs/slow_queries.jsonl; pg_stat_statements on; metrics at /metrics
  recovery  the default database is created when it is first used

Press Ctrl+C to stop.

Users and logins

Syntax:

HTTP Basic:   Authorization: Basic base64(user:password)
Log in once:  POST /api/login  {"user": "...", "password": "..."}   ->  token
Then:         Authorization: Bearer <token>     (or the kdb_login cookie in a browser)
Log out:      POST /api/logout

When the data folder has users, every request needs a login. Create users with CREATE USER or kdb user add. The details of each route are in HTTP login.

• Returns: a wrong login gets HTTP 401 {"error":"wrong user name or password","login":true}. A statement the user may not run gets HTTP 403 (see roles).
• Notes:
  • A login ends after 12 hours, at log out, when the server restarts, and when the user's password changes or the user is dropped.
  • Failed logins: after 5 failures for one user from one address (or 20 from one address for any users), that address must wait: 1 second, then 2, 4, 8, ... up to 15 minutes. Meanwhile the answer is HTTP 429 too many failed logins; try again in 1 s. A good login ends the wait for that user.
  • Every failed login is written to the server's output with the user name and the address, never the password: login failed: user 'admin' from 127.0.0.1 (wrong password; 5 failures in a row); next try allowed in 1 s.
  • The answer for an unknown user takes as long as for a wrong password, so nobody can find out which names exist.
  • Behind a reverse proxy every request comes from 127.0.0.1, so the waiting is shared by everyone who tries the same user name.
  • kdb sql and kdb shell never ask for a login. They open the files directly.
  • Users belong to a data folder. A server started with one database folder by its path (kdb serve D:\kdb\market) has no users; use --data-dir.

Command line: With a user, the server asks for a login

kdb user add admin --role admin
> Long-Admin-Passw0rd
> Long-Admin-Passw0rd
kdb serve

Output:

> kdb user add admin --role admin

user 'admin' added with role admin in ./kdb-data

> kdb serve

KDB 0.2.0-dev
  console   http://127.0.0.1:9900
  data      ./kdb-data (from KDB_DATA_DIR; one folder per database)
  default   default (used when a request names no database)
  backups   ./kdb-data/_backups/<database name> (from default)
  access    login required (1 user)
  capacity  32 HTTP threads (default), 500 PostgreSQL connections (default), lock wait 5 s (default)
  lanes     reads using 100 ms of CPU (three times in a row) wait for one of 16 heavy slots, at most 16 HTTP in line (--lanes on)
  reads     32 reads run at once (the CPUs, at least 2; follows the CPU limit), the rest wait in line (--read-slots)
  commits   synced to disk before they are confirmed (--sync-commits always)
  time zone Asia/Katmandu (the computer's); also timers and backup schedules
  readers   read while a long trigger or CALL writes (--reader-yield on); idle open transaction rolled back after 30 s (default)
  memory    26.95 GB for the whole process (85% of the machine's memory); table data 512.0 MB of it (--memory)
  cpu       32 CPUs (the machine's); 16 query threads (half of the CPUs, follows the limit), limits checked every 10 s
  limits    statement timeout 1 min, result memory 1.00 GB, 5000000 groups, 32 statements per user
  safety    writes allowed; DROP TABLE kept 7 days (UNDROP TABLE); audit log on (SHOW AUDIT)
  logs      slow queries >= 1000 ms to ./kdb-data/_logs/slow_queries.jsonl; pg_stat_statements on; metrics at /metrics
  recovery  the default database is created when it is first used

Press Ctrl+C to stop.

HTTP: A server without users has nothing to log in to

POST /api/login
Content-Type: application/json

{"user": "admin", "password": "Long-Admin-Passw0rd"}

Response:

HTTP 400 (application/json)

{
  "error": "this server has no users yet; create one with CREATE USER <name> WITH PASSWORD '...' ROLE admin"
}

HTTPS in front

Syntax:

browser  --HTTPS-->  Caddy or nginx (port 443)  --HTTP-->  kdb serve on 127.0.0.1:9900

Over plain HTTP, anyone on the network path can read passwords and tokens. When people or programs on other computers must reach KDB, either turn on KDB's own TLS, or put a web server with HTTPS (a reverse proxy) in front and keep KDB on 127.0.0.1. A proxy also gets free certificates by itself and renews them.

• Returns: not a KDB setting.
• Notes:
  • Caddy gets a free certificate by itself (see the first block below). With nginx, also send X-Forwarded-Proto (second block).
  • When the proxy sends X-Forwarded-Proto: https, KDB marks its login cookie Secure (only sent over HTTPS).
  • KDB still asks for the login behind the proxy. Create users first.
  • Even simpler for yourself: do not open the console to the internet at all. Use an SSH tunnel from your PC: ssh -L 9900:127.0.0.1:9900 you@your-vps, then open http://127.0.0.1:9900 on your PC.
  • A proxy does not cover the PostgreSQL port. Turn on KDB's TLS for it (sslmode=require), or reach it through an SSH tunnel.

Caddy, file /etc/caddy/Caddyfile:

kdb.example.com {
    reverse_proxy 127.0.0.1:9900
}

nginx, inside the server { listen 443 ssl; ... } block:

location / {
    proxy_pass http://127.0.0.1:9900;
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-Proto $scheme;
}

Password rules

Syntax:

at least 12 characters, not the user name, at most 1024 bytes;  long and random is best

What KDB needs from the password of a database user, and what is wise.

• Returns: a too short password gets the password is too short: use at least 12 characters (a password manager can make a long random one). Passwords set before this rule keep working.
• Notes:
  • KDB keeps two things per user: a PBKDF2-SHA-256 hash with 600,000 rounds (slow to guess), and a SCRAM-SHA-256 secret with only 4,096 rounds (the PostgreSQL default, faster to guess). This is why database users should have long random passwords.
  • kdb user add reads the password from a prompt or a pipe (echo "the password" | kdb user add report --role read), never from the command line.
  • SQL text with PASSWORD '...' may end up in your own scripts and logs. The web console's history and KDB's statement echo show PASSWORD '***'.
  • The passwords of your app's users are a different matter: store them with password_hash.

Example: A too short password is refused (shows an error)

CREATE USER admin WITH PASSWORD 'ktm2026' ROLE admin;

Result:

Error: the password is too short: use at least 12 characters (a password manager can make a long random one)

Checklist

Syntax:

bind 127.0.0.1  +  users  +  HTTPS or SSH tunnel  +  firewall  +  private data and backups

A small Linux VPS with your app and KDB on the same machine. Chapter 16 shows each step with commands.

• Returns: not a statement.
• Notes: rights are per database; table rights change them for single tables (no column or row rights). Logins are kept in the data folder (hashes only), so a restart logs nobody out. Give each app its own API token. More protection (timeouts, audit log, scheduled backups): protection.