Prepare a Private ClickHouse Host

Host bootstrap only

Standard OTLP and ClickHouse-backed Trace observations are part of the current release. This procedure prepares and capacity-checks one database host; by itself it does not apply the Mission Control telemetry schema, start the Collector, or configure the Console. Complete those steps through Operate the telemetry plane.

Purpose

Use scripts/provision_clickhouse_vps.sh to prepare one small, private-network ClickHouse node. The script installs the repository-pinned ClickHouse 26.3.21.7 LTS packages, applies conservative memory and concurrency limits, creates separate service roles, enables the systemd service, and validates access boundaries with a temporary canary table.

The bootstrap does not apply the Mission Control telemetry schema, start an OpenTelemetry Collector, change SSH, or manage host/provider firewalls. The canary table is removed after verification.

Prerequisites

• An Ubuntu or Debian amd64 host with at least 3 GiB RAM and 10 GiB free disk.
• A private RFC 1918 IPv4 address assigned to that host.
• Python 3, OpenSSL, and root access. The script installs other package prerequisites.
• Outbound HTTPS and DNS access for the first package installation.
• Provider-managed firewall rules that keep ClickHouse ports off the public network.
• A reviewed backup or a disposable new host. The script creates /swapfile when no swap is active and writes ClickHouse, APT, sysctl, and service configuration.

Examples below use the fictional host clickhouse.example.test, private address 10.23.0.12, and private client network 10.23.0.0/16.

Preview the configuration

Run from the Mission Control repository root, shown here as /srv/mission-control:

cd /srv/mission-control
scripts/provision_clickhouse_vps.sh \
  --render-config /tmp/mission-control-clickhouse-preview \
  --private-ip 10.23.0.12 \
  --allowed-cidr 10.23.0.0/16

Expected result: the command writes non-secret server and default-user XML below the preview directory. It rejects public listener addresses and public client CIDRs. Preview mode does not require root and does not change the host.

Provision the host

1. From /srv/mission-control, copy the script to the target:

   scp scripts/provision_clickhouse_vps.sh \
     root@clickhouse.example.test:/root/provision_clickhouse_vps.sh
   

2. Configure the provider firewall. Permit SSH only from the operator network and permit TCP 8123 and 9000 only from intended private-network clients. Do not expose 9009; the bootstrap disables the unused interserver listener.

3. Run the bootstrap:

   ssh root@clickhouse.example.test \
     '/root/provision_clickhouse_vps.sh \
       --private-ip 10.23.0.12 \
       --allowed-cidr 10.23.0.0/16'
   

4. Keep the generated /etc/mission-control/clickhouse.env file root-owned with mode 0600. Move required application credentials into the operator's secret-delivery system; do not copy this file into the repository, logs, general backups, or shell history.

The first run:

• creates a 2 GiB /swapfile only when the host has no active swap and sets vm.swappiness=10;
• verifies the official package-signing key and installs the exact pinned packages;
• places the packages on APT hold so an unattended update cannot change the tested version;
• listens on loopback and the selected private address only;
• caps ClickHouse server memory at 2.25 GiB, concurrent queries at four, and concurrent query threads at four;
• reduces mark and uncompressed caches;
• creates the empty mission_control and mission_control_restore databases plus an AES-256-CTR encrypted backup disk, but no telemetry tables or backup schedule;
• creates migration, gateway writer, repository read, deletion-admin, and backup/restore users with generated 256-bit passwords; and
• starts and enables clickhouse-server.

Re-running the command preserves credentials. It skips APT when all packages match the pin and skips a service restart when configuration is unchanged.

Role boundaries

Credential prefix in clickhouse.env	Intended use	Database rights
CLICKHOUSE_MIGRATION_	Versioned Mission Control schema migrations	Schema DDL, migration-ledger maintenance, guarded serving-table reads, and isolated restore-table cleanup; no serving-table insert or row mutation
CLICKHOUSE_COLLECTOR_	Collector exporter	Insert and show columns only within mission_control
CLICKHOUSE_REPOSITORY_	Mission Control telemetry repository	Select only within mission_control
CLICKHOUSE_ADMIN_	Scoped deletion	Select and row deletion mutations within mission_control; no backup
CLICKHOUSE_BACKUP_	Backup and isolated restore	Select/backup serving tables and create/insert/read isolated restore tables; no serving mutation
CLICKHOUSE_LOCAL_ADMIN_	Root-only local access administration	Default ClickHouse administrator, restricted to loopback

All service users accept connections only from loopback and --allowed-cidr. Changing a user password is effective for new connections and requires the matching consumer secret to be updated; it does not require a ClickHouse restart.

Expected result

The final bootstrap lines report that ClickHouse is healthy on the selected private HTTP and native endpoints and identify the credential file without printing its values. The package version is 26.3.21.7, systemd is active and enabled, and the temporary validation table has been removed. After schema migration, the serving tables are team_span_payloads, team_span_heads, and team_span_deletions.

The console and gateway keep bounded control/evidence state in their PostgreSQL database. To attach this ClickHouse host to the current telemetry plane, apply the released migrations, deliver each generated credential to only its named consumer, configure the Collector and console, and verify both capture modes by following Operate the telemetry plane.

Upgrade an earlier host bootstrap

Hosts provisioned by the earlier infrastructure-only script contain INGEST, READ, and combined ADMIN credentials. Copy the updated script to the host and rerun the normal provisioning command. The upgrade:

• preserves the existing migration, Collector/ingest, repository/read, and admin password values;
• rewrites /etc/mission-control/clickhouse.env with the current COLLECTOR, REPOSITORY, mutation-only ADMIN, and new backup-only identities;
• creates a new backup password and CLICKHOUSE_BACKUP_ENCRYPTION_KEY_HEX without printing either;
• removes the obsolete combined-role users after creating their least-privilege replacements; and
• restarts ClickHouse only when the encrypted-disk or systemd environment configuration changes.

Back up /etc/mission-control/clickhouse.env through the operator's secret mechanism before upgrading. After the rerun, redistribute the renamed usernames with their preserved passwords, deliver the new backup credential/key separately, and run --check.

Verification

Run the repeatable validation from the target host:

/root/provision_clickhouse_vps.sh \
  --check \
  --private-ip 10.23.0.12 \
  --allowed-cidr 10.23.0.0/16

Expected result:

[mission-control-clickhouse] ClickHouse 26.3.21.7 validation passed

The check verifies the exact version, systemd state, HTTP/native private connectivity, generated credentials, create/insert/read/delete/backup role separation, migration-role read access to final team_span_payloads, team_span_heads, and team_span_deletions, denial of serving-table insert and row mutation to that migration role, denial of backup permission to the deletion administrator, denial of serving mutation to the backup principal, denial of database-drop permission to the migration role, listener addresses, and canary cleanup.

Inspect host state without printing credentials:

systemctl is-active clickhouse-server
systemctl is-enabled clickhouse-server
ss -lnt | awk '$4 ~ /:(8123|9000|9009)$/ {print $4}'
stat -c '%a %U:%G' /etc/mission-control/clickhouse.env
free -h
df -h /var/lib/clickhouse

Expected results are active, enabled, listeners only on loopback and 10.23.0.12 for 8123 and 9000, no 9009 listener, credential mode 600 root:root, active swap, and adequate free memory and disk.

From a host outside the private network, TCP connection attempts to the public address on 8123, 9000, and 9009 must fail. From an allowed private client, use a securely delivered role credential and require SELECT version() to return 26.3.21.7.

Failure recovery

• If first installation cannot resolve or reach package hosts, repair host DNS/egress and rerun. An exact already-installed deployment does not require package-network access.
• If the script rejects /run, restore the operating system's standard 0755 root:root runtime-directory mode before retrying. Do not weaken the mode of credential or ClickHouse configuration directories.
• If ClickHouse does not start, inspect systemctl status clickhouse-server, journalctl -u clickhouse-server, and /var/log/clickhouse-server/clickhouse-server.err.log. Correct the reported configuration error and rerun; do not delete /var/lib/clickhouse.
• If startup reports a missing backup encryption key, restore the root-only /etc/mission-control/clickhouse-server.env and /etc/systemd/system/clickhouse-server.service.d/mission-control.conf generated by the script, run systemctl daemon-reload, and rerun provisioning. Losing the key makes existing encrypted backup media unreadable.
• If a public ClickHouse port is reachable, remove it from the provider firewall immediately and verify ss shows no public listener. The script intentionally does not repair firewall policy.
• If queries reach the 2.25 GiB server cap, swap is used, or latency fails the workload target, reduce query/ingestion concurrency or move to a larger host. Do not raise the cap without a measured load test and operating headroom.
• If a bootstrap run stops after creating roles, rerun it. Access provisioning and validation are idempotent, and generated credentials are preserved.

Related links

• Source deployment
• Current release
• Health and logging
• Backup and restore
• Operate the telemetry plane
• Planned capabilities

Current Team storage grants

The retained database account/role name collector is the gateway writer. The Collector process receives no database credential. The current role matrix is:

Role	Team-table privileges
Gateway writer (collector)	SELECT/INSERT payloads and heads; SELECT deletions; cannot insert tombstones
Repository reader	SELECT all three Team tables
Telemetry administrator	SELECT all three; INSERT deletions; ALTER DELETE payloads; no backup privilege
Backup	SELECT/BACKUP all three; no serving mutation
Migration	Schema/journal operations and SELECT current tables; no serving writes

The tables are team_span_payloads, team_span_heads and team_span_deletions. Frozen migration history may need narrowly retained old-table migration grants to execute the complete chain; those are not runtime serving access. The validator checks required and forbidden privileges, including writer denial of tombstone insertion and administrator permission to insert tombstones. Use telemetry operations for the complete serving/backup contract.