In ClickHouse-native hosting, the Altinity® SQL Browser is served from
ClickHouse itself — there is no separate web server. (The containerized Caddy
runtime in DEVELOPMENT.md is the alternative that does use
one; this document is about the native mode.) That makes "how do the asset bytes reach every node?" a
real design question on a multi-node cluster, because ClickHouse does not
replicate the user_files/ directory: it is a node-local folder.
This doc explains the trade-offs and what deploy/install.sh ships. The operator procedure for restricted
environments (preflight, tested grants, Altinity Cloud / clickhouse-operator examples, scale-out) is the
runbook HOSTED-INSTALL.md; this page stays the mechanism and design reference.
(A docs/sql.html mirror is also published to GitHub Pages on every tagged
release, for an unauthenticated try-it-now demo — see release.yml — but
that's a separate distribution channel from the cluster-serving problem
below, not a replacement for it: a real deployment still needs the bytes on
every ClickHouse node.)
Two independent things must be present on every node that can answer a request:
-
The
http_handlersconfig (a small XML fragment,deploy/http_handlers.xml). This already rides your existing config-management channel — clickhouse-operator, ACM cluster settings, a mounted ConfigMap, Ansible, etc. — which by construction reaches every node, including ones added later. Caveat: some managed providers forbid changes toconfig.d/orhttp_handlers. If you cannot installhttp_handlers, ClickHouse-native hosting is impossible; use the container image, the Helm chart, or any external static host instead (see DEVELOPMENT.md). -
The asset bytes (
sql.htmlandsql-config.json).sql.htmlis a single self-contained file of about 2 MB raw (measure your build:ls -l dist/sql.html); the config is tiny. HTTP compression (gzip/br) shrinks what goes over the wire to roughly a quarter to a third, but the stored file, and anything that caps stored size, sees the raw bytes. This is the part affected byuser_files/being node-local.
There is no single "correct" mechanism — each is reasonable, with different
trade-offs. The key tension is anonymous serving vs. native replication: the
/sql bootstrap page is fetched by the browser before OAuth, so it must be
served without credentials, which favours a static handler; native replication
of the bytes favours a table, whose read path needs a user.
install.sh writes the bytes into every node's local user_files/ using SQL
only (no ssh, no per-node upload), and the handler serves them with a
type=static file:// rule. With --cluster the fan-out is a server-side
CREATE TABLE ... ON CLUSTER ... ENGINE = File('RawBLOB', ...) AS SELECT ...;
see How the installer works.
- Pro: the page is served statically, with no authentication — exactly what an unauthenticated bootstrap page needs. Pure SQL; verified per node, byte-exact.
- Con:
user_files/is node-local, so a replica added or replaced later starts empty — re-run the install after a scale-out (it is idempotent and repairs any node). Every node must be up while it runs.
Keep the bytes in a ReplicatedMergeTree table and serve them with a
predefined_query_handler running e.g.
SELECT argMax(content, updated) FROM asb_assets WHERE name = 'sql.html' FORMAT RawBLOB.
- Pro: ClickHouse replication distributes the bytes to every replica
of the table's shard/replication group automatically, including replicas
added later. A
ReplicatedMergeTreedoes not replicate to other shards, so a multi-shard cluster needs the table (or its data) on each shard. Pure SQL, portable. - Con: a
predefined_query_handlerruns the query as a ClickHouse user, so the unauthenticated/sqlGET needs a small read-only grant on the assets table for whatever anonymous resolves to. TheReplicated*engine's ZooKeeper/Keeper path is cluster-specific (macros).
Place sql.html where your config channel puts it on every node — e.g.
clickhouse-operator spec.configuration.files, or a mounted ConfigMap — and
serve it with the same file:// static handler.
- Pro: rides the exact channel that already distributes
http_handlers.xmlto all nodes, so new replicas are covered for free; still served statically (no auth, no read-grant). - Con: the bytes live in config. The artifact is about 2 MB (measure your
build), which is too large for several channels: clickhouse-operator
spec.configuration.fileslands in a Kubernetes ConfigMap, which is capped at 1 MiB, and control planes that push settings inline (for example, ACM's setting-push is reported to have a small, ~8 KB URI limit; unverified) cannot carry it either. In practice it has to be a file delivered by some other mechanism (image layer, volume, init container), not an inline config value.
deploy/install.sh implements option A, for one node or a whole cluster.
It is the simplest mechanism and serves the bootstrap page anonymously. The
trade-off to be aware of:
After scaling a cluster out (or replacing a node), re-run the install so the new replica's
user_files/gets the assets.
For deployments where that re-push is undesirable — autoscaling fleets, frequent node replacement — option B (replicated table) or option C (config-distributed file) removes it.
CLICKHOUSE_PASSWORD=… ./deploy/install.sh \
--ch-host <any-node> --ch-user <deploy-user> --client-id <oauth-client-id> \
--cluster <cluster-name> \
[--artifact sql.html] [--pull-url <url>] [--transport native|http] [--http-port N] \
[--ddl-timeout 180] [--verify-only] [--secure]| Flag | Meaning |
|---|---|
--cluster C |
fan out to every node of cluster C (as named in system.clusters on the node you connect to). Without it only the connected node is updated (behind a load balancer that is one random node). |
--artifact PATH |
install this prebuilt sql.html (e.g. the release asset); skips the build, so no npm/node is needed. Required when there is no build tree (the release bundle). A zero-byte file is rejected before any write. |
--pull-url URL |
cluster mode: every node downloads the SPA from URL itself (needs egress + the URL grant). The bytes at URL must equal the artifact (--artifact, or the build): the installer verifies length and SHA-256 against the local artifact, so anything else fails phase-1 verification and the live files stay untouched. Mutually exclusive with --verify-only. Default is stage mode: the installer uploads once to the connected node and the nodes copy it over the interserver connection (no egress). |
--transport native|http |
native (default) drives clickhouse-client; http uses curl against the HTTP(S) port (--http-port, default 8123 / 8443 with --secure). The password never appears in argv: with http it is sent from a 0600 header file, with native it is handed to clickhouse-client alone through its environment (never to npm, node or the OIDC curl). |
--http-port N |
HTTP(S) port for --transport http (default 8123, or 8443 with --secure). |
--ddl-timeout S |
distributed_ddl_task_timeout for the ON CLUSTER statements (default 180, must be >= 1: 0 makes ClickHouse run DDL asynchronously and the installer would verify too early). Also bounds a --pull-url download (max_execution_time, HTTP receive timeout). The cleanup at the end uses at most 30 s. distributed_ddl_output_mode=throw is pinned on every request, so a profile default of never_throw cannot hide a dead node. |
--verify-only |
change nothing; compare every node's live files to the artifact and exit non-zero on any mismatch. |
Credentials come from CLICKHOUSE_PASSWORD (or a prompt). Check that a
personal ~/.clickhouse-client/config.xml is not overriding the host, user or
password you pass — clickhouse-client reads it and it can silently change
which server or account the installer talks to.
One run at a time. Concurrent installs or uninstalls against the same cluster are
unsupported (there is no lock): they share the _asb_t_* temp tables and the
_asb_next_* files and can corrupt each other.
Proxies. The scripts call curl -q (so ~/.curlrc is ignored) but curl still honours
HTTP_PROXY/HTTPS_PROXY/NO_PROXY; the installer adds no --noproxy. With
--transport http over plain HTTP a configured proxy would see the credentials, so use
--secure or unset the proxy variables.
uninstall.sh takes the same connection flags and empties both files on
every node (0 bytes, verified) and also the _asb_next_* copies (they hold a full copy of
the SPA). SQL cannot delete a file, so /sql then answers
200 with an empty body; remove the http_handlers fragment from config.d/
and reload to really disable the route.
Two facts shape it, both proven on 25.8 OSS, 26.3 OSS and Antalya 25.8.33
(spike 1,
and the follow-up design comment on #692): a write through clusterAllReplicas
is rejected (Code 55), so fan-out has to be CREATE ... ON CLUSTER ... AS SELECT
into a File(RawBLOB) table (the file survives the DROP); and a failed
CREATE ... AS SELECT truncates its target file to 0 bytes. So the live file
is never the target of anything that can fail on a remote source:
- Preflight (no writes): the node count from
system.clusters, and aclusterAllReplicasprobe proving every node is reachable and cross-node reads authenticate. - Pre-clean:
DROP TABLE IF EXISTS default._asb_* ON CLUSTER ... SYNC(a leftover table on one node would otherwise raiseTABLE_ALREADY_EXISTSthere), then both_asb_next_*files are emptied on every node, so a stale next file from an earlier run can never satisfy the phase-1 verification. - Phase 1 — next files. Stage mode uploads the artifact to the connected node as
_asb_stage_<token>.htmland every node copies it;--pull-urlhas every nodeurl()-download it (following redirects). Result:_asb_next_sql.htmland_asb_next_sql-config.jsonon every node. A failure here leaves the live files untouched. - Verify phase 1: every node's next files match the expected length and SHA-256. The
check is strict: exactly one row per node (
count() = 1), as many distinct node identities (serverUUID(), unique per server instance and independent of ports, so several instances on one host stay distinct) assystem.clusterslists, each with the expected length and hash - a dropped, extra or duplicated-host row fails it. (uninstall.sh's 0-byte check alone also acceptscount() = 0.) - Phase 2 — promote. Each node copies its own verified
_asb_next_*over the livesql.html/sql-config.json(a local read, so it cannot fail for network or auth reasons). - Verify live: per-node table; success only when every node matches.
- Always (trap): the stage file is truncated and the temp tables dropped. Cleanup is bounded: its DDL timeout
is capped at
min(--ddl-timeout, 30)s and every client call also carries a client-side bound of that value + 10 s (10 s connect timeout). With--transport http(curl --max-time) that is a total bound. With the native transport the call is wrapped intimeout(coreutils) and the bound is total when that command exists; otherwise (e.g. stock macOS) onlyclickhouse-client's--send_timeout/--receive_timeoutapply, which are idle timeouts, so a node that keeps trickling bytes could exceed it. Either way, with INT/TERM ignored while it runs, a dead node cannot hold cleanup for more than that per statement (5 statements at most). The credential header file is removed even if the cleanup SQL fails; live files are never touched by cleanup.
_asb_next_* is intentionally kept as a full copy next to the live files
(about 2 MB per node): it is the node-local, already-verified source for the
promote step, so promotion is a local read that cannot fail on network or auth. A node that is down during an ON CLUSTER statement
makes the installer fail after --ddl-timeout; the DDL stays queued and replays
when the node returns, against whatever source exists then — so after a failure
bring the node back and simply re-run the installer (idempotent) rather than
waiting for the replay. Re-running also repairs a node whose user_files/ was
wiped or whose file was tampered with; --verify-only detects both.
-
Interserver authentication. Cross-node reads (
clusterAllReplicas) need the cluster to have a<secret>inremote_servers(the operator default) or per-replica<user>/<password>, and the deploy user must exist on every node. Without it the preflight fails withAUTHENTICATION_FAILED (516). The<secret>path is what CI tests; the per-replica-credentials path was verified in a spike (#692) but is not in CI. -
Grants (tested;
READ/WRITE ON FILEare covered byFILE):Mode Grants stage (default) FILE ON *.*,CLUSTER ON *.*,REMOTE ON *.*,CREATE TABLE, DROP TABLE ON default.*,SELECT ON system.clusters--pull-urlthe above plus URL ON *.*--verify-only(no install grants)FILE,REMOTE,CREATE TEMPORARY TABLE ON *.*,SELECT ON system.clusterssingle node (no --cluster)tested with the stage grants; CLUSTER/REMOTEare not exerciseduninstall.shsame as stage These sets are tested sufficient (every mode runs in CI as a user holding exactly that set; the
--verify-onlyset also in adeploy_verifyuser). Minimality is only partly proven: the negative tests dropREMOTEandURL(both are rejected before any live file is touched, with a hint) and check that a--verify-only-only user cannot install; the other grants are not individually removed.CREATE TEMPORARY TABLE ON *.*is a separate, verify-only requirement: revoking it from thedeploy_verifyuser makes--verify-onlyfail withACCESS_DENIED ... CREATE TEMPORARY TABLE ON *.*on both tested images (ClickHouse 26.3.18.32 and Antalya 25.8.33), while the install/uninstall user, which holdsCREATE TABLE ON default.*but no separate temporary-table grant, runs the sameclusterAllReplicas(view(...))verification successfully on both. So the stage,--pull-urland uninstall rows do not list it; add it only for a verification user that lacksCREATE TABLE. -
Egress (
--pull-urlonly): every node must reach the URL andremote_url_allow_hostsmust permit it; GitHub release URLs redirect and are followed (up to 10 hops).
Expected length and hash come from the artifact (wc -c, sha256sum); the query returns a
row per node even when the file is missing:
SELECT id, host, n, len, sha FROM clusterAllReplicas('<cluster>', view(
SELECT toString(serverUUID()) id, hostName() host, count() n, sum(length(c)) len, lower(hex(SHA256(any(c)))) sha
FROM file('sql.html', 'RawBLOB', 'c String')))
ORDER BY host, id SETTINGS engine_file_empty_if_not_exists = 1;
SELECT count() FROM system.clusters WHERE cluster = '<cluster>';Repeat with sql-config.json. Success means exactly as many rows as the second query's
count, each with the expected len/sha.
Every step is idempotent: on any failure fix the cause and re-run the same command. If the
installer was killed, its trap may not have run: re-running pre-cleans the temp tables, but
a stale _asb_stage_<token>.html on the connected node (a plain copy of the SPA, never
served) stays. A killed installer (kill -9, power loss) can leave one such full-size file per killed run on
the node it was connected to; SQL cannot glob-delete it, and the next run reuses nothing from it (it
uploads a new stage under a new token). To find it and empty it by hand, on that node:
SELECT DISTINCT _file, _size FROM file('_asb_stage_*', 'One'); -- lists every stage file with its size, 0-byte ones too; does not read contents
INSERT INTO FUNCTION file('_asb_stage_<token>.html', 'RawBLOB')
SETTINGS engine_file_truncate_on_insert = 1 SELECT '';(Removing the file itself needs shell access to user_files/.)
tests/deploy/ (npm run test:deploy, CI job deploy-integration) runs the real scripts
against Keeper + a 2-shard x 2-replica cluster with a <secret>, a password-protected deploy
user holding exactly the grants above, and the shipped http_handlers.xml; every scenario
checks the byte-exact SHA-256 of /sql and /sql/config.json served by every node. Covered:
fresh install, upgrade, HTTP transport, --pull-url through a 2-hop redirect, wiped-node
repair, --verify-only on a tampered node and as the minimal deploy_verify user, a node stopped
before the run (the preflight rejects it), a node killed mid-run right after the stage upload
(the installer fails; each surviving node then serves byte-exactly the previous or the new version,
never an empty or partial file; the queued DDL replay settles; a re-install converges every node on the new one), an unreachable pull URL, missing REMOTE / URL
grants, a single node, and uninstall. Images (exact tags): clickhouse/clickhouse-server:26.3.18.32 and
altinity/clickhouse-server:25.8.33.20001.altinityantalya, with clickhouse/clickhouse-keeper:25.8.33.6 (CI; locally clickhouse-keeper:25.8 works too).
Not covered: the real GitHub release URL through the installer (a single-node url() download of it was probed by hand, see HOSTED-INSTALL.md), TLS (--secure) against a live server, and the
per-replica-credentials interserver path. The script logic (strict row checks, cleanup, the
password's reach) is additionally unit-tested against a simulator.
For a restricted environment where you cannot run the script. Replace <cluster>; run for both
files, and verify as above before and after promoting. Pull mode shown (<url> serves
sql.html); <json> is the config JSON as a SQL string literal (\ becomes \\, ' becomes \').
-- 1 pre-clean (drop the temp tables, then EMPTY the next files so a stale one cannot pass verification)
DROP TABLE IF EXISTS default._asb_t_next_html ON CLUSTER '<cluster>' SYNC;
DROP TABLE IF EXISTS default._asb_t_next_cfg ON CLUSTER '<cluster>' SYNC;
DROP TABLE IF EXISTS default._asb_t_live_html ON CLUSTER '<cluster>' SYNC;
DROP TABLE IF EXISTS default._asb_t_live_cfg ON CLUSTER '<cluster>' SYNC;
CREATE TABLE default._asb_t_next_html ON CLUSTER '<cluster>'
ENGINE = File('RawBLOB', '_asb_next_sql.html')
AS SELECT '' AS c SETTINGS engine_file_truncate_on_insert = 1;
CREATE TABLE default._asb_t_next_cfg ON CLUSTER '<cluster>'
ENGINE = File('RawBLOB', '_asb_next_sql-config.json')
AS SELECT '' AS c SETTINGS engine_file_truncate_on_insert = 1;
DROP TABLE IF EXISTS default._asb_t_next_html ON CLUSTER '<cluster>' SYNC;
DROP TABLE IF EXISTS default._asb_t_next_cfg ON CLUSTER '<cluster>' SYNC;
-- 2 phase 1: next files (a failure here cannot hurt the live files)
CREATE TABLE default._asb_t_next_html ON CLUSTER '<cluster>'
ENGINE = File('RawBLOB', '_asb_next_sql.html')
AS SELECT c FROM url('<url>', 'RawBLOB', 'c String')
SETTINGS max_http_get_redirects = 10, engine_file_truncate_on_insert = 1,
max_execution_time = 180, http_connection_timeout = 10, http_receive_timeout = 180;
CREATE TABLE default._asb_t_next_cfg ON CLUSTER '<cluster>'
ENGINE = File('RawBLOB', '_asb_next_sql-config.json')
AS SELECT '<json>' AS c SETTINGS engine_file_truncate_on_insert = 1;
DROP TABLE IF EXISTS default._asb_t_next_html ON CLUSTER '<cluster>' SYNC;
DROP TABLE IF EXISTS default._asb_t_next_cfg ON CLUSTER '<cluster>' SYNC;
-- 3 verify _asb_next_sql.html and _asb_next_sql-config.json on every node (query above)
-- 4 phase 2: promote each node's OWN copy
CREATE TABLE default._asb_t_live_html ON CLUSTER '<cluster>'
ENGINE = File('RawBLOB', 'sql.html')
AS SELECT c FROM file('_asb_next_sql.html', 'RawBLOB', 'c String')
SETTINGS engine_file_truncate_on_insert = 1;
CREATE TABLE default._asb_t_live_cfg ON CLUSTER '<cluster>'
ENGINE = File('RawBLOB', 'sql-config.json')
AS SELECT c FROM file('_asb_next_sql-config.json', 'RawBLOB', 'c String')
SETTINGS engine_file_truncate_on_insert = 1;
DROP TABLE IF EXISTS default._asb_t_live_html ON CLUSTER '<cluster>' SYNC;
DROP TABLE IF EXISTS default._asb_t_live_cfg ON CLUSTER '<cluster>' SYNC;
-- 5 verify sql.html and sql-config.json on every nodeNever point the step-4 CREATE at a remote source: a failed CREATE ... AS SELECT
empties its target. Keep engine_file_truncate_on_insert = 1 (without it a re-run appends).