Instrumentation and the audit log

Hardcharger™ can record what it does in SQL tables you query like any others: who connected, what they ran, how long it queued and ran, how many rows they fetched, and what failed. With access control on, it also keeps an audit log whose records are chained so that a changed or missing record shows.

Instrumentation

Turn it on with --enable-instrumentation. The tables go in a catalog named _hardcharger_instr: by default a DuckDB file next to the database (hardcharger_instrumentation.db), for development or a single server, or, with --instrumentation-catalog, a PostgreSQL catalog that your init SQL attaches, so that every replica writes to one place. Use PostgreSQL in production. A DuckLake catalog (or any catalog but PostgreSQL) stops the server at startup, with what to use instead.

The server writes the records in the background, in batches: a statement never waits for its own, and its rows appear a moment after it ends. A script that reads back what it just ran waits for them first, as these examples do:

$ export HARDCHARGER_PASSWORD='change-me'
$ hardcharger --host localhost --tls=false --username hardcharger --quiet --command "SELECT 42 AS answer" > /dev/null
$ for i in $(seq 50); do [ "$(hardcharger --host localhost --tls=false --username hardcharger --quiet --csv \
    --command "SELECT count(*) FROM _hardcharger_instr.execution_details WHERE sql_text = 'SELECT 42 AS answer' AND status = 'success'" | tail -1)" = 1 ] && break; sleep 0.1; done
$ hardcharger --host localhost --tls=false --username hardcharger --csv --command \
    "SELECT username, sql_text, status, rows_fetched FROM _hardcharger_instr.execution_details WHERE sql_text = 'SELECT 42 AS answer'"
username,sql_text,status,rows_fetched
hardcharger,SELECT 42 AS answer,success,1

Table or view	One row per
`instances`	server process: version, start and stop time, how it stopped, `instance_tag`
`sessions`	session: user, role, sign-in method, start and stop, `session_tag`
`sql_statements`	statement: SQL text (credentials redacted), whether it prepared, `query_tag`
`sql_executions`	execution: bind parameters, queue, run and fetch times, rows fetched, status, error, query profile
`execution_details` (view)	execution, joined with its statement and session, with `queue_wait_ms`
`session_activity`, `active_sessions`, `session_stats` (views)	session, with its executions summarized

Only administrators read these tables, and nobody writes, detaches or USEs them: they are the server’s. Non-administrators don’t see the catalog in metadata.
A server that was killed has its open rows closed at its next start.
--instance-tag (a JSON object) labels every row of a server, and query and session tags label statements and sessions.

$ HARDCHARGER_PASSWORD='change-me' hardcharger --host localhost --tls=false --username hardcharger --csv --command \
    "SELECT instance_tag->>'env' AS env, status FROM _hardcharger_instr.instances"
env,status
docs,running

Statement and execution ids

Statements and executions are recorded under the Warp SQL ids clients hold: the Warp-Statement-Id of a response, the statement an error response names, the statement_id of hardcharger_active_connections(), and the prepared_id of a prepared statement. A session’s session_id is its s_... id the same way.

Statement	`sql_statements.statement_id`	`sql_executions.execution_id`
submitted (`POST .../statements`)	its `q_...`	the same `q_...`
several statements in one request (a script)	its `q_...`	`q_....1`, `q_....2`, … by place in the script
prepared (`POST .../prepared`)	its `p_...`	each execute request’s own `q_...`

In a script, a SET hardcharger.* or KILL SESSION takes its number but has no execution row. Statements the server runs for itself (init SQL, access control’s own queries) are not recorded. The query log uses the same ids: its lines carry statement_id (q_..., or q_....N) and, for prepared statements, prepared_id. Here a statement looks itself up, from the id hardcharger_active_connections() shows for it:

$ export HARDCHARGER_PASSWORD='change-me'
$ q=$(hardcharger --host localhost --tls=false --username hardcharger --quiet --csv --command \
    "SELECT statement_id FROM hardcharger_active_connections() WHERE is_current" | tail -1)
$ for i in $(seq 50); do [ "$(hardcharger --host localhost --tls=false --username hardcharger --quiet --csv \
    --command "SELECT count(*) FROM _hardcharger_instr.execution_details WHERE execution_id = '$q' AND status = 'success'" | tail -1)" = 1 ] && break; sleep 0.1; done
$ hardcharger --host localhost --tls=false --username hardcharger --csv --command \
    "SELECT execution_id = '$q' AS same_id, statement_id = '$q' AS one_statement, status FROM _hardcharger_instr.execution_details WHERE execution_id = '$q'"
same_id,one_statement,status
true,true,success

An instrumentation table whose statement or execution ids are UUIDs, from an earlier build, is refused at startup with what to do: drop the instrumentation tables, rename them to keep their rows, or use a new instrumentation database.

Query profiles

With capture_query_profile set to standard or detailed (a startup option, or SET [GLOBAL] hardcharger.capture_query_profile), each execution stores DuckDB’s JSON query profile in sql_executions.query_profile:

$ export HARDCHARGER_PASSWORD='change-me'
$ hardcharger --host localhost --tls=false --username hardcharger --quiet > /dev/null <<'SQL'
SET hardcharger.capture_query_profile = 'standard';
SELECT count(*) FROM range(1000000);
SQL
$ for i in $(seq 50); do [ "$(hardcharger --host localhost --tls=false --username hardcharger --quiet --csv \
    --command "SELECT count(*) FROM _hardcharger_instr.execution_details WHERE sql_text = 'SELECT count(*) FROM range(1000000)' AND status = 'success'" | tail -1)" = 1 ] && break; sleep 0.1; done
$ hardcharger --host localhost --tls=false --username hardcharger --csv --command \
    "SELECT query_profile IS NOT NULL AS has_profile FROM _hardcharger_instr.execution_details WHERE sql_text = 'SELECT count(*) FROM range(1000000)'"
has_profile
true

Catalog logging

--enable-catalog-logging also writes every log line to _hardcharger_logs.logs, in a background writer: its own DuckDB file by default (hardcharger_logs.db), or a catalog the init SQL attaches (--log-catalog). Its columns are log_time, level, instance_id, cluster_id, session_id, username, role, peer, component, trace_id, span_id, pid, tid, source_file, source_line, func, message, fields.

Log lines

The server logs to stderr in text, or in JSON lines with --log-format json. --log-file writes to a file instead (- for stdout). Separate levels for statements, sign-ins and sessions (query_log_level, auth_log_level, session_log_level) let you keep one kind quiet; --print-queries (on by default) logs every statement. JSON lines carry instance_id and, when set, cluster_id.

The audit log

With access control on, every access-control decision and change is logged: statements allowed (with the objects they touched) and refused (with the reason), CREATE USER, GRANT and the rest. Each record carries a sequence number and the hash of the record before it, so the log forms a chain: change, remove or reorder a record and the chain breaks. With audit_chain_key the hashes are HMAC-SHA256, so only someone with the key can forge a chain; without it, SHA-256.

Run the server with JSON logs to a file and a chain key, then do some access control:

HARDCHARGER_AUDIT_CHAIN_KEY='audit-key-from-your-secret-store' hardcharger-server \
  --rbac-store-uri "$STORE_URI" --log-format json --log-file hardcharger.log

CREATE ROLE auditor;
CREATE USER sam WITH PASSWORD 'sam-secret';
GRANT auditor TO sam;

Check the log with the same key:

$ HARDCHARGER_AUDIT_CHAIN_KEY='audit-key-from-your-secret-store' hardcharger-server --verify-audit-log hardcharger.log
chain …
… audit records, intact

Now change one record, as someone covering their tracks would:

$ sed 's/"user":"hardcharger"/"user":"nobody"/' hardcharger.log > tampered.log
$ HARDCHARGER_AUDIT_CHAIN_KEY='audit-key-from-your-secret-store' hardcharger-server --verify-audit-log tampered.log | tail -1
… audit records, NOT intact

--verify-audit-log exits 0 for an intact log and 1 otherwise, and prints each problem it found. For a rotated log that starts in the middle of a chain, add --verify-audit-log-partial.