Masking sensitive data in ClickHouse logs

Masking sensitive data in ClickHouse logs starts with knowing where it lands

Applications that build SQL from user input or form data often put emails, card numbers and customer identifiers directly into query text. ClickHouse can record that text in system.query_log and in server text logs, so the raw values can persist long after the query finished. The query_masking_rules setting replaces matching text in query and log output so the raw values are not stored. Before you write any pattern, list every place a query is recorded: the query log tables, the server log files, any log shipper that collects them, and any backup or export of those tables. Each of those locations has its own retention period and its own set of people who can read it, and masking only protects the places it reaches.

Teams usually need this control for three reasons. Log retention often runs longer than the data's business purpose, and operations groups, support staff and monitoring vendors frequently have read access to logs that was never meant to hold customer data. Under Article 32 of the GDPR, security of processing covers appropriate technical measures, and keeping personal data out of routine logs is a common one to evidence. Where cardholder data could reach logs, the PCI DSS scope of the cardholder data environment may extend to those logs, so you should confirm with your assessor whether they fall inside it. Confirm these points with your legal, privacy and assessor contacts rather than assuming the answer.

Masking reduces exposure but does not replace fixing the application that puts data into SQL. Pattern rules can miss formats they were not written for, and a value that is never masked still reaches the database engine and any system that processes the query. Treat masking as a containment layer, and plan the application change, parameterized queries or tokenization of identifiers as the primary fix. The remaining sections explain how to write the patterns, apply them to every node and test that matches happen.

Where sensitive values end up in a ClickHouse deployment

Sensitive values rarely leak through one obvious door. They reach storage through the SQL text users submit, the server's own logs, the error messages returned to clients, and the logs kept by applications and proxies in front of ClickHouse. Each location needs its own check before masking rules can be trusted.

Query text in query_log

Every statement a user submits can be recorded in system.query_log, including literals such as an email address in a WHERE clause or a card number pasted into an INSERT. Masking rules apply to this text, but only if they match the way the value is written. Check which users and roles can read system.query_log, whether any dashboards or exports copy it elsewhere, and how long your configuration keeps it. Confirm those settings in your own configuration rather than relying on defaults.

Server text logs

The server writes its own text logs, and statements or parameters can reach them through error paths and debug output. A masking rule that covers query_log does not automatically cover these files, so you need to confirm that the same rule set is active for the logging path you use. Check the log file locations, the permissions on those files, and whether the log level you run in prints query text.

Exception messages sent to clients

When a query fails, the error text returned to the client can repeat part of the offending statement or a value from the data. Those messages are seen by the application, the developer console and often by the user who sent the query, so they fall outside the server log review. Test a few deliberately bad queries containing sample sensitive values and inspect the response bodies. Decide whether your applications should show raw database errors to end users at all.

Client and application logs

Applications, ORMs, job runners and ingestion agents often log the SQL they send before ClickHouse receives it. Those logs can hold the same values that masking was meant to hide, and they may be shipped to a central log platform. List every component that writes ClickHouse traffic to logs, and confirm which of them capture statement text, bind parameters or request bodies.

Proxy and gateway logs

Load balancers, API gateways and HTTP proxies in front of ClickHouse can record request URLs and bodies, and HTTP interface queries can place SQL in the URL. Check the access log format on each proxy, the fields it captures, and who receives those log exports. A gateway log that stores full request bodies can reintroduce values that ClickHouse masked on its side.

Log export and access reviews

Logs leave the cluster through shipping agents, backups and support bundles, and each copy is a new place for values to sit. Review who can read exported logs, where they are stored, and which backups include system tables. Keep a written list of recipients and confirm that access to the log platform follows the same role model you apply inside ClickHouse.

Setting up query_masking_rules on every ClickHouse node

This sequence takes you from an inventory of sensitive values in queries and logs to a tested set of masking rules that applies identically on every node. The result is log output where emails, card numbers and identifiers no longer appear in query_log or text logs.

Inventory query shapes and log destinations
Your team lists the queries, ingestion statements and client applications that can place sensitive values into SQL text, such as email addresses in WHERE clauses, card numbers in inserted rows or customer identifiers in dictionary lookups. For each, record which log destinations capture the text: the query_log system table, the server text log and any log shipper that forwards them. Done means you have a written list of data types, the statements that carry them and every destination that stores the resulting text, reviewed by the data owner.
Draft one named rule per data type
Write a separate rule for each data type on your list, such as email, payment card number and internal customer ID. Each rule has a descriptive name, an RE2 regular expression that matches the value format and a replacement string that keeps the text readable, for example a fixed token. Keep patterns narrow enough that they do not mask ordinary column names or numbers used in your queries. Done means each rule has a name, pattern and replacement recorded in a review document that a second engineer has checked.
Add the rules to server configuration on each node
Add the query_masking_rules block to the server configuration, using the same rule names, patterns and replacements on every replica and every shard. Deploy the identical file through your configuration management so no node runs a different version. Because a distributed query can be logged on whichever node receives it, a single node without the rules leaves sensitive text in its logs. Done means a checksum or diff of the configuration file matches across all nodes.
Restart or reload according to your version
Apply the configuration using the restart or reload behavior described in the ClickHouse configuration documentation for your version, and confirm for your version whether query_masking_rules is picked up on reload or needs a full restart. Roll the change one node at a time on replicated clusters so queries keep running. Done means every node reports the new rules as active, which you can check by issuing a test query on each node and confirming the rule is applied.
Run known test queries against each node
Send test queries that contain synthetic values matching each rule, such as a fake email address, a test card number that fails your own validation and an identifier in your format. Send them to every node directly, not only through a distributed table. Use clearly fake values so no real data is exposed during testing. Done means each test query is recorded with the synthetic value present in the original statement and absent from the log output.
Compare logged text on each replica
Query system.query_log on every node for the test statements and compare the stored query text with the expected masked output. Check the text log as well, since it can record errors that contain query fragments. Compare the results across replicas so you can see any node where a rule is missing or a pattern behaves differently. Done means the masked text matches on all nodes and the synthetic values appear in neither table.
Record evidence and schedule recurring checks
Save the rule file, the test queries, the query_log results and the node-by-node comparison as evidence for your security or compliance file. Set up a recurring check that runs the same test queries after each configuration change and each ClickHouse upgrade, since the logged text format can change between releases. Done means the evidence is stored with a date and version, and the recurring check has an owner who receives its results.

Masking rule settings and when each fits

This table compares the four parts of a masking rule you must decide: its name, its RE2 pattern, its replacement text and its scope. Read each row for when an option fits and what it costs you in false positives or missed values. Confirm exact setting keys in the ClickHouse documentation for your version before you write any configuration.

Rule part	What it controls	When it fits	Trade-off to weigh
Rule name	A label that identifies the rule in configuration, reviews and audit discussions	One rule per data class (for example, email addresses, payment card numbers, customer identifiers) so each can be reviewed separately	Vague names such as rule1 make later review hard, and one combined rule hides which data class a match came from
RE2 pattern	The expression that finds the sensitive text in query or log content	Tightly anchored patterns for formats you know well, such as a fixed-length card number with a Luhn-style check if you can express it	Broad patterns mask legitimate values such as version strings or hashes, while narrow patterns miss variants like spaces or dashes in card numbers
Replacement string	The text written in place of each match	A fixed placeholder that shows masking occurred, so analysts know a value existed without seeing it	Replacements that keep part of the value, such as the last four digits, leak more than a full mask and need a clear policy approval
Scope	Which text is masked: query text, or specific log outputs where the docs allow narrower targeting	Query text sent to query_log when users paste literal values into SQL, which is the most common leak path	Broader scope covers more outputs but may affect debugging; confirm which log tables and text logs your version applies rules to
Ordering and overlap	How rules interact when several patterns match the same text	Specific patterns that run before any general catch-all pattern for unknown identifiers	Overlapping rules can produce unexpected output, so test the combined effect rather than each rule alone
Coverage of unknown formats	Patterns for identifiers you do not yet classify	Temporary broad patterns while a data inventory is completed, with a planned review date	Broad patterns create noise in logs and can make incident investigation harder if they remove needed context

Writing RE2 patterns for ClickHouse log masking

RE2 has no backreferences and no lookaround assertions, so a masking pattern cannot rely on either. Patterns for query_masking_rules must match the full sensitive token and replace it in one pass, with no help from context that only a regex engine with lookahead could use. Confirm the RE2 behavior your ClickHouse version ships with before you write the first rule, because the supported syntax is the one defined by the engine it is compiled with, not by generic regex tutorials. Expect to write patterns that are a little broader than a developer would write for validation, and accept that trade-off deliberately.

For emails, a practical pattern matches a local part of letters, digits and common symbols, an @, and a domain with at least one dot, then replaces the whole match with a fixed mask. For payment card numbers, match 13 to 19 digits with optional spaces or hyphens between groups of four, since card data appears in both forms. Do not try to validate the Luhn checksum in the regex, because RE2 cannot express that logic, and a loose match is safer than a missed number. For customer or account IDs, match your internal format exactly, such as a fixed prefix followed by a known digit count, so the rule hides identifiers without masking every integer in the query. Overly broad patterns are the main risk: masking every long number can remove the literal values, table names and LIMIT clauses that a support engineer needs to reproduce a slow query.

Build a test set before deployment. Include realistic but non-production values in every format you expect, such as card numbers with and without separators, emails with subdomains, and IDs at the edges of their length range, plus near-miss strings that must not be masked, like timestamps and version numbers. Run each pattern against this set in a scratch environment, then run a sample of real query text through the same rules in a non-production cluster to confirm the query structure stays readable. Store the test set and expected output with the rule definitions so a later change is checked the same way. Document what the masked output looks like so that operators know the mask is intentional when they read logs.

Checking that masking matches in each log type

The query log is usually the first place sensitive literals end up, because ClickHouse records each statement with its text. Run a test statement that contains synthetic values matching your rules, tag it with a unique comment so you can find it again, then read the stored text. A passing result shows the masked replacement where the literal was, and the raw test value does not appear in the recorded query.

Use a unique marker. Run a query such as SELECT 'test.user@example.com', '4000 0000 0000 0002' /* masking-check-qlog */ so the entry is easy to filter.
Flush before reading. Run SYSTEM FLUSH LOGS or wait for the asynchronous write to complete before querying system.query_log.
Read the stored text. Filter on the marker and select the query column. Confirm that no raw test value remains.
Repeat per node. Query each replica separately, or through a method that reaches every node, since a rule loaded on one server does not protect the others.
Interpret a failure. A raw value usually means the regular expression does not match that literal's format, the rule is missing from the node's configuration, or the server has not reloaded the file.

Check that the raw test value is absent from query_log on every node you query, and record the marker, the time and the node name as evidence.

Server text logs can capture query fragments, parser messages and diagnostic output that never appear in the query log. Check them both in the log files on each host and in system.text_log if your deployment has that table enabled. Use a statement that produces server-side logging for the synthetic value, then search for it. A passing result shows no raw test value in any log destination you collect, with replacement text where the value would otherwise appear.

Choose a trigger. Use a statement that fails or logs at an information level and includes a synthetic card number, email or identifier in its text.
Search the files. Run a case-sensitive search for the raw value in the server log directory on every host, including rotated and compressed files.
Query the table. If system.text_log is enabled, search its message column for the raw value and for the replacement text.
Check collectors. Confirm that your log shipper, such as a forwarder to a central store, receives only masked lines and that no second copy of the raw line exists upstream.
Interpret a failure. A raw value in a file but not in the table often points to a message path that bypasses the rules, while a value on one host only usually points to a configuration that did not reach that node.

Confirm the behavior against your ClickHouse version, because the set of messages covered by masking is the detail most likely to differ between releases.

Error messages are returned to the client, and clients often write them to application logs, tickets or dashboards. Trigger an error with a synthetic value inside the statement, then inspect the message your client actually receives. A passing result shows the error text with the sensitive value masked, and the same holds for any copy the server writes to its own logs.

Build a failing statement. Use an invalid column or type with a synthetic identifier in a string literal, for example a fake email address, so the server must report an error that includes the query text.
Read the client output. Capture the exception message from the same client library your applications use, not only from an interactive console, because libraries may format errors differently.
Compare with the query log. Confirm that the masked form in the exception matches the masked form recorded in system.query_log for the same test.
Check application logs. Search the logs of the application or proxy that calls ClickHouse, since server masking does not cover text your own code writes.
Interpret a failure. A raw value in the client message usually means the rule does not apply to that error path in your version, or the statement failed before the rule was evaluated. Confirm the behavior with the vendor's documentation or a support channel before relying on it.

Record the exact error text and version for each test, so an auditor can see what clients received.

Limits of query masking to confirm before you rely on it

Query masking reduces what ends up in ClickHouse text logs, but it covers only part of the data path. The cards below list the limits to test on your own cluster, so the masking rules you write do not give a false sense of coverage.

Other paths still carry raw values

Masking rules act on text the server writes to its logs. Values that reach ClickHouse through client applications, ingestion pipelines, Kafka or PostgreSQL table engines, or caches can still sit in other systems in plain form. Confirm which upstream and downstream systems hold the same values, and apply controls there as well, such as field-level filtering at ingestion or restricted access to source topics.

Patterns match only written formats

A regular expression matches only the formats it was written for. An email pattern will miss addresses with unusual characters, and a card number pattern may miss values separated by spaces, hyphens or embedded in other text. Build a test set from real log samples, including edge cases, and check both the match and the replacement output before you rely on a rule.

Existing log data stays unchanged

New masking rules apply only to text written after the change. Log rows already stored in system.query_log and in server text log files keep their original content. Confirm how long those rows are retained, whether the logs are exported to another system, and whether you need a separate cleanup or deletion procedure for historical data.

Rules must reach every node

Masking rules are configured per server, so a rule present on one node does not protect logs written by another. Check the configuration on every replica and shard, confirm the rules survive restarts and configuration deployments, and monitor for drift. Confirm the exact configuration file location and reload behavior for your version in the ClickHouse documentation.

Masked output still needs access control

Masking reduces exposure but does not remove access to the logs. Users who can read system.query_log or log files still see the remaining context, query structure and any values the patterns missed. Restrict who can query system tables and read log files, and review those grants with the access model in your ClickHouse deployment.

Changes need regression tests

A later ClickHouse upgrade, a logging change or an edit to a regular expression can silently stop a rule from matching. Keep a regression suite of sample queries with known sensitive values, run it after every change and every upgrade, and confirm against the release notes whether logging behavior changed. Your compliance scope is your own assessment: GDPR and PCI DSS require the controller or merchant to determine which data and systems fall in scope, and DeployOpen prepares controls and evidence but does not issue audit reports or certifications.

Questions teams ask before masking ClickHouse logs

How much effort does a query masking rollout take?

The effort depends mostly on how many sensitive data types you need to mask, how many ClickHouse nodes must carry the same configuration, how many log destinations receive query text, and how much pattern testing the data requires. A rollout that covers one identifier type on a single node is a small change. A rollout that covers emails, card numbers and internal account IDs across a replicated cluster, with text logs and query_log both in scope, adds work at each step. Each data type needs its own RE2 pattern, each pattern needs test strings drawn from real but de-identified samples, and each node needs the same rule set loaded and verified. Ask any provider to break the quote down by these four drivers so you can see where the effort sits.

Can our team mask ClickHouse logs without a partner?

Yes, a capable internal team can do this, provided it has people who understand ClickHouse server configuration, regular expressions and the difference between query_log and the text logs. The work requires someone to write and test RE2 patterns, someone to edit server configuration consistently across nodes, someone who can read system tables to confirm masked output, and someone who owns the change through review. Teams without ClickHouse operators often underestimate the verification step. A partner is most useful when the team needs an outside review of its patterns, a test plan it can reuse, or help covering every node and log destination in a short window.

How long does masking work take to complete?

Duration is set by the same drivers as effort, plus the time it takes to get agreement on what counts as sensitive. Data classification decisions, approval of pattern changes by the data owner, access requests for each node and the change window for restarts or configuration reloads often take longer than writing the patterns. Confirm with your environment team how configuration changes are rolled out in your cluster, and ask any provider for a schedule tied to the written scope rather than a general estimate. Our engagements are fixed-scope, so the timeline is agreed in writing before work starts.

What do we need to provide before the work starts?

You need to supply four things: representative sample queries that show where sensitive values appear, a data classification that names the categories to mask and who owns each one, access to every ClickHouse node and to the log destinations you use, and a named contact who can approve pattern changes. Sample queries matter most, because a pattern that matches a test string may still miss a value written in a different format, inside a string literal or in a function argument. Node access matters because masking rules must be present on each replica, and a rule missing from one node leaves that node's logs exposed.

Does masking query logs satisfy GDPR or PCI DSS by itself?

No. Masking reduces the amount of personal data or cardholder data that appears in logs, but neither the GDPR nor PCI DSS assesses a single control in isolation. The GDPR's security of processing obligation in Article 32 is judged across the whole processing activity, and PCI DSS assessments look at the cardholder data environment as a whole, including access control, logging, network segmentation and key management. Masking supports those controls, and it should be documented as one measure among others. Independent auditors and assessors decide whether your overall set of controls meets the standard. DeployOpen prepares systems, controls and evidence for those assessments but does not issue audit reports or attestations.

What should a masking quote include?

A useful quote lists the scope in writing before any price appears:

The data types to mask, with the owner and the reason each one is sensitive
The log destinations covered, including query_log and text logs
The nodes and clusters that receive the configuration
The RE2 patterns to be written, with the test strings used to check each one
The verification steps, including how matches are confirmed on every node
The handover material, such as configuration files, a test script and a short runbook
Items excluded from scope, such as log shipping pipelines or retention changes

Ask how the quote separates one-time work from any ongoing retainer for pattern review after upgrades or new data types. We do not publish a price list, so the quote follows from the written scope.

What goes wrong after masking rules are deployed?

The most common failures are gaps, not crashes. A pattern may stop matching when an application changes how it formats a value, a new query shape may place a sensitive value where the pattern never looks, or one node may not receive the change after a restart or rolling upgrade. Engineers should check masked output after each ClickHouse upgrade, and after any new ingestion path or query tool is added, because either can write values that the earlier tests never covered. Keep the test strings and a simple check script under version control so the same tests run again after each change.

How DeployOpen works on this

DeployOpen is a Chainscore Labs company that plans, implements, hardens and supports self-hosted open-source platforms inside your own infrastructure. For ClickHouse log masking, we start from the query and text logs your teams actually produce, then write, deploy and verify the masking rules on every node in your environment.

How we scope

We inventory which log sources, query_log tables and text logs carry emails, card numbers and identifiers.
The written scope lists the patterns to cover, the nodes to configure and the match checks to run.
Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.

How we build

We draft RE2 patterns for each identifier type and test them against representative sample log lines.
Rules are deployed through configuration management to every ClickHouse node, so no server logs unmasked text.
We confirm that masked values appear in query_log and text logs after a controlled test query and restart.

How we hand over

You receive the rule set, its configuration files and a record of each pattern and the data type it targets.
The handover includes a repeatable check procedure your team can rerun after upgrades or new log sources.
Documentation explains which patterns are known gaps and which fields masking cannot cover, so risks stay visible.

How we support

Ongoing support, upgrades and continuous testing are available as retainers after handover.
We can review new query patterns and log formats that may expose identifiers the rules do not yet match.
Retainer work can rerun match checks when ClickHouse versions change, and confirm masking still applies.

Masking sensitive data in ClickHouse logs