ClickHouse - Hardened Open-Source Columnar OLAP Database

What is ClickHouse

ClickHouse is an open-source column-oriented online analytical processing (OLAP) database management system written in C++ and distributed as a single compiled native binary. It is designed for low-latency analytical queries over very large tables: aggregations, time-series rollups, ad-hoc SQL on billions of rows. Features include the MergeTree storage engine with automatic part merges and partitioning, distributed and replicated tables for horizontal scale-out, materialized views, dictionaries, projections, an HTTP REST API on port 8123, a fast native binary protocol on port 9000, and a built-in web SQL console at /play. SQL is extended with array, tuple, nested, JSON, and IPv4/IPv6 types. Standard ODBC and JDBC drivers and client libraries for most popular languages are available. Apache-2.0 license, no vendor lock-in.

Why self-host ClickHouse

Self-hosting keeps every row of analytical data inside your own tenant. Hot-data analytics workloads typically scan billions of rows: sending them to a managed warehouse means ingest egress, per-query fees, and exposing raw events to a third party. Running ClickHouse on a VM you control suits teams with data residency requirements, organisations operating under HIPAA, GDPR, or ISO 27001, and any product where storing customer events outside your own perimeter is not an option.

What this VM image adds

Security hardening:

• Strong random 32-character password generated per instance at first boot for the ClickHouse default user, stored only as SHA-256 hex in a users.d drop-in - never written in plain text inside the image
• IPv6 disabled by the CIS profile - listen_host = 0.0.0.0 with listen_try = 1 applied as a config.d drop-in, so the server starts cleanly without dual-stack assumptions
• Systemd UMask 0027 on clickhouse-server - data files written to /var/lib/clickhouse are not world-readable
• UFW firewall - TCP 8123 and 9000 open externally for buyer use, TCP 22 for SSH; all other inbound traffic dropped; Azure IMDS and WireServer egress pre-configured
• fail2ban - SSH brute-force protection
• AppArmor - mandatory access control
• CVE scan - every image is scanned with Trivy before release

OS hardening (CIS Level 1):

• CIS Ubuntu 24.04 LTS Level 1 Benchmark via ansible-lockdown
• auditd for system call auditing of critical paths
• SSH hardening - PasswordAuthentication disabled, key-only access, PermitRootLogin no, LoginGraceTime 60
• Kernel hardening - SYN cookies, ASLR, rp_filter, kexec disabled, IPv6 off
• /tmp as tmpfs with nosuid, nodev, noexec

Compliance artifacts (inside the VM):

• SBOM - CycloneDX 1.6 at /etc/lynxroute/sbom.json with ClickHouse pinned by version, PURL, Apache-2.0 license, supplier, and SHA-256 hash of the binary
• CIS Conformance Report at /etc/lynxroute/cis-report.html (OpenSCAP, Azure tailoring profile, 0 FAIL rules)
• Tailored CIS profile at /usr/share/doc/lynxroute/CIS_TAILORED_PROFILE.md
• Operator credentials file at /root/clickhouse-credentials.txt (mode 0600) with the default-user password, the HTTP API URL, the native TCP endpoint, and the /play web SQL console URL

Quick Start

1. Deploy VM from Azure Marketplace (Standard_D2s_v3 minimum, Standard_D4s_v3 recommended for production workloads)
2. Open NSG: TCP 8123 and TCP 9000 from your client networks, TCP 22 from your management IPs only
3. SSH: ssh -i key.pem azureuser@<PUBLIC_IP>, then sudo cat /root/clickhouse-credentials.txt for the default-user password
4. HTTP query: curl -u "default:<password>" "http://<PUBLIC_IP>:8123/?query=SELECT+version()"
5. Open the web SQL console at http://<PUBLIC_IP>:8123/play and log in as default with the password from the credentials file
6. For production, attach a managed Premium SSD disk and mount it at /var/lib/clickhouse for higher IOPS; terminate TLS in a reverse proxy in front of the HTTP endpoint