In this article, we will practice access control management in ClickHouse.
We will enable admin user, and create users with different roles in ClickHouse.
Enable RBAC and Create Admin User
Firstly, we have to enable RBAC and then Create Admin User
To create an admin user, we have to edit the user.xml file. set the access management property for the admin user. user.xml file location is as follows;
root@clickhouse01:/etc/clickhouse-server# pwd /etc/clickhouse-server root@clickhouse01:/etc/clickhouse-server# ls config.d config.xml users.d users.xml
vi users.xml
<clickhouse>
<users>
<default>
....
</default>
<admin>
<!--
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>65e84be33532can47c48129675f97js92u5mf57168c0ea744b2cf58ee02337c5</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>
-->
<password></password>
<networks>
<ip>::/0</ip>
</networks>
<!-- Settings profile for user. -->
<profile>default</profile>
<!-- Quota for user. -->
<quota>default</quota>
<!-- Set This parameter to Enable RBAC
Admin user can create other users and grant rights to them. -->
<access_management>1</access_management>
</admin>
...
</clickhouse>
As you can see there is a section for access management. We can enable it from here. (delete arrows <– –>)
<!-- Set This parameter to Enable RBAC Admin user can create other users and grant rights to them. --> <access_management>1</access_management>
Create Replication User
The replication user is usually default. There is no need to create this user unless you know what you’re doing because you need a specific user for replication. Ports 9009 and 9010(tls) provide low-level data access between servers. These ports should not be accessible from untrusted networks.
CREATE USER replication IDENTIFIED WITH sha256_password BY 'password' SETTINGS PROFILE 'default'
After this, assign this user to the inter-server credentials:
create role dashboard_role on cluster '{cluster}';
grant select on default.* to dashboard_role on cluster '{cluster}';
grant dictGet on *.* to dashboard_role on cluster '{cluster}';
Grant some permission to the dashboard role.
create settings profile or replace profile_dashboard_role on cluster '{cluster}'
settings max_concurrent_queries_for_user = 10 READONLY, -- user can run 10 queries (select, insert ...) simultaneously
max_threads = 16 READONLY, -- each query can use up to 10 cpu
max_memory_usage_for_user = '30G' READONLY, -- all queries of the user can use up to 30G RAM
max_memory_usage = '30G' READONLY, -- each query can use up to 25G RAM
max_execution_time = 60 READONLY, -- each query can executes no longer 200 seconds
max_rows_to_read = 1000000000 READONLY, -- each query can read up to 1 billion rows
max_bytes_to_read = '5000G' READONLY -- each query can read up to 5 TB from a MergeTree
TO dashboard_role;
Create a user for dashboard access
create user 'dashboardUser' identified by 'secretpassword' on cluster '{cluster}';
grant dashboard_role to dashboardUser on cluster '{cluster}';
Create another user.
create role ingester_rw on cluster '{cluster}';
grant select,insert on default.* to ingester_rw on cluster '{cluster}';
create settings profile or replace profile_ingester_rw on cluster '{cluster}'
settings max_concurrent_queries_for_user = 40 READONLY, -- user can run 40 queries (select, insert ...) simultaneously
max_threads = 10 READONLY, -- each query can use up to 10 cpu (READONLY means user cannot override a value)
max_memory_usage_for_user = '30G' READONLY, -- all queries of the user can use up to 30G RAM
max_memory_usage = '25G' READONLY, -- each query can use up to 25G RAM
max_execution_time = 200 READONLY, -- each query can executes no longer 200 seconds
max_rows_to_read = 1000000000 READONLY, -- each query can read up to 1 billion rows
max_bytes_to_read = '5000G' READONLY -- each query can read up to 5 TB from a MergeTree
TO ingester_rw;
create user `ingester_app1` identified by 'pass1234' on cluster '{cluster}';
grant ingester_rw to ingester_app1 on cluster '{cluster}';
Test
Now, let us test our scenarios.
$ clickhouse-client -u dashboardUser --password secretpassword create table test ( A Int64) Engine=Log; DB::Exception: dash1: Not enough privileges $ clickhouse-client -u DBA --password verysecretpassword create table test ( A Int64) Engine=Log; Ok. $ clickhouse-client -u ingester_app1 --password pass1234 select count() from system.numbers limit 1000000000000; DB::Exception: Received from localhost:9000. DB::Exception: Limit for rows or bytes to read exceeded, max rows: 1.00 billion
Drop Users And Roles
show profiles;
┌─name───────────────────┐
│ default │
│ profile_dashboard_role │
│ profile_ingester_rw │
│ readonly │
└────────────────────────┘
drop profile if exists readonly on cluster '{cluster}';
drop profile if exists profile_dashboard_role on cluster '{cluster}';
drop profile if exists profile_ingester_rw on cluster '{cluster}';
show roles;
┌─name───────────┐
│ dashboard_role │
│ DBADMIN │
│ ingester_rw │
└────────────────┘
drop role if exists DBADMIN on cluster '{cluster}';
drop role if exists dashboard_role on cluster '{cluster}';
drop role if exists ingester_rw on cluster '{cluster}';
show users;
┌─name────────────┐
│ dashboardUser │
│ default │
│ ingester_app1 │
│ DBA │
└─────────────────┘
drop user if exists ingester_app1 on cluster '{cluster}';
drop user if exists DBA on cluster '{cluster}';
drop user if exists dashboardUser on cluster '{cluster}';