ClickHouse 用户管理开始支持 SQL

原文：GOODBYE XML, HELLO SQL! CLICKHOUSE USER MANAGEMENT GOES PRO^[1]

作者：Robert Hodges^[2]

访问控制是数据库管理的基本功能之一。从 2019 年底开始，ClickHouse 的贡献者Vitaly Baranov^[3]开始引入功能强大、齐全的基于角色的访问控制^[4]（RBAC）。ClickHouse 现在可以真正地拥有企业级访问控制，全部管理命令使用 SQL。

用户管理是 RBAC 的大门。它控制着对 ClickHouse 本身的访问。本文介绍了诸如 CREATE USER 之类的新命令，这些命令使您可以方便地创建，更改和删除用户。我们将重点介绍控制单个 ClickHouse 服务器的身份验证的方法。

在此过程中，本文提供了一些技巧，以充分利用 SQL 用户管理，而不会在此过程中增加新的安全漏洞。我们走吧！

老版本 ClickHouse 用户管理

ClickHouse 用户最初使用 user.xml 定义的，现在仍然有效。

<users>    <!-- If user name was not specified, 'default' user is used. -->    <user_name>        <password></password>        <!-- Or -->        <password_sha256_hex></password_sha256_hex>        <access_management>0|1</access_management>        <networks incl="networks" replace="replace">        </networks>        <profile>profile_name</profile>        <quota>default</quota>        <databases>            <database_name>                <table_name>                    <filter>expression</filter>                <table_name>            </database_name>        </databases>    </user_name>    <!-- Other users settings --></users>

ClickHouse 无需重新启动即可自动识别文件，您可以使用以下命令登录：

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

• 如果没有指定用户，则使用default
  作为用户名，默认没有密码，也没有访问权限。
• 密码可以是：
• 明码(不推荐）<password>qwerty</password>
  ，

  -- Password stored in clear text; DON’T DO THIS!CREATE USER IF NOT EXISTS example_text  IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'secret';
  

• SHA256 哈希值<password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>
  
• Double SHA1,与 MySQL 兼容password_double_sha1_hexe395796d6546b1b65db9d665cd43f0e858dd4303
  

  -- Double SHA-1 for MySQL compatibility.CREATE USER IF NOT EXISTS example_mysql  IDENTIFIED WITH DOUBLE_SHA1_PASSWORD BY 'secret';
  

• LDAP server<ldap><server>my_ldap_server</server></ldap>
  
• 没有密码

  -- No password required to login.CREATE USER IF NOT EXISTS example_no_pw  IDENTIFIED WITH NO_PASSWORD;
  

• 如果需要访问权限，需设置<access_management>1</access_management>
  否则通过 SQL 管理用户的时候会报错

  Received exception from server (version 21.3.1):Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: default: Not enough privileges. To execute this query it's necessary to have grant SHOW USERS ON *.*.
  

• 使用网络掩码(支持 IPv4 和 IPv6）限制网络访问权限

  <networks>   <ip>::/0</ip></networks>
  

SQL 用户管理

ClickHouse 从 20.5 以后开始支持 SQL 命令管理用户，这些 SQL 命令管理用户操作的整个生命周期。例如，我们可以如下创建用户。

CREATE USER IF NOT EXISTS example IDENTIFIED WITH SHA256_PASSWORD BY 'secret';

返回，

CREATE USER IF NOT EXISTS example IDENTIFIED WITH sha256_hash BY '2BB80D537B1DA3E38BD30361AA855686BDE0EACD7162FEF6A25FE97BF527A25B'Query id: 8c753616-f660-4163-af3b-613044a4555bOk.0 rows in set. Elapsed: 0.003 sec.

ClickHouse 自动生成 SHA-256 哈希。我们可以使用 ALTER USER 命令轻松地更改密码。这将生成另一个 SHA-256 哈希并将其正确存储。

ALTER USER example  IDENTIFIED WITH SHA256_PASSWORD BY 'topsecret';

如果现在使用用户example
登录，则需要使用topsecret
作为密码。我们可以很容易地看到系统上的所有用户，如下所示,

show users

返回,

SHOW USERSQuery id: c5429c29-4eeb-4337-b0e7-528933bd0228┌─name────┐│ default ││ example │└─────────┘2 rows in set. Elapsed: 0.008 sec.

我们还可以查看到如何创建特定用户。

SHOW CREATE USER example┌─CREATE USER example─────────────────────────────────┐│ CREATE USER example IDENTIFIED WITH sha256_password │└─────────────────────────────────────────────────────┘

最后，当不再需要该用户时，我们可以将其删除。

DROP USER example

MySQL 用户将注意到，用户管理命令对他们来说是熟悉的。MySQL 语法对 ClickHouse 产生了很大的影响。

一个更现实的例子

到目前为止，我们已经显示了非常简单的命令，但是 CREATE USER 和相关命令非常强大，并且涵盖了帐户管理的整个范围，包括用户配置文件^[5]，配额^[6]和角色^[7]。以下命令创建用户设置配置文件，用于只读查询的角色，该角色的配额以及具有所有这些的用户。

CREATE SETTINGS PROFILE IF NOT EXISTS ro_profileSETTINGS  max_threads = 2 MIN 1 MAX 4,  max_memory_usage = 10000000 MIN 1000000 MAX 20000000READONLYCREATE ROLE IF NOT EXISTS ro_role  SETTINGS PROFILE 'ro_profile'CREATE QUOTA IF NOT EXISTS batch_quotaFOR INTERVAL 3600 second  MAX queries 60,  MAX result_rows 1000000TO ro_roleCREATE USER ro_user  IDENTIFIED WITH SHA256_PASSWORD BY 'top_secret'  DEFAULT ROLE ro_role

RBAC实体图关系如下：

现在，您可以使用登录该用户的身份，并检查通过角色分配给该用户的设置和特权。

clickhouse-client -u ro_user --password top_secret

我们可以对设置进行快速查询，以确保配置文件设置正确。

SELECT name, value, min, maxFROM system.settingsWHERE name IN ('max_threads', 'max_memory_usage')┌─name─────────────┬─value────┬─min─────┬─max──────┐│ max_threads      │ 2        │ 1       │ 4        ││ max_memory_usage │ 10000000 │ 1000000 │ 20000000 │└──────────────────┴──────────┴─────────┴──────────┘

我们还可以尝试创建表，以证明该帐户没有这样做的特权。

CREATE TABLE foo (`id` UInt32) ENGINE = tinylogReceived exception from server (version 21.1.2):Code: 497. DB::Exception: Received from localhost:9000.DB::Exception: ro_user: Not enough privileges.To execute this query it's necessary to havegrant CREATE TABLE ON default.foo.

留点作业：如何确认 ro_user 具有配额? 我通过SHOW QUOTAS ON *.* to ro_user
来做到这一点。这样 ro_user 可以运行SHOW QUOTA
并查看限制。可能还有另一种方法。

RBAC
授权的引入为控制对以前不存在的数据的访问提供了机会。最明显的改进是，您现在可以管理网络上的用户访问和特权，而根本无需访问本地文件系统。如前面的示例所示，这也打开了在单个 ClickHouse 上进行多租户操作的可能性。探索这一点必须等待以后的文章。

clickhouse.tech 网站上有关于 RBAC 命令的^[8]出色参考文档^[9]，请随时浏览。同时，我们将回到管理用户对 ClickHouse 访问的主题。

围绕用户密码的安全策略

任何依赖本地密码的系统都容易引起与安全管理有关的麻烦。老式的 XML 用户定义文件将密码存储在磁盘上，这往往会触发知道该密码的公司 Infosec 团队。SQL 用户管理有帮助吗？我们看看吧！

首先，我们将重新运行该命令以创建一个示例用户。

CREATE USER IF NOT EXISTS example  IDENTIFIED WITH SHA256_PASSWORD BY 'secret';

ClickHouse 将所得的用户定义存储在/var/lib/clickhouse/access/
中。例如，在运行上面命令后,查看该目录下的 users.list

$sudo cat /var/lib/clickhouse/access/users.listexample0543d30b-638c-046f-5402-ad1ce29f478d

查看 example 用户对应键值(0543d30b-638c-046f-5402-ad1ce29f478d)的 sql

$ sudo cat /var/lib/clickhouse/access/0543d30b-638c-046f-5402-ad1ce29f478d.sqlATTACH USER example IDENTIFIED WITH sha256_hash BY '2BB80D537B1DA3E38BD30361AA855686BDE0EACD7162FEF6A25FE97BF527A25B';

也可以通过如下命令

SELECT  * from system.users;

返回，

SELECT *FROM system.usersQuery id: 0fa32c72-6a5b-49c4-b0ec-155a6f13971a┌─name────┬───────────────────────────────────id─┬─storage─────────┬─auth_type──────────┬─auth_params─┬─host_ip──┬─host_names─┬─host_names_regexp─┬─host_names_like─┬─default_roles_all─┬─default_roles_list─┬─default_roles_except─┐│ default │ 94309d50-4f52-5250-31bd-74fecac179db │ users.xml       │ plaintext_password │ {}          │ ['::/0'] │ []         │ []                │ []              │                 1 │ []                 │ []                   │
│ example │ 0543d30b-638c-046f-5402-ad1ce29f478d │ local directory │ sha256_password    │ {}          │ ['::/0'] │ []         │ []                │ []              │                 1 │ []                 │ []                   │
└─────────┴──────────────────────────────────────┴─────────────────┴────────────────────┴─────────────┴──────────┴────────────┴───────────────────┴─────────────────┴───────────────────┴────────────────────┴──────────────────────┘

2 rows in set. Elapsed: 0.009 sec.

可知密码仍存在磁盘上，但是换了个位置。ClickHouse 确保此目录具有权限，以便只有 root 或 clickhouse 用户可以访问它。但是，如果使用文件系统快照（例如）备份/var/lib/clickhouse/access/，则快照将包含纯文本或哈希形式的密码。

您可以通过更新config.xml
中的<local_directory>
标记将用户管理文件移动到另一个位置。确保在新位置检查文件权限，以免泄露密码。

使用网络掩码保护帐户

default 用户默认没有密码，从安全角度来看，似乎没有用，但实际 ClickHouse 允许您使用网络掩码限制访问。

比如一个只能从本地主机登录的用户,

CREATE USER example_hostlocal
  IDENTIFIED WITH NO_PASSWORD
  HOST LOCAL

从其他主机登录将失败，并显示适当的模棱两可的错误消息：

clickhouse-client --user=example_hostlocal --host=ch-1
ClickHouse client version 21.1.2.15 (official build).
Connecting to ch-1:9000 as user example_hostlocal.
Code: 516. DB::Exception: Received from ch-1:9000.
DB::Exception: example_hostlocal: Authentication failed:
password is incorrect or there is no user with such name.

ClickHouse 网络过滤器有许多有用的变体，如：

• 仅限于从特定子网登录的用户

CREATE USER example_subnet
  IDENTIFIED WITH NO_PASSWORD
  HOST IP '10.2.0.0/24'

• 可以从对应于特定主机的 IP 地址登录的用户。可以通过用逗号分隔不同的过滤器来组合网络掩码。您可以根据需要添加任意数量的过滤器

CREATE USER example_host
  IDENTIFIED WITH NO_PASSWORD
  HOST NAME 'ch-1', NAME 'ch-2', NAME 'ch-3'

• 使用正则表达式来匹配主机 ch-1，ch-2 和 ch-3 的登录名。它还具有一个额外的 HOST LOCAL 掩码，以允许从本地主机登录。这定义了一个用户，该用户可以从三个命名主机登录，也可以从服务器本身运行所在的主机本地登录。

CREATE USER example_regexp
  IDENTIFIED WITH NO_PASSWORD
  HOST LOCAL, REGEXP '^ch-[123]$'

最后一点：当心代理！如果将 NGINX 反向代理放在 ClickHouse 服务器的前面，并通过它连接到 HTTP 端口 8123 或 8443，ClickHouse 看到的是代理主机的网络地址，而不是原始客户端地址。如果在连接到达 ClickHouse 之前执行 TLS 终止，则同样适用。您将看到终止主机，而不是客户端程序主机。

总之，网络过滤器很有用，但不能提供完整的访问保护。除非您完全控制网络路径，否则最好用密码补充它们。

结论

基于 SQL 的用户管理和基于角色的访问控制代表了 ClickHouse 安全性的巨大进步。本文重点介绍对单个 ClickHouse 服务器的访问控制。在以后的文章中，我们将讨论 ClickHouse 群集上的用户管理。

这里，我们几乎没有涉及 ClickHouse 现在提供的许多保护数据的机制。以及更多其它方面。我的同事瓦西里·内姆科夫（Vasily Nemkov）刚刚发表了一篇有关AES 加密功能^[10]的文章。我们将发布有关 LDAP，RBAC，Kerberos 支持（正在发生！）以及许多其他主题的更多文章。

参考资料