1.语法

clickhouse创建用户语法：

CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1]
[, name2 [ON CLUSTER cluster_name2] ...]
[NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']} | {WITH ssl_certificate CN 'common_name'}]
[HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
[DEFAULT ROLE role [,...]]
[DEFAULT DATABASE database | NONE]
[GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

其中

[HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]

可以设置ip白名单，如果用户已经创建后，需要修改白名单，可以通过alter修改。

ALTER USER [IF EXISTS] name1 [ON CLUSTER cluster_name1] [RENAME TO new_name1]
[, name2 [ON CLUSTER cluster_name2] [RENAME TO new_name2] ...]
[NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']} | {WITH ssl_certificate CN 'common_name'}]
[[ADD | DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
[DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ]
[GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

举例：
创建用户：

create user marketing IDENTIFIED WITH PLAINTEXT_PASSWORD BY '123456';

增加ip地址：

alter user marketing add host '233.44.5.66'

删除ip地址：

alter user marketing drop host '233.44.5.66'

2.有可能遇到的问题

如果增加了白名单ip后，通过客户端还是连接不上clickhouse服务器，可能是连接经过了代理转发，如果想要能够连接上，ip白名单地址必须为代理服务器的ip地址。

两种路径

路径1是直连clickhouse，所以这种情况需要配置的ip地址就是原连接ip就可以；如果是路径2，中间经过了代理，想要整个连接可用，则需要配置的白名单是代理ip的地址。当配置了ip地址还是不通，有可能就是经过了代理。

怎么获取这个ip地址：
1.先用客户端连接clickhouse服务器触发一下报错

clickhouse客户端
2.如果ip地址和用户名，密码都对，只是没设置ip白名单，则该请求会到达clickhouse服务器，clickhouse服务器会拦截报错，所以错误日志应该会有相关信息，所以我们进入clickhouse容器，查看错误日志（默认路径是/var/log/clickhouse-server/clickhouse-server.err.log）
错误日志
错误日志里面会有相关ip信息，通过这个信息可以获取到代理的ip地址，将这个ip地址加到白名单即可。

参考文章：
https://clickhouse.com/docs/en/sql-reference/statements/alter/user
https://clickhouse.com/docs/en/sql-reference/statements/create/user