Protection - timeouts, limits, safe updates, read-only

Stop slow statements (timeout and cancel), limit the memory of one query and the statements of one user, refuse UPDATE or DELETE without WHERE, and make a database read-only.

• Category: Users & security
• Keywords: protection, statement timeout, statement_timeout, timeout, cancel, cancel query, stop query, kill query, CancelRequest, 57014, query_canceled, X-KDB-Request-Id, X-KDB-Statement-Timeout, X-KDB-Safe-Updates, memory limit, too many rows, too many groups, 54000, max_result_memory, max_groups, safe updates, safe_updates, read-only, READ, ONLY, WRITE, --statement-timeout, --max-result-memory, --max-groups, --max-queries-per-user, --rate-limit-ip, --rate-limit-user, rate limit, 53400, --safe-updates, --read-only, 408, 413, 429, 503, Retry-After, drain, --drain-timeout, graceful shutdown
• See also: Keeping kdb serve safe, Audit log, The trash - UNDROP TABLE, Table rights - GRANT ON TABLE, Two-factor login (TOTP), Backups on a schedule, kdb serve, PostgreSQL protocol (port 8832), Run SQL over HTTP (/exec, /exp)

These settings keep one bad query, one busy user or one typing mistake from hurting the server. They cost almost nothing: a running query looks at one flag every 16,384 rows, never at every row.

kdb sql, kdb shell and programs that use the KDB library have no limits unless they set DatabaseOptions.

SET statement_timeout

Syntax:

kdb serve --statement-timeout 60s          (0 = no limit)
SET statement_timeout = '5s' | 5000 | 0 | DEFAULT
HTTP header:  X-KDB-Statement-Timeout: 5s

A statement (one request, or one Postgres query message) that runs longer is stopped. Nothing of a stopped change is written: an UPDATE stopped halfway changes no row.

• Returns: the error canceling statement due to statement timeout (5 s); .... HTTP 408, PostgreSQL SQLSTATE 57014.
• Notes:
  • A user who is not an admin can only make the limit shorter.
  • An admin can give one user their own limit, shorter or longer than the server's: ALTER USER charts SET statement_timeout = '2s' (see Users and roles). That limit then stands in for --statement-timeout for that user.
  • Over HTTP, SET statement_timeout lasts for the rest of the request (a shorter time counts at once, a longer one from the next request), or for the whole HTTP session when one is used. The header works for one request.
  • Waiting for another session's transaction (at most 5 s) counts too. A statement that waits for another writer (a long CALL, a trigger job, a load) stops at its time limit or when cancelled, within a few milliseconds, not when that writer is done.
  • The limit is checked inside every long loop: table scans, rows made by generate_series, joins, GROUP BY, window functions, WHILE loops and each statement of a CALL. A statement stops within a fraction of a second after its time is up. Sorting millions of rows, or freeing the memory they took, can add a little.
  • A CALL stopped inside its body says where first (procedure 'p', line 4: canceling statement due to statement timeout ...). It is still HTTP 408 and SQLSTATE 57014.
  • Triggers count toward the timeout of the statement that fired them. This is also true for POST /write and POST /imp: the header X-KDB-Statement-Timeout works there too, with the same rules. A trigger that runs too long gives trigger <name>: statement timeout (<N> s) (HTTP 408), and nothing of the request is written.

Example: A time for the rest of this session (sample data: sample)

SET statement_timeout = '5s';
SELECT count(*) FROM ochl_day;

Result:

SET statement_timeout = '5s'

statement_timeout is 5 s for this session

SELECT count(*) FROM ochl_day

(1 row)

Cancel a running statement

Syntax:

PostgreSQL:  CancelRequest (pgx: cancel the context; psql: Ctrl+C)
HTTP:        POST /exec with X-KDB-Request-Id: <id>, then DELETE /exec/<id>
HTTP:        close the connection (the browser or program gave up)
SQL:         SELECT pg_cancel_backend(pid)   (pid from pg_stat_activity; pg_terminate_backend also closes it)

Stops a statement that is running now.

• Returns: the stopped statement gets canceling statement due to user request (HTTP 408, SQLSTATE 57014).
• Notes:
  • The Postgres port answers the CancelRequest of the connection's key only, like PostgreSQL.
  • When an HTTP client closes the connection, KDB notices it within a fifth of a second and stops the statement.
  • From SQL, over either port: find the statement in pg_stat_activity and stop it with pg_cancel_backend. Users may stop only their own; admins every one.

DELETE /exec/<id>

Syntax:

DELETE /exec/<id>

Stops the /exec request that was sent with the header X-KDB-Request-Id: <id>. The id is chosen by the program (for example a random text).

• Returns: {"request_id":"<id>","cancelled":true}, or HTTP 404 when no request with that id is running (it finished).
• Notes: users may stop only their own requests; admins may stop every request. A second request with the same id while the first runs gets HTTP 409.

HTTP: Nothing with this id is running

DELETE /exec/report-17

Response:

HTTP 404 (application/json)

{"error": "no running request has the id 'report-17' (it may have finished)"}

Memory limits

Syntax:

kdb serve --max-result-memory 1GB --max-groups 5000000        (0 = no limit)

Stops a query that would keep too much in memory: the rows of the result, rows waiting for ORDER BY, DISTINCT or window functions, and the groups of GROUP BY, SAMPLE BY and LATEST ON.

• Returns: the query keeps too many rows in memory (about N MB; the limit is M MB, max_result_memory) or the query makes more than N groups (max_groups). HTTP 413, SQLSTATE 54000.
• Notes: add a WHERE or a LIMIT, group by fewer columns, or read a big result in parts with a cursor.
• Big answers over the Postgres port stream: a plain read (no ORDER BY, GROUP BY, DISTINCT or OFFSET) sends its rows while it reads them, so this limit does not stop it (big answers stream).
• INSERT ... SELECT: a plain copy (no GROUP BY, DISTINCT, window, ORDER BY or LIMIT) writes the rows while it reads them, so this limit does not stop it (INSERT ... SELECT).
• GROUP BY and SAMPLE BY bigger than memory: when the groups of a query need more than half of --max-result-memory, the rows of the groups that are not in memory yet go to sorted temporary files in the database folder (_tmp/) and are added up after the scan, one group at a time. The result is exactly the same, only slower. The files are removed when the query ends, fails or is cancelled, and when the database opens after a crash. One query may write at most 16 GB of them (DatabaseOptions::max_spill_bytes); past that it stops with the GROUP BY needs more than N MB of temporary files (max_spill_bytes). A read-only database never writes them.

--max-queries-per-user

Syntax:

kdb serve --max-queries-per-user 32        (0 = no limit)

How many statements one user may run at the same time, over HTTP and the Postgres port together.

• Returns: HTTP 429 user 'x' already runs 32 statements at the same time; on the Postgres port SQLSTATE 55P03.
• Notes: the --token and a server without users are not counted. A connection pool of your app normally stays below the limit.

--rate-limit-ip and --rate-limit-user

Syntax:

kdb serve --rate-limit-ip 50 --rate-limit-user 20       (requests per second, 0 = no limit)
kdb.conf:  "rate_limit_ip": 50, "rate_limit_user": 20
env:       KDB_RATE_LIMIT_IP=50  KDB_RATE_LIMIT_USER=20

How many requests one client address, and one user, may send per second. Both are off unless set.

• Returns: HTTP 429 with a Retry-After header (whole seconds) and rate limit: user 'charts' sends more than 20 requests per second (--rate-limit-user); try again in 1 s. On the PostgreSQL port SQLSTATE 53400. Nothing of the request runs. Inside BEGIN ... COMMIT on the PostgreSQL port the refusal is an error like any other: the transaction is aborted, and COMMIT rolls it back.
• Notes:
  • Each address and each user has a "bucket" of N requests that refills at N per second. A client may send N at once, then N per second on average.
  • The address limit is checked before the login, so a flood of wrong passwords is slowed too. The user limit is checked after it.
  • Requests from this computer (127.x) are never limited by address, so the console and health checks always get through. The --token (no user) is limited only by address.
  • The web console's files, /health and /help are not counted.
  • Behind Docker's port mapping or a proxy, every client may reach KDB from the same address. Then use --rate-limit-user, or set --rate-limit-ip high.
  • Read when the server starts. The Settings page shows the values in use.

SET safe_updates

Syntax:

kdb serve --safe-updates
SET safe_updates = on | off
HTTP header:  X-KDB-Safe-Updates: on | off

Refuses UPDATE and DELETE without WHERE, the classic mistake that changes every row.

• Returns: safe updates are on: an UPDATE without WHERE would change every row of 't'. Add a WHERE ...
• Notes: WHERE true changes every row on purpose. --safe-updates makes it the start value of every session; a session can turn it off.

Example: UPDATE without WHERE is refused (sample data: sample; shows an error)

SET safe_updates = on;
UPDATE securities SET name = 'oops';

Result:

safe updates are on for this session

Error: safe updates are on: an UPDATE without WHERE would change every row of 'securities'. Add a WHERE (WHERE true changes every row on purpose), or turn safe updates off for this session: SET safe_updates = off

ALTER DATABASE ... SET READ ONLY

Syntax:

ALTER DATABASE <name> SET READ ONLY
ALTER DATABASE <name> SET READ WRITE
kdb serve --read-only

A read-only database takes no changes: INSERT, UPDATE, DELETE, DDL, CALL, the line protocol. SELECT, backups and checks work.

• Returns: database 'x' is read-only now: only reads are allowed. A refused change gets the database is read-only (...), HTTP 403, SQLSTATE 25006.
• Notes:
  • Needs a server admin. The setting is kept in the data folder (_system) and holds after a restart. A read-only database cannot be dropped.
  • kdb serve --read-only makes every database read-only, and refuses new databases and user changes. Good for a report server on a copy of the data.

Example: Read-only, then writable again (shows an error)

ALTER DATABASE demo SET READ ONLY;
SELECT count(*) FROM securities;
INSERT INTO brokers VALUES (99, 'New broker');

Result:

database 'demo' is read-only now: only reads are allowed

Error: the database is read-only (ALTER DATABASE demo SET READ ONLY; undo with SET READ WRITE): only reads are allowed

Stopping the server without cutting requests off

Syntax:

kdb serve --drain-timeout 30s          (0 = do not wait; env KDB_DRAIN_TIMEOUT; 5s in the Docker image)

When the server is asked to stop (Ctrl+C, SIGTERM, docker stop), it does not drop what is running:

• GET /health also answers 503 while the server stops, so a load balancer or Docker's health check stops sending traffic.
• The 503 body is {"error": "the server is shutting down; send the request again in a moment"}, and the server log shows the request with 503 0 ms (stopping).
• There is no drain when the server restarts itself (POST /api/restart) or when its Windows console window is closed (Windows gives it only about 4 seconds). Running statements are cancelled at once then.
• The log says stopping: running requests finished in N ms, or stopping: requests still running after 30s (--drain-timeout); they are cancelled.
• Clients should send a request again after a 503, a refused connection or a connection closed before the reply. A cut-off /write or /exec is rolled back as a whole, so repeating it is safe.
• Keep the drain time below how long your service manager waits before it kills the process. See Docker.