Databases AWS

chproxy for ClickHouse 1.30.0 on AWS User Guide

| Product: chproxy for ClickHouse 1.30.0 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying chproxy for ClickHouse 1.30.0 on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of the cloudimg Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace; you pick it as the software version when you subscribe and launch. The other tools in the listing are described on the listing's guide hub.

chproxy is an HTTP proxy and load balancer for ClickHouse. Clients send ordinary ClickHouse HTTP requests to chproxy, which authenticates them as proxy users, applies per-user limits (concurrent queries, queue length, execution time), checks the client's source network and then forwards each query to a ClickHouse cluster as a mapped ClickHouse user. It is a common front door for BI tools, dashboards and applications that share one ClickHouse deployment.

The image ships chproxy 1.30.0, compiled by cloudimg from the unmodified upstream source with Go 1.27.3 and patched golang.org/x modules (the upstream release binary was built with an older Go and carries Go security advisories that govulncheck reports as reachable; the rebuilt binary has none). A local ClickHouse 26.8 LTS server from ClickHouse's official repository is installed as the default backend, so the image answers real queries from its first boot. You can point chproxy at your own ClickHouse nodes instead (Step 8).

What is on the image:

  • chproxy 1.30.0 (chproxy.service) serving HTTPS only on port 8443 with a certificate generated on your instance's first boot
  • ClickHouse 26.8.31.2 (clickhouse-server.service) on 127.0.0.1:8123 (HTTP) and 127.0.0.1:9000 (native protocol) only
  • A proxy user with a random name and password, unique to your instance
  • Two ClickHouse users: default (the administrator, for local administration) and chproxy (the user chproxy connects as, with data rights only)
  • chproxy-selftest, an end-to-end check you can run at any time

Default user: ubuntu · Architecture: x86_64 · Root volume: 20 GiB gp3

Security posture

  • No default credential. The image contains no usable password. First boot generates the proxy user's name and password, the ClickHouse default and chproxy passwords, and the TLS key and certificate, and writes them to /root/chproxy-credentials.txt (readable by root only). ClickHouse and chproxy refuse to start until first boot has done this, and the ClickHouse default user never has an empty password.
  • HTTPS only. chproxy listens for HTTPS on 8443 with TLS 1.2 or later. There is no plain-HTTP listener, and the proxy user is configured to refuse plain HTTP anyway.
  • Per-instance certificate. First boot creates this instance's own private key and self-signed certificate for its public DNS name and addresses. No key ships in the image.
  • ClickHouse on localhost only. ClickHouse binds 127.0.0.1; its MySQL and PostgreSQL wire-protocol ports and its replication port are switched off. The only ports reachable from other machines are 22 (SSH) and 8443 (chproxy).
  • Source-network control on the real client address. chproxy accepts HTTPS only from loopback, the RFC 1918 private ranges and 100.64.0.0/10. The client address is the TCP connection's own address; chproxy is not configured to trust X-Forwarded-For or similar headers, so a client cannot claim to be somewhere else.
  • Least privilege upstream. chproxy connects to ClickHouse as the chproxy user, which can read and write data and manage tables but cannot manage users and cannot use the url(), s3(), remote() or file() table functions. A proxy user therefore cannot make ClickHouse fetch an arbitrary URL.
  • No metadata access for any service. ClickHouse, chproxy and the first-boot service are all blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254), so even the ClickHouse administrator's url() cannot read instance credentials. Amazon Time Sync still works.
  • IMDSv2 required. The AMI is registered with ImdsSupport v2.0, so instances launched from it refuse token-less (IMDSv1) metadata requests.
  • No secrets in logs or on command lines. Passwords are never passed on a command line, so they do not appear in /var/log/auth.log or the journal. Ready-made root-only client files let you query without typing a password.
  • Hardened service. chproxy runs as an unprivileged system user in a strict systemd sandbox (read-only system, no capabilities, restricted address families).
  • Per-instance identity. SSH host keys and the machine ID are generated fresh on every instance.

Prerequisites

  1. An AWS account and permission to launch EC2 instances and create security groups
  2. An EC2 key pair in the Region you launch in
  3. A VPC subnet, with a public IP on the instance if you connect over the internet

Recommended instance type: m5.large. chproxy itself needs very little CPU or memory; size the instance for the local ClickHouse workload, or keep it small if chproxy only fronts your own ClickHouse cluster.

Connecting to your instance

Version Operating system SSH user
chproxy for ClickHouse 1.30.0 on Ubuntu 24.04 Ubuntu 24.04 LTS ubuntu

Step 1: Launch the instance

Option A: AWS Marketplace console

  1. Open the cloudimg Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace and choose View purchase options, then Subscribe. One subscription covers every version in the listing.
  2. Once the subscription is active, choose Launch your software.
  3. Choose One-click launch from AWS Marketplace (or Launch from EC2 Console for the full launch wizard). In Version, choose chproxy for ClickHouse 1.30.0 on Ubuntu 24.04, then choose your Region.
  4. Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
  5. Security group: the listing's default group allows SSH (22) and chproxy (8443). Restrict both to your own networks: SSH to your administration address, 8443 to the VPC or VPN ranges your ClickHouse clients use. chproxy itself also refuses public client addresses until you allow them (Step 7).
  6. Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page, and the VPC, subnet, key pair and CIDRs with your own:

SG=$(aws ec2 create-security-group --group-name chproxy \
  --description "chproxy for ClickHouse" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 8443 --cidr <your-vpc-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=chproxy}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required.

Step 2: Connect and read your credentials

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. Show the connection details (the three password lines are left out here; read them with sudo cat /root/chproxy-credentials.txt and keep them in your secrets manager):

sudo grep -vE '^(CHPROXY_PASSWORD|CLICKHOUSE_ADMIN_PASSWORD|CLICKHOUSE_UPSTREAM_PASSWORD)=' /root/chproxy-credentials.txt

# chproxy for ClickHouse on Ubuntu 24.04 — Per-instance Credentials
# Generated: Tue Oct 20 06:50:04 UTC 2026
#
# Every value below was generated on THIS instance at first boot. None was baked into the image and
# no two cloudimg instances share any of them. Keep this file secret.
#
# chproxy client (HTTPS on port 8443, HTTP basic auth or user/password query parameters):
CHPROXY_USERNAME=ch_7a99c9d7
CHPROXY_URL=https://ec2-54-210-167-124.compute-1.amazonaws.com:8443/
#
# ClickHouse administrator (listens on 127.0.0.1 only; for local administration on this instance):
CLICKHOUSE_ADMIN_USER=default
#
# ClickHouse user chproxy connects as (data rights only; loopback only):
CLICKHOUSE_UPSTREAM_USER=chproxy
...

Your proxy user name and host name will differ. First boot also writes two root-only client files so you never type a password: /etc/cloudimg/chproxy-client.curl (the proxy user, for curl -K) and /etc/cloudimg/clickhouse-admin.xml (the ClickHouse administrator, for clickhouse-client --config-file).

Step 3: Confirm chproxy and ClickHouse are running

chproxy -version
clickhouse-server --version
systemctl is-active clickhouse-server.service chproxy.service
sudo ss -Hltn

chproxy ver. 1.30.0, rev. 3aea406897580f4dd50096ebab63d0a611e25f14, built at 2025-09-10T08:27:22Z
ClickHouse server version 26.8.31.2 (official build).
active
active
LISTEN 0      4096         0.0.0.0:22   0.0.0.0:*
LISTEN 0      4096         0.0.0.0:8443 0.0.0.0:*
LISTEN 0      4096       127.0.0.1:9000 0.0.0.0:*
LISTEN 0      4096   127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      4096      127.0.0.54:53   0.0.0.0:*
LISTEN 0      4096       127.0.0.1:8123 0.0.0.0:*
LISTEN 0      4096            [::]:22      [::]:*

chproxy listens on 8443 on every interface; ClickHouse listens on 127.0.0.1 only. Port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub.

Step 4: Run a query through chproxy

Send a query over HTTPS as the proxy user. The client file supplies the user name and password, and --cacert pins this instance's own certificate:

sudo curl -sS -K /etc/cloudimg/chproxy-client.curl --cacert /etc/chproxy/tls/chproxy.crt \
  --data-binary 'SELECT version(), currentUser() FORMAT PrettyCompactMonoBlock' https://127.0.0.1:8443/

   ┌─version()─┬─currentUser()─┐
1. │ 26.8.31.2 │ chproxy       │
   └───────────┴───────────────┘

The query ran on ClickHouse as the chproxy user, the ClickHouse identity your proxy user is mapped to. Any ClickHouse SQL works the same way:

sudo curl -sS -K /etc/cloudimg/chproxy-client.curl --cacert /etc/chproxy/tls/chproxy.crt \
  --data-binary 'SELECT number, number * number AS square FROM numbers(5) FORMAT PrettyCompactMonoBlock' https://127.0.0.1:8443/

   ┌─number─┬─square─┐
1. │      0 │      0 │
2. │      1 │      1 │
3. │      2 │      4 │
4. │      3 │      9 │
5. │      4 │     16 │
   └────────┴────────┘

ClickHouse drivers that speak the HTTP interface (clickhouse-connect, the JDBC driver, Grafana's ClickHouse data source and most BI tools) connect to chproxy with host <your-host>, port 8443, HTTPS on, and the proxy user name and password.

Step 5: Confirm requests without valid credentials are refused

A request with no credentials, or with a wrong password, gets HTTP 401 from chproxy and never reaches ClickHouse:

curl -q -sS --cacert /etc/chproxy/tls/chproxy.crt -w '\nHTTP %{http_code}\n' \
  --data-binary 'SELECT 1' https://127.0.0.1:8443/
curl -q -sS --cacert /etc/chproxy/tls/chproxy.crt -w '\nHTTP %{http_code}\n' \
  -u "$(sudo sed -n 's/^CHPROXY_USERNAME=//p' /root/chproxy-credentials.txt):not-the-password" \
  --data-binary 'SELECT 1' https://127.0.0.1:8443/

"127.0.0.1:50116": invalid username or password for user "default"; query: "SELECT 1"

HTTP 401
"127.0.0.1:50118": invalid username or password for user "ch_7a99c9d7"; query: "SELECT 1"

HTTP 401

The proxied user also cannot make ClickHouse fetch a URL:

sudo curl -sS -K /etc/cloudimg/chproxy-client.curl --cacert /etc/chproxy/tls/chproxy.crt -w '\nHTTP %{http_code}\n' \
  --data-binary "SELECT * FROM url('http://169.254.169.254/latest/meta-data/', 'RawBLOB')" https://127.0.0.1:8443/

Code: 497. DB::Exception: chproxy: Not enough privileges. To execute this query, it's necessary to have the grant READ ON URL. (ACCESS_DENIED) (version 26.8.31.2 (official build))

HTTP 403

Step 6: Check the certificate

openssl x509 -in /etc/chproxy/tls/chproxy.crt -noout -subject -ext subjectAltName -enddate

subject=CN = ec2-54-210-167-124.compute-1.amazonaws.com, O = cloudimg
X509v3 Subject Alternative Name: 
    DNS:ec2-54-210-167-124.compute-1.amazonaws.com, IP Address:54.210.167.124, DNS:ip-172-31-87-187.ec2.internal, IP Address:172.31.87.187, IP Address:127.0.0.1, DNS:localhost
notAfter=Jan 22 06:50:04 2029 GMT

The certificate covers this instance's public and private DNS names and addresses. Clients verify it by pinning the certificate file: copy /etc/chproxy/tls/chproxy.crt (it is public) to each client and pass it as the CA certificate (curl --cacert, ca_cert in clickhouse-connect, a trust store for JDBC).

To serve chproxy under your own DNS name, point the name at the instance and regenerate the certificate for it. The name is saved in /etc/cloudimg/chproxy-hostname and chproxy restarts with the new certificate:

sudo chproxy-set-hostname clickhouse-proxy.example.com

For production, replace /etc/chproxy/tls/chproxy.crt and chproxy.key with a certificate from your own CA (keep the key root:chproxy, mode 0640), then run sudo systemctl restart chproxy.

Step 7: Connect from other machines

The listing's security group opens 8443, and chproxy accepts clients from loopback, 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16 and 100.64.0.0/10, which covers clients inside your VPC. From another instance in the VPC (with the certificate copied over as chproxy.crt):

curl -sS --cacert chproxy.crt -u '<proxy-user>:<proxy-password>' \
  --data-binary 'SELECT 1' https://<private-ip>:8443/

A client outside those ranges gets https connections are not allowed from <address> (HTTP 403), even if it sends an X-Forwarded-For header. To admit another range (for example your office's public range), add it to allowed_networks under server.https in /etc/chproxy/config.yml and restart chproxy:

sudo nano /etc/chproxy/config.yml
sudo systemctl restart chproxy

Keep the security group rule for 8443 limited to the same ranges.

Step 8: Configure users and clusters

/etc/chproxy/config.yml (root and the chproxy group only) holds the proxy users and the clusters they are routed to. The image's configuration has one proxy user routed to the local ClickHouse as the chproxy user:

sudo grep -vE '^ *password:' /etc/chproxy/config.yml | grep -vE '^#'

log_debug: false
hack_me_please: false

server:
  https:
    listen_addr: ":8443"
    cert_file: "/etc/chproxy/tls/chproxy.crt"
    key_file: "/etc/chproxy/tls/chproxy.key"
    allowed_networks: ["127.0.0.0/8", "10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16", "100.64.0.0/10"]
  metrics:
    allowed_networks: ["127.0.0.0/8"]

users:
  - name: "ch_7a99c9d7"
    to_cluster: "local"
    to_user: "chproxy"
    deny_http: true
    max_concurrent_queries: 20
    max_execution_time: 2m
    max_queue_size: 50
    max_queue_time: 30s

clusters:
  - name: "local"
    scheme: "http"
    nodes: ["127.0.0.1:8123"]
    users:
      - name: "chproxy"
        max_concurrent_queries: 20
        max_execution_time: 2m

Typical changes (see the chproxy configuration reference):

  • Add proxy users for each application or team, each with its own password, limits and to_user.
  • Front your own ClickHouse cluster: add a cluster whose nodes (or replicas) are your ClickHouse servers and whose users are ClickHouse accounts on them, and route proxy users to it with to_cluster. chproxy balances queries across the nodes and stops sending to unhealthy ones.
  • Enable response caching with a caches section and a cache setting on the user.

Keep hack_me_please: false so chproxy's own security checks stay on, then apply the change with sudo systemctl restart chproxy and check systemctl status chproxy.

Step 9: Administer the local ClickHouse

ClickHouse listens on 127.0.0.1 only. Use the root-only administrator client file on the instance:

sudo clickhouse-client --config-file /etc/cloudimg/clickhouse-admin.xml \
  -q "SELECT name, auth_type FROM system.users ORDER BY name FORMAT PrettyCompactMonoBlock"

   ┌─name────┬─auth_type───────────┐
1. │ chproxy │ ['sha256_password'] │
2. │ default │ ['sha256_password'] │
   └─────────┴─────────────────────┘

Both users are defined in /etc/clickhouse-server/users.d/cloudimg-users.xml with SHA-256 password hashes and may connect from loopback only. ClickHouse is pinned to 26.8.31.2 by /etc/apt/preferences.d/clickhouse-pin so an operating-system upgrade never changes the database version under you; to move to a newer 26.8 LTS release, edit the version in that file and run sudo apt-get update && sudo apt-get install clickhouse-server clickhouse-client clickhouse-common-static.

Step 10: Verify the appliance end to end

chproxy-selftest checks the services, the ClickHouse and chproxy credentials (and that wrong ones are refused), the certificate, the exact listening ports and the metadata block:

sudo chproxy-selftest

ok   clickhouse-server and chproxy active; first boot complete
ok   ClickHouse: per-VM passwords accepted; no-password and wrong-password logins refused (authentication error)
ok   chproxy HTTPS: anonymous and wrong-password refused (401); per-VM user proxied to ClickHouse 26.8.31.2 as 'chproxy'
ok   url() table function is not granted to the proxied user (ClickHouse access denied)
ok   certificate verifies for the configured name ec2-54-210-167-124.compute-1.amazonaws.com; plain HTTP is not served
ok   per-VM certificate for ec2-54-210-167-124.compute-1.amazonaws.com (key 0640 root:chproxy); credentials file 0600 root
ok   off-box listeners exactly 22 and 8443; ClickHouse 8123/9000 on 127.0.0.1 only
ok   instance metadata refused inside the clickhouse-server and chproxy cgroups (loopback reachable; 200 from this shell)
SELFTEST OK - per-VM credentials, HTTPS only, exact ports, metadata blocked.

The metadata block is a systemd setting on each service, which you can inspect:

systemctl show -p IPAddressDeny chproxy.service clickhouse-server.service chproxy-firstboot.service

IPAddressDeny=169.254.169.254/32 fd00:ec2::254/128 168.63.129.16/32

IPAddressDeny=169.254.169.254/32 fd00:ec2::254/128 168.63.129.16/32

IPAddressDeny=169.254.169.254/32 fd00:ec2::254/128 168.63.129.16/32

Build provenance and licences

chproxy is MIT licensed and ClickHouse is Apache-2.0. The chproxy licence and a build record (source archive and its checksum, Go toolchain, the dependency changes, the binary's checksum and the govulncheck result) are in /usr/share/doc/chproxy/:

cat /usr/share/doc/chproxy/BUILD-PROVENANCE.txt

Support

cloudimg provides 24/7 support for this image by email at support@cloudimg.co.uk. Include your instance ID, the Region and the output of sudo chproxy-selftest.