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

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.

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.
• 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.

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 '...', 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.

Secret-bearing statements stay on the redacted legacy path. Routing them through the canonical path would risk writing credential literals to the OCSF file before redaction applies. The legacy path runs redaction inline, so bearer tokens and catalog credentials embedded in SQL text are stripped before any byte is written.

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.

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; spool size above this emits a WARN
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 the server logs a warning at startup and the exporter does not start.

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

Spool size threshold in bytes. When the spool file exceeds this value the exporter emits a WARN log. The exporter continues and queries are never blocked. Default: 1 073 741 824 (1 GiB). Spool rotation and automatic pruning are not implemented in this release; size management is left to the operator.

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. Records are never removed from the spool. 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. 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

• Spool rotation and retention. Only a bounded-growth WARN at max_spool_bytes is implemented. Rotation, age-based pruning, and size-capped compaction are not built yet.
• 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.

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.