ClickHouse

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:

config.yaml

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:

hoop start sidecar --config config.yaml --validate

Then start the lane and point the client at its listen address:

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.

config.yaml

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

hoop start sidecar --config config.yaml --validate

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: <message> (ACCESS_DENIED)`, connection closes	`FATAL: <message>`, connection closes	`ERROR 1142 (42000): <message>`, 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

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:

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.

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:

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:

config.yaml

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:

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:

config.yaml

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:

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:

curl -s localhost:19000/config | jq '.lanes[] | select(.name == "analytics")'
curl -s 'localhost:19000/api/events?connection=analytics' | jq

Guardrails

Deny ClickHouse statements by operation, table, text pattern or sensitive request values.

Data Masking

Choose entity, column and replacement strategies for native result values.

Config File Reference

Listener inheritance, TLS fields, audit and startup validation.

Sidecar Architecture

Follow one connection through inspection, policy, audit and response rewriting.

Getting Started

Sidecar

Features

Control Plane Setup

One Sidecar for ClickHouse, PostgreSQL and MySQL

What each lane resolves to

The same rule, three protocols

Connect to each lane

What the lane reads

Protocol revision

Compression and memory limits

Masking result columns

Denials

TLS on each leg

Native, HTTP or an emulation port

Troubleshooting

Next

Guardrails

Data Masking

Config File Reference

Sidecar Architecture

​One Sidecar for ClickHouse, PostgreSQL and MySQL

​What each lane resolves to

​The same rule, three protocols

​Connect to each lane

​What the lane reads

​Protocol revision

​Compression and memory limits

​Masking result columns

​Denials

​TLS on each leg

​Native, HTTP or an emulation port

​Troubleshooting

​Next

Guardrails

Data Masking

Config File Reference

Sidecar Architecture

One Sidecar for ClickHouse, PostgreSQL and MySQL

What each lane resolves to

The same rule, three protocols

Connect to each lane

What the lane reads

Protocol revision

Compression and memory limits

Masking result columns

Denials

TLS on each leg

Native, HTTP or an emulation port

Troubleshooting

Next