Security Best Practices for ClickHouse® Deployments
Introduction
As organizations increasingly rely on real-time analytics, the security of analytical databases becomes just as important as their performance. ClickHouse® powers business intelligence dashboards, monitoring systems, financial reporting, IoT analytics, and countless data-driven applications. These systems often contain sensitive customer information, financial records, operational metrics, and proprietary business data.
While ClickHouse® is optimized for speed and analytical performance, a default installation is not designed to be production-ready from a security perspective. Without proper configuration, a database server can become vulnerable to unauthorized access, data leaks, denial-of-service attacks, or accidental misuse.
This guide walks through practical security best practices that every production ClickHouse® deployment should implement.
Why Database Security Matters
A secure ClickHouse® deployment protects your organization from:
- Unauthorized database access
- Data breaches and information leaks
- Insider threats
- Accidental data modification
- Denial-of-service (DoS) attacks
- Compliance violations
- Operational downtime
Implementing security early significantly reduces operational risks and improves long-term reliability.
1. Disable the Default User
A fresh ClickHouse® installation creates a default user. In production, this account should never be used for applications.
Instead:
- Set a strong password
- Create dedicated users
- Remove unnecessary accounts
- Avoid shared credentials
Create a dedicated user:
CREATE USER analytics
IDENTIFIED BY 'StrongPassword123!';
Or configure the password inside users.xml:
<users>
<default>
<password>your_strong_password</password>
</default>
</users>
Best Practice: Never leave the default user without a password—even in development environments.
2. Use Role-Based Access Control (RBAC)
Applications should never connect using administrative credentials.
Instead, create dedicated users with only the permissions they actually require.
Example:
CREATE USER analytics_user
IDENTIFIED BY 'strong_password';
GRANT SELECT ON default.* TO analytics_user;
Avoid granting excessive permissions:
Good:
GRANT SELECT ON sales.orders TO analyst;
Avoid:
GRANT ALL ON *.* TO analyst;
Following the Principle of Least Privilege greatly reduces security risks.
3. Restrict Network Access
By default, ClickHouse® listens on all network interfaces.
In production:
- Restrict listening interfaces
- Allow only trusted IP addresses
- Never expose ClickHouse® directly to the public Internet
- Use private networks whenever possible
Limit the server to localhost:
<listen_host>127.0.0.1</listen_host>
Restrict user access by IP:
CREATE USER analytics_user
IDENTIFIED BY 'strong_password'
HOST IP '10.0.0.0/24';
Recommended practices include:
- VPNs
- Private VPCs
- Internal networks
- IP allowlists
4. Configure Firewall Rules
Firewall rules provide another layer of defense even if ClickHouse® is already configured securely.
Example:
sudo ufw allow from 10.0.0.5 to any port 8123
sudo ufw allow from 10.0.0.5 to any port 9000
sudo ufw deny 8123
sudo ufw deny 9000
Important ClickHouse® ports:
| Port | Purpose |
|---|---|
| 8123 | HTTP API |
| 9000 | Native TCP |
| 8443 | HTTPS |
| 9440 | Secure Native TCP |
| 9009 | Replication |
Only expose the ports your deployment actually requires.
5. Enable SSL/TLS Encryption
Without TLS, all traffic between clients and ClickHouse® travels unencrypted.
Generate a certificate:
openssl req -newkey rsa:2048 -nodes \
-keyout /etc/clickhouse-server/server.key \
-x509 -days 365 \
-out /etc/clickhouse-server/server.crt
Enable secure ports:
<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 securely:
curl https://localhost:8443/?query=SELECT+1 \
-u default:password \
--cacert /etc/clickhouse-server/server.crt
For production environments, always use certificates issued by a trusted Certificate Authority.
6. Set Resource Quotas
One poorly optimized query can consume all available resources.
Use quotas to protect your cluster.
Example:
CREATE QUOTA analytics_quota
FOR INTERVAL 1 HOUR
MAX queries = 1000,
MAX read_rows = 1000000000,
MAX result_rows = 10000000
TO analytics_user;
Resource quotas improve overall cluster stability and prevent abuse.
7. Monitor Resource Usage
Monitoring helps identify both performance issues and potential attacks.
Useful system tables:
SELECT *
FROM system.metrics;
Active queries:
SELECT *
FROM system.processes;
Watch for:
- High CPU usage
- Memory spikes
- Large disk consumption
- Excessive network traffic
- Long-running queries
- Unexpected workload increases
Regular monitoring allows administrators to detect issues before they become incidents.
8. Enable Query Logging and Auditing
Every production database should maintain an audit trail.
Enable query logging:
<query_log>
<database>system</database>
<table>query_log</table>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
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;
Logs help investigate:
- Login activity
- Failed queries
- Heavy queries
- Suspicious access
- User behavior
- Compliance audits
9. Disable Unused Interfaces
Every enabled service increases the attack surface.
Disable interfaces you don't need.
Example:
<!-- Disable MySQL Interface -->
<!-- <mysql_port>9004</mysql_port> -->
<!-- Disable PostgreSQL Interface -->
<!-- <postgresql_port>9005</postgresql_port> -->
<!-- Disable Interserver HTTP -->
<!-- <interserver_http_port>9009</interserver_http_port> -->
Keeping only essential services enabled improves security.
10. Secure Configuration Files
Configuration files often contain passwords, connection details, and sensitive settings.
Restrict permissions:
sudo chmod 640 /etc/clickhouse-server/users.xml
sudo chmod 640 /etc/clickhouse-server/config.xml
sudo chown clickhouse:clickhouse /etc/clickhouse-server/users.xml
sudo chown clickhouse:clickhouse /etc/clickhouse-server/config.xml
Only trusted administrators should have access.
11. Keep ClickHouse® Updated
Every new release includes:
- Security patches
- Bug fixes
- Performance improvements
- Stability enhancements
- New features
Always test upgrades in a staging environment before deploying to production.
Following official release notes helps ensure your deployment remains secure.
12. Use Row-Level Security
ClickHouse® supports Row Policies that restrict which records individual users can access.
Example:
CREATE ROW POLICY india_policy
ON default.orders
FOR SELECT
USING country='IN'
TO analytics_user;
Now:
SELECT *
FROM default.orders;
returns only rows where:
country = 'IN'
This is especially useful in multi-tenant environments and applications serving multiple customers.
Production Security Checklist
| Security Measure | Priority |
|---|---|
| Set password for default user | Critical |
| Create dedicated users | Critical |
| Restrict network access | Critical |
| Configure firewall rules | Critical |
| Enable SSL/TLS | High |
| Restrict user permissions | High |
| Configure quotas | High |
| Enable query logging | High |
| Disable unused interfaces | Medium |
| Secure configuration files | Medium |
| Keep ClickHouse® updated | Medium |
| Implement Row-Level Security | Medium |
Final Thoughts
Security is not a single feature—it is a collection of best practices that work together to protect your database.
A secure ClickHouse® deployment starts with strong authentication, least-privilege access, restricted network exposure, and encrypted communication. From there, adding auditing, monitoring, quotas, and row-level security creates multiple layers of defense against both accidental misuse and malicious attacks.
By implementing these practices from the beginning, you can confidently run ClickHouse® in production while protecting your organization's most valuable asset—its data.
Original article - https://www.quantrail-data.com/clickhouse-data-sampling-querying-billions-of-rows-fast
Top comments (0)