Audit log

KDB writes security events (logins, users, DDL, backups, refused rights) and, for chosen tables, every changed row with who, when, old and new values.

• Category: Users & security
• Keywords: audit, audit log, audit trail, hash chain, tamper, tamper-evident, SHOW AUDIT VERIFY, verify, user agent, User-Agent, application_name, agent, --token-name, KDB_TOKEN_NAME, who changed, history of changes, login history, failed logins, lockout, _audit_log, AUDIT, SET AUDIT ON, SET AUDIT OFF, old values, new values, accounting, compliance, --audit, --audit-keep-days, retention, erase user, delete user data, right to be forgotten, GDPR, privacy, personal data, erasure
• See also: Protection - timeouts, limits, safe updates, read-only, Users and roles, Keeping kdb serve safe, Table rights - GRANT ON TABLE, Two-factor login (TOTP), Logins, tokens and roles over HTTP

The audit log is a table named _audit_log. KDB writes it; nobody can change, delete or drop its rows (it is append-only). It goes through the log like any change, so it survives crashes, and backups hold it.

Columns of _audit_log: time, event, user, client, db, object, detail, row_key, old_values (JSON), new_values (JSON), session, ok, seq, hash, agent.

client is the address and protocol (10.0.0.5 (http)). agent is the program that sent the request: the HTTP User-Agent header (curl/8.4.0, the browser of the web console), or the PostgreSQL application_name (psql, DBeaver). It is at most 200 characters, and NULL when the client sent none. A client can write anything there, so it helps to tell programs apart but proves nothing.

An action done with the server's --token is written with user = token, or the name given with --token-name (or KDB_TOKEN_NAME), never with an empty user.

SHOW AUDIT

Syntax:

SHOW AUDIT [LIMIT n] [WHERE <condition>] [LIMIT n]

The newest security events first (100 when there is no LIMIT). The condition uses the columns above.

• Returns: rows of _audit_log of _system (on a server that opened one folder by its path: of that database).
• Notes: needs a server admin. The condition may only use the audit rows: no subqueries of other tables.

Example: Failed logins of the last day

SHOW AUDIT WHERE event = 'login_failed' AND time > dateadd('d', -1, now()) LIMIT 20;

Result:

(0 rows)

no audit events yet

ALTER TABLE ... SET AUDIT ON

Syntax:

ALTER TABLE <name> SET AUDIT ON
ALTER TABLE <name> SET AUDIT OFF

From now on, every row a statement adds, changes or deletes in the table is also written to _audit_log of the database, in the same transaction as the change: there is never a change without its audit row, or an audit row without its change.

• Returns: audit is on for table 'accounts': every changed row is written to _audit_log.
• Notes:
  • Tables without SET AUDIT ON cost nothing extra. An audited table's writes cost one row lookup and one audit row per changed row.
  • An UPDATE that changes nothing in a row writes no audit row. Rolled back transactions write none.
  • Read the rows with SELECT ... FROM _audit_log (needs role admin on the database).
  • Older KDB programs refuse a database with an audited table (the setting would be lost).

Example: Audit an accounts table

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING, balance DECIMAL(2));
ALTER TABLE accounts SET AUDIT ON;
INSERT INTO accounts VALUES (1, 'Sita', 100.00);
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
SELECT event, row_key, old_values, new_values FROM _audit_log ORDER BY time;

Result:

CREATE TABLE accounts (

table 'accounts' created

ALTER TABLE accounts SET AUDIT ON

audit is on for table 'accounts': every changed row is written to _audit_log

INSERT INTO accounts VALUES (1, 'Sita', 100.00)

1 row(s) inserted

UPDATE accounts SET balance = balance - 25 WHERE id = 1

1 row(s) updated

SELECT event, row_key, old_values, new_values FROM _audit_log ORDER BY time

(2 rows)

Example: Nobody can change the audit rows (shows an error)

CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL(2));
ALTER TABLE accounts SET AUDIT ON;
DELETE FROM _audit_log;

Result:

CREATE TABLE accounts (

table 'accounts' created

ALTER TABLE accounts SET AUDIT ON

audit is on for table 'accounts': every changed row is written to _audit_log

Error: the audit table _audit_log is append-only: KDB writes it, nobody can change or drop it (old rows are removed by the audit retention setting)

The hash chain

Every row gets a number and a hash, so a row changed or removed outside KDB (by editing the files) is found.

A changed row no longer matches its hash. A removed row leaves a gap in seq. SHOW AUDIT VERIFY, kdb check <database> and POST /api/check check the whole chain and name the rows that do not fit.

• Returns: kdb check prints audit chain: N row(s) checked, seq 1 to N, newest hash ..., or a TAMPERED: line per problem (exit code 2).
• Notes:
  • Note the newest hash somewhere else now and then (a ticket, an email). Then even the newest rows cannot be cut off unseen.
  • Rows written before KDB had the chain keep seq and hash empty; the check counts them apart.
  • When --audit-keep-days removes old months, the check starts at the oldest row left.
  • The agent column came after the chain. It is in the hashed values only when it has a value: older rows (no agent) keep the hash they were written with, and a row's agent can not be changed or cleared unseen.

Example: The chain of an audited table

CREATE TABLE ledger (id INT PRIMARY KEY, amount DECIMAL(2));
ALTER TABLE ledger SET AUDIT ON;
INSERT INTO ledger VALUES (1, 10.00), (2, 20.00);
SELECT seq, event, row_key, length(hash) AS hash_length FROM _audit_log ORDER BY seq;

Result:

CREATE TABLE ledger (

table 'ledger' created

ALTER TABLE ledger SET AUDIT ON

audit is on for table 'ledger': every changed row is written to _audit_log

INSERT INTO ledger VALUES (1, 10.00), (2, 20.00)

2 row(s) inserted

SELECT seq, event, row_key, length(hash) AS hash_length FROM _audit_log ORDER BY seq

(2 rows)

SHOW AUDIT VERIFY

Syntax:

SHOW AUDIT VERIFY

Checks the hash chain from SQL, the same check as kdb check, without stopping the server.

• Returns: one row per audit log, and the message the audit hash chain is intact (N rows checked) or the audit hash chain is broken: ....
• Notes:
  • Needs a server admin, like SHOW AUDIT.
  • It reads the whole chain: on a log of millions of rows it takes a few seconds.

Example: Check the chain of an audited table

CREATE TABLE ledger (id INT PRIMARY KEY, amount DECIMAL(2));
ALTER TABLE ledger SET AUDIT ON;
INSERT INTO ledger VALUES (1, 10.00), (2, 20.00);
UPDATE ledger SET amount = 15.00 WHERE id = 1;
SHOW AUDIT VERIFY;

Result:

CREATE TABLE ledger (

table 'ledger' created

ALTER TABLE ledger SET AUDIT ON

audit is on for table 'ledger': every changed row is written to _audit_log

INSERT INTO ledger VALUES (1, 10.00), (2, 20.00)

2 row(s) inserted

UPDATE ledger SET amount = 15.00 WHERE id = 1

1 row(s) updated

SHOW AUDIT VERIFY

(1 row)

the audit hash chain is intact (3 rows checked)

GET /api/audit

Syntax:

GET /api/audit?limit=100&where=<condition>
GET /api/audit?db=<name>&kind=data&limit=100&where=<condition>

The same as SHOW AUDIT over HTTP. With kind=data: the data-change rows of the database db.

• Returns: {"query", "columns", "dataset", "count"}.
• Notes: needs a server admin (for kind=data: admin on that database). HTTP 404 when no table of the database has SET AUDIT ON yet.

HTTP: The newest security events

GET /api/audit?limit=5

Response:

HTTP 200 (application/json)

{
  "query": "SHOW AUDIT LIMIT 5",
  "columns": [
    {"name": "time", "type": "TIMESTAMP"},
    {"name": "event", "type": "STRING"},
    {"name": "user", "type": "STRING"},
    {"name": "client", "type": "STRING"},
    {"name": "db", "type": "STRING"},
    {"name": "object", "type": "STRING"},
    {"name": "detail", "type": "STRING"},
    {"name": "row_key", "type": "STRING"},
    {"name": "old_values", "type": "JSON"},
    {"name": "new_values", "type": "JSON"},
    {"name": "session", "type": "LONG"},
    {"name": "ok", "type": "BOOL"},
    {"name": "seq", "type": "LONG"},
    {"name": "hash", "type": "STRING"},
    {"name": "agent", "type": "STRING"}
  ],
  "dataset": [],
  "count": 0
}

--audit-keep-days

Syntax:

kdb serve [--audit on|off] [--audit-keep-days N]

--audit off writes no security events. --audit-keep-days N removes security events older than N days, a whole month at a time, once a day (0 = keep forever, the default).

• Notes:
  • Security events (login_failed, lockout, denied, user, protection) are written to disk before the request that caused them gets its answer. A successful login and the other events (ddl, timer, backup ...) are written in groups every 200 ms, so a crash can lose the events of that moment. A new connection does not wait for a disk write.
  • Keep the audit log of accounting for as long as the law asks (often several years).
  • --audit-keep-days only removes security events (the _audit_log of _system). The data-change rows of SET AUDIT ON tables, in each database's own _audit_log, are kept as long as the database lives (see erase one user's data).

Erase one user's data

A person asks you to delete everything you hold about them. Three steps: find the tables, delete the rows, and know what the audit log keeps.

1. Find every table with a user column. information_schema.columns lists the columns of every table.

Example: Which tables hold a user id or an email?

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY, email STRING NOT NULL);
CREATE TABLE orders (id INT PRIMARY KEY, user_id INT REFERENCES app_users(id) ON DELETE CASCADE, amount DECIMAL(2));
CREATE TABLE page_views (time TIMESTAMP, user_id INT, page STRING) TIMESERIES(time) PARTITION BY DAY;

SELECT table_name, column_name FROM information_schema.columns
WHERE column_name IN ('user_id', 'email') ORDER BY table_name;

Result:

(3 rows)

2. Delete the rows in one transaction. Tables that point to the user with REFERENCES ... ON DELETE CASCADE lose their rows together with the user's row (REFERENCES). Tables without such a key (a time-series table of page views, for example) need their own DELETE. One BEGIN ... COMMIT makes it all or nothing.

Example: Delete user 1 everywhere, then check

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY, email STRING NOT NULL);
CREATE TABLE orders (id INT PRIMARY KEY, user_id INT REFERENCES app_users(id) ON DELETE CASCADE, amount DECIMAL(2));
CREATE TABLE page_views (time TIMESTAMP, user_id INT, page STRING) TIMESERIES(time) PARTITION BY DAY;
INSERT INTO app_users VALUES (1, 'ram@example.com'), (2, 'sita@example.com');
INSERT INTO orders VALUES (10, 1, 250.00), (11, 2, 99.50), (12, 1, 10.00);
INSERT INTO page_views VALUES ('2026-09-24T10:00:00Z', 1, '/home'), ('2026-09-24T10:01:00Z', 2, '/home');

BEGIN;
DELETE FROM page_views WHERE user_id = 1;
DELETE FROM app_users WHERE id = 1;
COMMIT;
SELECT 'orders' AS table_name, count(*) AS rows_left FROM orders WHERE user_id = 1
UNION ALL SELECT 'page_views', count(*) FROM page_views WHERE user_id = 1
UNION ALL SELECT 'app_users', count(*) FROM app_users WHERE id = 1;

Result:

BEGIN

transaction started

DELETE FROM page_views WHERE user_id = 1

1 row(s) deleted

DELETE FROM app_users WHERE id = 1

1 row(s) deleted

COMMIT

transaction committed: 4 change(s) saved

SELECT 'orders' AS table_name, count(*) AS rows_left FROM orders WHERE user_id = 1 UNION ALL SELECT

(3 rows)

3. What the audit log keeps. If a table has SET AUDIT ON, its _audit_log row for the DELETE holds the deleted row in old_values, and the older insert and update rows hold it too. These rows cannot be erased with SQL. The log is append-only on purpose: DELETE, UPDATE, TRUNCATE and DROP of _audit_log are refused, and every row is linked to the one before by the hash chain, so a row removed by editing the files is reported by kdb check. --audit-keep-days does not reach these rows either: it only removes security events.

Example: The deleted row is still in old_values

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY, email STRING NOT NULL);
ALTER TABLE app_users SET AUDIT ON;
INSERT INTO app_users VALUES (1, 'ram@example.com');

DELETE FROM app_users WHERE id = 1;
SELECT event, row_key, old_values FROM _audit_log WHERE event = 'delete';

Result:

DELETE FROM app_users WHERE id = 1

1 row(s) deleted

SELECT event, row_key, old_values FROM _audit_log WHERE event = 'delete'

(1 row)

• Notes: backups hold the deleted rows too, until the backup schedule removes the old backups (backup_keep_daily, backup_keep_weekly).
• Notes: in many places the law lets you keep records you must keep for accounting or security, even after an erasure request. Say in your privacy policy that the audit log keeps the changes of audited tables, and for how long.