Analytics PipelineClickHouse & Materialized Views
Guide ClickHouse Core Architecture & Analytics Fundamentals

Configuring ClickHouse Network Security Groups

When a ClickHouse cluster moves from a single-node proof of concept to a replicated production deployment, the network layer becomes the first thing that breaks: INSERT batches time out mid-stream, Distributed table queries return partial results, and ReplicatedMergeTree fetch queues stall because a cloud Network Security Group (NSG) silently drops interserver traffic between availability zones. The failure is invisible in the firewall logs and looks like an engine bug until you map every listener the server opens to an explicit NSG rule. This page walks through provisioning the security group for a production cluster on AWS, Azure, or GCP, aligning it with ClickHouse’s native listener bindings, and verifying that every path — client, interserver, and coordination — is reachable. It builds on the perimeter model described in Security & Access Control Boundaries and the distributed execution mechanics covered in ClickHouse Core Architecture & Analytics Fundamentals.

Prerequisites

Port Topology and Listener Map

An NSG is a stateful packet filter: return traffic on an established connection is admitted automatically, so you allowlist by originating direction. ClickHouse opens several distinct listeners, and each maps to a service boundary that a specific caller depends on. The rules below are the minimum inbound set for a replicated cluster inside a VPC or VNet.

  • 8123/tcp — HTTP/HTTPS interface for the REST API, JDBC/ODBC bridges, BI tools, and the embedded web UI.
  • 9000/tcp — native TCP binary protocol for clickhouse-client, Python ingestion drivers, and high-throughput batch loads.
  • 9009/tcp — interserver HTTP port for Distributed query execution, shard-to-shard exchange, and part fetches.
  • 9010/tcp — interserver HTTPS / replication coordination for ReplicatedMergeTree background merges and part transfer when TLS is enabled.
  • 9181/tcp — ClickHouse Keeper client port for distributed DDL, leader election, and partition metadata sync (2181/tcp if you run ZooKeeper).

Scope every inbound rule to a source CIDR: client ports (8123, 9000) open only to application and ETL subnets; interserver and coordination ports (9009, 9010, 9181) open only to the deployment’s own security group referenced as the source. That self-referencing source is what keeps replication traffic private while still allowing every node to reach every peer.

NSG port topology — client ports scoped to caller CIDRs, interserver and coordination ports scoped to the group itself BI tools and ETL runners on the application and ETL subnets cross the security-group boundary as scoped ingress: BI tools on 8123 HTTP, ETL runners on 9000 native TCP. Inside the ClickHouse security group, whose inbound source is a self-reference to the group, clickhouse-node-01 and clickhouse-node-02 exchange interserver traffic on 9009 and replication on 9010, and each node reaches ClickHouse Keeper on 9181 for distributed DDL and leader election. Client ingress is scoped to caller CIDRs; interserver and coordination traffic stays inside the self-referencing group. Every listener maps to one rule — client ports scoped to caller CIDRs, interserver ports scoped to the group itself. CLIENTS application + ETL subnets BI tools JDBC · ODBC · web UI ETL runners batch ingestion CLUSTER SECURITY GROUP inbound source = this group (self-referencing) clickhouse-node-01 8123 · 9000 · 9009 · 9010 clickhouse-node-02 8123 · 9000 · 9009 · 9010 ClickHouse Keeper DDL · leader election 8123 HTTP 9000 native 9009 interserver 9010 replication 9181 9181 Solid arrows cross the boundary as scoped ingress; interserver and coordination traffic stays inside the self-referencing group.

Step-by-Step: Provisioning the Security Group

1. Create the group and capture its ID

bash
aws ec2 create-security-group \
  --group-name clickhouse-analytics-prod \
  --description "ClickHouse analytics cluster boundary" \
  --vpc-id vpc-0a1b2c3d4e5f

Expected output — record the returned identifier for the self-referencing interserver rules:

json
{ "GroupId": "sg-0abc123def4567890" }

2. Admit client traffic from application and ETL subnets only

bash
aws ec2 authorize-security-group-ingress --group-id sg-0abc123def4567890 \
  --ip-permissions \
    'IpProtocol=tcp,FromPort=8123,ToPort=8123,IpRanges=[{CidrIp=10.20.0.0/20,Description="app subnet HTTP"}]' \
    'IpProtocol=tcp,FromPort=9000,ToPort=9000,IpRanges=[{CidrIp=10.20.16.0/20,Description="etl runners native"}]'

A successful call returns "Return": true. Restricting the source CIDR here is the single most effective control on this page — it removes the internet-facing attack surface without touching a single ClickHouse setting.

3. Admit interserver and coordination traffic from the group to itself

bash
aws ec2 authorize-security-group-ingress --group-id sg-0abc123def4567890 \
  --ip-permissions \
    'IpProtocol=tcp,FromPort=9009,ToPort=9010,UserIdGroupPairs=[{GroupId=sg-0abc123def4567890,Description="interserver replication"}]' \
    'IpProtocol=tcp,FromPort=9181,ToPort=9181,UserIdGroupPairs=[{GroupId=sg-0abc123def4567890,Description="keeper coordination"}]'

Referencing sg-0abc123def4567890 as its own source means only members of this security group — the ClickHouse nodes — can reach ports 9009, 9010, and 9181. This is what isolates part fetching and Keeper coordination from every other workload in the VPC.

4. Align the ClickHouse listeners with the NSG

An NSG rule is only half the path. ClickHouse binds its own listeners at the application layer, and a mismatch produces Connection refused errors that never appear in firewall logs. Declare the bindings explicitly in /etc/clickhouse-server/config.xml:

xml
<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <tcp_port>9000</tcp_port>
    <http_port>8123</http_port>
    <interserver_http_port>9009</interserver_http_port>
    <interserver_http_host>clickhouse-node-01.internal</interserver_http_host>

    <remote_servers>
        <analytics_cluster>
            <shard>
                <replica>
                    <host>clickhouse-node-01.internal</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>clickhouse-node-02.internal</host>
                    <port>9000</port>
                </replica>
            </shard>
        </analytics_cluster>
    </remote_servers>
</clickhouse>

The interserver_http_host value must resolve to a routable internal name that peers can reach over 9009/9010. If it defaults to localhost, replication queues stall with DNS resolution failed even when every NSG rule is correct. Apply the change without a restart:

bash
clickhouse-client --query "SYSTEM RELOAD CONFIG"

An empty result (exit code 0) confirms the reload succeeded.

5. Probe every path from the caller that will use it

Run these from the machine that actually originates the traffic — the client checks from an ETL runner, the interserver checks from a peer node:

bash
# native + HTTP reachability from an ETL runner
nc -zv clickhouse-node-01.internal 9000
nc -zv clickhouse-node-01.internal 8123

# interserver reachability from a peer node
curl -s -o /dev/null -w "%{http_code}\n" http://clickhouse-node-02.internal:9009/ping

Expected output:

text
Connection to clickhouse-node-01.internal 9000 port [tcp/*] succeeded!
Connection to clickhouse-node-01.internal 8123 port [tcp/*] succeeded!
200

For a Python ingestion path, confirm the driver completes a full handshake rather than only a TCP open — a permissive NSG can still hide a TLS or auth mismatch:

python
import clickhouse_connect

client = clickhouse_connect.get_client(
    host="clickhouse-node-01.internal",
    port=8123,
    username="etl_ingest",
    password="•••",
    connect_timeout=5,
)
print(client.query("SELECT 1").result_rows)  # -> [(1,)]
Isolating an NSG failure — walk the path from the caller outward before blaming the engine A top-down decision flow starting from a pipeline stall (INSERT timeout, partial results). Decision one: is the port reachable with nc from the caller? No leads to an NSG or routing rule fault (source CIDR too tight, or the return path is filtered). Yes leads to decision two: does HTTP /ping return 200? No leads to a listener binding fault (interserver_http_host unresolved or wrong bind host). Yes leads to decision three: is system.replicas.is_readonly equal to 1? Yes means the interserver ports 9009 and 9010 are blocked between availability zones. No means it is not a network fault and the next step is query-level diagnosis. Isolate the network path from the caller outward before assuming an engine bug. pipeline stall INSERT timeout · partial results nc host:port reachable? TCP opens from the caller HTTP /ping = 200? listener is answering is_readonly = 1? from system.replicas NSG / routing rule source CIDR too tight, or the return path is filtered listener binding interserver_http_host unresolved or wrong bind interserver port blocked 9009 / 9010 filtered between availability zones not a network fault move to query-level diagnosis yes no yes no yes no

Verification

Once the paths probe clean, confirm ClickHouse’s own view of connectivity from inside the server. These queries turn “the firewall looks right” into “replication and coordination are actually healthy.”

sql
-- Every configured host should show errors_count = 0 and no recovery backoff.
SELECT cluster, host_name, host_address, port, errors_count, estimated_recovery_time
FROM system.clusters
WHERE cluster = 'analytics_cluster'
ORDER BY host_name;

-- A blocked interserver port surfaces here as is_readonly = 1 and a growing queue.
SELECT database, table, replica_name, is_readonly, queue_size, absolute_delay
FROM system.replicas
WHERE queue_size > 0 OR is_readonly = 1;

-- Network error codes accumulate here when a rule is too tight.
SELECT name, code, value
FROM system.errors
WHERE name IN ('NETWORK_ERROR', 'SOCKET_TIMEOUT', 'NO_REMOTE_SHARD_AVAILABLE')
  AND value > 0;

A healthy cluster returns rows only from the first query, all with errors_count = 0; the second and third return empty sets. Persistent is_readonly = 1 with a non-zero queue_size is the signature of a blocked 9009/9010 rule between zones, not a disk or merge problem.

Gotchas & Edge Cases

  • Stateful NSGs still need egress for health probes. Return traffic on an established connection is free, but cloud load-balancer health checks and cross-account PrivateLink calls originate outbound from the node. If egress is locked down, system.clusters shows a healthy config while the load balancer marks the target unhealthy. Add explicit outbound rules mirroring the interserver ports.

  • SYSTEM RELOAD CONFIG does not rebind listener ports. Changing tcp_port, http_port, or interserver_http_port in config.xml requires a full clickhouse-server restart — the reload only refreshes hot-reloadable sections like remote_servers and users. Editing a port and reloading leaves the server listening on the old port while your NSG allows the new one, producing Connection refused.

  • Keeper coordination failure masquerades as a DDL hang. If 9181 is blocked but the data ports are open, ingestion keeps working while ON CLUSTER DDL statements hang indefinitely waiting on the distributed DDL queue. Check system.distributed_ddl_queue for stuck entries before assuming the NSG is fine because queries still run.

  • Asymmetric routing bypasses the symmetric-looking rule. In multi-AZ transit-gateway topologies, the reply can take a different route than the request. The NSG allows the SYN but the return path is filtered elsewhere, so nc succeeds intermittently. Confirm route tables, not just security-group rules, when reachability flaps. This is the same class of failure that undermines DNS-based fallback routing for analytics when TTLs and route convergence drift apart.

Up one level: Security & Access Control Boundaries.