Search code examples
clickhouse

Distributed query failed due to default user authentication failed

I create a CH with 10 shards, and each shard has 3 replicas on 10 machines(A100-A110). When I run a query for a distributed table on the other 9 machines. It will fail with the authentication failed error of A105:

Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: Received from A105:9000. DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED)

I can log in to Clickhouse on A105 with the default user and password.

clickhouse-client -u default --password=$pw

I can successfully run distributed queries on A105. I can log in to Clickhouse of A105 from other machines and run distributed query successfully.

clickhouse-client -h A105 -u default --password $pw

My host_regexp and user&password is correct.

How to debug such an issue?

Solution

  • Check <networks><ip> restrictions for default user and if no restriction or it 0.0.0.0/0 and you change only default user password

    add <secret> section into <remote_servers> to allow authentication with initial user security context

    something like that

    <remote_servers>
 <cluster_name>
  <secret>secret</secret>
...
 <cluster_name>
</remote_servers>

    look comments related to secret in https://clickhouse.com/docs/en/engines/table-engines/special/distributed#distributed-clusters