# Audit logging

Recording who did what to which data, and when: logins, queries, access changes and administrative actions, kept so that they can be searched later and cannot be quietly altered.

> [!NOTE]
>
> **Before reading**
>
> [Authentication and authorization: OIDC and JWT](<https://wiki.4tm0.net/data/authn-vs-authz-oidc-jwt.html>) · [Row-level security and masking](<https://wiki.4tm0.net/data/row-level-security-masking.html>)

## Why it matters

Access controls say what should happen; audit logs show what did happen. They answer the questions that come after a leak, a complaint or an audit: who read these customers' data, who granted that access, who dropped the table. Privacy law, security standards and customer contracts expect organisations to be able to answer them. Without logs, the honest answer is "we don't know".

## How it works

Audit logs come from every layer, because each sees different actions.

- The identity provider logs sign-ins, failed attempts, token issuance and changes to users and groups.
- Query engines log each query: who ran it, the SQL, the tables and columns read, the policies applied, rows returned and time taken. Trino, for example, emits query events through event listeners.
- The catalog logs table creation, schema changes, drops and permission changes, and, with credential vending, which identity received access to which table.
- Object storage logs requests to buckets and objects, which catches access that bypasses engines.
- The KMS logs every use of an encryption key (per bucket, not per object, when bucket-level keys are used).
- Orchestrators, CI and infrastructure tools log deployments and configuration changes.

Every entry needs the same core fields: the identity, acting directly or on behalf of a user; the action; the resource; the time; the result, allowed or denied; and where the request came from. When services act for users, both identities are recorded, so that an agent's query shows the person it ran for.

```
flowchart LR
  IDP[Identity provider] --> C[Central audit store:<br/>write-once,<br/>separate access]
  ENG[Query engines] --> C
  CAT[Catalog] --> C
  OBJ[Object storage] --> C
  KMS[KMS] --> C
  C --> Q[Investigations,<br/>access reviews,<br/>reports]
  C --> A[Alerts on<br/>unusual access]
```

Logs are collected centrally, in one store with a common schema, often itself a set of lakehouse tables or a security information and event management (SIEM) system. The store must be protected from the people being audited: write-once storage or object lock, separate administrators, and alerts on attempts to change or delete logs. Retention follows legal and contractual requirements, often a year or more, and audit logs themselves contain personal data, such as user names and query text, so access to them is restricted and logged too.

Logs are used, not only kept. Access reviews compare who has access with who uses it, so unused grants can be removed. Alerts flag unusual patterns: a user exporting far more rows than usual, access to restricted columns at night, a service account querying tables it never used before. Investigations reconstruct what happened from the combined logs. Reports answer requests such as a customer asking who accessed their data.

Query text in logs needs care. A query can contain personal data in its literals, such as WHERE email = 'ana@mail.com', so logged queries are classified as confidential.

## Key terms

- Audit log A record of security-relevant actions: who, what, which resource, when, result.
- Event listener An engine plugin that receives events, such as completed queries.
- Write-once storage Storage where records cannot be changed or deleted for a retention period.
- SIEM A system that collects security logs and detects suspicious patterns.
- Access review A periodic check of who has access against who needs it.
- On-behalf-of identity The user for whom a service performs an action.

## Example

The privacy team asks: who read customer email addresses in October?

The shop's audit tables combine engine query logs, with the columns each query read and the columns it saw masked (both arrays), and identity data:

```
SELECT q.user_name, q.on_behalf_of, count(*) AS queries,
       sum(q.rows_returned) AS rows_returned
FROM audit.query_log AS q
CROSS JOIN UNNEST(q.columns_read) AS c(column_name)
WHERE q.query_start >= TIMESTAMP '2026-10-01 00:00:00 UTC'
  AND q.query_start <  TIMESTAMP '2026-11-01 00:00:00 UTC'
  AND c.column_name = 'shop.clean_customers.email'
  AND NOT contains(q.masked_columns, 'shop.clean_customers.email')
GROUP BY 1, 2
ORDER BY rows_returned DESC;
```

Six support staff and the CRM sync service read unmasked emails. One analyst appears through the support assistant, which ran on their behalf; their access is reviewed and the assistant's tool is changed to return masked emails. Object storage logs show no direct reads of the customer files outside the engines.

## Pitfalls

- Logs only at the engine. Direct storage access and catalog changes go unseen.
- Logs administrators can edit. An audit trail that can be changed proves nothing.
- Service identities only. Without the on-behalf-of user, every agent or API query looks the same.
- Collected and never read. Logs without reviews and alerts find nothing until it is too late.
- Unprotected query text. Logged SQL can contain personal data.

## Learn more

- [NIST SP 800-92, Guide to Computer Security Log Management](<https://csrc.nist.gov/pubs/sp/800/92/final>)
- [Trino documentation, event listeners](<https://trino.io/docs/current/develop/event-listener.html>)

## Related

[Encryption and key management](<https://wiki.4tm0.net/data/encryption-and-key-management.html>) [Observability and SLOs](<https://wiki.4tm0.net/data/observability-and-slos.html>) [Privacy lifecycle](<https://wiki.4tm0.net/data/privacy-lifecycle.html>) [PII and data classification](<https://wiki.4tm0.net/data/pii-and-classification.html>)