ClickHouse audit logging with query_log and session

Which compliance requirements ClickHouse audit logging helps with

When an auditor or regulator asks who accessed analytical data and what they ran, the answer has to come from a record the organization can produce and defend. In a self-managed ClickHouse cluster, much of that record can come from system tables such as system.query_log and system.session_log, which capture login events and executed queries. DeployOpen configures these tables, sets their retention, and documents how the evidence is collected and reviewed. DeployOpen does not issue audit reports, attestations or certifications. Independent auditors and certification bodies make those determinations, and the value of the log depends on how completely the organization can show it is complete, protected and reviewed.

The frameworks in scope share a small set of control areas that audit logging touches: access control and identification of users, logging and monitoring of activity, protection of logs from alteration, and incident detection. SOC 2 addresses these through its security common criteria, ISO/IEC 27001:2022 covers them in its logging and monitoring and access control controls in Annex A, and GDPR, HIPAA, PCI DSS, DORA and NIS2 each expect some form of accountability or traceable access to sensitive data. The exact obligations differ, so the organization should confirm with its compliance lead and auditor which control IDs and evidence formats apply to its scope.

Several parts stay with the organization regardless of how well the logging is built. It must decide who may read the audit tables, how long records are kept under its legal and contractual duties, who reviews them and how often, and how exports to a SIEM are monitored. It must also keep the platform on a supported ClickHouse version so that logging behavior is maintained, and it must verify that query text does not expose sensitive values. Before the audit, confirm with the auditor which evidence they will sample, such as login records for a period and the queries behind a specific data access.

Mapping audit requirements to ClickHouse logging capabilities

This table maps common audit requirements to the ClickHouse capabilities that address them and the configuration each one needs. Read each row as a question to answer for your environment: the capability shows what ClickHouse offers, and the configuration column shows what your team must set and verify. The main takeaway is that identity, access and logging only produce a usable audit trail when the default user is restricted and every login maps to a named identity.

Requirement area	Platform capability	Configuration needed
Unique identity for every person	Users and external authenticators (LDAP, Kerberos, SSL X.509, HTTP, JWT)	Create named users or map external identities so no queries run under shared accounts. Confirm how each authenticator reports the username into session logs.
Default account control	Default user starts with all permissions	Restrict or disable the default user, then confirm it cannot run administrative or data queries after rollout.
Least privilege access	Roles, grants and settings profiles	Grant access through roles, not direct grants. Document each role's purpose and review membership on a set schedule.
Restricted row-level access	Row policies	Define row policies for sensitive tables and test them with representative users. Log policy changes as configuration changes.
Resource and access limits	Quotas and settings profiles	Assign quotas per role or user so runaway or abusive sessions are limited and visible in logs.
Record of who queried what	query_log system table	Enable query logging, confirm the user and query text fields are populated, and restrict read access to the log tables themselves.
Record of logins and sessions	session_log system table	Confirm login and failure events are recorded for each authenticator. Test with a failed login and check the entry appears.
Protection of logged sensitive values	Query text recorded as submitted	Decide how literal values with personal or cardholder data are handled before logs are retained. Plan masking or avoid literals in queries.

How query_log and session_log build the audit trail

An audit trail in ClickHouse is only as complete as the system tables you configure to record it. The two tables that matter most for accountability are the session log, which records authentication events such as who connected, from which interface and whether the attempt succeeded, and the query log, which records what each session asked the server to do, the query text and its outcome. Both are written by the server itself, but their scope, verbosity and retention depend on server configuration, so the first task is to confirm in the documentation for your exact ClickHouse version which columns each table exposes, which events it captures and how long it keeps rows before the server discards them. Treat any default you assume today as something to verify, not something to rely on during an audit.

A login record and a query record answer different questions, and you need both. The session record shows that a user or service account authenticated, through which protocol and under which identity, which is the point where access was granted. The query record shows what that identity did afterward, including queries that failed because a permission was missing, which is often as informative as the successful ones. Joining the two on session and user identifiers lets an investigator move from "this account ran a large export at 02:00" to "this account authenticated from this client at 01:58 through a shared service login". Where your users authenticate through external systems such as LDAP or JWT, confirm which identity string the server records, because it determines whether you can map a log line back to a person.

Plan the log pipeline before you need it. Decide which logs are copied out of the cluster to your SIEM, how often, and who may read or delete the exported copies, because a trail that an administrator can silently truncate does not satisfy most reviewers. Check whether the query log entries contain literal values from the query text, since a WHERE clause can carry personal data or credentials, and mask or restrict those values before they reach a long-retention store. Finally, test the trail with a scripted scenario: create a test account, run a known set of queries, confirm each appears in both tables and in the SIEM, and record the result. That test is the evidence that the audit trail works, and it should be repeated after each upgrade.

Configuring the audit trail step by step

This sequence turns ClickHouse's built-in session and query logs into an audit trail that ties every action to a named person, keeps the records for a set period and reaches your SIEM.

Restrict the default user
Before anything else, review the default user, which starts with all permissions, and restrict it so it cannot be used for routine access. Your DBA or platform engineer makes the change through SQL-driven access control, then confirms that the default account no longer has broad rights and that administrative actions are only possible through named administrator accounts. Done when a login attempt with the default account is denied for ordinary operations and the change is recorded in your change log. Confirm the exact statements against the access control documentation for your version.
Create named users and least-privilege roles
Create one account per person and per service, assign them to roles that grant only the database and table privileges each job needs, and use row policies or settings profiles where data must be narrowed further. An auditor should be able to see which role grants which privilege without reading application code. The step is complete when a test user can run the queries their role allows and is refused on a table outside it. Keep the role definitions in version control so later reviews have a history.
Connect external authentication so logins map to individuals
Connect ClickHouse to your identity source so that people sign in with their corporate identities rather than shared passwords. ClickHouse supports LDAP directories, Kerberos, SSL X.509 certificates, an HTTP authenticator and JWT as external authenticators, so the right option depends on what your organization already runs. Your identity team and the ClickHouse administrator test a login for each method in use, and confirm that the username recorded in the logs matches the person's directory identity. Verify the configuration against the external authenticators documentation for your version before relying on it.
Enable and verify session and query logging
Enable the session log and query log settings in the server configuration, then run a known login and a known query. Query the session log and the query log system tables to confirm that each event shows the user, the client address, the time and the query text, and that failed logins are recorded. Store the configuration in your infrastructure code so that a rebuilt node cannot silently lose logging. The step is verified when both test events appear on every node that serves queries, not only on one host.
Set retention for the log tables
Decide how long session and query records are kept, then apply a retention rule to the log tables so they are removed on schedule rather than growing without limit. The period should come from your own policy and from the regulations that apply to you, not from a default. Confirm that the retention rule removes only the log tables you intend, and check that the storage needed for the retained data fits your capacity plan. Record the chosen period and the rule in your control documentation, since auditors will ask how it was set.
Mask sensitive literals before they are stored
Query text often contains personal data or secrets in literal values, so define a masking approach before logs accumulate. Options include application-side parameterized queries, so that values are not embedded in the SQL text, and the log masking settings the ClickHouse security documentation describes for your version. Your security engineer runs a query containing a fake card-like or personal identifier value and checks that the stored log entry does not contain it. Treat any value that still appears as a finding to fix before go-live.
Export the logs to your SIEM
Ship the session and query records from the log tables to your SIEM, such as Wazuh, Graylog or another platform your security team runs, using a collector or scheduled export that you operate inside your environment. Confirm the export preserves the timestamp, user and client fields, and decide who may read the exported copy, since it contains the same sensitive content as the source. Check that exports keep flowing after a node restart and that a gap in delivery raises an alert.
Test that a sample action appears end to end
Finish with an end-to-end test: a named test user logs in, runs a permitted query and attempts a forbidden one, and the security team then finds all three events in the SIEM with the correct user and outcome. Repeat the test after any change to authentication, roles or logging, and after each ClickHouse upgrade, and keep the results as evidence. Have someone other than the person who built the trail run the test, so that gaps in the design are found before an auditor finds them.

What audit evidence ClickHouse produces and how to keep it

Auditors rarely ask for a log file. They ask whether access was controlled, who approved it, and whether the record can be trusted later. The cards below cover the evidence a self-managed ClickHouse cluster can produce, how to export each item, and how to show it has not been altered.

User and role definitions

Users, roles, row policies, settings profiles and quotas are defined in SQL and stored in ClickHouse access control. Export the current definitions with SHOW CREATE statements and keep them under version control so each change has a diff, an author and a reviewer. Check that the default user has been restricted, because it starts with all permissions. Access changes should be signed off by the data owner and the platform lead before they are applied.

Login records from session_log

The session_log system table records login and logout attempts, including failed ones, along with the authentication method used. Export it on a fixed schedule to write-once storage outside the cluster. Confirm that the authentication path for each user matches the approved method, whether that is a password, LDAP, Kerberos, X.509 certificates or JWT. Review failed logins for patterns, and record who reviewed them and when.

Query records from query_log

The query_log table records each executed query with the user, client address, timestamps and the text of the statement. Ship it to a SIEM or immutable bucket rather than leaving it on the node, since the table itself is subject to retention settings and can be truncated by someone with administrative rights. Before export, confirm that literal values containing personal or card data are masked, because the log text otherwise carries them into the evidence store.

Retention settings and their history

Auditors ask how long each log is kept and whether that period is enforced. Record the TTL and retention values for every audit table and the configuration file that sets them, and keep those files in version control. Store a periodic hash or checksum of exported log files and their manifest, so a gap in the record can be detected. Who signs off: the compliance owner approves retention periods, and the platform team demonstrates they are in place.

Access review exports

An access review compares the list of active users and roles with the list of people who should hold them. Export the user and grant list, match it against the identity provider or HR roster, and mark each exception with a decision and a deadline. Keep the signed review, the export it was based on and the record of removals. The reviewing manager signs the review, and the platform owner signs the removals as completed.

SIEM forwarding proof

Forwarding is itself a control, so auditors may ask for proof that audit logs reach the SIEM without loss. Keep the forwarder configuration, a sample of events with their source timestamps, and periodic counts comparing rows in the ClickHouse audit tables with events received in the SIEM. Gaps should trigger an alert and a documented investigation. Ask the SIEM team to confirm which timestamps and time zones they store, since mismatched clocks make correlation unreliable.

Keeping sensitive values out of audit logs

An audit log is a second copy of your data, and query text is where sensitive values most often leak into it. A query such as a lookup by email address, card token, patient identifier or account number is written to system.query_log in full, along with the literal values it contained. That means personal, cardholder or health data can end up in the audit trail itself, where it is usually kept longer, read by more people and exported to other systems. Treat the query log as a regulated dataset in its own right: it needs the same access restrictions, retention limits and deletion process as the tables it describes.

The most effective control is to keep literals out of SQL in the first place. Parameterized queries, where the application sends values as bound parameters rather than concatenated strings, reduce what the log records and also protect against injection. Review the application and any BI or ad hoc tooling for queries that embed identifiers in text, since parameter binding does not always cover table names or filters built dynamically. Log access should be limited through ClickHouse roles so that only a small audit group can read system.query_log and system.session_log, and the log tables should be queried through a controlled path, not shared accounts. Masking, where your version supports a masking policy or equivalent feature, is a second layer and not a substitute for these steps. Confirm the exact mechanism, its syntax and its behavior on both live and exported log entries against your version, then test it with realistic but synthetic values before relying on it for any compliance claim.

What ClickHouse audit logging does not cover

The query_log and session_log tables record what ClickHouse saw, but they are one part of an audit trail. The gaps below sit with your auditors, your retention policy, your operating system and network teams, your security monitoring stack and your administrators.

Certification and audit reports

ClickHouse logs do not create an audit report, attestation or certification. Independent auditors and certification bodies assess whether your logging controls operate as designed. DeployOpen prepares the systems, controls and evidence for that assessment but does not issue the report. Confirm early which evidence your auditor expects from the logs and in what format.

Regulatory retention periods

The system tables keep rows for periods set by their own configuration and by the TTL settings you apply, and they do not decide how long your organization must keep audit records. Map each framework's retention requirement to a documented period, then confirm that the table TTLs and any export copies match it. Check that backups hold the same history you expect to restore.

Operating system and network access

Query and session records show activity inside ClickHouse only. They do not show who logged into the host over SSH, who changed files under the data directory, or which network paths reached the server. Collect host authentication logs, file integrity events and firewall logs in a separate pipeline and correlate them with ClickHouse records during investigations.

SIEM and correlation rules

ClickHouse writes records but does not alert on them, correlate them across systems or hold them for investigators. Your security team must choose the destination, write detection rules for suspicious logins or bulk reads, and test that alerts fire. Decide whether a SIEM, a platform such as Wazuh, or a log tool will own this work.

Tamper resistance against administrators

A user with administrative rights in ClickHouse, or on the host, can alter or delete rows in the system tables and the files behind them. Protect the trail by shipping records to storage that administrators cannot rewrite, restricting who can change logging settings, and reviewing access to that storage. Ask who can change the audit configuration and how changes are recorded.

Operating effectiveness testing

Showing that logging was configured once does not show that it kept working over a period. Auditors who test operating effectiveness sample logs, check that exports arrived, and check that reviews happened. Plan periodic checks that confirm log volume, export status and review sign-off, and keep the results as evidence.

Questions buyers ask about ClickHouse audit logging

How much effort does a compliant ClickHouse audit trail take?

The effort depends on how many clusters you run, how many people and service accounts can connect, which SIEM receives the data and how much masking the logs need. A single cluster with a small set of named users and an existing SIEM is a bounded job. Each added cluster repeats the configuration, retention and export work, and each identity source (LDAP, Kerberos, certificates or JWT) adds mapping and testing. Masking needs add the most design work, because someone must decide which values can appear in query text and then prove that they do not. Ask any partner to break the estimate down by these drivers rather than quoting one lump sum.

Can an in-house team set up ClickHouse audit logging alone?

Yes, if the team already runs ClickHouse and understands its system tables, access control and log pipeline. The core settings are documented, and the work is mostly configuration, testing and documentation. The gaps usually sit outside ClickHouse: confirming that session events line up with identity provider records, agreeing retention with legal and compliance staff, and getting the SIEM parsing right. Teams without recent ClickHouse operations experience often benefit from an outside review of the design before they build it. Whichever route you choose, assign one owner for the audit trail so that changes to users, roles or log settings are themselves recorded.

What does the client need to provide for an audit logging engagement?

A partner will need working access to the ClickHouse cluster or a staging copy, read access to the identity systems that authenticate users, and named owners on the SIEM side who can confirm the ingestion format and alert rules. You also need to make the retention decisions in advance, since the audit trail must match your policy and any regulatory duty you carry. Expect to supply a list of user types, including humans, applications and service accounts, plus a sample of the queries your teams run so masking rules can be tested against real patterns. Confirm access terms and where the work is performed before the kickoff.

How long does it take to verify that the audit trail works?

No duration can be stated without knowing the cluster count, the SIEM and the number of identity sources, so treat any fixed number you see quoted for this work with caution. Verification has distinct parts that you can plan separately: confirming that logins create session records with the expected user and client details, confirming that query records appear in the SIEM within your agreed delay, testing that masked values never reach the logs, and rehearsing a retention cleanup. Ask for the verification steps and the evidence each one produces as part of the written scope, and agree the timeline only after those steps are defined.

What should a consultant's scope for ClickHouse audit logging include?

A complete scope usually covers these areas:

Inventory of users, roles, authentication methods and the default user, which should be restricted.
Configuration of session and query logging, with retention and rotation settings written down.
Design of how records are exported to the SIEM, including parsing, field mapping and dashboards.
Masking rules for sensitive values in query text, with tests that prove them.
Access reviews for the audit configuration itself and a change record for each update.
Runbook steps for investigating a login or query event, plus handover to your operations team.

The scope should also name what is excluded, such as SIEM licensing, identity provider changes or legal retention advice. Those items belong to other owners.

How do I ask a partner for a fixed-scope quote on this work?

Send the partner a written description of your cluster count, ClickHouse version, identity sources, SIEM product and version, user population, masking requirements and the compliance frameworks that apply. Ask for deliverables, acceptance tests, the client inputs each step depends on and the assumptions behind each price component. DeployOpen works from a written scope that sets out deliverables and timelines before work starts, and it does not publish a price list, so the quote follows from that scope. Ask which items are one-time and which recur, such as log review or upgrade support, and how changes to scope would be priced.

Does ClickHouse audit logging need a separate SIEM?

The audit trail lives in ClickHouse system tables, so you do not strictly need a SIEM to record events. However, most security teams want events correlated with identity and endpoint data, alerted on and retained outside the database. Your choice of SIEM affects ingestion effort, parsing work and licensing, so decide it before the design is fixed. Open-source options such as Wazuh are one category to evaluate, and you should confirm how any candidate ingests ClickHouse output in your environment. Keep in mind that a SIEM holding copies of sensitive query text becomes part of your data protection scope.

Choosing how audit events reach your SIEM

The first decision is whether audit events leave ClickHouse by push (a collector forwards rows as they are written) or by pull (a scheduled job queries system.query_log and system.session_log and sends the results on). Push fits organizations that already run a log collector such as syslog, Fluent Bit or Filebeat, because it reuses existing transport, retry and monitoring. Pull is simpler to stand up and keeps the SIEM credentials away from the database hosts, but it adds a delay equal to the schedule interval and can miss events if the query window is wrong or the job fails silently. Whichever path you choose, confirm the exact ingestion method with your SIEM's documentation, and test that a failed collector or job produces an alert.

Loss handling is where the two options differ most. Push pipelines need a buffer and a delivery acknowledgment, so you must decide what happens when the SIEM is unreachable. Pull jobs need a stored high-water mark, such as the last event timestamp or query_id exported, so a restart resumes where it stopped instead of re-reading or skipping a window. In either case, compare the SIEM's counts against the ClickHouse log tables on a regular basis. Access also differs: a pull job needs a database user with SELECT on the log tables and nothing else, which should be created through the access control workflow described elsewhere on this page, while a push agent usually needs only file read permissions.

Wazuh is one open-source option for the receiving side. It is licensed under the GNU General Public License version 2 with an OpenSSL linking exception, and it documents agentless monitoring through syslog collection, so ClickHouse log lines forwarded over syslog can be decoded and alerted on without installing an agent on the database host. Whether that path suits your environment depends on how you want to parse JSON-formatted log rows and on your existing SIEM contract, which is the organization's decision. Confirm the supported syslog formats, decoder approach and volume limits in the Wazuh documentation, and test with a realistic sample of your own query log before committing to a design.

How audit logging supports each framework

SOC 2 examinations test whether controls operate as described against the 2017 Trust Services Criteria. Audit logging from ClickHouse mainly supports the common criteria on logical access (CC6) and system operations (CC7), where examiners look for evidence of who accessed the system and what happened inside it. The trail is one input. The service organization still writes the system description and designs the controls.

Access evidence (CC6): session_log records show successful and failed logins, which feed periodic access reviews.
Activity evidence (CC7): query_log records show statements run against the cluster, which helps during incident investigation.
Integrity of records: exported logs should be write-protected or copied to a separate store so that the examiner can trust them.
Scope decisions: the organization decides whether availability, confidentiality or other categories apply, and which systems the logs cover.

Ask your SOC 2 auditor which criteria apply and which log samples they will request before you fix retention and export settings.

How DeployOpen scopes and delivers ClickHouse audit logging

DeployOpen treats the audit trail as a designed deliverable: which identities, query records and session events must be retained, where they are exported, and how masking and retention are verified before handover. The work runs inside your own ClickHouse infrastructure, under a written scope agreed before build starts.

How we scope

We inventory which users, roles and applications connect to your ClickHouse clusters and how they authenticate today.
We agree which query_log and session_log fields your audit questions require, and which sensitive values must never be written to logs.
We define retention periods, export targets and access to the audit data itself in a written, fixed-scope agreement before work starts.

How we build

We configure logging settings, system table retention and export pipelines on a pre-production cluster with representative queries first.
We test masking rules with deliberately sensitive test queries to confirm that protected values do not appear in stored log entries.
We connect the export path to your SIEM and confirm that login and query events arrive with the fields your analysts need.

How we hand over

We document the configuration, table retention settings, masking rules, export path and the access model for the audit data.
We provide runbooks for reviewing login failures, investigating a query history and checking that the export pipeline is healthy.
We walk your operations and security teams through test results, open items and the steps needed to change logging later.

How we support

Ongoing support, upgrades and continuous testing are available as a retainer, so logging stays aligned with each ClickHouse release.
We can re-verify masking and retention after upgrades, because release changes can affect system table columns and settings.
Your team confirms the supported ClickHouse versions and the changelog review process before any upgrade affecting audit logging.

Audit logging in ClickHouse with query_log and session_log