ClickHouse with the default user wide open — fixing the four classic mistakes

If `default` was created by `users.xml` (the standard Docker / apt path), it is **immutable from SQL** — `ALTER USER default IDENTIFIED…` fails with an access-manager error. Edit the XML instead.

Verify it worked: # Reload happens on file write (CH watches /etc/clickhouse-server/users.d/). # Confirm with: clickhouse-client --user default --password '' --query "SELECT 1" # expect: "Authentication failed" clickhouse-client --user default --password 'your-strong-password' --query "SELECT 1" # expect: 1

Note: CH polls `users.d/` every few seconds — no restart needed. If the change doesn't take effect within ~30s, check `tail /var/log/clickhouse-server/clickhouse-server.err.log` for an XML parse error.

Users created via `CREATE USER` (SQL-managed) can be altered the same way. This is the right path for any non-default user, and for `default` ONLY if it was originally created by SQL (rare).

ALTER USER my_sql_user
  IDENTIFIED WITH sha256_password BY 'a-strong-rotated-password'
  HOST IP '10.0.0.0/8';

Verify it worked: SELECT name, auth_type, host_ip FROM system.users WHERE name = 'my_sql_user'; -- expect: my_sql_user | sha256_password | ['10.0.0.0/8']

Verify it worked: -- Login as prom_user and confirm it can read but cannot mutate. -- (Run from a separate session.) clickhouse-client --user prom_user --password 'a-strong-rotated-password' \ --query "SELECT count() FROM system.metrics" # expect: a number clickhouse-client --user prom_user --password 'a-strong-rotated-password' \ --query "CREATE TABLE t (x Int32) ENGINE=Memory" # expect: ACCESS_DENIED

Note: `readonly = 2` is the strict mode: queries cannot change session settings either. `readonly = 1` lets the client set per-query settings (timeouts, max memory) which is fine for most exporters; pick `1` if your exporter complains about "setting cannot be changed in readonly mode".

If the source IP is a known internal service, the fix is almost always a credential rotation that missed somebody. If the source is external and unfamiliar, restrict reachability at the network layer.

# Group the most recent auth failures by source IP + username:
grep AUTHENTICATION_FAILED /var/log/clickhouse-server/clickhouse-server.err.log \
  | sed -E 's/.*\{[^}]*\} <Error> ServerErrorHandler: Code: 516\. DB::Exception: ([^:]+): Authentication failed.*from ([0-9.]+).*/\1 \2/' \
  | sort | uniq -c | sort -rn | head -10

Verify it worked: # After fixing the misconfigured client (or blocking the bad IP): grep AUTHENTICATION_FAILED /var/log/clickhouse-server/clickhouse-server.err.log \ | awk -F'[][]' '{print $2}' \ | awk '$0 > strftime("%Y.%m.%d %H:%M:%S", systime()-300)' \ | wc -l # expect: a number trending toward zero

There is no built-in CH way to put a password on the Prometheus endpoint. The right answer is network-layer isolation: a Kubernetes NetworkPolicy in cluster, a security group in cloud, or a `bind_host: 127.0.0.1` if the exporter runs on the same box.

Verify it worked: # From a pod OUTSIDE monitor-agent namespace — expect timeout: kubectl run -it --rm probe --image=curlimages/curl --restart=Never -- \ curl --max-time 5 http://clickhouse.<your-ns>.svc:9363/metrics # expect: connection timed out # From a pod INSIDE monitor-agent namespace — expect metrics: kubectl -n monitor-agent run -it --rm probe --image=curlimages/curl --restart=Never -- \ curl --max-time 5 http://clickhouse.<your-ns>.svc:9363/metrics | head -5

Note: This is the same NetworkPolicy our [/exporters/clickhouse/scoped-user](/exporters/clickhouse/scoped-user) page recommends — repeated here so the post is self-contained.