In this article we will talk about how to secure your users and will enrich article with examples.
First lets start with users security options.
Increasing ClickHouse security at the user level involves the following steps:
User Configuration: Setup secure default users, roles and permissions through configuration or SQL.
User Network Settings: Limit communications by hostname or IP address
Secure Password: Store user informations as hashed.
Quotas: Limit how many resources users can use in given intervals.
Profiles: Use profiles to set common security settings across multiple accounts.
Database Restrictions: Set limitation the databases, tables and rows that a user can access.
Enable Remote Authentication: Enable LDAP authentication or Kerberos authentication to prevent storing hashed password information, and enforce password standards.
User XML Settings
Users informations stored inside the
user.xml file under the
It is recommended that when creating users, rather than lumping them all into the user.xml file is to place them as separate XML files under the directory users.d, typically located in /etc/clickhouse-server/users.d/.
For example, the following section will create two users called ClickHouse and Paul:
- clickhouse: This user has the password
clickhouse_passwordstored in a sha256 hash, is assigned the profile
clickhouse, and can access the ClickHouse database from any network host.
- Paul: User can only access the database from
localhost, has a basic password of
Pauland is assigned to the
<users> <clickhouse> <networks> <ip>127.0.0.1</ip> <ip>0.0.0.0/0</ip> <ip>::/0</ip> </networks> <password_sha256_hex>716b36073a90c6fe1d445ac1af85f4777c5b7a155cea359961826a030513e448</password_sha256_hex> <profile>clickhouse_operator</profile> <quota>default</quota> </clickhouse> <Paul> <networks> <ip>127.0.0.1</ip> </networks> <password_sha456_hex>73d1b1b1bc1dabfb97f216d897b7968e44b06457920f00f2dc6c1ed3be25ad4c</password_sha256_hex> <profile>default</profile> </Paul> </users>
User SQL Settings
Access management must be enabled at the user level with the
access_management setting. In this example, Access Management is enabled for the user Paul:
<users> <Paul> <access_management>1</access_management> </Paul> </users>
Once Access Management is enabled, settings can be managed through SQL commands. For example, to create a new user called Michelle with their password set as a sha256 hash and restricted to a specific IP address subnet, the following SQL command can be used:
CREATE USER IF NOT EXISTS Michelle IDENTIFIED WITH SHA256_PASSWORD BY 'verysecretpassword' HOST IP '192.168.22.47/24' SETTINGS readonly=1;
User Network Settings
Users can have their access to the ClickHouse environment restricted by the network they are accessing the network from. Users can be restricted to only connect from:
- IP: IP address or netmask.
- For all IP addresses, use
- For all IP addresses, use
- Host: The DNS resolved hostname the user is connecting from.
- Host Regexp (Regular Expression): A regular expression of the hostname.
Accounts should be restricted to the networks that they connect from when possible.
User Network SQL Settings
For example, to restrict the user Paul to only connect from the local subnet of ‘192.168.0.0/16’:
ALTER USER Paul HOST IP '192.168.0.0/16';
Or to restrict this user to only connecting from the specific host names
ALTER USER Paul HOST REGEXP 'chistadata.com';
User Network XML Settings
For example, the following will allow only from
<networks> <ip>127.0.0.1</ip> </networks>
The following will restrict the user only to the site example.com or from chistadata1.com, chistadata2.com:
<networks> <host>example.com</host> <host_regexp>chistadata.com</host_regexp> </networks>
Passwords can be stored in plaintext or SHA256.
SHA256 format passwords are labeled with the
<password_sha256_hex> element in the config file. SHA256 password can be generated through the following command:
To generate a SHA256 password please run the following command:
echo -n "password" | sha256sum | tr -d '-'
echo -n "PasswordForUserPaul" | shasum -a 256 | tr -d '-' c15bc95c03932a67f8c3d6b6370e44416b62fd690982c591754afb2f375cdc36
Secure Password SQL Settings
Passwords can be set when using the
CREATE USER OR
ALTER USER with the
IDENTIFIED WITH option. For complete details, see the ClickHouse Official Create User page.
For example, to store the sha256 hashed value of “password” for the user Paul:
ALTER USER Paul IDENTIFIED WITH sha256_hash BY '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8';
Secure Password XML Settings
Passwords can be set as part of the user’s settings in the user configuration files in
/etc/clickhouse-server/config.d. For complete details, see the Clickhouse Offical User Settings Page.
<users> <John> <password_sha256_hex>5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8</password_sha256_hex> </John> </users>
Quotas set how many resources can be accessed in a given time, limiting a user’s ability in the system. To more details please visit ClickHouse Official Quotas page.
Quota XML Settings
Quotas are set by intervals, which can be set to different restrictions. For example, this quota named
limited has one interval that sets maximum queries at 1000, and another interval that allows a total of 10000 queries over a 24 hour period.
<quotas> <limited> <interval> <duration>3600</duration> <queries>1000</queries> </interval> <interval> <duration>86400</duration> <queries>10000</queries> </limited> </quotas>
Profiles allow settings that can be applied to multiple uses applied with the same name. To more details please visit Official ClickHouse Docs.
Profile XML Settings
Profiles are applied to a user with the profile element. For example, this assigns the
restricted profile to the user
<users> <Paul> <networks> <ip>127.0.0.1</ip> <ip>0.0.0.0/0</ip> <ip>::/0</ip> </networks> <password_sha256_hex>c15bc95c03932a67f8c3d6b6370e44416b62fd690982c591754afb2f375cdc36</password_sha256_hex> <profile>restricted</profile>
<profiles> <restricted> <!-- The maximum number of threads when running a single query. --> <max_threads>8</max_threads> </default> </profiles>
Restrict users to the databases they need, and when possible only the tables or rows within tables that they require access to.
To more details please visit ClickHouse Official Docs.
Database Restrictions XML Settings
The following restricts the user
Paul to only access the database
employee, and from there only the table marked
employee = 'Paul':
<Paul> <databases> <employee> <clients> <filter>employee = 'Paul'</filter> </clients> </employee> </databases> </Paul>
Enable Remote Authentication
One method of reducing the exposure of user passwords is to use external authentication sources. This avoids storing password data on local file systems and allows changes in user authentication to be managed from a single source.
LDAP servers are defined in the ClickHouse configuration settings like
/etc/clickhouse-server/config.d/ldap.xml. For more details, check the ClickHouse Official Docs.
To add one or more LDAP servers to your ClickHouse environment, each node will require the
<ldap> <server>ldapserver_hostname</server> <roles> <my_local_role1 /> <my_local_role2 /> </roles> </ldap>
When creating users, specify the ldap server for the user:
create user if not exists newUser identified with ldap by 'ldapserver_hostname' host any;