Secure ClickHouse access

Do not expose an unauthenticated ClickHouse server. A fresh install listens on port 8123 (HTTP) and 9000 (native) with a default user that historically has no password — bind it to the internet and automated scanners will find it within hours. Restrict network paths, use TLS, create separate users and roles, and grant only the databases and operations each workload needs.

Network first: keep ClickHouse listening on private interfaces only, and let applications reach it through a private network or a tunnel. TLS on the client ports keeps credentials and query results off the wire. Then split identities per workload — a pipeline that only inserts and a dashboard that only reads should not share credentials, because a leaked dashboard key must not be able to delete a table.

Least-privilege users

SQL-driven access control makes this concrete:

CREATE USER ingest IDENTIFIED WITH sha256_password BY 'a-long-random-secret';
GRANT INSERT ON analytics.events TO ingest;

CREATE USER dashboards IDENTIFIED WITH sha256_password BY 'another-long-secret';
GRANT SELECT ON analytics.* TO dashboards;

ingest can write events and nothing else — it cannot even read them back. dashboards can read anything in analytics and change nothing. With more than a couple of users, create roles (CREATE ROLE readonly_analytics), grant privileges to the role, and grant the role to users.

Resource limits are security too

Analytical queries can consume large CPU and memory. Apply quotas, timeouts, and resource settings so one dashboard or ad hoc query cannot exhaust the cluster:

CREATE SETTINGS PROFILE dashboard_limits SETTINGS
  max_memory_usage = 10000000000,
  max_execution_time = 30
TO dashboards;

Now a runaway GROUP BY from the dashboard user dies at 10 GB or 30 seconds instead of taking ingestion down with it. A CREATE QUOTA can additionally cap queries per hour for ad hoc users. This is availability protection: an unbounded query is a denial of service, whether or not anyone meant it.

Prove the boundaries hold

Create an ingestion user and a read-only dashboard user. Prove each account is denied the other account’s privileged operation:

$ clickhouse-client --user dashboards --password '...' \
    --query "INSERT INTO analytics.events VALUES (...)"
Code: 497. DB::Exception: dashboards: Not enough privileges.

Run the mirror test too — ingest attempting a SELECT should fail the same way. An access model you haven’t tested from the outside is a diagram, not a control. The realistic failure here isn’t an exotic exploit: it’s one shared superuser credential pasted into every service config, waiting for the first leak to become a full compromise.