User Hardening

User hardening security procedures.

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.xml file. However, this file can be overwritten during vendor upgrades. To preserve configuration settings it is recommended to store them in /etc/clickhouse-server/config.d as separate XML files.

User Configuration

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 /etc/clickhouse-server/users.d/.

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 default will be used.

For example, the following section will create two users:

  • clickhouse_operator: This user has the password clickhouse_operator_password stored 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 John and is assigned to the default profile.
<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 0.0.0.0/0 for IPv4, ::/0 for IPv6
  • 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 awesomeplace1.com, awesomeplace2.com, etc:

ALTER USER john
  HOST REGEXP 'awesomeplace[12345].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>: Hostname.
  • <host_regexp>: Regular expression of the host name.

For example, the following will allow only from localhost:

<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[1234].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>

Secure Password

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 '-'

OR:

echo -n "secret" | shasum -a 256 | tr -d '-'
  • IMPORTANT NOTE: The -n option removes the newline from the output.

For example:

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>

Set Quotas

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>

Use Profiles

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 John:

<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 GROUP BY sort.
  • max_bytes_before_external_sort: Maximum RAM allocated for sort commands.

Database Restrictions

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:

  1. Update user configuration files in /etc/clickhouse-server/config.d or update their permissions through SQL queries.
  2. For each user to update:
    1. Add the <databases> element with the following branches:
      1. The name of the database to allow access to.
      2. Within the database, the table names allowed to the user.
      3. Within the table, add a <filter> to match rows that fit the filter.

Database Restrictions XML Settings Example

The following restricts the user John to only access the database sales, and from there only the table marked clients where 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.

Enable LDAP

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 settings:

<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.