ClickHouse【环境搭建 02】设置用户密码的两种方式（明文+SHA256）及新用户添加及只读模式 Cannot execute query in readonly mode 问题解决

1.查看user.xml文件可知设置密码的多种方式

<!-- Password could be specified in plaintext or in SHA256 (in hex format).
If you want to specify password in plaintext (not recommended), place it in 'password' element.
Example: <password>qwerty</password>.
Password could be empty.

If you want to specify SHA256, place it in 'password_sha256_hex' element.
Example: <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>
Restrictions of SHA256: impossibility to connect to ClickHouse using MySQL JS client (as of July 2019).

If you want to specify double SHA1, place it in 'password_double_sha1_hex' element.
Example: <password_double_sha1_hex>e395796d6546b1b65db9d665cd43f0e858dd4303</password_double_sha1_hex>

If you want to specify a previously defined LDAP server (see 'ldap_servers' in the main config) for authentication,
place its name in 'server' element inside 'ldap' element.
Example: <ldap><server>my_ldap_server</server></ldap>

If you want to authenticate the user via Kerberos (assuming Kerberos is enabled, see 'kerberos' in the main config),
place 'kerberos' element instead of 'password' (and similar) elements.
The name part of the canonical principal name of the initiator must match the user name for authentication to succeed.
You can also place 'realm' element inside 'kerberos' element to further restrict authentication to only those requests
whose initiator's realm matches it. 
Example: <kerberos />
Example: <kerberos><realm>EXAMPLE.COM</realm></kerberos>

How to generate decent password:
Execute: PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
In first line will be password and in second - corresponding SHA256.

How to generate double SHA1:
Execute: PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'
In first line will be password and in second - corresponding double SHA1.
-->

2.测试一下明文和SHA256方式

2.1 明文

# 修改配置文件
vim /etc/clickhouse-server/users.xml
# 密码改为 <password>666666</password>

# 重启服务
systemctl restart clickhouse-server.service

# 使用密码登录
[root@tcloud ~]# clickhouse-client --password 666666
ClickHouse client version 21.6.6.51 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.6.6 revision 54448.

2.2 SHA256

# 这是配置文件里的说明
How to generate decent password:
Execute: PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
In first line will be password and in second - corresponding SHA256.

# 根据说明生成SHA256密码
[root@tcloud ~]# PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
zQEkhhcL
cfaaae90d863c47187d9a9a58e9e1ef919fcd0d765c67e961bd3e56e96bfea8a

# 上边是随机生成的 这个是土豪方式
[root@tcloud ~]# PASSWORD=88888888; echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
88888888
615ed7fb1504b0c724a296d7a69e6c7b2f9ea2c57c1d8206c5afdf392ebdfd25

# 修改配置文件
vim /etc/clickhouse-server/users.xml
# 密码改为 <password_sha256_hex>615ed7fb1504b0c724a296d7a69e6c7b2f9ea2c57c1d8206c5afdf392ebdfd25</password_sha256_hex>

# 再次重启服务
systemctl restart clickhouse-server.service

# 使用密码登录
[root@tcloud ~]# clickhouse-client --password 88888888
ClickHouse client version 21.6.6.51 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.6.6 revision 54448.

3.新用户添加

想象中加一个用户标签和密码就OK了，配置走起！

# 修改配置文件
vim /etc/clickhouse-server/users.xml
# 添加一下标签 <admin>就是用户名 内部的标签就是当前用户的设置项
<admin>
	<password>123456</password>
	<networks>
		<ip>::/0</ip>
	</networks>
	<profile>default</profile>
	<quota>default</quota>
</admin>

# 再再次重启服务
systemctl restart clickhouse-server.service

# 使用用户名和密码登录 如果只使用密码则是default用户
[root@tcloud ~]# clickhouse-client --user admin --password 123456
ClickHouse client version 21.6.6.51 (official build).
Connecting to localhost:9000 as user admin.
Connected to ClickHouse server version 21.6.6 revision 54448.

3.1 readonly mode设置

如果要设置一个只读用户，可以修改user.xml里用户的标签属性。我们先看一下官方配置文件的说明：

    <profiles>
        <!-- Default settings. -->
        <default>
            <!-- Maximum memory usage for processing single query, in bytes. -->
            <max_memory_usage>10000000000</max_memory_usage>

            <!-- How to choose between replicas during distributed query processing.
                 random - choose random replica from set of replicas with minimum number of errors
                 nearest_hostname - from set of replicas with minimum number of errors, choose replica
                  with minimum number of different symbols between replica's hostname and local hostname
                  (Hamming distance).
                 in_order - first live replica is chosen in specified order.
                 first_or_random - if first replica one has higher number of errors, pick a random one from replicas with minimum number of errors.
            -->
            <load_balancing>random</load_balancing>
        </default>

        <!-- Profile that allows only read queries. -->
        <readonly>
            <readonly>1</readonly>
        </readonly>
    </profiles>

测试：

# 修改配置文件
vim /etc/clickhouse-server/users.xml
# 添加一下标签 <admin>就是用户名 内部的标签就是当前用户的设置项
<admin>
	<password>123456</password>
	<networks>
		<ip>::/0</ip>
	</networks>
	<profile>readonly</profile>
	<quota>default</quota>
</admin>

# 再再再次重启服务
systemctl restart clickhouse-server.service

# 使用用户名和密码登录【此时用户是只读模式】
[root@tcloud clickhouse-server]# clickhouse-client --user admin --password 123456
ClickHouse client version 21.7.2.7 (official build).
Connecting to localhost:9000 as user admin.
Connected to ClickHouse server version 21.7.2 revision 54449.

# 测试 先进行查询操作
tcloud :) select * from tb_stat;

SELECT *
FROM tb_stat
Query id: 323ffecb-6e19-4c1a-998c-d415410c0de8
┌─id─┬─region──┬─group─┬─yesterday─┬─today─┬───────────stat_date─┐
│ 4  │ 1232364 │ 222   │        89 │   222 │ 2020-03-25 12:13:00 │
│ 7  │ 1232364 │ 333   │        76 │   128 │ 2020-03-25 12:11:00 │
└────┴─────────┴───────┴───────────┴───────┴─────────────────────┘
┌─id─┬─region──┬─group─┬─yesterday─┬─today─┬───────────stat_date─┐
│ 1  │ 1232364 │ 111   │        32 │     2 │ 2021-07-09 12:56:00 │
└────┴─────────┴───────┴───────────┴───────┴─────────────────────┘
┌─id─┬─region──┬─group─┬─yesterday─┬─today─┬───────────stat_date─┐
│ 2  │ 1232364 │ 111   │        34 │    44 │ 2021-07-09 12:21:00 │
└────┴─────────┴───────┴───────────┴───────┴─────────────────────┘
4 rows in set. Elapsed: 0.009 sec.

# 数据入库操作【报错Cannot execute query in readonly mode 可见当前用户是只读模式】
tcloud :) INSERT INTO tb_stat VALUES( '1','1232364', '111', 32, 2, '2021-07-09 12:56:00' );

INSERT INTO tb_stat VALUES
Query id: b9dc2175-554c-4b26-9127-bb0b88064d42
0 rows in set. Elapsed: 0.027 sec.
Received exception from server (version 21.7.2):
Code: 164. DB::Exception: Received from localhost:9000. DB::Exception: admin: Cannot execute query in readonly mode.