ClickHouse: User account locking after given number of consecutive login failures

Use case Lock users after given number of failed login attempts.

Describe the solution you’d like MySQL has the user account locking feature and we need about the same feature in ClickHouse. It’s a part of ClickHouse certification requirements.

This article explains how does it work in MySQL.

In MySQL it’s possible:

CREATE USER ‘percona’@’localhost’ IDENTIFIED BY ‘password’ FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 3;

Where is PASSWORD_LOCK_TIME is number of days. Probably in ClickHouse it should be number of seconds (this is the topic to discuss). This way we may lock the user for given number of hours, for example.

Probably in the initial version we may implement it without the usage of FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME parameters in CREATE USER, for example, we can simply add the parameters in <users> sections or even better in <profiles> sections of users.xml.

In MySQL the lock status is saved in account_locked column of mysql.user system table. In ClickHouse system.users table is auto-generated. We cannot save information there.

Therefore the main problem is where to save account_locked and other needed columns (like number of failed attempts)?

Variant 1: new system.user_status system table

Right now most of system tables like query_log have SystemLog as base class and it’s possible only to append log data using flushImpl().

In PR #54410 @sirvickr is implementing SystemLogStorage and derived BackupsStorage class with:

1. flushImpl() to append data
2. update() to update data. The same approach we may use in new UserStatus system table as derived class of SystemLogStorage base class.

Variant 2: use existing system.session_log table

We may run a complex SQL query on system.session_log table to calculate number of failed attempts etc. But this is not probably the best way to run the complex query on non-production level system.session_log table for checking user lockout status. Second: it’s possible to turn off system.session_log in config.xml.

@Demilivor is already working on this feature. The authorizations part of work is clear how to implement. The unclear part of about the storage.

@tavplubix do you have any suggestions about which storage to use for account_locked etc?

@den-crane may you can remember how it’s implemented in other DBMSs?

cc @alexey-milovidov, @rschu1ze