CIS ClickHouse Benchmark

This recommendation verifies that ClickHouse is running the latest stable version on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Verify ClickHouse version
clickhouse-client --query 'SELECT version()'
clickhouse-server --version

# Update ClickHouse to latest stable
apt-get update && apt-get install --only-upgrade clickhouse-server clickhouse-client
systemctl restart clickhouse-server

This recommendation verifies that ClickHouse runs as a dedicated non-root user on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check ClickHouse runs as dedicated user
ps -eo user,pid,comm | grep clickhouse
grep '^clickhouse' /etc/passwd

# Configure dedicated user
# ClickHouse installs as 'clickhouse' user by default
chown -R clickhouse:clickhouse /var/lib/clickhouse /var/log/clickhouse-server /etc/clickhouse-server

This recommendation verifies that strong password authentication is configured on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check authentication configuration
grep -E '<password|<users>' /etc/clickhouse-server/users.xml
grep -A5 'default' /etc/clickhouse-server/users.d/*.xml 2>/dev/null

# Configure strong authentication
# In /etc/clickhouse-server/users.d/default-password.xml:
<clickhouse>
  <users>
    <default>
      <password_sha256_hex>$(echo -n 'SecurePass!' | sha256sum | head -c 64)</password_sha256_hex>
    </default>
  </users>
</clickhouse>
# Or use LDAP/Kerberos:
# <ldap_server>ldap.internal</ldap_server>

This recommendation verifies that RBAC roles and user profiles are configured on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check user access controls and profiles
clickhouse-client --query "SELECT name, storage FROM system.users"
clickhouse-client --query "SELECT * FROM system.settings_profiles" | head -20

# Configure RBAC with SQL
clickhouse-client --query "CREATE USER reader IDENTIFIED BY 'pass' SETTINGS PROFILE 'readonly'"
clickhouse-client --query "CREATE ROLE data_reader"
clickhouse-client --query "GRANT SELECT ON database.* TO data_reader"
clickhouse-client --query "GRANT data_reader TO reader"
# Disable default user external access:
# <networks><ip>127.0.0.1</ip></networks>

This recommendation verifies that TLS is enabled for client HTTPS connections on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check TLS configuration
grep -E '<https_port|<openSSL>' /etc/clickhouse-server/config.xml

# Configure TLS
# In /etc/clickhouse-server/config.xml:
<https_port>8443</https_port>
<openSSL>
  <server>
    <certificateFile>/etc/clickhouse-server/ssl/server.crt</certificateFile>
    <privateKeyFile>/etc/clickhouse-server/ssl/server.key</privateKeyFile>
    <caConfig>/etc/clickhouse-server/ssl/ca.crt</caConfig>
    <verificationMode>strict</verificationMode>
    <disableProtocols>sslv2,sslv3,tlsv1,tlsv1_1</disableProtocols>
  </server>
</openSSL>

This recommendation verifies that inter-server communication uses TLS with credentials on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check inter-server TLS
grep -E 'interserver_https_port|interserver_http_credentials' /etc/clickhouse-server/config.xml

# Configure inter-server TLS
# In config.xml:
<interserver_https_port>9010</interserver_https_port>
<interserver_http_credentials>
  <user>interserver</user>
  <password>$(openssl rand -base64 24)</password>
</interserver_http_credentials>
# Disable plaintext ports:
<!-- <http_port>8123</http_port> -->
<!-- <interserver_http_port>9009</interserver_http_port> -->

This recommendation verifies that query logging with thread tracking is enabled on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check query logging
clickhouse-client --query "SELECT count() FROM system.query_log WHERE event_date = today()"
grep 'query_log' /etc/clickhouse-server/config.xml

# Configure query logging
# In config.xml:
<query_log>
  <database>system</database>
  <table>query_log</table>
  <partition_by>toYYYYMM(event_date)</partition_by>
  <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
<query_thread_log>
  <database>system</database>
  <table>query_thread_log</table>
</query_thread_log>

This recommendation verifies that server log rotation is configured on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check server log configuration
grep -E '<log>|<errorlog>|<logger>' /etc/clickhouse-server/config.xml

# Configure server logging
# In config.xml:
<logger>
  <level>information</level>
  <log>/var/log/clickhouse-server/clickhouse-server.log</log>
  <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
  <size>1000M</size>
  <count>10</count>
</logger>
# Configure log rotation:
find /var/log/clickhouse-server -name '*.log.*' -mtime +30 -delete

This recommendation ensures that ClickHouse binds only to required interfaces on the ClickHouse columnar database. Enforcing this requirement establishes a minimum security standard and prevents insecure configurations.

Without this enforcement, the ClickHouse columnar database may accept insecure configurations that weaken overall security posture. Mandating this control ensures consistent protection against common attack vectors.

# Check network binding and listen
grep -E '<listen_host|<http_port|<tcp_port' /etc/clickhouse-server/config.xml
ss -tlnp | grep clickhouse

# Restrict network binding
# In config.xml:
<listen_host>10.0.1.5</listen_host>
<!-- Remove: <listen_host>::</listen_host> -->
# Firewall:
ufw allow from 10.0.0.0/8 to any port 8443
ufw allow from 10.0.0.0/8 to any port 9440

This recommendation verifies that query and memory resource limits are configured on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check max connections and query limits
clickhouse-client --query "SELECT name, value FROM system.settings WHERE name LIKE '%max%' AND changed" | head -20

# Configure resource limits in users.xml or profiles:
<profiles>
  <restricted>
    <max_execution_time>30</max_execution_time>
    <max_memory_usage>10000000000</max_memory_usage>
    <max_rows_to_read>1000000000</max_rows_to_read>
    <max_concurrent_queries_for_user>10</max_concurrent_queries_for_user>
    <readonly>0</readonly>
  </restricted>
</profiles>

This setting ensures that data directory permissions are restricted on the ClickHouse columnar database. Restricting this capability limits potential abuse and enforces the principle of least privilege across the environment.

Unrestricted access to this capability could allow unauthorized users or processes to perform actions beyond their intended scope. Applying least-privilege principles to the ClickHouse columnar database is essential for defense in depth.

# Check data directory permissions
stat -c '%a %U %G' /var/lib/clickhouse
find /var/lib/clickhouse -maxdepth 1 -type d -exec stat -c '%a %n' {} \;

# Restrict data directory permissions
chown -R clickhouse:clickhouse /var/lib/clickhouse
chmod 750 /var/lib/clickhouse
chmod 640 /etc/clickhouse-server/config.xml
chmod 640 /etc/clickhouse-server/users.xml
chmod 600 /etc/clickhouse-server/ssl/*.key
chmod 750 /etc/clickhouse-server

This recommendation verifies that encryption at rest is configured for sensitive data on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check data encryption at rest
grep -E 'encryption_codecs|encrypted' /etc/clickhouse-server/config.xml

# Configure encryption at rest
# In config.xml:
<encryption_codecs>
  <aes_128_gcm_siv>
    <key_hex>$(openssl rand -hex 16)</key_hex>
  </aes_128_gcm_siv>
</encryption_codecs>
# Apply to tables:
# CREATE TABLE secure_table (...) ENGINE = MergeTree
# SETTINGS storage_policy='encrypted'

This recommendation verifies that automated backup procedures are configured on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check backup configuration
clickhouse-client --query "SELECT * FROM system.backups" 2>/dev/null
ls /var/lib/clickhouse/backups/ 2>/dev/null

# Configure backup procedures
clickhouse-client --query "BACKUP DATABASE production TO Disk('backups', 'daily/$(date +%F).zip')"
# Schedule automated backups:
0 2 * * * clickhouse-client --query "BACKUP DATABASE production TO Disk('backups', 'daily/$(date +\%F).zip') SETTINGS compression_method='lz4'"
# Configure backup disk in config.xml:
<storage_configuration>
  <disks>
    <backups><path>/mnt/backups/clickhouse/</path></backups>
  </disks>
</storage_configuration>

This recommendation verifies that data retention TTL policies are configured on the ClickHouse columnar database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the ClickHouse columnar database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check TTL and merge settings
clickhouse-client --query "SELECT database, table, name FROM system.parts WHERE active AND modification_time < now() - INTERVAL 90 DAY" | head -20

# Configure data retention with TTL
# ALTER TABLE events MODIFY TTL event_date + INTERVAL 90 DAY DELETE;
# Configure merge settings:
# In config.xml:
<merge_tree>
  <max_suspicious_broken_parts>5</max_suspicious_broken_parts>
  <parts_to_throw_insert>300</parts_to_throw_insert>
</merge_tree>
# Monitor merges:
clickhouse-client --query "SELECT * FROM system.merges"