Clickhouse遇到密码错误如何修改密码

输入错误密码报错

root@DAILACHDBUD001:/var/log# clickhouse-client
ClickHouse client version 23.4.2.11 (official build).
Connecting to localhost:9000 as user default.
Password for user (default):
Connecting to localhost:9000 as user default.
Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.

If you have installed ClickHouse and forgot password you can reset it in the configuration file.
The password for default user is typically located at /etc/clickhouse-server/users.d/default-password.xml
and deleting this file will reset the password.
See also /etc/clickhouse-server/users.xml on the server where ClickHouse is installed.

. (AUTHENTICATION_FAILED)

解决方法
clickhouse 密码有三种设置方式：明文、SHA256 和 SHA1 三种方式。

我们打开记录默认的登录账号密码的配置文件/etc/clickhouse-server/users.d/default-password.xml，发现默认密码是SHA256加密算法生成，由password_sha256_hex标签定义，我们只需要在/etc/clickhouse-server/users.d/default-password.xml重新设置新密码对应的SHA256字符串并不需要管/etc/clickhouse-server/users.xml配置文件，或把/etc/clickhouse-server/users.d/default-password.xml文件删除掉并在/etc/clickhouse-server/user.xml文件中修改账户密码。

明文:明文密码放在标签,当 password 标签为空时，代表免密码登录。
SHA256:SHA256密码放在<password_sha256_hex></password_sha256_hex>，明文密码字符串W123可以通过echo 的命令加-sha256或sha256sum显示成SHA256的密码，有如下两种方法

echo -n W123 | openssl dgst -sha256
echo -n W123 | sha256sum | tr -d '-'

SHA1：SHA1密码放在<password_double_sha1_hex></password_double_sha1_hex>，明文密码字符串W123可以通过echo 的命令加sha1sum显示成SHA256的密码，方法如下

echo -n W123 | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'

安装的时候设置密码，需要修改密码，则只需要修改/etc/clickhouse-server/users.d/default-password.xml中密码，不需要管/etc/clickhouse-server/users.xml配置文件，同时需要修改/etc/clickhouse-server/config.xml配置文件中关于replication配置的密码，则需要defaultXX<password_sha256_hex>YY</password_sha256_hex>三项包括明文密码项XX一起，否则报错DB::Exception: Received from DAILACHDBUD003:9000. DB::Exception: default: Authentication failed

/etc/clickhouse-server/users.d/default-password.xml如下两种形式都行
形式1

<yandex>
    <users>
        <default>
            <password>W123</password>
        </default>
    </users>
</yandex>

形式2

<yandex>
    <users>
        <default>
            <password remove='1' />
            <password_sha256_hex>9174c7d10af34f4cb84c82</password_sha256_hex>
        </default>
    </users>
</yandex>

/etc/clickhouse-server/config.xml配置文件中关于replication配置的密码修改信息如下
修改前密码Good123!

<host>DAILACHDBUD003</host>
<port>9000</port>
<user>default</user>
<password>Good123!</password>
<password_sha256_hex>6481ef8efdab023b7c5b1b1d711561a14d</password_sha256_hex>

修改后密码W123

<host>DAILACHDBUD003</host>
<port>9000</port>
<user>default</user>
<password>W123</password>
<password_sha256_hex>9174c7d10af34f4cb84c82</password_sha256_hex>

新建用户Lukes使用明文密码如下，则会在/var/lib/clickhouse/access/users.list文件中新增Lukes8f40b0e7-0c32-0e06-d01a-94af37ef43bf和新建一个文件/var/lib/clickhouse/access/8f40b0e7-0c32-0e06-d01a-94af37ef43bf.sql，这个.sql文件内容为ATTACH USER

Lukes IDENTIFIED WITH plaintext_password BY 'Se0ertl_o1' SETTINGS PROFILE ID('4bfbe653-9137-0ea6-b97d-dc391ec9a919');
create user Lukes on cluster cluster_2shared2replicas IDENTIFIED WITH plaintext_password by 'Se0ertl_o1' host any SETTINGS PROFILE default;