Security Best Practices for ClickHouse® Deployments

Introduction

ClickHouse® is increasingly being deployed in production environments to power real-time analytics, business intelligence dashboards, and data pipelines. As with any database system, securing your ClickHouse® deployment is critical, especially when it stores sensitive business data, customer information, or financial records.

By default, ClickHouse® prioritizes performance and flexibility. However, a default installation is not production-secure out of the box. Without proper configuration, your ClickHouse® instance could be exposed to unauthorized access, data breaches, or accidental data loss.

In this blog, you'll learn practical security best practices that can help you build a secure and reliable ClickHouse® deployment.

Why Database Security Matters

A secure ClickHouse® deployment helps protect against:

• Unauthorized database access
• Data leaks
• Insider threats
• Accidental data modification
• Denial-of-service attacks
• Compliance violations

Implementing security from the beginning reduces operational risks and improves overall system reliability.

Common Security Best Practices

1. Disable the Default User

By default, ClickHouse® creates a default user with no password. In production environments, avoid using this account for application access.

Instead:

• Set a password for the default user
• Create dedicated users
• Assign strong passwords
• Disable unnecessary accounts

Set a password using clickhouse-client:

CREATE USER analytics IDENTIFIED BY 'StrongPassword123!';

Or set it directly in users.xml:

 <users>
    <default>
        <password>your_strong_password</password>
    </default>
</users>
 

Important: Never leave the default user without a password in any environment - development, staging, or production.

2. Use Role-Based Access Control (RBAC)

Avoid using the default user for application connections. Instead, create dedicated users with only the permissions they need.

Create a read-only user:

CREATE USER analytics_user
IDENTIFIED BY 'strong_password';
 
GRANT SELECT ON default.* TO analytics_user;

Key principle: Each application or service connecting to ClickHouse® should have its own dedicated user with only the minimum permissions required.

Grant only what is necessary:

-- Good: specific table access
GRANT SELECT ON sales.orders TO analyst;
 
-- Avoid: full admin access
GRANT ALL ON *.* TO analyst;

Users should receive only the permissions required for their responsibilities.

3. Restrict Network Access

By default, ClickHouse® listens on all network interfaces. In production, restrict it to only the interfaces and IP addresses that need access. Never expose ClickHouse® directly to the public internet.

Bind ClickHouse® to localhost only:

<!-- /etc/clickhouse-server/config.xml -->
<listen_host>127.0.0.1</listen_host>

Restrict a user to a specific IP range:

CREATE USER analytics_user
IDENTIFIED BY 'strong_password'
HOST IP '10.0.0.0/24';

Recommended approaches:

• Use firewalls
• Restrict trusted IP addresses
• Deploy behind VPNs or private networks
• Close unused ports

4. Use Firewall Rules

Even with ClickHouse® configured to listen on specific interfaces, always add firewall rules as an additional layer of protection.

# Allow HTTP interface (port 8123) only from trusted IP
sudo ufw allow from 10.0.0.5 to any port 8123
 
# Allow native TCP interface (port 9000) only from trusted IP
sudo ufw allow from 10.0.0.5 to any port 9000
 
# Block all other access to ClickHouse® ports
sudo ufw deny 8123
sudo ufw deny 9000

Default ClickHouse® ports to secure:

5. Enable SSL/TLS Encryption

By default, ClickHouse® transmits data over unencrypted connections. In production, always enable TLS to encrypt data in transit.

Generate a self-signed certificate (for testing):

openssl req -newkey rsa:2048 -nodes \
  -keyout /etc/clickhouse-server/server.key \
  -x509 -days 365 \
  -out /etc/clickhouse-server/server.crt

Enable TLS in config.xml:

<!-- /etc/clickhouse-server/config.xml -->
<https_port>8443</https_port>
<tcp_port_secure>9440</tcp_port_secure>
 
<openSSL>
    <server>
        <certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
        <privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
        <verificationMode>none</verificationMode>
        <cacheSessions>true</cacheSessions>
    </server>
</openSSL>

Connect using HTTPS:

curl https://localhost:8443/?query=SELECT+1 \
  -u default:password \
  --cacert /etc/clickhouse-server/server.crt

For production: Use certificates from a trusted Certificate Authority (CA) rather than self-signed certificates.

6. Set Resource Quotas and Limits

Without limits, a single user or query can consume all available resources and affect other users. Use quotas to prevent this.

Create a quota:

CREATE QUOTA analytics_quota
    FOR INTERVAL 1 HOUR
    MAX queries = 1000,
    MAX read_rows = 1000000000,
    MAX result_rows = 10000000
TO analytics_user;

7. Monitor Resource Usage

Unexpected spikes in resource usage may indicate malicious activity or poorly optimized queries.

Monitor using system tables:

-- View current metrics
SELECT * FROM system.metrics;
 
-- View active queries
SELECT * FROM system.processes;

Monitor regularly for:

• CPU and memory spikes
• Unusual disk usage
• High network traffic
• Long-running queries

8. Enable Query Logging and Auditing

Logging all queries is essential for security auditing, debugging, and compliance.

Enable query logging in config.xml:

<!-- /etc/clickhouse-server/config.xml -->
<query_log>
    <database>system</database>
    <table>query_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>

Query the log to audit recent activity:

SELECT *
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 DAY
ORDER BY event_time DESC
LIMIT 20;

You can audit:

• Login activity
• Long-running queries
• Failed queries
• Heavy resource usage
• User activity

Regular monitoring helps identify potential security issues early.

9. Disable Unused Interfaces

ClickHouse® exposes several interfaces by default. Disable any interfaces you do not use to reduce the attack surface.

<!-- Disable MySQL compatibility interface -->
<!-- <mysql_port>9004</mysql_port> -->
 
<!-- Disable PostgreSQL compatibility interface -->
<!-- <postgresql_port>9005</postgresql_port> -->
 
<!-- Disable interserver HTTP port if not using replication -->
<!-- <interserver_http_port>9009</interserver_http_port> -->

10. Secure the Configuration Files

ClickHouse® configuration files contain sensitive information including passwords and connection strings. Secure them at the file system level.

# Restrict config file permissions
sudo chmod 640 /etc/clickhouse-server/users.xml
sudo chmod 640 /etc/clickhouse-server/config.xml
 
# Ensure only the clickhouse user can read them
sudo chown clickhouse:clickhouse /etc/clickhouse-server/users.xml
sudo chown clickhouse:clickhouse /etc/clickhouse-server/config.xml

11. Keep ClickHouse® Updated

Security vulnerabilities are regularly discovered and patched. Always keep your ClickHouse® version up to date.

Benefits of upgrading include:

• Security patches
• Bug fixes
• Performance improvements
• New security features

Always test upgrades in a staging environment before deploying to production. Subscribe to the ClickHouse® release notes to stay informed about security patches.

12. Use Row-Level Security

ClickHouse® supports row-level security through row policies, allowing you to restrict which rows a user can see.

Create a row policy:

-- User can only see orders from their own country
CREATE ROW POLICY india_policy ON default.orders
    FOR SELECT
    USING country = 'IN'
TO analytics_user;

Now when analytics_user queries the orders table, they will only see rows where country = 'IN':

-- This query by analytics_user will only return IN rows
SELECT * FROM default.orders;

Security Checklist

Final Thoughts

Securing a ClickHouse® deployment requires attention at multiple layers - user management, network access, encryption, auditing, and configuration. None of these steps alone is sufficient, but together they provide a robust security posture for production deployments.

Start with the critical items - set passwords, create dedicated users, restrict network access, and enable firewall rules. Then progressively add encryption, logging, quotas, and row-level security as your deployment matures.

A well-secured ClickHouse® deployment gives you the confidence to store and analyze sensitive data at scale without compromising on security.

References

• ClickHouse® Documentation - Access Control
• ClickHouse® Documentation - TLS Configuration
• ClickHouse® Documentation - Quotas
• ClickHouse® Documentation - Row Policies
• ClickHouse® Documentation - Users and Roles