Administering Users

Administering Users

When we first start a new instance of ClickHouse, one user is configured with the username default.

It is of course best practice to create usernames and passwords for each of your real world users so that we can control their individual access rights, audit what they do, and disable their account later if necessary.

Creating Users

In earlier versions of ClickHouse, users were created statically in an XML file. The newer, reccomended route is to use SQL commands to administer them via the client. From time to time however, we do still need to interact with the users.xml file.

Enabling User Creation Via SQL

Out of the box, it is not possible to create users via SQL and we have to explicitly turn this feature on.

Having start ClickHouse, let's try to create a new user with a static password:

create user if not exists benjaminwootton identified with plaintext_password by 'password321';

If you try to execute this command, you will find that the default user is not able to administer users on a new install of ClickHouse using the SQL route. It has to be explicitly enabled.

Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: default: Not enough privileges. To execute this query it's necessary to have grant CREATE USER ON *.*. (ACCESS_DENIED)

The way to enable this is using the ClickHouse users.xml file. Once this has been turned on in the XML file, we can grant the permission to create users to other users in the system via SQL. This is purely a security feature when bootstrapping a new ClickHouse server.

We can edit the users.xml file in the following location:

nano /etc/clickhouse-server/users.xml

And uncomment the line regarding access_management:

<users>
    <default>
            <access_management>1</access_management>

Uncommenting and setting this flag to 1 effectively says that the default user has permission to create new users and grant permissions to them.

We can then restart the ClickHouse server, being careful to reference the main configuration file, which in turn will reference the users.xml file we have just edited:

clickhouse-server -C /etc/clickhouse-server/config.xml

If we then attempt to recreate our user from the ClickHouse client:

create user if not exists benjaminwootton identified with plaintext_password by 'password321';

We should see that it has been succesfully created:

CREATE USER IF NOT EXISTS benjaminwootton IDENTIFIED WITH plaintext_password BY 'password321'

Query id: b19e48ab-f7bd-48bf-a313-e0eec6b22a0c

Ok.

0 rows in set. Elapsed: 0.004 sec.

The new user, in this case benjaminwootton will then be able to login at the client with the credentials specified.

Resetting Passwords

Passwords can be reset with the ALTER USER command.

alter user benjaminwootton set password = 'password123'

Disabling Users

Users can be disabled with the ALTER USER command.

alter user benjaminwootton set disabled