> ## Documentation Index > Fetch the complete documentation index at: https://mintlify.hoop.dev/docs/llms.txt > Use this file to discover all available pages before exploring further. # ClickHouse > Protect ClickHouse native TCP queries with guardrails, AI risk analysis, audit and response masking, alone or beside PostgreSQL and MySQL lanes A `clickhouse` lane decodes ClickHouse's native TCP protocol, used by `clickhouse-client` and native Go, Java and Python drivers. It reads Query packets before ClickHouse executes them and rebuilds typed result blocks when a mask changes a value. Use the native lane in front of ClickHouse port `9000` for plaintext or its configured secure native port, commonly `9440`, for TLS: ```yaml config.yaml theme={"dark"} pii: entities: [EMAIL_ADDRESS, BR_CPF] listeners: - name: analytics protocol: clickhouse listen: 0.0.0.0:19006 upstream: clickhouse.internal:9000 clickhouse: max_frame_bytes: 16777216 # 16 MiB; optional max_block_bytes: 67108864 # 64 MiB; optional guardrails: rules: - name: no-cpf-in-query type: pii entities: [BR_CPF] message: do not put a taxpayer id in a query mask: rules: - {name: emails, entities: [EMAIL_ADDRESS], strategy: redact} ``` Validate before binding the port: ```bash theme={"dark"} hoop start sidecar --config config.yaml --validate ``` Then start the lane and point the client at its `listen` address: ```bash theme={"dark"} hoop start sidecar --config config.yaml clickhouse-client \ --host 127.0.0.1 \ --port 19006 \ --user appuser \ --password apppass \ --database appdb \ --query 'SELECT name, email FROM customers ORDER BY name' ``` A native driver uses the same address. For example, use `clickhouse://appuser:apppass@127.0.0.1:19006/appdb` instead of the database's direct address. *** ## One Sidecar for ClickHouse, PostgreSQL and MySQL One process runs one lane per upstream, and the lanes share nothing on the wire: a `clickhouse` lane speaks native ClickHouse, a `postgres` lane pgwire, a `mysql` lane the MySQL protocol. What they share is the top of the file. `pii`, `guardrails`, `mask` and `analyzer` at the top level are **defaults** every lane inherits, so a rule written once protects the warehouse and both transactional databases. Each lane adds or overrides only what differs. ```yaml config.yaml theme={"dark"} license: /etc/hoop-inspect/license.json # more than one rule needs one; see Licensing admin: listen: 127.0.0.1:19000 audit: file: "-" pii: entities: [EMAIL_ADDRESS, BR_CPF, CREDIT_CARD] guardrails: # inherited by all three lanes rules: - name: no-cpf-in-query type: pii entities: [BR_CPF] message: do not put a taxpayer id in a query; it lands in the database's own logs - name: protect-customers type: table tables: [customers] access: write require_table_match: true message: nothing may write to the customer ledger through this proxy mask: # inherited unless a lane replaces the list rules: - {name: emails, entities: [EMAIL_ADDRESS], strategy: redact} - {name: cards, entities: [CREDIT_CARD], strategy: partial, keep_last: 4} analyzer: # provider and credential are process-wide provider: vertex model: claude-sonnet-4-5@20250929 extra: {project: my-gcp-project, region: global} send: redacted # detected values never leave the process fail_open: true cache: {size: 4096, ttl_sec: 900} max_calls: 500 listeners: - name: warehouse protocol: clickhouse listen: 0.0.0.0:19006 upstream: clickhouse.internal:9000 guardrails: rules: - name: no-warehouse-ddl type: operation operations: [drop, truncate, unknown] message: schema changes go through migrations, not this proxy analyzer: trigger: {operations: [alter, delete]} # mutations and lightweight deletes high: block medium: warn - name: appdb protocol: postgres listen: 0.0.0.0:15432 upstream: appdb.internal:5432 upstream_tls: ca_file: /etc/hoop-inspect/certs/appdb-ca.crt guardrails: rules: - name: no-destructive-sql type: operation operations: [drop, delete, truncate] message: destructive statements are not permitted on appdb mask: rules: # REPLACES the default list on this lane only - {name: ssn-column, columns: [ssn], strategy: partial, keep_last: 4} - {name: emails, entities: [EMAIL_ADDRESS], strategy: redact} analyzer: trigger: {operations: [update, delete]} high: block medium: warn message: refused by risk analysis - name: orders protocol: mysql listen: 0.0.0.0:13306 upstream: mysql.internal:3306 guardrails: rules: - name: no-destructive-sql type: operation operations: [drop, delete, truncate] message: destructive statements are not permitted on orders analyzer: trigger: {operations: [update, delete]} high: block medium: warn max_calls: 100 # overrides the inherited 500 on this lane only ``` ### What each lane resolves to The file does not show the merge; the running process does. Inheritance follows one rule per block, and the same rule on every protocol: | Block | Merge | In this file | | - | - | - | | `guardrails.rules` | concatenate, lane first | every lane enforces `no-cpf-in-query` and `protect-customers` plus its own | | `mask.rules` | replace | `warehouse` and `orders` mask emails and cards; `appdb` masks the `ssn` column and emails, not cards | | `analyzer` | per lane; unset fields inherit | one provider and credential; `orders` caps its own budget at 100 calls, the others inherit 500 | ```bash theme={"dark"} hoop start sidecar --config config.yaml --validate ``` ```text theme={"dark"} config OK: 3 listener(s) license: valid. enterprise "Acme Corp", expires 2027-01-30, features: all (from the "license" config key) limits: unlimited guardrail rule(s), unlimited data masking rule(s) warehouse clickhouse enforcing 3 rule(s) + masking + ai analyzer appdb postgres enforcing 3 rule(s) + masking + ai analyzer orders mysql enforcing 3 rule(s) + masking + ai analyzer ``` `GET /config` on the admin listener returns the same resolved stack by rule name. With `provider: vertex`, `--validate` also mints one token, so a bad credential fails here rather than on the first risky statement. ### The same rule, three protocols The classifier reads each dialect with its own lexer, so one rule set means one thing on every lane: | Statement | Lane | Classified as | Result | | - | - | - | - | | `ALTER TABLE customers DELETE WHERE id = 1` | `warehouse` | `alter`, writes `customers` | denied by `protect-customers` | | `DELETE FROM customers WHERE id = 1` | `warehouse` | `delete`, writes `customers` | denied by `protect-customers` | | `DELETE FROM customers WHERE id = 1` | `appdb`, `orders` | `delete` | denied by `no-destructive-sql`, the lane's own rule, first | | `INSERT INTO staging SELECT * FROM customers` | any | writes `staging`, reads `customers` | allowed by `protect-customers`; `access: write` ignores the read | | `SELECT email FROM appdb.customers` | any | `select`, reads `appdb.customers` | allowed; the result is masked | | `OPTIMIZE TABLE customers FINAL` | `warehouse` | `unknown` | denied by `no-warehouse-ddl`, which names `unknown` | | `SELECT * FROM customers WHERE cpf = '123.456.789-09'` | any | `select` | denied by `no-cpf-in-query` before it reaches the database log | A table rule matches a schema-qualified name by its last segment, so `tables: [customers]` covers `appdb.customers` on ClickHouse and `public.customers` on PostgreSQL alike. ClickHouse mutations are `ALTER` statements: a rule or trigger that names only `delete` misses `ALTER TABLE … DELETE`, which is why the `warehouse` analyzer triggers on `alter` too. The controls surface in each client's native error and result framing: | Control | `clickhouse` | `postgres` | `mysql` | | - | - | - | - | | Guardrail or analyzer `block` | `Code: 497. DB::Exception: (ACCESS_DENIED)`, connection closes | `FATAL: `, connection closes | `ERROR 1142 (42000): `, session stays open | | Masking | typed blocks decoded, rewritten and re-framed with new LZ4 frames | `DataRow` re-framed per value | text and binary rows re-framed per value | | Column rule names | result block column names | `RowDescription` names | column definition names | | `downstream_tls` | supported, TLS on connect | supported, in-band `SSLRequest` | refused; client leg is plaintext | | `upstream_tls` | supported | supported | supported | ### Connect to each lane ```bash theme={"dark"} clickhouse-client --host 127.0.0.1 --port 19006 --user appuser --password apppass \ --database appdb --query 'SELECT name, email FROM customers LIMIT 5' PGSSLMODE=disable psql -h 127.0.0.1 -p 15432 -U appuser -d appdb \ -c 'SELECT name, email, ssn FROM customers LIMIT 5' mysql -h 127.0.0.1 -P 13306 -u appuser -p --ssl-mode=DISABLED orders \ -e 'SELECT name, email FROM customers LIMIT 5' ``` Every lane writes to the one audit stream, keyed by `connection`, so a session that touched all three databases reads as three connections with one principal: ```bash theme={"dark"} curl -s 'localhost:19000/api/events?connection=warehouse' | jq curl -s 'localhost:19000/api/sessions?limit=10' | jq '.sessions[] | {connection, verdict, risk_level}' ``` Roll the analyzer out with every tier on `warn` and read `by_risk` in `/api/stats` before moving `high` to `block`. The guardrails deny for free and never leave the process; keep every risk you can name in an `operation` or `table` rule and spend the model only on what those cannot express. See [AI Analyzer Reference](/docs/setup/configuration/hoop-sidecar/risk-analysis). *** ## What the lane reads The codec follows one connection in both directions because a Query packet decides how its reply is framed. | Direction | The codec reads | What Hoop uses it for | | - | - | - | | client → ClickHouse | Hello, Query, Data, Cancel and Ping packets | SQL text, query id, operation, tables, policy and audit | | ClickHouse → client | Hello, Data, Totals, Extremes, Progress, Profile, Exception and EndOfStream packets | result columns, row count, errors, response masking and audit | The ClickHouse lexer understands backtick identifiers, `#` and `--` comments, backslash string escapes and double-quoted identifiers. A statement is classified before its Query packet is sent upstream. Multi-statement text is split and every statement is evaluated, even though a native ClickHouse server normally rejects multi-statements itself. The lane is a byte relay, not a ClickHouse account or connection pool. The client still authenticates directly to ClickHouse, and ClickHouse remains the authority for database permissions. *** ## Protocol revision ClickHouse native packets have no outer length. Their layout depends on the protocol revision negotiated in the two Hello packets. Reading one packet with the wrong layout would also lose the boundary of every packet after it. The lane rewrites both advertised revisions to at most `54450` before either Hello is decoded or forwarded. ClickHouse already negotiates the lower peer revision, so clients and servers use their normal backward-compatibility path. The rewrite is transparent to ordinary queries, but features introduced after the pin are unavailable on this connection. The lane fails closed on an unknown packet or a flow outside the implemented ordinary query path. It never forwards a stream after it can no longer prove where the next Query packet begins. *** ## Compression and memory limits LZ4 is ClickHouse's default native compression and is supported. Uncompressed blocks are supported too. ZSTD is refused because the lane does not inflate it. If a client selects ZSTD, keep native compression enabled and select LZ4: ```bash theme={"dark"} clickhouse-client \ --host 127.0.0.1 --port 19006 \ --compression=true \ --network_compression_method=lz4 ``` Every compressed frame carries a CityHash checksum and declared compressed and decompressed sizes. The lane verifies the checksum and checks both sizes before allocating the decompressed buffer. | Field | Default | Bounds | | - | -: | - | | `clickhouse.max_frame_bytes` | 16 MiB | one frame's compressed payload and declared decompressed output | | `clickhouse.max_block_bytes` | 64 MiB | one complete decompressed native block | A result set can contain any number of blocks. The lane holds and reuses memory for one block at a time; it does not accumulate the result. The generic stream reassembly limit is derived from `max_block_bytes` with bounded compression and packet-header slack, so a valid block larger than 8 MiB is not rejected by the generic inspector first. Set limits from the largest **single block**, not from the total result size. ClickHouse commonly emits about 1 MiB compression frames and up to 65,000 rows per block. Keep the defaults unless a measured query logs a limit refusal, then raise the affected limit only enough for that block shape. The limits are per active connection. Raising them raises the amount of memory a client can make the Sidecar retain. A frame declaration above `max_frame_bytes` closes the session before allocation; `max_block_bytes` closes it as the decompressed frames accumulated for that block cross the cap. *** ## Masking result columns ClickHouse sends typed columns rather than row strings. The lane decodes a complete block, masks each supported string value, re-encodes the block and rebuilds its LZ4 frames and checksums. A replacement may be longer or shorter without corrupting the client stream. | Column type | Masking behaviour | | - | - | | `String` and byte strings | entity and column rules rewrite each value | | `FixedString(N)` | replacement is truncated or NUL-padded back to exactly `N` bytes | | `Nullable(String)` | non-NULL values are rewritten; NULL stays NULL | | `LowCardinality(String)` | values are rewritten and the dictionary is rebuilt | | Numeric, date and other non-string types | preserved while the surrounding block is rebuilt | | `Array(String)`, string-bearing `Map` or `Tuple`, and other unsupported nested strings | the masking-enabled session is refused rather than returned in cleartext | Prefer column rules for columns whose names are stable. Entity rules are useful when the query shape changes or the same sensitive value appears in several columns: ```yaml config.yaml theme={"dark"} mask: rules: - {name: customer-email, columns: [email], strategy: redact} - {name: discovered-email, entities: [EMAIL_ADDRESS], strategy: redact} ``` When masking is disabled, supported ordinary query blocks are inspected and forwarded without rebuilding unchanged values. *** ## Denials A matching guardrail stops the Query packet before it reaches ClickHouse and returns a native ClickHouse Exception with code `497` and the rule's message: ```text theme={"dark"} Code: 497. DB::Exception: do not put a taxpayer id in a query. (ACCESS_DENIED) ``` The audit trail records a `violation` with the statement, operation, tables, rule and message. Allowed queries produce a client-side `statement` event and a server-side completion event at Exception or EndOfStream. A rewritten result also emits `masked` with entity names and a count, never the original values. *** ## TLS on each leg ClickHouse native TLS starts with a TLS ClientHello on the first byte. The two legs are independent: | Leg | Configuration | | - | - | | client → Sidecar | plaintext when `downstream_tls` is absent; TLS terminated by the Sidecar when present | | Sidecar → ClickHouse | plaintext when `upstream_tls` is absent; verified TLS when present | To encrypt both legs: ```yaml config.yaml theme={"dark"} listeners: - name: analytics protocol: clickhouse listen: 0.0.0.0:19006 upstream: clickhouse.internal:9440 downstream_tls: cert_file: /etc/hoop-inspect/certs/lane.crt key_file: /etc/hoop-inspect/certs/lane.key upstream_tls: ca_file: /etc/hoop-inspect/certs/clickhouse-ca.crt server_name: clickhouse.internal ``` Connect to a TLS-enabled client leg with `--secure`: ```bash theme={"dark"} clickhouse-client --secure --host sidecar.internal --port 19006 \ --user appuser --password apppass --database appdb ``` `upstream_tls` does not enable ClickHouse's secure port. Configure `tcp_port_secure` and the server certificate in ClickHouse first, then point `upstream` at that port. Certificate verification or handshake failure closes the connection; the lane does not downgrade to plaintext. *** ## Native, HTTP or an emulation port One ClickHouse server can expose several protocols. They are separate Sidecar listeners and do not share capabilities. | ClickHouse interface | Listener protocol | Use it for | | - | - | - | | native `9000` / secure native port | `clickhouse` | **Recommended:** native clients, query guardrails and typed response masking | | HTTP `8123` | `http` | HTTP method/path policy, bounded body capture, and result masking: TSV, CSV, `JSONEachRow` and `FORMAT JSON` results are masked as they stream. ClickHouse POST SQL is body metadata, not SQL guardrail text | | MySQL emulation `9004` | `mysql` | clients that require MySQL compatibility, subject to MySQL capability negotiation | | PostgreSQL emulation `9005` | `postgres` | clients that require pgwire compatibility | Do not point a `clickhouse` listener at the HTTP or emulation ports. The `protocol` field selects the wire decoder; a mismatch fails the connection. **The HTTP interface behind an `http` lane.** ClickHouse answers every request `Transfer-Encoding: chunked`, and the codec re-chunks what it rewrites. `FORMAT TSV` and `CSV` rows are lines and go out line by line; `JSONEachRow` is NDJSON and goes out row by row, each value handed to the masker under its column name, so `columns: [email]` names the column the way it does on the native lane; `FORMAT JSON` is one document with the rows under `data`; `RowBinary` and `Native` are binary and pass untouched. With `enable_http_compression=1`, gzip and deflate are undone around the masker; a client that asks for zstd, br or lz4 on a text or JSON result is answered 403 and the trail gets an `error` row, because that response would be the one that left unmasked. Guardrails read the request line, so a SQL rule never sees the POST body; `?query=` and `?param_x=` on a GET are on the line and are scanned. Set `http.capture_body: true` to record the SQL in the audit trail; it records the response as the server sent it too, so the trail holds the unmasked result set even when the client received it masked. *** ## Troubleshooting | Symptom | Check | | - | - | | Connection closes before the query runs | Read the Sidecar log for an unsupported packet, revision flow or malformed native frame. | | Log says ZSTD is unsupported | Set `network_compression_method` to `lz4` on the client/session. | | Log names `max_frame_bytes` or `max_block_bytes` | Measure the largest frame or single block and raise only that listener's limit. | | A flat string is returned unmasked | Check `/config` for the resolved mask rules, then prefer a `columns` rule for known column names. | | The log names `Array(String)`, `Map` or `Tuple` | Return the sensitive field as a flat `String`, or remove masking from that listener. | | TLS client sends unreadable bytes to a plaintext lane | Add `downstream_tls`, or remove `--secure` from the client. | | Upstream TLS handshake fails | Confirm ClickHouse's secure native port, CA, certificate name and `upstream_tls.server_name`. | Inspect the resolved listener and its audit events: ```bash theme={"dark"} curl -s localhost:19000/config | jq '.lanes[] | select(.name == "analytics")' curl -s 'localhost:19000/api/events?connection=analytics' | jq ``` *** ## Next Deny ClickHouse statements by operation, table, text pattern or sensitive request values. Choose entity, column and replacement strategies for native result values. Listener inheritance, TLS fields, audit and startup validation. Follow one connection through inspection, policy, audit and response rewriting.