Secure ClickHouse access

Never expose an unauthenticated ClickHouse server. A fresh install listens on port 8123 (HTTP) and 9000 (native) with a default user that historically had no password. Bind that to the internet and automated scanners find it within hours.

Security here is four layers. Restrict the network, use TLS, create separate users and roles, and grant each one only the databases and operations it needs.

Network comes first. Keep ClickHouse listening on private interfaces, 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 must not share credentials. A leaked dashboard key should never be able to delete a table.

Least-privilege users

ClickHouse has SQL-driven access control, so this is a few statements:

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 can’t even read them back. dashboards can read anything in analytics and change nothing.

With more than a couple of users, create roles instead. CREATE ROLE readonly_analytics, grant privileges to the role, then grant the role to users. Changing one role updates everyone who holds it.

Resource limits are security too

Analytical queries can eat huge amounts of CPU and memory. Apply quotas, timeouts, and resource settings, so one dashboard or one ad hoc query can’t take the whole server down:

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 stalling ingestion. A CREATE QUOTA can also 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 the two users, then try the operation each one is not allowed to do:

$ 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 password pasted into every service config, waiting for the first leak to become a full compromise. Two users and two grants take five minutes. Do it on day one.