ClickHouse Open Access Sentinel

ClickHouse ships a default user with no password, reachable from any host, unless an operator locks it down by hand - and that single misconfiguration, left on an internet-facing instance, is exactly how a wave of real ClickHouse honeypot and crypto-mining breaches happened. There is no built-in alert for it; the server runs exactly as configured, risk and all. This blueprint queries system.users for any account that is both passwordless and allowed to connect from any host, alerts on breach, and can restrict that account down to a safe CIDR with ALTER USER ... HOST IP - touching only the host restriction, never the password or any other grant.

How it works

1. periodic_access_check (io.kestra.plugin.core.trigger.Schedule) runs every 30 minutes, always forcing auto_remediate: "false" and dry_run: "true" through its own inputs: override regardless of this flow's defaults. Shipped disabled so you can validate a manual run first.
2. audit_open_access (io.kestra.plugin.jdbc.clickhouse.Query) reads system.users with fetchType: FETCH, filtering to accounts where the auth_type array contains 'no_password' and the host_ip array contains '::/0' or '0.0.0.0/0' - the exact notation ClickHouse's own default users.xml uses for "allow from any host".
3. evaluate_open_access_gate (io.kestra.plugin.core.flow.If) flags a breach whenever audit_open_access.size > 0.
   • then (breach): route_remediation (io.kestra.plugin.core.flow.Switch on auto_remediate) either considers remediation ("true", gated again by dry_run through check_dry_run) or only logs that remediation is disabled ("false"). When both gates allow it, remediate_open_access (io.kestra.plugin.core.flow.Loop) runs restrict_account_host for each flagged row, executing ALTER USER <name> HOST IP '<safe_host_cidr>'. alert_open_access_risk always fires to Slack regardless of which path ran.
   • else (compliant): log_access_healthy records a clean check.
4. log_audit_complete always runs last, printing the headline numbers regardless of which branch fired.
5. The errors block alerts Slack separately if the flow itself fails - an unreachable instance or a user without rights to read system.users must not read as "no risk".

What you get

• A concrete, named list of accounts that are both passwordless and open to any host - not a generic "check your users" reminder.
• Remediation that changes only the HOST restriction via ALTER USER; the account's password, roles, and every other grant are left exactly as they were.
• Two independent gates (auto_remediate, dry_run) between "risk detected" and "host access actually restricted".
• A final status log every run, risk or not, so the execution history doubles as an access-posture history for the instance.

Who it's for

• Platform and security teams running self-hosted ClickHouse who need to catch a default-user misconfiguration before it is found by a port scan.
• SREs standing up a new ClickHouse cluster who want this check as a pre-production gate.
• Anyone who has read about a ClickHouse honeypot breach and wants to know, concretely, whether their own instance has the same exposure.

Why orchestrate this with Kestra

system.users is a live, read-only snapshot; querying it by hand answers one moment in time and fixes nothing. Kestra supplies the schedule, the risk classification as a first-class branch, two independent authorization gates before anything is changed, and an execution history that shows exactly when an account went exposed and whether it was restricted.

Prerequisites

• A reachable ClickHouse server, and a clickhouse_username with rights to read system.users and, if remediation runs, execute ALTER USER.
• A Slack incoming webhook for alerts.

Local testing: docker run -d --name clickhouse-sentinel -p 8124:8123 -p 9001:9000 --ulimit nofile=262144:262144 clickhouse/clickhouse-server docker network connect YOUR_KESTRA_NETWORK clickhouse-sentinel (only needed if the Kestra Worker runs in a separate Docker network than this container; set clickhouse_url to jdbc:clickhouse://clickhouse-sentinel:8123/ from inside that network, or jdbc:clickhouse://localhost:8124/ from the host). The official image's default user ships exactly in the audited state - no password, any host - so a first run against it is a guaranteed, realistic breach.

Secrets

• CLICKHOUSE_PASSWORD: password for clickhouse_username. The official image's default user has no password; if your CLICKHOUSE_PASSWORD secret is unset in that case, set it to an empty value rather than omitting the password property, so the task does not pass a stale credential.
• SLACK_WEBHOOK_URL: Slack incoming webhook used by alert_open_access_risk and the errors block.
• In Kestra OSS (no Enterprise secrets backend), secrets are supplied as environment variables prefixed SECRET_, base64-encoded, and read back in flows with {{ secret('NAME') }} - for example SECRET_SLACK_WEBHOOK_URL=$(echo -n 'https://hooks.slack.com/...' | base64). This keeps credentials out of the flow YAML but, per Kestra's own documentation, offers no encryption at rest or access control beyond the host environment; use the Enterprise secrets backend for stronger guarantees.

Inputs

• clickhouse_url (STRING, default jdbc:clickhouse://localhost:8123/): JDBC connection URL.
• clickhouse_username (STRING, default default): account used to run the audit and, if enabled, the remediation.
• safe_host_cidr (STRING, default 127.0.0.1/32): HOST value applied to a flagged account when remediation runs.
• auto_remediate (SELECT: "false", "true"; default "false"): must be "true" for remediation to even be considered.
• dry_run (SELECT: "true", "false"; default "true"): must be explicitly "false", together with auto_remediate: "true", for ALTER USER to actually run.

Outputs

• outputs.audit_open_access.size / .rows: the count and full row set (name, auth_type, host_ip) of flagged accounts for every run.

Quick start

1. Start ClickHouse locally with the command above - the default default user is already in the audited (exposed) state, no setup required.
2. Add the CLICKHOUSE_PASSWORD (empty value is fine for the default install) and SLACK_WEBHOOK_URL secrets.
3. Run the flow manually with the defaults and confirm alert_open_access_risk fires naming default, and that log_remediation_disabled logs without changing anything.
4. Run once with auto_remediate: "true" and dry_run: "true" and confirm log_dry_run_remediation describes the restriction without applying it.
5. Run once with auto_remediate: "true" and dry_run: "false" against a non-production instance, then confirm with SELECT name, host_ip FROM system.users WHERE name = 'default' that host_ip now shows only safe_host_cidr.
6. Enable periodic_access_check once you trust the check; it always runs in the safe auto_remediate: "false" / dry_run: "true" mode regardless of what you leave the flow's own defaults set to.

How to extend

• Add a second audit against system.grants (access_type = 'ALL' with is_wildcard = 1) to also catch accounts with unrestricted privilege grants, not just open host access.
• Loop over several clickhouse_url values with io.kestra.plugin.core.flow.Loop (ForEach is deprecated) to sweep a whole fleet of clusters in one run.
• Route the remediation branch through a Pause approval task instead of (or alongside) the static auto_remediate input, for a human-reviewed restriction on production clusters.
• Extend restrict_account_host to also set a password with ALTER USER ... IDENTIFIED WITH sha256_password BY {{ secret(...) }}, rather than relying on host restriction alone.

Pitfalls

• auth_type and host_ip are Array columns, not scalars. Verified from ClickHouse's own system.users documentation; this is why the audit uses has(array, value) rather than = equality, which would silently never match.
• ::/0 and 0.0.0.0/0 are ClickHouse's own notation for "any host". Verified against the default users.xml shipped with the official image; a host restricted to a specific IP or subnet never contains either literal, so the audit does not false-positive on a properly locked-down account.
• A bare ALTER USER ... HOST IP '<cidr>' (no ADD/DROP) replaces the account's entire host list. Verified from ClickHouse's own ALTER USER syntax reference. This is intentional here - it removes the open ::/0 entry - but it also removes any other host that account was previously allowed to connect from; review safe_host_cidr before enabling auto_remediate on an account with host rules you need to keep.
• auto_remediate and dry_run are independent gates, not a single boolean. Both must be "true"/"false" respectively for ALTER USER to run; setting only one leaves the other still blocking.
• Switch case keys "true"/"false" are quoted strings. auto_remediate renders as the literal string "true" or "false"; unquoted true:/false: YAML map keys would parse as booleans instead and would not match.

Links

• plugin-jdbc-clickhouse documentation
• ClickHouse system.users table reference
• ClickHouse ALTER USER syntax reference
• Kestra secrets (OSS vs Enterprise)