ACM extension: same-origin, cookie-authed SQL Browser opened from the ACM console

[BorisTyshkevich]

Direction changed (2026-07-21). Reworked from a standalone "sup build with a pasted ACM key" into an ACM extension: same-origin, cookie-authenticated, opened in a new browser tab. This dissolves the control-plane-key security model entirely (see "Out of scope / dissolved"). Original design + the review that prompted this pivot are in the comments.

Goal

Extend ACM's existing simple SQL editor (acm-ui → cluster-explore/query) with the full Altinity SQL Browser, for the support team. The SQL Browser is served same-origin under acm.altinity.cloud and opened in a new tab from a link in the ACM console (cluster/explore page). It authenticates via ACM's existing HttpOnly session cookie — no token, no login form, no pasted key.

Why this shape

• Same-origin ⇒ cookie auth "just works". A JS fetch('/api/…', {credentials:'same-origin'}) inherits ACM's session (verified live: GET /api/account returns 200 with no auth header). Nothing is stored in JS; XSS can't read an HttpOnly cookie.
• Authorization + audit are ACM's job (server-side, per the account's role/rights). Our app enforces nothing — it calls the API and surfaces 403s.
• Least coupling. No iframe, no web component, no Angular changes beyond a link. Our repo stays a standalone single-file SPA, delivered as a static asset into ACM's docroot.

Verified API contract

(See the probe-transcript comment — still valid.) POST /api/cluster/{id}/query accepts a JSON body, is cookie-authed, and returns a per-node array [{node,label,result,meta{queryId,error},summary}]; result is a string (the raw CH text for the requested FORMAT); errors are signalled by meta.error, never HTTP status. acm-ui's own cluster-explore/query already consumes this exact shape and already manages support-credential mint/refresh (/support/credentials, /support/refresh).

Deployment model (from the acm repo)

acm-ui is ng build'd and its dist/ is baked into the altinity/acm image docroot /var/www/html/, alongside the PHP /api (symlinked). We add our built sql.html to that same docroot. Two constraints found in the vhost:

1. FallbackResource /index.html (Angular SPA catch-all) → serve our app as a real file at a concrete path (e.g. /sql/index.html), not a virtual route, so Apache serves it directly.
2. Strict page CSP — ACM's page CSP has no 'unsafe-inline'/'unsafe-eval' for scripts; our single-inline-<script> bundle would be blocked. Fix with a scoped <Location /sql/> CSP block (precedent: /api/, /signup already have their own) — the /api/ CSP ('self' 'unsafe-inline' 'unsafe-eval' *.gstatic.com data:) is exactly what we need. (Alternative: a CSP-friendly build emitting external JS from 'self' with no inline/eval.)

Three-repo change set

Repo	Change	Issue
altinity-sql-browser	Cookie-auth ChCtx/transport variant; read cluster/node from URL; transport adapter; {name:Type} render (or consume server-forwarded params)	#353 #354 #355 #356
acm (backend/distrib)	Add our artifact to the docroot at build; <Location /sql/> CSP block	#357
acm-ui	A link on the cluster/explore page → /sql/?cluster=…&node=… (new tab)	#357

Connection lifecycle

1. New tab at /sql/?cluster=<id>&node=<n> (cluster/node from ACM console context — no URL-resolution guessing).
2. GET /api/account (cookie) → engineer email (CH user= attribution).
3. GET /api/cluster/{id}/support/credentials (cookie) → temp CH pass; keep alive via /support/refresh or re-mint.
4. POST /api/cluster/{id}/query (cookie, JSON body, explicit node).
5. Cancel → /query-kill / actionKillProcesses (best-effort; see [sup] Best-effort cancel via /query-kill; remove streaming; export + session-script limits #356).

Transport constraints (proven; unchanged by the auth reframe)

• Native param_* not forwarded → {name:Type} client-side render, or (preferred, given tight integration) an ACM-side ask to forward a structured param/settings map ([sup] Parameters: forward natively server-side, else strict {name:Type} renderer + parity test #355).
• Query-string settings dropped; must go in-SQL SETTINGS (verified effective) ([sup] ACM-mode transport adapter: cookie auth, JSON-body /query, per-format result unwrap, settings-into-SQL, fake-stream #353).
• No sessions → temp-table / cross-statement SET scripts unsupported (declared limitation).
• No streaming (buffered proxy) → live progress removed; export must be disabled or hard-limited; big results risk memory/edge-timeout.
• Node targeting must be explicit — omitting it load-balances to a nondeterministic replica (verified).

Out of scope / dissolved by this model

• Control-plane API key in JS; pasted key / #user sentinel; Chrome-password-store handling; key-in-localStorage/share-link/export leakage; scoped-role token issuance; per-cluster entitlement enforcement; audit/consent/approval flows. All handled by ACM server-side or made impossible by cookie-only auth. The security review ([sup] Security review (reduced): no-token-in-JS, transport escaping, CSP, token rotation #358) shrinks accordingly.

Sub-issues

#353 transport adapter · #354 cookie-auth session + URL context + temp-cred lifecycle · #355 {name:Type} render / ACM param-forward · #356 cancel + remove streaming + export/session limits · #357 same-origin deployment (acm/acm-ui) · #358 (reduced) security review.

[BorisTyshkevich]

Live probe transcript

Probed against the demo cluster otel (id=9572, ClickHouse 26.6.1.20000.altinityantalya) via https://acm.altinity.cloud/api, auth header X-Auth-Token: <ACM key>. Keys and minted temp passwords are omitted.

CORS preflight (browser can call it cross-origin)

OPTIONS /api/cluster/9572/query   Origin: https://sql.demo.altinity.cloud
→ 200
access-control-allow-origin: *
access-control-allow-headers: x-auth-token, … accept
access-control-allow-methods: POST

Support identity — GET /account

Returns the key's account; relevant field: email (→ used as CH user=). (Response also contains credential fields — not shown.)

Mint temp CH password — GET /cluster/9572/support/credentials

{ "password": "<16-char, omitted>", "ttl": "1" }

No login field → we supply the account email. TTL = 1 hour.

Query attribution — POST /cluster/9572/query with user=&password=

SELECT currentUser() AS u, version() AS v FORMAT JSONCompact →

[{ "node":"otel.internal.demo.altinity.cloud:8443", "label":"…",
   "result":"{ \"meta\":[…], \"data\":[[\"btyshkevich@altinity.com\",\"26.6.1.20000.altinityantalya\"]], \"rows\":1, … }",
   "meta":{ "queryId":"…", "queryTime":0.0009, "error":false },
   "summary":{ "read_rows":"1", … } }]

Note: always HTTP 200; result is a string (double-parse); one array entry per node.

Default output is a pretty ASCII table (not JSON)

SELECT 1 AS a, 'x' AS b → result = " ┌─a─┬─b─┐\n1. │ 1 │ x │\n └───┴───┘". So the adapter must force FORMAT JSON/JSONCompact.

Errors are in the envelope, not the HTTP status

SELECT nonsense_func() → HTTP 200, meta.error: true,
result: "Code: 46. DB::Exception: Function with name \nonsense_func` does not exist. … (UNKNOWN_FUNCTION)"`.

Proven transport constraints

1. Native bind-params NOT forwarded
query=SELECT {x:UInt8} AS v FORMAT JSONCompact + param_x=5 →
meta.error:true, Code: 456 … Substitution \x` is not set. (UNKNOWN_QUERY_PARAMETER)`.

2. Query-string settings NOT forwarded; in-SQL SETTINGS ARE (decisive)

query=SELECT value FROM system.settings WHERE name='max_threads' … &max_threads=7   → "auto(2)"   (stripped)
query=… SETTINGS max_threads=7 FORMAT JSONCompact                                   → "7"        (effective)

→ the adapter must smuggle settings into an in-SQL SETTINGS clause.

3. Sessions NOT preserved

call A: CREATE TEMPORARY TABLE t_probe (a UInt8) ENGINE=Memory   (session_id=probe-sess-1)   → ok
call B: SELECT * FROM t_probe … (same session_id)                                            → Code: 60 UNKNOWN_TABLE

→ each proxy call is a fresh CH session; temp password also rotates. Temp-table / cross-statement SET scripts can't be preserved.

Cluster metadata for the node header — GET /cluster/9572

endpoint = otel.demo.altinity.cloud:9440
nodes[]  (len 2): { id, status:"online", state:"ready", host:"chi-otel-otel-0-0", publicHost:"otel-0-0.demo.altinity.cloud", … }
layouts[] (shard/replica layout, nested nodes)