Taking ClickHouse® User Authentication to the Next Level: Multiple Authentication Methods Support

If you love ClickHouse®, Altinity can simplify your experience with our fully managed service in your cloud (BYOC) or our cloud and 24/7 expert support. Learn more.

One of the challenges in database security is ensuring that user credentials remain secure over time. A key part of this is regularly rotating passwords to reduce the risk of unauthorized access. When passwords are not rotated frequently, they are more likely to be compromised, whether through leaks, weak encryption, or plain guesswork.

Avoiding downtime while rotating passwords is a major concern. It is the famous chicken-and-egg problem: which should be updated first, the client applications or the database? Both will inevitably lead to downtime if not done carefully.

Traditionally, the method employed in ClickHouse is to create an entirely new user with updated credentials. Once the new user is up and running, the client applications can be safely updated. After making sure client applications are behaving properly and the old user is not being used anymore, the old user can be deleted and the credentials have been successfully rotated.

That approach ensures client applications are always using valid credentials by pointing to one of the two valid users.

At a second glance, the goal is to rotate passwords, not users. It sounds like overkill to duplicate an entire user in order to rotate passwords. Which raises the question: what if a user could hold two – or more – authentication methods? Not only that, but what if each authentication method could have its own expiration date? This is exactly what Altinity implemented in pull requests 65277 and 70090.

Multiple authentication methods support

This feature allows a user to be created or altered to a state with multiple authentication methods of the same and/or different type. All it takes is to specify the authentication methods in a comma separated list.

arthur :​​​) CREATE USER user1 IDENTIFIED WITH plaintext_password BY '1', sha256_password BY '2';

CREATE USER user1 IDENTIFIED WITH plaintext_password BY '1' sha256_password BY '2'

Query id: 35443f4d-272a-4478-976d-3be00366399d

Ok.

0 rows in set. Elapsed: 0.016 sec. 

The short syntax that relies on the default authentication type works as well:

CREATE USER user1 IDENTIFIED BY '1', sha256_password by '2';

Authentication methods can also be added to the existing list; no need to rewrite the whole list. This is done using the ADD keyword:

arthur :​​) ALTER USER user1 ADD IDENTIFIED WITH plaintext_password by '3';

ALTER USER user1 ADD IDENTIFIED WITH plaintext_password BY '3'

Query id: 68a2cf35-8151-438f-a506-8b5f9ba3c9ab

Ok.

0 rows in set. Elapsed: 0.011 sec. 

The SHOW CREATE USER query reveals the user has now three authentication methods.

arthur :​) show create user user1;

SHOW CREATE USER user1

Query id: 69da43b5-d4fe-4803-ac4a-42753697da00

   ┌─CREATE USER user1─────────────────────────────────────────────────────────────────────────┐
1. │ CREATE USER user1 IDENTIFIED WITH plaintext_password, sha256_password, plaintext_password
   └───────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.006 sec. 

And all of those can be used to log in:

laptop@arthur:~/work/ClickHouse$ cmake-build-debug/programs/clickhouse-client -mn --user user1 --password '1'
ClickHouse client version 24.11.1.1.
Connecting to localhost:9000 as user user1.
Connected to ClickHouse server version 24.11.1.

arthur :​​) exit;
Bye.
laptop@arthur:~/work/ClickHouse$ cmake-build-debug/programs/clickhouse-client -mn --user user1 --password '2'
ClickHouse client version 24.11.1.1.
Connecting to localhost:9000 as user user1.
Connected to ClickHouse server version 24.11.1.

arthur :​​) exit;
Bye.
laptop@arthur:~/work/ClickHouse$ cmake-build-debug/programs/clickhouse-client -mn --user user1 --password '3'
ClickHouse client version 24.11.1.1.
Connecting to localhost:9000 as user user1.
Connected to ClickHouse server version 24.11.1.

arthur :​) exit;
Bye.
laptop@arthur:~/work/ClickHouse$ cmake-build-debug/programs/clickhouse-client -mn --user user1 --password '4'
ClickHouse client version 24.11.1.1.
Connecting to localhost:9000 as user user1.
Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: user1: Authentication failed: password is incorrect, or there is no user with such name.. (AUTHENTICATION_FAILED)

One might say: ok, enough. I want to go back to a single authentication method, how do I do that? Or perhaps: how does the old ALTER USER user1 IDENTIFIED WITH plaintext_password by '1' fit in this new implementation? 

Previously, it would simply change the existing authentication method. It used to be like a password change operation.

Now, it still does the exact same thing. This feature is backwards-compatible, meaning the end result will be a user with a single authentication method, just like before.

arthur :​) ALTER USER user1 IDENTIFIED WITH no_password;

ALTER USER user1 IDENTIFIED WITH no_password

Query id: 2935ede7-f705-4c7e-856f-a977f093157f

Ok.

0 rows in set. Elapsed: 0.012 sec. 

arthur :​) show create user user1;

SHOW CREATE USER user1

Query id: f83e298a-62b4-4c51-94b0-3926c5a8b5a8

  ┌─CREATE USER user1─────────────────────────────┐
1 │ CREATE USER user1 IDENTIFIED WITH no_password │
  └───────────────────────────────────────────────┘

1 row in set. Elapsed: 0.006 sec. 

Behind the scenes, an IDENTIFIED WITH without the leading ADD keyword will drop all existing authentication methods in favor of the new one.

It is important to note that the NO_PASSWORD authentication type cannot co-exist with other authentication methods. Therefore, it can’t be present in an ADD IDENTIFIED WITH command or in a comma separated list of authentication methods.

arthur :​) CREATE USER user2 IDENTIFIED WITH plaintext_password by '1', no_password;

CREATE USER user2 IDENTIFIED WITH plaintext_password BY '1' no_password

Query id: bb84a68c-29bb-4f7d-bcf3-5dd79a32d742

Elapsed: 0.261 sec.

Received exception from server (version 24.11.1):
Code: 36. DB::Exception: Received from localhost:9000. DB::Exception: Authentication method 'no_password' cannot co-exist with other authentication methods. (BAD_ARGUMENTS)

arthur :​) ALTER USER user1 ADD IDENTIFIED WITH no_password;

Syntax error: failed at position 38 ('no_password'):

ALTER USER user1 ADD IDENTIFIED WITH no_password;

Expected one of: PLAINTEXT_PASSWORD, SHA256_PASSWORD, DOUBLE_SHA1_PASSWORD, LDAP, KERBEROS, SSL_CERTIFICATE, BCRYPT_PASSWORD, SSH_KEY, HTTP, JWT, SHA256_HASH, DOUBLE_SHA1_HASH, BCRYPT_HASH, end of query

Solving password rotation

Ok, but how does it help with password rotation? It is true the above alone does not fully solve the password rotation problem. To accomplish that, another SQL instruction has been added. The RESET AUTHENTICATION METHODS TO NEW, which was inspired by MySQL’s DISCARD OLD PASSWORD, drops all authentication methods and keeps the most recent one.

With multiple authentication methods support and the ability to keep only the most recent, it is now easier to rotate passwords. Just do:

arthur :​) CREATE USER user1 IDENTIFIED WITH plaintext_password by 'old_pwd';

CREATE USER user1 IDENTIFIED WITH plaintext_password BY 'old_password'

Query id: 6f6288c9-c2b3-42fb-8811-c3e1f83e97ec

Ok.

0 rows in set. Elapsed: 0.007 sec. 

arthur :​) ALTER USER user1 ADD IDENTIFIED WITH plaintext_password by 'new_pwd';

ALTER USER user1 ADD IDENTIFIED WITH plaintext_password BY 'new_pwd'

Query id: 6957aaa2-e5c0-4095-ab0c-145f892947de

Ok.

0 rows in set. Elapsed: 0.009 sec. 

Update the client applications, and then:

arthur :​) ALTER USER user1 RESET AUTHENTICATION METHODS TO NEW;

ALTER USER user1 RESET AUTHENTICATION METHODS TO NEW

Query id: ec5300b1-7bf7-4883-96ae-c26c09153b24

Ok.

0 rows in set. Elapsed: 0.005 sec. 

The RESET AUTHENTICATION METHODS TO NEW might not sound correct at first. It simply drops all authentication methods in favor of the most recent one and does not allow for more fine-grained control.

In order to have better control over which authentication methods should be dropped or updated, there would need to be a way to uniquely identify a password. That is hard to do while keeping the system safe. A few things come to mind.

1. A unique integer-based id for each authentication method. Bad. DBA needs to remember ids in order to drop the right ones. Error prone.
2. Named passwords. Bad, might be misused and become a security breach.
3. A unique integer-based id and a way to print passwords in plaintext on show create user. Bad. Security breach.

Therefore, in order to avoid security breaches and keep things simple, the RESET AUTHENTICATION METHODS TO NEW seemed like the only viable option.

To keep things safer and prevent misuse of the functionality, a safeguard setting has been added: max_authentication_methods_per_user. As the name suggests, this setting dictates the maximum number of authentication methods a user can hold. It defaults to 100. 

It is important to note that the setting will be checked only upon CREATE/ALTER statements. For instance, suppose max_authentication_methods_per_user is set to its default value (100). If there are 90 users and the limit is reduced to 85, this won’t prevent users from logging in. It’ll only prevent creation of new users until the limit has been adjusted.

(Note that this setting does not account for multiple SSH keys, although issue 70898 proposes to change this.)

Expiration time for authentication methods

It is true that ClickHouse already supported the VALID UNTIL clause, but prior to 70090, it was implemented as part of the user entity, not the authentication method. Therefore, the user itself expired, not the authentication method.

It was perhaps easier to just implement it this way and, in practice, there was not a difference. The client applications would not be able to log in either way. But once the Multiple authentication methods PR was merged, the design was broken.

The VALID UNTIL feature that was implemented as part of the user entity would now apply to all existing authentication methods, rather than to individual ones. Therefore, the code had to be re-designed to support individual expiration time.

The syntax is basically the same: just combine the multiple authentication methods in a comma-separated list with the VALID UNTIL clause.

arthur :​) CREATE USER user1 IDENTIFIED BY '1' VALID UNTIL 'infinity', by '2' valid until '2050-01-01';

CREATE USER user1 IDENTIFIED BY '1' VALID UNTIL 'infinity' BY '2' VALID UNTIL '2050-01-01'

Query id: ff6b4238-af6e-4abb-a3e0-8bc1a7bdf906

Ok.

0 rows in set. Elapsed: 0.013 sec. 

And the ALTER case:

arthur :​) ALTER USER user1 ADD IDENTIFIED WITH sha256_password by '3' VALID UNTIL '3000-01-01';

ALTER USER user1 ADD IDENTIFIED WITH sha256_password BY '3' VALID UNTIL '3000-01-01'

Query id: 8042786e-a7aa-4485-be08-fe0bf407d869

Ok.

0 rows in set. Elapsed: 0.005 sec. 

arthur :​) SHOW CREATE USER user1;

SHOW CREATE USER user1

Query id: 853f0603-aa7d-4acb-9365-5f45ce3dc94e

  ┌─CREATE USER user1───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE USER user1 IDENTIFIED WITH sha256_password, sha256_password VALID UNTIL '2050-01-01 00:00:00', sha256_password VALID UNTIL '2299-12-31 00:00:00' │
  └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.007 sec.

It is that simple: just add the VALID UNTIL clause after the authentication method. Ok, but what about backwards compatibility? It was perfectly fine to omit the authentication method since there was only one.

ALTER USER user1 VALID UNTIL '2040-01-01';

We’ve got you covered. This still works, and the end result is the same. The user won’t be able to log in with any of the existing authentication methods. Behind the scenes, it simply expires all authentication methods. In the source code, this is called the global_valid_until.

arthur :​​) ALTER USER user1 VALID UNTIL '2040-01-01';

ALTER USER user1 VALID UNTIL '2040-01-01'

Query id: 67213678-dd0e-467b-bd1d-176bb13a7936

Ok.

0 rows in set. Elapsed: 0.009 sec.

arthur :​​) SHOW CREATE USER user1;

SHOW CREATE USER user1

Query id: 7faa36e7-c9a9-4831-aea7-5e13ba0448d2

  ┌─CREATE USER user1─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE USER user1 IDENTIFIED WITH sha256_password VALID UNTIL '2040-01-01 00:00:00', sha256_password VALID UNTIL '2040-01-01 00:00:00', sha256_password VALID UNTIL '2040-01-01 00:00:00' │
  └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.007 sec.

Last but not least, the system.users table has been updated accordingly. The auth_type and auth_params columns were transformed into arrays to accommodate multiple authentication methods.

Summary

The introduction of multiple authentication methods in ClickHouse enhances database security and user management. This feature allows a single user to have multiple authentication methods simultaneously, eliminating the practice of creating duplicate users solely for credential updates. Administrators can now add new authentication methods to a user and, when appropriate, discard all previous methods except the most recent one using a single SQL command. The new approach streamlines the password rotation process, reduces administrative overhead, and minimizes potential downtime.

While the ideal scenario would include fine-grained control to drop or update individual authentication methods, implementing such functionality poses challenges. The main issue lies in uniquely identifying each authentication method without compromising security. Since there’s no straightforward way to achieve this, we kept it simple and adopted an approach that has been validated by other systems: a single instruction that discards all but the most recent authentication method.

Enforcing security policies also involves ensuring that authentication methods have a defined lifespan, including short-lived passwords when appropriate. This is accomplished by allowing an exclusive VALID UNTIL clause per authentication method. 

Last but not least, it opens up the door for short-lived credentials and integration with more complex types of authentication. All in all, this is a great feature that lets you improve the security posture of your ClickHouse cluster, its users, and its client applications in a simple, elegant way. Let us know what you think of the new feature! You can join our Slack workspace or contact us directly to talk.