Connecting eltmaestro to ClickHouse — plaintext password auth¶

Operator guide for wiring the eltmaestro container to authenticate against ClickHouse using a plaintext password stored on both sides. Covers both ends end-to-end so clickhouse-client (and the eltmaestro engine shell-outs that use it) never prompt for a password.

ClickHouse supports several auth modes (plaintext password, password_sha256_hex, password_double_sha1_hex, SSH key, LDAP, Kerberos). This document is the plaintext password path — the simplest mode and what's deployed today. SHA256 / SSH-key / other modes are left as future hardening; they can be added back here as separate sections when needed.

Why plaintext for now: Both server (users.d/maestro_etl.xml) and client (~/.clickhouse-client/config.xml) store the password in plain text inside chmod 600 root-owned files. On a dev box on a private LAN this is acceptable. The pain points of moving to SHA256 (matching echo -n exactly, no trailing newlines, no shell quoting surprises) aren't worth the marginal security gain here. When that changes — when this stack moves to a non-LAN environment, or when the password file stops being root-owned — flip to SHA256 (see §6).

Topology assumed¶

Piece	Value
ClickHouse server image	`clickhouse/clickhouse-server:24.8` (LTS) on `192.168.1.201`
Native TCP (host)	`:9100` → container `:9000`
Native TCP + TLS (host)	`:9440`
HTTP (host)	`:8123`
ClickHouse config root (host)	`/home/jenkins/dev-dockers/clickhouse/`
eltmaestro container	published via `eltmaestro-ci` Jenkins job, runs the engine + meta-service + `clickhouse-client` (apt `lts` channel — same minor as the server)
User to authenticate	`maestro_etl`
Password	`welcome123` (example — replace with the value you actually use)

Full host topology is in the reference_clickhouse_host.md memory.

1. ClickHouse server-side — `192.168.1.201`¶

1.1. Where things live¶

Two homes: the repo (this tree, version-controlled) and the host (the live bind-mount on 192.168.1.201). The sync-clickhouse-config Jenkins ACTION pushes repo → host non-destructively. Files marked host-only never get synced (secrets + per-host runtime state).

Repo (source of truth for operator policy)¶

Path in repo	Purpose
`source/root-clickhouse-install/config/users.d/maestro_etl.xml.example`	Template for the per-user `maestro_etl.xml` (host-only). The `.example` suffix is excluded from the sync.
`source/root-clickhouse-install/bin/sync-clickhouse-config.sh`	Script the Jenkins ACTION runs.
`source/root-clickhouse-install/README.md`	Operator workflow + two-tier sync model.

Host bind-mount (`/home/jenkins/dev-dockers/clickhouse/`)¶

Path	Purpose	Owned by
`docker-compose.yml`	Service def — port publishes (`9100:9000`, `9440:9440`, `8123:8123`, `9004-9005`, `9009`), volumes, version	host-only today — Tier-2 sync source pending repo bootstrap (see §1.4)
`.env`	Overrides: `CLICKHOUSE_VERSION`, `CH_*_PORT`, `DATA_ROOT`, `TZ`	host-only (secrets)
`users.d/maestro_etl.xml`	Per-user auth + RBAC for `maestro_etl` — holds the cleartext password	host-only (secrets)
`users.d/{default,maestro}.xml`	Other users (admin, default)	host-only — leave alone
`users.d.bak.<BUILD_NUMBER>/`	Snapshots taken by `sync-clickhouse-config` before each tier-1 write	created by sync
`/home/jenkins/docker/clickhouse/data` (UID 101)	Bind to container `/var/lib/clickhouse`	host-only
`/home/jenkins/docker/clickhouse/logs` (UID 101)	Bind to container `/var/log/clickhouse-server`	host-only

1.2. The XML — `users.d/maestro_etl.xml`¶

<clickhouse>
  <users>
    <maestro_etl>
      <password>welcome123</password>
      <networks>
        <ip>::/0</ip>
      </networks>
      <profile>default</profile>
      <quota>default</quota>
      <access_management>1</access_management>
      <named_collection_control>1</named_collection_control>
      <show_named_collections>1</show_named_collections>
      <show_named_collections_secrets>1</show_named_collections_secrets>
    </maestro_etl>
  </users>
</clickhouse>

Field meanings: - <password> — plaintext password. Must match exactly what the client sends. No leading/trailing whitespace inside the element — XML preserves it. - <networks><ip>::/0</ip></networks> — accept connections from any source. Narrow to your LAN/VPN CIDR for prod. - <profile> / <quota> — server-side resource limits; default is fine for most setups. - <access_management> + <named_collection_control> + <show_named_collections> + <show_named_collections_secrets> — RBAC flags (1 = enabled). Keep or strip per your operator policy; not auth-related.

If you previously had <ssh_keys> or <password_sha256_hex> in this file, remove them. Mixing modes was the source of the earlier Code: 516 ... Expected authentication with SSH key confusion — clients can pick the wrong path, and the server doesn't fall back. Plaintext-only keeps the negotiation trivial.

1.3. Bring up / reload¶

cd /home/jenkins/dev-dockers/clickhouse
docker compose pull && docker compose up -d

After editing users.d/maestro_etl.xml on a running container, no command is needed — ClickHouse watches users.d/ and hot-reloads on file change. Confirm with the server log:

docker logs clickhouse 2>&1 | grep -i 'reloading\|users.d' | tail

You should see a line like Reloading users config from /etc/clickhouse-server/users.d/maestro_etl.xml.

1.4. `allow_nullable_key` — why it's per-query, not a server default¶

The integrator's $QUERY_CACHE_BUILDER template builds MergeTree cache tables with ORDER BY ($COLUMNS) where any column might be nullable (see integrators/clickhouse/system.cfg). Without allow_nullable_key=1 the server rejects the CREATE with NULLABLE_COLUMN_NOT_ALLOWED_IN_PARTITION_KEY.

A previous setup carried a users.d/00-defaults.xml overlay that set <allow_nullable_key>1</allow_nullable_key> under <profiles><default>. This breaks ClickHouse 24.8.14 — the server bails during access-control load with:

Code: 115. DB::Exception: Setting allow_nullable_key is neither a
builtin setting nor started with the prefix 'SQL_' registered for
user-defined settings: while parsing profile 'default' in users
configuration file. (UNKNOWN_SETTING)

allow_nullable_key is a MergeTree-level setting, not a user/profile setting; the parser refuses it at profile scope and the container crash-loops (exit 115). The 2026-05-28 incident was triggered by an ungraceful VM shutdown that re-read the overlay on container restart. The overlay file was deleted from the repo (source/root-clickhouse-install/config/users.d/00-defaults.xml) in that same fix; if you see it in an older worktree, drop it before the next sync or the host's overlay will be re-introduced and CH will crash again on the next restart.

The working patterns instead:

Use case	What enables `allow_nullable_key`
Engine workloads (the `$QUERY_CACHE_BUILDER` template)	Inline `SETTINGS allow_nullable_key=1` on the per-query `CREATE`. Already present in the template; no server-side config needed.
Operator ad-hoc `CREATE TABLE ... ORDER BY (<nullable_col>)`	Append `SETTINGS allow_nullable_key=1` manually on the statement.
Server-wide default (future work)	A `config.d/00-merge-tree.xml` with `<merge_tree><allow_nullable_key>1</allow_nullable_key></merge_tree>` — not yet wired. Would also need `docker-compose.yml` to bind-mount `./config.d:/etc/clickhouse-server/config.d:ro` and `sync-clickhouse-config.sh` to handle the new tier.

The sync-clickhouse-config Jenkins ACTION (and the underlying bin/sync-clickhouse-config.sh) still exists for pushing other valid users.d/ overlays (auth user definitions, profile overrides that are valid at profile scope). See CI-CD.md ACTION cheat-sheet for usage.

2. eltmaestro container-side — same host, different container¶

2.1. Where things live (eltmaestro container)¶

Path inside container	Purpose	Persistence
`/root/.clickhouse-client/config.xml`	Default config picked up by `clickhouse-client` automatically	Host bind-mount from `/home/jenkins/maestro-ch-client/config.xml` (read-only). Survives destructive deploy. Wired in `source/Jenkinsfile` Deploy stage.
`/root/.env_integrator`	Sets `CLICKHOUSE_CONFIG=$HOME/.clickhouse-client/config.xml`. Sourced by all shells (`.bashrc`, `.bash_profile`, `BASH_ENV`)	Baked into image

The image bakes clickhouse-client from apt lts channel, so the client minor matches clickhouse/clickhouse-server:24.8 automatically. See CI-CD.md § In-image client tooling.

2.2. Bootstrap `config.xml` on the host (one-time per host)¶

The config now lives on the host at /home/jenkins/maestro-ch-client/config.xml and gets bind-mounted into the container at runtime. Bootstrap it once; thereafter every destructive deploy picks it up automatically — no in-container re-laying needed.

# On 192.168.1.201, as the jenkins user:
sudo install -d -o jenkins -g jenkins -m 0700 /home/jenkins/maestro-ch-client

cat > /home/jenkins/maestro-ch-client/config.xml <<'XML'
<?xml version="1.0"?>
<config>
  <host>192.168.1.201</host>
  <port>9100</port>
  <user>maestro_etl</user>
  <password>welcome123</password>
</config>
XML

chmod 600 /home/jenkins/maestro-ch-client/config.xml

The cleartext password sits only in this chmod 600 jenkins-owned file on the host. The server-side users.d/maestro_etl.xml also holds it as <password>welcome123</password> (different host path, same host).

Replace welcome123 with the password you actually use.

For native TCP + TLS use <port>9440</port> + <secure>true</secure>. For HTTP use <port>8123</port> (works the same way with this config).

Precondition check: the Jenkinsfile Deploy stage's destructive block fails fast with a clear error if this file is missing or empty, before tearing down the running container. So if you blow away the host file by mistake, the next destructive deploy refuses to run rather than silently leaving the container with an unreadable mount.

2.3. clickhouse-client config search order¶

1. --config-file <path>        (CLI override)
2. $CLICKHOUSE_CONFIG           ← env_integrator sets this to priority-4 default
3. ./clickhouse-client.xml      (cwd)
4. ~/.clickhouse-client/config.xml   ← bind-mounted from the host file §2.2 bootstraps
5. /etc/clickhouse-client/config.xml

env_integrator setting CLICKHOUSE_CONFIG to priority 4's default path is for clarity + flexibility — moving the config to a mounted volume later is a one-line change to env_integrator instead of changing every invocation. Direct docker exec eltmaestro clickhouse-client ... (no shell) still hits priority 4 and finds the same file.

3. Engine-side integration (JDBC)¶

The eltmaestro engine reaches ClickHouse via the clickhouse4j-1.4.4.jar driver (cc.blynk fork — see root-engine-install/jdbc_list.json) for batch loads. JDBC connections come from WPF-defined entries in t_jdbc; supply the same cleartext password (welcome123 in the examples) in the WPF connection definition. The driver sends it over the wire; the server matches it against <password> in users.d. Same byte-for-byte comparison as the CLI path.

4. Verify¶

From the host (or inside the container, drop the docker exec):

docker exec eltmaestro clickhouse-client --query "SELECT version(), currentUser()"

Expected output (no prompt):

24.8.x.x        maestro_etl

5. Common errors¶

Symptom	Cause
`Code: 210 ... Connection refused (192.168.1.201:9100)`	ClickHouse container not running, OR docker-compose's port mapping isn't `9100:9000`. Confirm with `docker ps`.
`Code: 102 ... Unexpected packet from server (...:8123)`	Pointed `clickhouse-client` (native protocol) at the HTTP port. Use `--port 9100` for native, or use `curl` for HTTP.
`Connected to server version 0.125.124` + `Code: 131 TOO_LARGE_STRING_SIZE`	Bogus handshake. You connected to `localhost:9000` inside a container where HDFS namenode owns 9000 (the eltmaestro container itself). Use external host `192.168.1.201:9100`, not `localhost:9000`.
`Code: 516 ... Password is incorrect`	The plaintext `<password>` on the server doesn't byte-for-byte match what the client sent. Watch for whitespace inside the XML element (some editors auto-indent inside `<password>`); the password must be the exact string between the open and close tags.
`Code: 516 ... Expected authentication with SSH key`	Server-side `users.d/maestro_etl.xml` still has a `<ssh_keys>` block. For this all-plaintext setup, remove the `<ssh_keys>` element entirely.
Client connects but warns "ClickHouse server version is older than client"	Client is on apt `stable` channel (e.g. 26.5) while server is `:24.8`. Rebuild the eltmaestro image — its Dockerfile now installs `clickhouse-client` from apt `lts` channel, matching the server. See CI-CD.md § In-image client tooling.

6. Per-destructive-deploy operator checklist¶

Nothing — the host bind-mount described in §2 makes this automatic.

Each Invoke-JenkinsAction -Action build -Destructive rebuilds + replaces the eltmaestro container; the container's writable layer is gone, but /root/.clickhouse-client/config.xml is re-populated by the -v mount from /home/jenkins/maestro-ch-client/config.xml on the host. The Jenkinsfile Deploy stage's precondition check (§2.2) fails fast if the host file is missing, so the only failure mode is "operator never ran the §2.2 bootstrap" — which surfaces as a clean error before the running container is touched.

Verify with §4 after any destructive deploy if you want belt-and-braces.

7. Hardening (future)¶

When you're ready to stop storing the cleartext password in users.d/maestro_etl.xml:

Generate a SHA256 of the same cleartext:
```
echo -n 'welcome123' | sha256sum | awk '{print $1}'
```
Critical: echo -n so there's no trailing newline in the hashed input. A trailing newline produces a different hash and you'll get an opaque "Password is incorrect" error.
Replace the server-side <password>welcome123</password> with <password_sha256_hex>{hex_from_step_1}</password_sha256_hex>.
Leave the container-side config.xml unchanged — clients still send cleartext over the wire; the server hashes for comparison.
ClickHouse hot-reloads. Verify with §4.

Similarly, SSH-key auth (which keeps the secret material on the client only) is a layer beyond SHA256. Both are deferred for now; this doc covers the working path, and they can be added back as separate sections when the threat model justifies the operational cost.

8. Cross-references¶

reference_clickhouse_host.md (memory) — host topology, port table, dev-dockers layout, diagnostic traps (incl. why localhost:9000 inside eltmaestro lies about being a ClickHouse server)
CI-CD.md § In-image client tooling — the lts apt channel choice that keeps client + server versions aligned
OPERATIONS.md § Connect to ClickHouse without password prompts — operator quick-reference; this file is the deep ClickHouse-auth cut