ClickHouse creates a user named ‘default’, at the time of installation/fresh deployment. This default user has unrestricted access to the data in the cluster and we can also provide access management privilege in the users.xml file (which is not turned on by default).
It is not a good idea to share the credentials of the default user to multiple people, who might require admin privileges. The best practice is to create a role with ‘admin’ privilege and grant this role to the users who are designated to be the ‘admins’. Let us look at the steps to create an admin role and assign it to the users.
Step 1 – Update the users.xml
Open the users.xml file in /etc/clickhouse-server/ and add update the following for default user.
<access_management>1</access_management> <named_collection_control>1</named_collection_control> <show_named_collections>1</show_named_collections> <show_named_collections_secrets>1</show_named_collections_secrets>
The above configs need to be enabled for creating role with ‘Admin’ level privileges.
Step 2 – Create the admin role
Let us create a role named ‘admin’ and grant all the privileges to the role.
CREATE ROLE 'admin'; GRANT ALL ON *.* TO admin WITH GRANT OPTION;
Step 3 – Create a user and grant admin role
Let us create a new user called ‘test’ and grant ‘admin’ role to the ‘test’ user.
create user test identified with plaintext_password by 'test'; GRANT admin to test;
Conclusion
We have seen how to create admin users in ClickHouse in this article. Admin users are indispensable to any database system and we can create users with admin role using the steps listed.
References
https://github.com/ClickHouse/ClickHouse/issues/47092
https://clickhouse.com/docs/en/operations/access-rights