Password authentication

Elena Dvoryadkina

Contents

Set user passwords
Change user passwords
Set the default user password

ADQM can authenticate users by passwords. It supports the following password authentication methods that differ in how a password is stored on an ADQM server and transmitted over the connection:

sha256_password — ADQM stores a password hash calculated using the SHA-256 algorithm. For additional password protection, you can use salt (SALT) — an arbitrary string that is added to the beginning or end of a password before passing the password to the hash function.
double_sha1_password — ADQM stores a password hash calculated using the double SHA-1 algorithm (for MySQL compatibility).
plaintext_password — a password is stored unencrypted. Since this method is insecure, use it only if the connection is protected by SSL encryption.
no_password — a user can connect to ADQM without password.

You can assign a password to a user in a CREATE USER/ALTER USER query or in the users.xml configuration file.

Set user passwords

sha256_password

To use the SHA-256 algorithm for hashing a user’s password, you can specify the password in one of the following ways:

set the password as a string — ADQM will automatically calculate the SHA-256 hash for the entered string and save it;
calculate the SHA-256 hash for a desired password string by yourself and then pass the hash to ADQM.

Set password string

Use one of the following queries to set the user password as a string:

CREATE USER <user_name> IDENTIFIED WITH sha256_password BY '<user_password>';

CREATE USER <user_name> IDENTIFIED BY '<user_password>'

When executing these queries, ADQM adds a random SALT value to the entered password string, hashes the resulting value, and saves the hash and SALT value.

Example

Query:

CREATE USER andrew IDENTIFIED BY 'qwerty';

The output:

CREATE USER andrew IDENTIFIED WITH sha256_hash BY 'E422256F5E13AB9C590BAFE5A91592BCF2417E8F992D1C74B9CF138C4D3646FF' SALT '1EE6C0E7CA2F95253F6F0A5C2855AB4D7C00F55AB0BA83E5AB0ED505F168CC06'

Query id: e1de17ec-74fb-4b0a-ad25-e5ef2199d727

Ok.

0 rows in set. Elapsed: 0.001 sec.

To connect to ADQM as this user, provide the specified password:

$  clickhouse-client --user andrew --password qwerty

Set password hash

To assign a password to a user as a hash, use the following query:

CREATE USER <user_name> IDENTIFIED WITH sha256_hash BY '<hash>';

If you’ve used a SALT value for the hash calculation, pass the hash and salt to a query as follows:

CREATE USER <user_name> IDENTIFIED WITH sha256_hash BY '<hash>' SALT '<salt>';

Example

Generate a hash for the qwerty string:

$ PASSWORD="qwerty"; echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'

In the output, the first line is the password and the second line is the password hash:

qwerty
65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5

When creating a user, pass the generated hash:

CREATE USER alex IDENTIFIED WITH sha256_hash BY '65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5';

Make sure you can connect to ADQM as the created user with the qwerty password:

$  clickhouse-client --user alex --password qwerty

You can also set the SHA-256 hash in the users.xml configuration file — place it in the password_sha256_hex element:

<users>
    <user_name>
        <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>
        <!-- Other settings -->
    </user_name>
</users>

double_sha1_password

To use the double SHA-1 algorithm for hashing a user’s password, you can:

set the password as a string — ADQM will automatically calculate the double SHA-1 hash for the entered string and save it;
calculate the double SHA-1 hash for a desired password string by yourself and then pass the hash to ADQM.

Set password string

Use the following query to set the user password as a string:

CREATE USER <user_name> IDENTIFIED WITH double_sha1_password BY '<user_password>';

When executing this query, ADQM calculates the hash for the entered string using the double SHA-1 algorithm and stores the hash.

Example

Query:

CREATE USER john IDENTIFIED WITH double_sha1_password BY 'qwerty';

The output:

CREATE USER john IDENTIFIED WITH double_sha1_hash BY 'AA1420F182E88B9E5F874F6FBE7459291E8F4601'

Query id: 7441ff9b-d349-4c29-a687-61612da40323

Ok.

0 rows in set. Elapsed: 0.001 sec.

The john user can now connect to ADQM using its password:

$  clickhouse-client --user john --password qwerty

Set password hash

To assign a password as a double sha-1 hash, use the following query:

CREATE USER <user_name> IDENTIFIED WITH double_sha1_hash BY '<hash>';

Example

Generate a double sha-1 hash for the qwerty string:

$ PASSWORD="qwerty"; echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'

In the output, the first line is the password and the second line is the password hash:

qwerty
AA1420F182E88B9E5F874F6FBE7459291E8F4601

When creating a user, pass the generated hash to the CREATE USER query as follows:

CREATE USER mary IDENTIFIED WITH double_sha1_hash BY 'AA1420F182E88B9E5F874F6FBE7459291E8F4601';

Check that you can connect to ADQM as the mary user with the qwerty password:

$  clickhouse-client --user mary --password qwerty

You can also set a double SHA-1 hash for a user password in the users.xml configuration file. To do this, put the hash in the password_double_sha1_hex element:

<users>
    <user_name>
        <password_double_sha1_hex>AA1420F182E88B9E5F874F6FBE7459291E8F4601</password_double_sha1_hex>
        <!-- Other settings -->
    </user_name>
</users>

plaintext_password

ADQM stores a user password in the clear text format if you set the password in one of the following ways.

Use the IDENTIFIED WITH plaintext_password clause in a CREATE USER query:

CREATE USER <user_name> IDENTIFIED WITH plaintext_password BY '<user_password>';

Place a password string in the password element of the user configuration (the users section of the users.xml configuration file):

<users>
    <user_name>
        <password>user_password</password>
        <!-- Other settings -->
    </user_name>
</users>

Empty password element allows a user to connect to ADQM without a password:

<users>
    <user_name>
        <password></password>
        <!-- Other settings -->
    </user_name>
</users>

CAUTION

It is not recommended to store user passwords unencrypted.

no_password

You can also create an ADQM user without a password with one of the following queries:

CREATE USER <user_name>;

CREATE USER <user_name> IDENTIFIED WITH no_password;

The auth_type field of the system.users table for the user will contain the no_password value (unlike a user with an empty password element in users.xml — in this case, auth_type returns plaintext_password).

Change user passwords

To change the password for an existing user, execute the ALTER USER query:

ALTER USER <user_name>
    [NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}]
    BY {'<password>' | '<hash>'}}];

Example

Run the following query to change the password for the mary user:

ALTER USER mary IDENTIFIED WITH sha256_password BY 'test';

ADQM adds some SALT value to a new password and generates a new SHA-256 hash for resulting string:

ALTER USER mary IDENTIFIED WITH sha256_hash BY 'FDBC16CDEBD27426D53E8EF5AC17A02894CB195C3F213009913E5827228A7049' SALT '48F8DEDC2E1818BE4888553B8A55B2705F1E98494CB0387A36D6F89F06DF745B'

Query id: 6adb558b-3370-4829-9a60-f8264d366a00

Ok.

0 rows in set. Elapsed: 0.001 sec.

Now, to connect to ADQM as the mary user, you should use the test password:

$  clickhouse-client --user mary --password test

To modify a user password, you can also enter a new value in the corresponding element (password_sha256_hex, password_double_sha1_hex or password) for the user in the users.xml file.

Set the default user password

By default, ADQM creates a default user that can connect without a password. You can set or change this user password through the ADCM interface. To do this, go to the configuration settings of the ADQMDB service (ADQM cluster → Services → ADQMDB → Primary Configuration), set and confirm the password in the Configuration → Default user and policy settings section, click Save, and execute the Reconfig and restart action for the ADQMDB service.

Change the default user password

Found a mistake? Seleсt text and press Ctrl+Enter to report it