Increasing ClickHouse security at the user level involves the following major 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 information as hashed values.
Set Quotas: Limit how many resources users can use in given intervals.
Use Profiles: Use profiles to set common security settings across multiple accounts.
Database Restrictions: Narrow 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.
IMPORTANT NOTE: Configuration settings can be stored in the default
/etc/clickhouse-server/config.xmlfile. However, this file can be overwritten during vendor upgrades. To preserve configuration settings it is recommended to store them in
/etc/clickhouse-server/config.das separate XML files.
The hardening steps to apply to users are:
- Restrict user access only to the specific host names or IP addresses when possible.
- Store all passwords in SHA256 format.
- Set quotas on user resources for users when possible.
- Use profiles to set similar properties across multiple users, and restrict user to the lowest resources required.
- Offload user authentication through LDAP or Kerberos.
Users can be configured through the XML based settings files, or through SQL based commands.
Detailed information on ClickHouse user configurations can be found on the ClickHouse.Tech documentation site for User Settings.
User XML Settings
Users are listed under the
user.xml file under the
users element. Each element under
users is created as a separate user.
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
Note that if your ClickHouse environment is to be run as a cluster, then user configuration files must be replicated on each node with the relevant users information. We will discuss how to offload some settings into other systems such as LDAP later in the document.
Also note that ClickHouse user names are case sensitive:
John is different than
john. See the ClickHouse.tech documentation site for full details.
- IMPORTANT NOTE: If no user name is specified when a user attempts to login, then the account named
defaultwill be used.
For example, the following section will create two users:
- clickhouse_operator: This user has the password
clickhouse_operator_passwordstored in a sha256 hash, is assigned the profile
clickhouse_operator, and can access the ClickHouse database from any network host.
- John: This user can only access the database from
localhost, has a basic password of
Johnand is assigned to the
<users> <clickhouse_operator> <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_operator> <John> <networks> <ip>127.0.0.1</ip> </networks> <password_sha456_hex>73d1b1b1bc1dabfb97f216d897b7968e44b06457920f00f2dc6c1ed3be25ad4c</password_sha256_hex> <profile>default</profile> </John> </users>
User SQL Settings
ClickHouse users can be managed by SQL commands from within ClickHouse. For complete details, see the Clickhouse.tech User Account page.
Access management must be enabled at the user level with the
access_management setting. In this example, Access Management is enabled for the user John:
<users> <John> <access_management>1</access_management> </John> </users>
The typical process for DCL(Data Control Language) queries is to have one user enabled with access_management, then have the other accounts generated through queries. See the ClickHouse.tech Access Control and Account Management page for more details.
Once enabled, Access Management settings can be managed through SQL queries. For example, to create a new user called newJohn 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 newJohn IDENTIFIED WITH SHA256_PASSWORD BY 'secret' HOST IP '192.168.128.1/24' SETTINGS readonly=1;
Access Management through SQL commands includes the ability to:
- Set roles
- Apply policies to users
- Set user quotas
- Restrict user access to databases, tables, or specific rows within tables.
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
User access from specific networks can be set through SQL commands. For complete details, see the Clickhouse.tech Create User page.
Network access is controlled through the
HOST option when creating or altering users. Host options include:
- ANY (default): Users can connect from any location
- LOCAL: Users can only connect locally.
- IP: A specific IP address or subnet.
- NAME: A specific FQDN (Fully Qualified Domain Name)
- REGEX: Filters hosts that match a regular expression.
- LIKE: Filters hosts by the LIKE operator.
For example, to restrict the user john to only connect from the local subnet of ‘192.168.0.0/16’:
ALTER USER john HOST IP '192.168.0.0/16';
Or to restrict this user to only connecting from the specific host names
ALTER USER john HOST REGEXP 'awesomeplace.com';
User Network XML Settings
User network settings are stored under the user configuration files
/etc/clickhouse-server/config.d with the
<networks> element controlling the sources that the user can connect from through the following settings:
<ip>: IP Address or subnet mask.
<host_regexp>: Regular expression of the host name.
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 supercool1.com, supercool2.com, etc:
<networks> <host>example.com</host> <host_regexp>supercool.com</host_regexp> </networks>
If there are hosts or other settings that are applied across multiple accounts, one option is to use the Substitution feature as detailed in the ClickHouse.tech Configuration Files page. For example, in the
/etc/metrika.xml. file used for substitutions, a
local_networks element can be made:
<local_networks> <ip>192.168.1.0/24</ip> </local_networks>
This can then be applied to a one or more users with the
incl attribute when specifying their network access:
<networks incl="local_networks" replace="replace"> </networks>
Passwords can be stored in plaintext or SHA256 (hex format).
SHA256 format passwords are labeled with the
<password_sha256_hex> element. SHA256 password can be generated through the following command:
echo -n "secret" | sha256sum | tr -d '-'
echo -n "secret" | shasum -a 256 | tr -d '-'
- IMPORTANT NOTE: The -n option removes the newline from the output.
echo -n "clickhouse_operator_password" | shasum -a 256 | tr -d '-' 716b36073a90c6fe1d445ac1af85f4777c5b7a155cea359961826a030513e448
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.tech Create User page. The following secure password options are available:
- sha256password BY ‘STRING’: Converts the submitted STRING value to sha256 hash.
- sha256_hash BY ‘HASH’ (best option): Stores the submitted HASH directly as the sha256 hash password value.
- double_sha1_password BY ‘STRING’ (only used when allowing logins through mysql_port): Converts the submitted STRING value to double sha256 hash.
- double_sha1_hash BY ‘HASH’(only used when allowing logins through mysql_port): Stores the submitted HASH directly as the double sha256 hash password value.
For example, to store the sha256 hashed value of “password” for the user John:
ALTER USER John 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.tech User Settings.
To set a user’s password with a sha256 hash, use the
password_sha256_hex branch for the user. For example, to set the sha256 hashed value of “password” for the user John:
<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 to tie up resources in the system. More details can be found on the ClickHouse.tech Quotas page.
Quota SQL Settings
Quotas can be created or altered through SQL queries, then applied to users.
For more information on ClickHouse quotas, see the ClickHouse.tech Access Control page on Quotas.
Quota XML Settings
These are defined in the
users.xml file under the element
quotas. Each branch of the quota element is the name of the quota being defined.
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. More details on Settings Profiles are available on the ClickHouse.tech site.
Profile XML Settings
Profiles are applied to a user with the profile element. For example, this assigns the
restricted profile to the user
<users> <John> <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>restricted</profile>
Profiles are set in the users.xml file under the profiles element. Each branch of this element is the name of a profile. The profile restricted shown here only allows for eight threads to be used at a time for users with this profile:
<profiles> <restricted> <!-- The maximum number of threads when running a single query. --> <max_threads>8</max_threads> </default> </profiles>
Recommended profile settings include the following:
readonly: This sets the profile to be applied to users but not to be changed.
max_execution_time: Limits the amount of time a process will run before being forced to time out.
max_bytes_before_external_group_by: Maximum RAM allocated for a single
max_bytes_before_external_sort: Maximum RAM allocated for sort commands.
Restrict users to the databases they need, and when possible only the tables or rows within tables that they require access to.
Full details are found on the ClickHouse.tech User Settings documentation.
Database Restrictions XML Settings
To restrict a user’s access by data in the XML file:
- Update user configuration files in
/etc/clickhouse-server/config.dor update their permissions through SQL queries.
- For each user to update:
- Add the
<databases>element with the following branches:
- The name of the database to allow access to.
- Within the database, the table names allowed to the user.
- Within the table, add a
<filter>to match rows that fit the filter.
- Add the
Database Restrictions XML Settings Example
The following restricts the user
John to only access the database
sales, and from there only the table marked
salesman = 'John':
<John> <databases> <sales> <clients> <filter>salesman = 'John'</filter> </clients> </sales> </databases> </John>
Enable Remote Authentication
One issue with user settings is that in a cluster environment, each node requires a separate copy of the user configuration files, which includes a copy of the sha256 encrypted password.
One method of reducing the exposure of user passwords, even in a hashed format in a restricted section of the file system, it to use external authentication sources. This prevents password data from being stored in local file systems and allows changes to user authentication to be managed from one source.
LDAP servers are defined in the ClickHouse configuration settings such as
/etc/clickhouse-server/config.d/ldap.xml. For more details, see the ClickHouse.tech site on Server Configuration settings.
Enabling LDAP server support in ClickHouse allows you to have one authority on login credentials, set password policies, and other essential security considerations through your LDAP server. It also prevents password information being stored on your ClickHouse servers or cluster nodes, even in a SHA256 hashed form.
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;
When the user attempts to authenticate to ClickHouse, their credentials will be verified against the LDAP server specified from the configuration files.