ClickHouse最佳实战之Clickhouse用户配置文件user.xml详解
官方文档:
访问权限和账户管理 | ClickHouse文档clickhouse.tech ClickHouse的用户及访问权限控制均可由配置文件直接进行标准化配置,一般由user.xml文件设置,该文件名在/etc/clickhouse-server/config.xml中修改,详情可参考clickhouse-server配置文件详解,若需要对某一个用户单独设置例如dba用户,可放入/etc/clickhouse-server/users.d/dba.xml,下面会描述该文件的配置示例
文件中可以看出users.xml主要由以下三部分设置组成:
- profile:类似于用户角色,可以实现最大内存、负载方式等配置的服用
profile是一组设置的集合,类似于角色的概念,每个用户都有一个profile。
可以有两种方式使用profile:
在会话中应用profile的所有设置,例如:SET profile = 'web' 。
在users.xml的users部分为某个用户指定profile。
profiles在users.xml配置文件的profiles标签下配置:
<!-- Settings profiles -->
<profiles>
<!-- Default settings -->
<default>
<!-- The maximum number of threads when running a single query. -->
<max_threads>8</max_threads>
</default>
<!-- Settings for quries from the user interface -->
<web>
<max_rows_to_read>1000000000</max_rows_to_read>
<max_bytes_to_read>100000000000</max_bytes_to_read>
<max_rows_to_group_by>1000000</max_rows_to_group_by>
<group_by_overflow_mode>any</group_by_overflow_mode>
<max_rows_to_sort>1000000</max_rows_to_sort>
<max_bytes_to_sort>1000000000</max_bytes_to_sort>
<max_result_rows>100000</max_result_rows>
<max_result_bytes>100000000</max_result_bytes>
<result_overflow_mode>break</result_overflow_mode>
<max_execution_time>600</max_execution_time>
<min_execution_speed>1000000</min_execution_speed>
<timeout_before_checking_execution_speed>15</timeout_before_checking_execution_speed>
<max_columns_to_read>25</max_columns_to_read>
<max_temporary_columns>100</max_temporary_columns>
<max_temporary_non_const_columns>50</max_temporary_non_const_columns>
<max_subquery_depth>2</max_subquery_depth>
<max_pipeline_depth>25</max_pipeline_depth>
<max_ast_depth>50</max_ast_depth>
<max_ast_elements>100</max_ast_elements>
<readonly>1</readonly>
</web>
</profiles>
上面的配置指定了两个profile:default和web。默认的default不能省略,它包含默认的设置,在服务器启动时应用。web是常规的profile,可以使用SET语句或HTTP查询中的URL参数进行设置。profile设置可相互继承。如果使用继承,在配置文件中列出的其他设置之前, 先指定一个或多个profile。如果一个设置在不同的profile都定义了,则使用最新的设置。
- users:设置包括用户名、密码、权限等
xml文件的users标签的结构如下:
<users>
<!-- If user name was not specified, 'default' user is used. -->
<user_name>
<password></password>
<!-- Or -->
<password_sha256_hex></password_sha256_hex>
<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>
<allow_databases>
<database>test</database>
</allow_databases>
<allow_dictionaries>
<dictionary>test</dictionary>
</allow_dictionaries>
</user_name>
<!-- Other users settings -->
</users>
在上面的配置中,定义了一个user_name的用户,该用户的所有其他配置都通过该标签的子标签配置。下面详细介绍每个具体子标签的配置信息。
(1)、user_name/password
密码可以使用明文、SHA256或SHA1指定。
明文形式的密码通过password标签指定。
例如: <password>qwerty</password>。密码可以为空。
SHA256散列密码通过password_sha256_hex标签指定。
示例:<password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>。
可通过如下shell命令生成SHA256散列:
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
命令的输出如下:
pmpjaK2V
21c4185b8155e532ca5a1eb0d4ca74ecde83eddee4ca55af393007f7c29f7eb7
第一行是明文的密码,第二行对应的SHA256加密散列。
SHA1散列密码通过password_double_sha1_hex标签指定。
SHA1密码是为了兼容MySQL客户端。
使用示例:
<password_double_sha1_hex>08b4a0f1de6ad37da17359e592c8d74788a83eb0</password_double_sha1_hex>。
可通过如下shell命令生成SHA1散列:
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'
命令的输出如下:
KCXM95It
de4bbcb9bbb35e3e497fcbebafc0f04b3dcef383
第一行是明文的密码,第二行对应的SHA1加密散列。
(2)、user_name/networks
networks用于配置网络列表,只有网络列表范围内的用户可以连接到ClickHouse。
列表中的元素可以使用如下形式定义:
<ip> : IP地址或带掩码的IP地址。
例如:213.180.204.3, 10.0.0.1/8, 10.0.0.1/255.255.255.0, 2a02:6b8::3, 2a02:6b8::3/64, 2a02:6b8::3/ffff:ffff:ffff:ffff::。
<host> : 主机名。
例如:example01.host.ru。
<host_regexp> : 主机名的正则表达式。
例如:^exampledd-dd-d.host.ru$。
使用这种方式指定网络列表,强烈建议regexp以$结尾。
示例1:用户可以从任意网络访问,指定:
<ip>::/0</ip>
示例2:仅限本地网络访问,指定:
<ip>::1</ip>
<ip>127.0.0.1</ip>
(3)、user_name/profile
profile用于给用户分配一个配置好的profile,这个profile是在users.xml配置文件的profiles标签下配置的。
(4)、user_name/quota
用于给用户分配quota,这里quota是在users.xml配置文件的quotas标签下配置的。quota用于在一定时间内跟踪或限制资源的使用。
(5)、user_name/databases
用于限制当前用户的SELECT查询返回的行,可实现基本的行级安全性。
示例:
以下的配置将限制用户user1只能查询table1表中id=1000的行,其他记录对用户不可见,就好像表中不存在其他行一样。
<user1>
<databases>
<database_name>
<table1>
<filter>id = 1000</filter>
</table1>
</database_name>
</databases>
</user1>
使用这种方式需要注意WHERE子句的查询谓词不会下推,即禁用了WHERE移动到PREWHERE的优化。
(6)、user_name/allow_databases
用于指定用户可以访问的数据库列表。默认情况下,用户可以访问所有数据库。
Note: 用户对system数据库的访问始终是有权限的,因为用户需要根据此数据库处理查询。
配置示例,配置用户可访问test1和test2数据库:
<allow_databases>
<database>test1</database>
<database>test2</database>
</allow_databases>
(7)、user_name/allow_dictionaries
配置可以访问的字典列表。默认情况下,用户可访问所有字典。
示例, 配置用户可访问test1和test2字典:
<allow_dictionaries>
<dictionary>test1</dictionary>
<dictionary>test2</dictionary>
</allow_dictionaries>
- quotas:限制一段时间内的资源使用等
Quotas用于在一段时间内跟踪资源的使用情况或限制资源的使用。quotas在user.xml配置文件的quotas标签下配置,在users标签下分配给用户。
注意:quota用于限制一组查询,并且将所有远程服务器上的分布式查询处理的资源纳入限制范围,而不是限制单个查询。
quotas的配置示例1:
<!-- Quotas -->
<quotas>
<!-- Quota name. -->
<default>
<!-- Restrictions for a time period. You can set many intervals with different restrictions. -->
<interval>
<!-- Length of the interval. -->
<duration>3600</duration>
<!-- Unlimited. Just collect data for the specified time interval. -->
<queries>0</queries>
<errors>0</errors>
<result_rows>0</result_rows>
<read_rows>0</read_rows>
<execution_time>0</execution_time>
</interval>
</default>
默认情况下,quota只跟踪一个小时内的资源使用情况,并不会限制资源的使用。每个时间间隔内的资源消耗在每次请求之后输出到服务器日志。
quota配置示例2:
<statbox>
<!-- Restrictions for a time period. You can set many intervals with different restrictions. -->
<interval>
<!-- Length of the interval. -->
<duration>3600</duration>
<queries>1000</queries>
<errors>100</errors>
<result_rows>1000000000</result_rows>
<read_rows>100000000000</read_rows>
<execution_time>900</execution_time>
</interval>
<interval>
<duration>86400</duration>
<queries>10000</queries>
<errors>1000</errors>
<result_rows>5000000000</result_rows>
<read_rows>500000000000</read_rows>
<execution_time>7200</execution_time>
</interval>
</statbox>
上面配置了一个名称为statbox的quota,在每小时(3600秒)和每24小时(86400秒)设置了对资源的限制。间隔结束时,将清除所有收集的值,在接下来的下一个时间区间,quota将重新开始计算。
可用于quota限制的资源如下:
queries : 请求总数。
errors: 抛出异常的总数。
result_rows : 作为结果给出的总行数。
read_rows : 在所有远程服务器上,从表中读取用于运行查询的源总行数。
execution_time : 查询执行的总耗时,单位为秒。
如果在至少一个时间间隔内超过了限制,则会引发异常,异常信息包括限制的类型、时间间隔以及新时间间隔的开始时间。
对于分布式查询处理, 资源的累积量存储在请求服务器上,因此,如果用户转到另一个服务器,则这个服务器的quota将重新开始累积。
重启服务器后,quota将重置。
ClickHouse中的查询可分为如下几种类型
读取数据查询:SELECT、SHOW、DESCRIBE、EXISTS。
写数据查询:INSERT、OPTIMIZE。
更改设置查询:SET、USE。
DDL查询:CREATE、ALTER、RENAME、ATTACH、DETACH、 DROP、TRUNCATE。
KILL QUERY
下面的设置可用户配置用户的查询权限:
readonly :限制读取、写入和更改三类查询的权限。
allow_ddl:限制DDL查询的权限。
KILL QUERY不受任何设置的限制。。
1. readonly
用于限制读取、写入和更改三类查询的权限,默认值为0。
readonly的取值如下:
0 : 允许执行所有查询。
1 : 仅允许读取数据的查询。
2 : 允许读取数据和更改设置。
在设置readonly=1后,用户将无法在当前会话中更改readonly和allow_ddl的设置。
在HTTP请求中使用GET方法时将自动设置readonly=1。如果在http请求中修改数据,则必须使用POST方法。
设置readonly=1将禁止用户更改所有的设置。如果要禁止用户修改某些特定的设置,可以在users.xml配置文件中配置profiles的约束。
2. allow_ddl
用于配置是否允许DDL操作的权限,默认值为1。
可设置的值如下:
0 : 不允许DDL查询。
1 : 允许DDL查询。
如果当前会话的allow_ddl=0,则无法执行: SET allow_ddl=0。
关于配置的详细的解析,可以参考博文:
ClickHouse学习系列之二【用户权限管理】 - jyzhou - 博客园www.cnblogs.com写的非常的清楚,当然查看官方文档是第一选择
user.xml官方文档:
Quotas
User settings
Permissions for queries
Access Rights
使用经验总结:
在user.xml中要配置好根据机器的资源,业务情况-是否有大量的join等,用户的查询情况的内存,最大内存等关键的配置
给一些线上的配置参数:
<!-- Maximum memory usage for processing single query, in bytes. -->
<join_default_strictness>ALL</join_default_strictness>
<max_memory_usage>140000000000</max_memory_usage>
<max_memory_usage_for_all_queries>160000000000</max_memory_usage_for_all_queries>
<!--<max_memory_usage_for_user>100000000000</max_memory_usage_for_user>-->
<max_memory_usage_for_user>160000000000</max_memory_usage_for_user>
参考:
https://blog.csdn.net/lcl_xiaowugui/article/details/105206594
https://www.cnblogs.com/zhoujinyi/p/12613026.html