Secure ClickHouse with cnspec

Scan the ClickHouse servers you run yourself to find security risks before they become incidents. cnspec evaluates users and how they authenticate, the hosts they can connect from, roles and grants, settings profiles, quotas, clusters, and server settings.

cnspec connects over the ClickHouse native protocol and inventories the server through read-only queries against the system.* tables. It never touches your application data. The provider uses a pure-Go driver, so it needs no native libraries.

This is the provider for ClickHouse servers you operate. For the managed service's control plane, see Secure ClickHouse Cloud with cnspec. The two are complementary: a Cloud service with a locked-down IP allow list can still hold a passwordless user inside it, and only this provider will find that.

If you're new to cnspec, start with the Quickstart. For an overview of every database cnspec can scan, see the database scanning overview.

Prerequisites

To scan ClickHouse with cnspec, you must have:

• cnspec installed on your workstation
• Network access to the native protocol port (9000, or 9440 for TLS)
• A user with SELECT on system.* and SHOW ACCESS

Authenticate

cnspec authenticates with a user and password:

cnspec shell clickhousedb db.contoso.com --user auditor --ask-pass

Create a least-privileged auditing user. These grants read the access catalog without reaching any application data:

CREATE USER auditor IDENTIFIED WITH sha256_password BY '<password>';
GRANT SELECT ON system.* TO auditor;
GRANT SHOW ACCESS ON *.* TO auditor;

Reading users, roles, and grants also requires SQL-driven access management to be enabled on the server. Without it, or without the catalog privileges, those collections come back empty rather than failing the scan, so confirm clickhousedb.instance.users is populated before trusting a passing account check.

Connection options

Verify with a quick ClickHouse check

cnspec shell clickhousedb db.contoso.com --user auditor --ask-pass

cnspec> clickhousedb.instance { version }
clickhousedb.instance: {
  version: "24.8.4.13"
}

If cnspec connects and shows the version, you're ready to scan.

Scan ClickHouse

cnspec scan clickhousedb db.contoso.com --user auditor --ask-pass

When a scan completes, cnspec prints a summary of all the checks it ran, grouped by policy, along with a risk score from 0 (no risk) to 100 (highest risk). Failed checks include remediation guidance to help you fix issues. To learn more about reading scan results, read Understand cnspec Results.

Mondoo doesn't yet ship an out-of-the-box ClickHouse policy, so use the checks below as a starting point and create your own policies to meet your specific requirements.

Explore and test checks interactively

cnspec shell clickhousedb db.contoso.com --user auditor --ask-pass

List users

cnspec> clickhousedb.instance.users { name authTypes hasPassword anyHost hostIps storage }

List roles and their grants

cnspec> clickhousedb.instance.roles { name grants }
clickhousedb.instance.roles: [
  0: {
    name: "analyst"
    grants: ["SELECT ON *.*"]
  }
]

Read a server setting

cnspec> clickhousedb.instance.serverSettings.where(name == "tcp_port_secure") { name value changed }

List settings profiles and quotas

cnspec> clickhousedb.instance.settingsProfiles { name appliesToAll appliesTo numElements }
cnspec> clickhousedb.instance.quotas { name keys appliesToAll appliesTo }

List clusters

cnspec> clickhousedb.instance.clusters { name shardCount maxReplicaCount }

Example security checks

Ensure every user requires a credential

cnspec> clickhousedb.instance.users.all(hasPassword == true)
[ok] value: true

Ensure no user authenticates with no_password

cnspec> clickhousedb.instance.users.all(authTypes.none(_ == "no_password"))
[ok] value: true

Ensure no user is reachable from any host

cnspec> clickhousedb.instance.users.none(anyHost == true)
[ok] value: true

Ensure no plaintext-password authentication is configured

cnspec> clickhousedb.instance.users.all(authTypes.none(_ == "plaintext_password"))
[ok] value: true

Ensure no user can grant its own privileges onward

WITH GRANT OPTION lets the holder hand out what it has, which makes every other grant provisional:

cnspec> clickhousedb.instance.users.all(grants.none(_ == /WITH GRANT OPTION/))
[ok] value: true

Ensure no user holds a global privilege directly

Grants belong on roles, so that access can be reviewed and revoked in one place:

cnspec> clickhousedb.instance.users.all(grants.none(_ == /ON \*\.\*/))
[ok] value: true

Ensure access is managed in SQL rather than the config file

A user defined in users.xml can't be changed or revoked without editing the file and reloading:

cnspec> clickhousedb.instance.users.all(storage == "local_directory")
[ok] value: true

Ensure the secure TCP port is configured

cnspec> clickhousedb.instance.serverSettings.where(name == "tcp_port_secure").all(value != "0")
[ok] value: true

Ensure a quota constrains every user

An unconstrained user can exhaust the server for everyone else:

cnspec> clickhousedb.instance.quotas.any(appliesToAll == true)
[ok] value: true

Review the default user

The default user is the account an unauthenticated client lands on:

cnspec> clickhousedb.instance.users.where(name == "default") { hasPassword authTypes anyHost hostIps grants }

Review users defined outside SQL

cnspec> clickhousedb.instance.users.where(storage == "users_xml") { name authTypes anyHost }

Learn more

• ClickHouse Resource Pack Reference: every ClickHouse resource and field cnspec can query
• Secure ClickHouse Cloud with cnspec: for the managed service's control plane
• Write Effective MQL: guide to authoring checks and queries