deployment: SQL-only ClickHouse-hosted install for restricted and multi-node clusters (Altinity Cloud, operator) #692

Goal

Make ClickHouse-hosted SQL Browser installs reliable, verifiable, and documented for operators who can run SQL against ClickHouse but cannot touch the server filesystem — Altinity Cloud, clickhouse-operator deployments, and other managed/restricted environments — on single nodes, single-shard multi-replica clusters, and multi-shard clusters.

docs/ASSET-DISTRIBUTION.md stays the canonical guide; DEPLOYMENT.md, README, and .wiki/ align to it. Add a separate hosted-install page only if the runbook becomes unwieldy.

Background

• deploy/install.sh builds dist/sql.html from source (needs npm/node/curl), creates a temporary File('RawBLOB', '/var/lib/clickhouse/user_files/<name>') table (ON CLUSTER if --cluster), uploads through INSERT INTO FUNCTION clusterAllReplicas(...), then drops the table. It refuses >1 shard.
• Serving needs two independent things on every node: the bytes in user_files/ and an <http_handlers> config fragment. Shipping bytes through SQL does not enable /sql by itself.
• install.sh is never executed against a real ClickHouse in CI (only shellcheck), so its multi-node behavior is unproven.
• Each GitHub release already publishes a prebuilt sql.html asset.
• On Altinity Cloud / operator clusters individual replicas are usually not reachable from outside (load balancer / Service). Fan-out must happen server-side; "connect to each node and upload" is not a general answer.
• clickhouse-operator spec.configuration.files lands in a ConfigMap (1 MiB cap): fine for http_handlers.xml, too small for the ~2 MB artifact.

Delivery — three PRs, this issue closes with the last

PR 1 — confirmed corrections (no mechanism change)

• Remove stale artifact sizes (~65 KB ×2 in ASSET-DISTRIBUTION, ~500 KB → ~230 KB in DEPLOYMENT); explain raw vs HTTP-compressed size and how to measure.
• Correct prerequisites: source install needs npm, node 22+, curl, clickhouse-client; mention the prebuilt release asset.
• Fix applicability claims: "no separate web server" holds only for ClickHouse-native hosting; distributing http_handlers is not guaranteed (providers may forbid config.d) → fallback is the container image / Helm / any static host. Option C: ConfigMap 1 MiB cap. Option B: replication is within a shard only.
• Fix auth wording: Bearer id_token by default, access_token with --audience, JWT-as-Basic-password with ch_auth: "basic" (OSS + ch-jwt-verify); show --ch-auth basic / --audience in the install example.
• Fix http${SECURE:+s} (prints https when SECURE=0) and the missing HTTP port in the printed URL / redirect URI; regression test for both cases.
• Fix .wiki/Deployment-and-Security.md describing the standalone image as nginx (it is Caddy).

PR 2 — SQL-only upload, server-side fan-out, per-node verification

Target mechanism (each item is validated by the test harness below before it ships; record results):

• Single node / HTTP-only: upload with the file() table function — no temporary table, path relative to user_files_path, no hard-coded /var/lib/clickhouse:

  INSERT INTO FUNCTION file('sql.html', 'RawBLOB')
  SETTINGS engine_file_truncate_on_insert = 1 FORMAT RawBLOB

  works identically over clickhouse-client (stdin) and HTTP(S) (curl --data-binary @sql.html), so the native protocol is not required.
• Replicated / multi-shard fan-out — decided (see the experiment comment: writes through clusterAllReplicas/cluster, chained file() included, fail with Code 55 on 25.8/26.3/Antalya, and the current --cluster path is broken on every multi-replica cluster). Use a server-side pull via ON CLUSTER DDL; every host on every shard writes its own copy:

  CREATE TABLE default._asb_pull ON CLUSTER '<cluster>' ENGINE = File('RawBLOB', 'sql.html')
    AS SELECT * FROM url('<release-or-mirror-url>/sql.html', 'RawBLOB')
    SETTINGS engine_file_truncate_on_insert = 1;   -- mandatory: re-runs append otherwise
  DROP TABLE default._asb_pull ON CLUSTER '<cluster>' SYNC;

  Sources: (a) url() to the GitHub release asset / internal mirror / s3() (needs node egress, URL/S3 grant, remote_url_allow_hosts; verify redirect handling against real GitHub release URLs); (b) no egress: stage bytes on the initiator with file(), pull with remote('<initiator>', file(...)) (needs REMOTE grant + credentials). Re-running repairs replaced/added nodes. Do not inline the 2 MB payload into ON CLUSTER DDL (Keeper znode size limit); sql-config.json as a literal AS SELECT '<json>' is small enough — test it. Remove the clusterAllReplicas INSERT path.
• Per-node integrity check, run by the installer and documented as a standalone query:

  SELECT hostName(), length(c), hex(SHA256(c))
  FROM clusterAllReplicas('<cluster>', file('sql.html', 'RawBLOB', 'c String'))

  Fail on any missing node or hash mismatch versus the local artifact; never rely on a load-balanced /sql smoke test alone. Same for sql-config.json.
• Installer: --artifact <path|url> (prebuilt) vs source build; HTTP(S)-only mode; clean up temporary objects on failure without deleting good assets; clear errors for disabled file()/url(), missing grants, unreachable replicas.
• Test harness (docker-compose: Keeper + 2 shards × 2 replicas; OSS ClickHouse supported versions + one Altinity/Antalya build) exercising fresh install, re-install/upgrade, node wipe + repair, partial failure, least-privilege user; run in CI (path-filtered) like docker-smoke.

PR 3 — restricted-environment runbook (written from PR 2's tested results)

• Preflight decision table: can you push config.d (ACM cluster settings: yes; operator: spec.configuration.files for http_handlers.xml)? can SQL write user_files (file() enabled, grants)? node egress to the artifact URL? native vs HTTP-only? single node / replicas / shards? → which procedure, or "ClickHouse-native hosting impossible → container image / Helm / static host". ClickHouse Cloud: File engine unavailable → standalone runtime.
• Tested least-privilege grants per procedure, with version-specific privilege names (e.g. FILE/URL sources, newer READ/WRITE ON source grants), CREATE/DROP, CLUSTER. No guessed grants.
• End-to-end binary-safe examples: clickhouse-client, curl over HTTPS, Altinity Cloud (ACM cluster setting for handlers + SQL upload), clickhouse-operator (CHI files for handlers + SQL upload).
• Single node / single-shard multi-replica / multi-shard procedures; scale-out and node replacement (re-run the pull, then verify); retry guidance.
• Enabling and verifying handlers: OAuth redirect URI, /sql, /sql/config.json, sign-in, SELECT currentUser(). State that sql-config.json is public and must not hold private credentials.
• Option B (Replicated table + predefined_query_handler) kept as a described alternative only (needs an anonymous read grant; replicates within a shard).

Definition of done

• Fresh install, upgrade, and node-replacement repair proven byte-exact on every node of a 2×2 cluster, on at least one OSS version and one Altinity build (versions recorded in the docs).
• Multi-shard: a tested procedure or an explicit, installer-enforced "unsupported" result — never a silent partial deployment.
• Least-privilege grants tested, or provider restrictions listed explicitly; no blanket claim that hosted providers allow SQL file writes.
• Regression tests for --secure output, upload failure cleanup/retry, and the fan-out/verification logic.
• No stale statements left in docs/README/wiki.

Out of scope

A new replicated-table serving backend, OAuth feature changes. Describe such alternatives; don't build them.

References

deploy/install.sh, deploy/http_handlers.xml, docs/ASSET-DISTRIBUTION.md, docs/DEPLOYMENT.md, docs/DEVELOPMENT.md, docs/CLICKHOUSE-OSS-OAUTH.md; ClickHouse docs: File engine, file(), url(), cluster/clusterAllReplicas.