Audit Logging

SQE writes a tamper-evident audit log for authentication events, session lifecycle changes, permission grants and revokes, catalog DDL, and a subset of query executions (see Coverage in this release below). The log is append-only JSONL (one JSON object per line). Each record carries an integrity block that lets offline tooling detect modification or truncation.

Enabling the log

Set audit_log_path under [metrics]:

[metrics]
audit_log_path = "/var/log/sqe/audit/audit.jsonl"

An empty string disables logging. The path must exist or be writable by the SQE process. Under Helm, set audit.enabled = true (default) and audit.persistence.enabled = true to back the log with a persistent volume claim.

[metrics.audit] config block

[metrics.audit]
format                = "native"   # "native" | "ocsf" | "both"
gdpr_tags             = []         # tag names that mark a column as GDPR-sensitive
gdpr_identifier_mode  = "tokenize" # "tokenize" | "drop" | "keep"
superdebug_log_results = false      # NEVER true in production
max_file_bytes        = 268435456  # rotate at 256 MiB; 0 = never
max_files             = 0          # rotated files to keep; 0 = keep all
min_free_bytes        = 1073741824 # below 1 GiB free, delete oldest rotated files; 0 = never

All keys are optional. The defaults above are the production-safe values.

format

Controls which wire schema is written to disk.

For example, if audit_log_path = "/var/log/sqe/audit/audit.jsonl" and format = "both", the files are audit.jsonl (native) and audit.ocsf.jsonl (OCSF).

gdpr_tags

A list of tag names. Any column in a queried Iceberg table whose tag set (read from the Iceberg table property sqe.column-tags) contains one of these names is treated as GDPR-sensitive. Before the event is chained and written, the matching column identifiers and their adjacent literal values are removed from the logged SQL text.

Empty list (the default) disables GDPR column masking. PII pattern redaction (emails, SSNs, phone numbers, card numbers, and secret-keyword literals) runs unconditionally regardless of this setting.

Tag resolution uses the existing policy metadata cache via sqe-policy’s TagSource. No extra network calls are made on the audit write path.

Fail-closed rule: when the tag state for a table is unknown at write time (cache miss, parse error), all SQL literals are stripped from the query text rather than risking a leak. A known-empty tag map is not a cache miss; it means the table has no tags and no masking is applied.

gdpr_identifier_mode

Controls how tagged column identifiers appear after masking. Has no effect when gdpr_tags is empty.

The token is derived as sha256(salt + lowercase(column_name)). The salt is set once at startup and does not need to be secret; it separates token namespaces across deployments.

superdebug_log_results

Default false. When true, SQE emits a loud WARN log line and writes a self-audit event of kind admin_ddl to the audit trail recording that the flag is active. Result rows are never written to any audit sink regardless of this flag; the flag name is intentionally alarming. Enable only in isolated development environments and disable before going to production.

Enabling superdebug_log_results in production violates SOC 2, ISO 27001, and GDPR data-minimisation requirements.

max_file_bytes and max_files

max_file_bytes defaults to 256 MiB and max_files to 0: files rotate, and every rotated file is kept. max_file_bytes = 0 turns rotation off and the file grows without limit. Once the native file or, with format = "ocsf" or "both", the OCSF file reaches the limit, both move to <audit_log_path>.<generation> and audit.ocsf.jsonl.<generation> (generation 00000001, 00000002, …) and the writer opens fresh files.

Rotation does not break the hash chain. The first record of the new file chains to the last record of the rotated one, and a restart on a still-empty file resumes from the newest rotated file. --verify-audit <audit_log_path> checks every generation in order (see below).

By default nothing is deleted while the disk has room. Audit history is removed in two cases only:

• The volume is about to fill. Every 10 seconds the writer checks free space on the audit volume. Below min_free_bytes (default 1 GiB) it deletes the oldest rotated generations, one at a time, until the floor is met again. The newest rotated file is never deleted. Each deletion logs an ERROR and writes a maintenance record (error_code = "PRUNED", the deleted generations in the message) into the chain, so the gap is explained inside the log itself. Set min_free_bytes = 0 to never delete. A full disk then drops new audit events (sqe_audit_events_dropped_total{reason="io"}), and anything else sharing that volume runs out of space too.
• max_files is set. The writer keeps that many rotated generations and deletes the oldest, with the same chain record.

Deleting is intended truncation of the history, not tampering: the oldest kept file simply does not start at genesis, and the PRUNED record says why. If your retention policy needs the full trail, ship rotated files off the volume before the floor is reached.

Auth provider claim paths

The oidc_password and bearer_token auth providers accept three optional claim-path fields that enrich the audit actor identity:

Example bearer_token provider config:

[[auth.providers]]
type = "bearer_token"
jwks_url = "https://auth.corp.example/.well-known/jwks.json"
audience = "sqe"
subject_claim = "sub"
email_claim = "email"
groups_claim = "groups"

When extraction is enabled, the enriched fields appear in the actor block of every event: actor.subject, actor.email, actor.groups. Fields that are absent from the token are omitted from the event rather than serialized as null.

OCSF class mapping

When format = "ocsf" or format = "both", each canonical AuditEvent is mapped to an OCSF class before writing. The mapping is fixed at the kind level.

A policy denial (policy_decision kind with Failure outcome) maps to class 6005 with status_id = 2. This lets SIEM tools correlate policy denials alongside normal query activity in a single class.

Standard OCSF fields used:

• class_uid / category_uid: from the table above.
• status_id: 1 for success, 2 for failure.
• time: millisecond epoch timestamp.
• severity_id: fixed at 1 (Informational).
• actor.user.name: username.
• actor.user.uid: subject claim, when present.
• actor.user.email_addr: email claim, when present.
• actor.user.groups: array of group objects {"name": "..."}.
• actor.user.roles: roles array.
• actor.app_name: the initiating client, when the protocol reports one: X-Trino-Source on Trino HTTP, mcp:<tool>:<client> on the MCP endpoint. Absent for Flight SQL and for system events.
• resources: array of {"name": "catalog.namespace.table", "type": "Table"|"View"}.
• src_endpoint.ip: client IP, when present.
• metadata.product.name: "SQE".
• metadata.uid: integrity hash of the record.

SQE-specific fields that have no OCSF home (query hash, statement type, scan stats, policy decisions) travel under unmapped as a flat object.

Tamper-evident hash chain

Every record carries an integrity block:

"integrity": {
  "seq": 42,
  "prev_hash": "a3b4c5...",
  "hash": "d6e7f8..."
}

The hash formula is:

hash = sha256(prev_hash || canonical_json_with_hash_field_blanked)

The first record uses a genesis sentinel (0000...0000, 64 hex zeros) as prev_hash. Sequence numbers are zero-based and strictly increasing.

The verify_chain function in sqe-metrics walks a loaded slice of events and returns an error if any record has an unexpected sequence number, a prev_hash that does not match the previous record’s hash, or a recomputed hash that does not match the stored value. This detects tampering, record deletion, and truncation anywhere in the file.

Redaction and GDPR masking run before chain stamping so the chain covers the post-redaction bytes. Modifying a record to restore redacted content will break the chain.

Verifying a log or an extract

sqe-server --verify-audit <file> checks the chain of a native audit file and exits without starting a server. Exit code 0 means intact, 1 means broken, with the first bad line named. The report carries what a chain-of-custody record needs:

sha256:          2f1e56dc...   (the file as handed over)
records:         151 (seq 100..=250)
first prev_hash: 0414fcbc...   (hash of seq 99 in the source log)
last hash:       0f2b6079...   (prev_hash of seq 251 in the source log)
chain:           OK

Pointed at a rotated log’s active path, it verifies every <file>.<generation> oldest first, then the active file, and fails if any file does not chain to the one before it. Pass a single rotated file to verify only that file.

Verification needs the native file: format = "native" or "both". An ocsf-only deployment has nothing to recompute the hashes from, so pick "both" if you may ever need to prove a log is intact.

Extracting records for an investigation

1. Copy the audit file. Never edit or filter the live file in place.
2. Cut a contiguous line range from the copy with sed -n 'FIRST,LASTp'. Find the line numbers with grep -n '"seq":100,'. Do not rewrite lines with jq or an editor: a reformatted line no longer matches its hash.
3. Run sqe-server --verify-audit on the source copy and on the extract. Both must say OK.
4. Check the anchors. The extract’s first prev_hash must equal the hash of the record before it in the source, and its last hash the prev_hash of the record after it. Matching anchors prove nothing was cut from either end.
5. Record the extract’s sha256, sequence range and both anchors, with who made it and when, and hand over the extract and the report together. The receiver reruns --verify-audit and compares the sha256.

A broken segment line in the report marks a restart after a crash left a torn record. The writer starts a fresh segment on purpose, so the log stays verifiable. Keep the report line with the extract: it explains the gap.

PII redaction (always-on)

Before any record is written, redact_pii runs unconditionally on the SQL query text. It replaces:

• Email addresses with [EMAIL]
• SSNs (XXX-XX-XXXX) with [SSN]
• Phone numbers with [PHONE]
• Credit-card-like sequences with [CARD]
• Secret-keyword literals (TOKEN '...', PASSWORD '...', ACCESS_KEY_ID '...', KEY_ID '...', SECRET_ACCESS_KEY '...', SESSION_TOKEN '...', API_KEY '...', CLIENT_SECRET '...', BEARER '...') with [REDACTED]

The secret-literal pass guards against CREATE SECRET ... TOKEN '<jwt>' landing verbatim in the log. This is belt-and-suspenders: the token is redacted regardless of whether the statement is the direct SQL text or arrives via a prepared statement.

redact_pii is pattern-matching, not a SQL parser. It catches known PII shapes but does not catch free-form sensitive literals such as WHERE patient_id = 'P-998877'. For that, GDPR column masking (see above) strips all literals adjacent to tagged columns, and the fail-closed path strips all literals when tag state is unknown.

Coverage in this release

The table below describes what produces a canonical AuditEvent written to the OCSF spool and OCSF file.

A transaction writes two transaction records besides the records of its statements. The first goes out when the transaction’s first statement runs, with action begin. The second goes out when it ends, with action commit or rollback and a reason: committed, conflict, rolled_back, session_ended, idle_timeout, lifetime or shutdown. A lost COMMIT is action commit, reason conflict, status_id 2 and error code TRANSACTION_CONFLICT. The end record carries duration_ms and, after a successful COMMIT, published: the main snapshot id each table now points at. unmapped.transaction.id is the transaction id the client holds, so the two records join to each other and to the statement records in between by username and time.

The actor on a transaction record is the username alone. The record has no session id, groups or client address, because a transaction outlives the request that opened it and the coordinator ends some on its own (idle timeout, lifetime, shutdown). A transaction that ends without running a statement started nothing and writes no transaction record.

Secret-bearing statements keep the redacted legacy path. The entry is redacted once, before it is queued, so bearer tokens and catalog credentials embedded in the SQL are stripped before any byte is written. The OCSF line is built from that already-redacted entry, never from the raw SQL, and carries the native record’s hash as metadata.uid. Its text is byte-for-byte the text in audit.jsonl, so an OCSF-only SIEM sees these statements without seeing anything the native file hides.

The legacy AuditEntry format is flat JSON. It carries username, statement type, duration, status, and tables_touched (unqualified table names), but not structured resources, actor email, groups, or policy decision fields. The OCSF form of a legacy entry has the same gaps: no resources, no query id, no trace id.

A result-cache hit never plans, so its record takes resources and the policy block from the run that filled the entry. The policy block still applies: a hit is served only under the policy fingerprint it was produced under. The schema has no cache-hit field. A hit is a successful query record with timing.execution_ms, stats.bytes_scanned and stats.rows_scanned all 0.

Hits are rare in this release. The cache is read only on the buffered path, and a cache entry belongs to one session. Flight SQL, Trino HTTP and Quack stream their SELECTs and never read the cache. MCP runs SELECTs buffered but opens a session per call. Every hit is audited all the same.

Deliberately not audited

Three paths write no audit record. Each is a decision, not an oversight.

Statements over query.max_sql_bytes. The size cap rejects the text before it is parsed, and the client gets InvalidArguments. No record is written today. The text is attacker-sized and unparsed, so it must never be logged. The decided shape for a later release is a failed query record carrying query_hash only, with no text. Until then, an operator who needs these attempts counted should alert on the client-facing error rate.

Admission rejections under overload. Memory-pressure rejections, and the per-user and global concurrency limits (max_concurrent_per_user, max_concurrent_queries), write no audit record by design. They fire exactly when the coordinator is saturated. A record per rejection would add audit I/O to an overload, and with a fail-closed audit log a full queue would turn that overload into an outage for every caller. Memory-pressure rejections log a WARN line with the user name. The concurrency limits log nothing and have no metric yet; a counter by rejection reason is the planned signal, not a per-event record.

Trino session-control statements answered by the protocol layer. PREPARE, DEALLOCATE PREPARE, SET SESSION, RESET SESSION and SET CATALOG never reach the engine. They change client-held session state in response headers and read or change no data, grants or catalog objects. The statements that use that state are audited when they run: EXECUTE hands its bound text to the executor, and the record carries that text. USE is not in this list: it goes to the executor and is audited like any other statement.

Exporting to a SIEM (OTLP)

SQE ships a background exporter that tails the OCSF spool and forwards records to any OTLP-compatible collector (OpenTelemetry Collector, Grafana Alloy, Datadog Agent, etc.). The exporter is off by default.

Config block

[metrics.audit_export]
enabled          = false          # set to true to activate
target           = "otlp"         # only "otlp" is supported; "kafka" is reserved but not built
otlp_endpoint    = ""             # e.g. "http://otel-collector:4317"; empty -> falls back to metrics.otlp_endpoint
spool_path       = ""             # empty -> <audit_log_path>.ocsf.spool.jsonl
batch_max        = 512            # maximum records per OTLP export batch
flush_interval_ms = 2000          # shipper poll interval in milliseconds
max_spool_bytes  = 1073741824     # 1 GiB; hard cap, the oldest unshipped records go past it
start_at         = "now"          # "now" (default) | "beginning"

All keys are optional. The defaults shown are the production-safe values.

enabled

false by default. Setting to true activates the OTLP exporter and the spool writer. Disabling (false) leaves the rest of the audit stack unchanged: the OCSF file is still written when format = "ocsf" or format = "both", and the hash chain is unaffected. The export spool is a separate file.

target

Only "otlp" is implemented. "kafka" is reserved for a future release. Configuring any other value logs a warning and the exporter does not start.

otlp_endpoint

The OTLP/gRPC endpoint URL for the collector. If empty, the exporter falls back to metrics.otlp_endpoint. If both are empty, or the exporter cannot be built, the server logs a warning at startup, the exporter does not start and no spool is written. Nothing would drain or cap it.

Use an https:// endpoint whenever the collector is not on the same host. SQE verifies the collector certificate against the system trust store; point SSL_CERT_FILE at your bundle for a private CA. Audit events carry user identities and SQL text, so a plain http:// hop across the network leaks them.

spool_path

Path to the OCSF JSONL spool file that buffers events before export. If empty, the path is derived from audit_log_path by replacing the extension with .ocsf.spool.jsonl (e.g. audit.jsonl -> audit.ocsf.spool.jsonl). The file is created on startup if it does not exist.

The export spool is independent of the format setting. When audit_export.enabled = true, every canonical event written via log_event goes to the spool regardless of whether format is native, ocsf, or both.

batch_max

Maximum number of OCSF records sent in a single OTLP export call. Default: 512.

flush_interval_ms

How often the background shipper polls the spool for new records. Default: 2000 ms. Lower values reduce latency to the SIEM at the cost of more frequent OTLP calls.

max_spool_bytes

Hard cap on the spool file in bytes. Default: 1 073 741 824 (1 GiB).

The shipper empties the spool once every record in it has shipped and it has reached 16 MiB, so a healthy exporter keeps it small. A spool only grows past that while the collector is unreachable. When it passes max_spool_bytes the shipper first cuts the records it has already shipped. If that is not enough, it drops the oldest unshipped records until the spool is at half the cap, logs a WARN and counts them in sqe_audit_export_spool_dropped_records_total. An outage therefore cannot fill the disk, and queries are never blocked or refused because of it.

The spool is only the delivery buffer for the SIEM. The local audit log (audit_log_path) and its hash chain keep every record, so dropped records can be recovered from it, and the SIEM sees the gap as missing audit.seq values. Size the cap for the outage you want to ride out: at 1 KB per record, 1 GiB holds about a million statements. The SqeAuditExportStalled alert is the signal to act before the cap is reached.

The cap does not cover the local audit log itself, which SQE only appends to. Rotate it with your platform’s log rotation and keep its disk monitored. A write that fails there (for example on a full disk) is counted in sqe_audit_events_dropped_total{reason="io"} and does not refuse queries.

start_at

Controls where the shipper starts on the first run, when no cursor file exists.

On subsequent restarts the persisted cursor (<spool_path>.cursor) always wins. start_at is not re-applied when a cursor file exists. This guarantees at-least-once delivery: a restart resumes exactly where the last successful export acked.

Behavior and durability

The exporter provides at-least-once delivery for every record that stays in the spool. Records leave the spool once shipped, or when an outage pushes it past max_spool_bytes (see above). The background shipper tails the spool from the byte offset recorded in the cursor file, batches up to batch_max records, sends them to the collector, and advances the cursor only after the collector returns a successful ack.

A collector outage grows the spool up to max_spool_bytes. Queries continue without delay. When the collector recovers, the shipper replays from the last cursor position.

The exporter uses a dedicated OTLP log pipeline. It is not connected to the trace_sample_rate tracing bridge, so audit records are never sampled or dropped by the trace sampler.

OTLP record mapping

Each spool line (one OCSF JSON object) becomes one LogRecord in the OTLP batch:

Indexed attributes set on every record:

SIEM queries that filter on any of these attributes avoid parsing the full body.

Export metrics

sqe_audit_export_spool_lag_bytes = 0 means the shipper is caught up. A rising value while sqe_audit_export_batch_failures_total is also rising points to a collector connectivity problem.

Deferred

• Kafka target. The target = "kafka" config key is reserved. It is not implemented in this release.

Operator dashboard gate

When [metrics] web_ui = true (default: false), the operator dashboard is enabled on the health port. The dashboard exposes live query state, worker health, and performance counters to authenticated admins. It requires a bearer token and an admin role before it serves any content.

Which routes are gated

The three open endpoints serve Kubernetes liveness/readiness probes and load-balancer health checks. They must not require credentials.

Role check

The admin role list is auth.admin_roles. The same list gates coordinator DDL (CREATE/DROP/ALTER TABLE). A token that validates but holds none of the listed roles gets a 403 Forbidden. An empty admin_roles list is fail-closed: every caller receives 403, because has_admin_role returns false when the configured list is empty. Operators must configure at least one role to grant anyone dashboard access.

Audit behavior

Dashboard access attempts produce an OCSF Authentication event (kind: auth).

Two cases produce an audit line:

• Access granted (200 OK): an auth event with status: success, the actor’s username, roles, and any subject/email/groups claims carried in the token.
• Admin role missing (403 Forbidden): an auth event with status: failure, error_type: DashboardAccessDenied, and the actual principal named in actor.username (not “unknown”).

One case does NOT produce an audit line:

• No token or wrong scheme (401 Unauthorized): no principal was established, so no audit line is written. The counter sqe_dashboard_auth_anonymous_denied_total is incremented instead. This prevents k8s probe traffic from flooding the audit spool and the downstream SIEM.

The bearer token value is never placed in any audit field.

What the dashboard shows

Authenticated admins see per-query records populated from the in-memory tracker. Each record includes:

• username and roles of the submitting user.
• client_ip of the submitting client, when available.
• SQL text after redact_pii masking. Known PII patterns (emails, SSNs, phone numbers, card numbers, secret-keyword literals) are replaced with bracketed placeholders before the value leaves the query layer. Raw SQL is never placed in the response.

The redact_pii pass runs at record assembly time, not at query admission time, so the stored text in the tracker is the original SQL. Masking is applied every time a record is serialised for the dashboard wire format.

client_ip threading

client_ip is threaded end to end through the query path. The execute and execute_stream functions accept client_ip: Option<String>. The value is stored in QueryRecord by the query tracker and carried into audit events for buffered executions, streaming SELECT finalisers, DML/DDL completions, and GRANT/REVOKE statements. The dashboard displays the value directly from QueryRecord.client_ip.

Dashboard-access audit events (kind: auth) carry client_ip when the peer address is available. The health server is served with into_make_service_with_connect_info, so ConnectInfo<SocketAddr> is always present for real TCP connections. If [security] trusted_proxies is configured, the XFF header is honoured with the same rightmost-untrusted-hop rule used by the Flight SQL and Quack paths.

trusted_proxies takes exact addresses and CIDR ranges, IPv4 and IPv6 (["10.42.0.0/16", "fd00::/8"]). An IPv4 peer reported as IPv4-mapped IPv6 by a dual-stack listener matches IPv4 entries. A range trusts every address in it: any of them can set X-Forwarded-For and so choose the client IP that lands in the audit record. Keep the range as narrow as the ingress allows. An entry that is neither an address nor a range fails config validation.

Field consistency

Audit and trace emit sites use the following canonical field names:

• username (actor identifier)
• session_id (session correlation)
• query_id (per-query tracing field)
• client_ip (source address)

Never-log-result-rows policy

Result rows are never written to any audit sink. AuditEvent has no field for result data and the serialization path has no code path that writes row values. The superdebug_log_results flag does not change this; it is a marker for a future diagnostic mode and its only current effect is the warning and self-audit event described above.