Secure CockroachDB with cnspec

Scan your CockroachDB clusters to find security risks before they become incidents. cnspec evaluates whether the cluster runs in insecure mode, its cluster settings and host-based authentication rules, roles and how their passwords are stored, database and table privileges, node certificates and encryption at rest, and backup schedules.

cnspec connects to any node of the cluster over SQL and inventories the whole cluster through read-only queries against crdb_internal, system tables, and SHOW statements. It never touches your application data. Every node resolves to the same asset, identified by the cluster ID, so scanning two nodes of one cluster doesn't create two assets.

CockroachDB speaks the PostgreSQL wire protocol, so the PostgreSQL provider can also connect to it. That provider reads the PostgreSQL catalog and reports the asset as PostgreSQL, though. Use the cockroachdb provider to read CockroachDB's own catalog, including the settings, nodes, certificates, and backups that PostgreSQL has no equivalent for.

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

PrerequisitesCopy Anchor Link

To scan CockroachDB with cnspec, you must have:

• cnspec installed on your workstation
• Network access to the SQL port of any node in the cluster (26257 by default)
• A user with the privileges in the table below. A member of the admin role can read everything.

What the connecting user can read decides what cnspec can report:

When the user lacks a privilege, the field that needs it fails with an access-denied error that names the missing privilege. It never returns an empty answer that a check could mistake for a pass.

AuthenticateCopy Anchor Link

cnspec authenticates with client certificates or with a password.

Client certificatesCopy Anchor Link

Point --certs-dir at a certificate directory, as cockroach cert writes it. cnspec uses ca.crt and client.<user>.crt and client.<user>.key from that directory, and verifies the server certificate (--sslmode verify-full):

cnspec shell cockroachdb db.contoso.com --certs-dir certs

PasswordCopy Anchor Link

cnspec shell cockroachdb db.contoso.com --user auditor --ask-pass --sslmode verify-full --sslrootcert certs/ca.crt

Create an auditing userCopy Anchor Link

A user with these two system privileges reads the cluster settings, authentication rules, nodes, certificates, and stores without reaching any application data:

CREATE USER auditor WITH PASSWORD '<password>';
GRANT SYSTEM VIEWCLUSTERSETTING, VIEWCLUSTERMETADATA TO auditor;

Role options, password storage, session defaults, and backup schedules come from system tables that CockroachDB doesn't allow you to grant access to, so those fields need a member of the admin role.

Connection optionsCopy Anchor Link

Verify with a quick CockroachDB checkCopy Anchor Link

cnspec shell cockroachdb db.contoso.com --certs-dir certs

cnspec> cockroachdb.cluster { id version clusterVersion insecure }
cockroachdb.cluster: {
  clusterVersion: "25.4"
  version: "25.4.17"
  insecure: false
  id: "f360f812-a251-4c4b-981e-65bdbfea456c"
}

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

Scan CockroachDBCopy Anchor Link

cnspec scan cockroachdb db.contoso.com --certs-dir certs

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 CockroachDB policy, so use the checks below as a starting point and create your own policies to meet your specific requirements.

Explore and test checks interactivelyCopy Anchor Link

cnspec shell cockroachdb db.contoso.com --certs-dir certs

List host-based authentication rules in evaluation orderCopy Anchor Link

cnspec> cockroachdb.cluster.hbaRules { position connectionType users address method }
cockroachdb.cluster.hbaRules: [
  0: {
    users: [
      0: "root"
    ]
    connectionType: "host"
    address: "all"
    method: "cert"
    position: 1
  }
  1: {
    users: [
      0: "app"
    ]
    connectionType: "hostssl"
    address: "10.0.0.0/8"
    method: "cert-scram-sha-256"
    position: 2
  }
]

Read cluster settingsCopy Anchor Link

cnspec> cockroachdb.cluster.settings.where(name == /^(server.auth_log.sql_connections.enabled|security.ocsp.mode)$/) { name value isDefault }
cockroachdb.cluster.settings.where: [
  0: {
    name: "security.ocsp.mode"
    value: "off"
    isDefault: true
  }
  1: {
    name: "server.auth_log.sql_connections.enabled"
    value: "false"
    isDefault: true
  }
]

List settings changed from their defaultsCopy Anchor Link

cnspec> cockroachdb.cluster.settings.where(isDefault == false) { name value defaultValue origin }

List roles and how they authenticateCopy Anchor Link

cnspec> cockroachdb.cluster.roles { name canLogin isAdmin hasPassword passwordHashMethod validUntil }

Review node certificates and encryption at restCopy Anchor Link

cnspec> cockroachdb.cluster.nodes { id version certificates { type expiresAt } stores { path encrypted } }
cockroachdb.cluster.nodes: [
  0: {
    certificates: [
      0: {
        expiresAt: 2036-10-26 16:46:17 +0100 CET
        type: "ca"
      }
      1: {
        expiresAt: 2031-10-23 17:46:17 +0200 CEST
        type: "node"
      }
    ]
    version: "25.4.17"
    id: 1
    stores: [
      0: {
        encrypted: false
        path: "/cockroach/cockroach-data"
      }
    ]
  }
]

List backup schedulesCopy Anchor Link

cnspec> cockroachdb.cluster.backupSchedules { label status recurrence encrypted nextRun }

CockroachDB redacts secrets in each schedule's statement, so a backup passphrase appears only as '*****'.

Example security checksCopy Anchor Link

Ensure the cluster doesn't run in insecure modeCopy Anchor Link

A cluster started with --insecure accepts unauthenticated, unencrypted connections from anyone who can reach it:

cnspec> cockroachdb.cluster.insecure == false
[ok] value: false

Ensure no authentication rule trusts the clientCopy Anchor Link

A trust rule lets a matching client log in as any user it names without a credential:

cnspec> cockroachdb.cluster.hbaRules.none(method == "trust")
[ok] value: true

Ensure SQL connection attempts are loggedCopy Anchor Link

cnspec> cockroachdb.cluster.settings.where(name == "server.auth_log.sql_connections.enabled").all(value == "true")
[ok] value: true

Ensure no password is still stored as bcryptCopy Anchor Link

With a crdb-bcrypt hash, the client sends the cleartext password to the server at every login. SCRAM-SHA-256 proves the client knows the password without ever sending it:

cnspec> cockroachdb.cluster.roles.none(passwordHashMethod == "crdb-bcrypt")
[ok] value: true

Ensure every login role has an idle session timeoutCopy Anchor Link

cnspec> cockroachdb.cluster.roles.where(canLogin).all(defaultSessionVariables["idle_in_session_timeout"] != null)
[ok] value: true

Ensure no node certificate expires within 30 daysCopy Anchor Link

cnspec> cockroachdb.cluster.nodes.all(certificates.all(expiresAt > time.now + 30*time.day))
[ok] value: true

Ensure every store is encrypted at restCopy Anchor Link

cnspec> cockroachdb.cluster.nodes.all(stores.all(encrypted == true))
[ok] value: true

Ensure every backup schedule is encryptedCopy Anchor Link

cnspec> cockroachdb.cluster.backupSchedules.all(encrypted == true)
[ok] value: true

Ensure the public role holds no privilege beyond CONNECTCopy Anchor Link

Every role is a member of public, so a privilege granted to it is granted to every user:

cnspec> cockroachdb.cluster.databases.all(grants.where(grantee == "public").all(privilege == "CONNECT"))
[ok] value: true

Review administrators that can log inCopy Anchor Link

Membership in admin is inherited, so a role that is a member of a role that is a member of admin is an administrator too. isAdmin resolves the whole chain:

cnspec> cockroachdb.cluster.roles.where(isAdmin && canLogin) { name memberOf { name } passwordHashMethod }

Learn moreCopy Anchor Link

• Remote Scanning with Inventory Files: scan multiple CockroachDB clusters at once using an inventory file instead of one at a time
• CockroachDB Resource Pack Reference: every CockroachDB resource and field cnspec can query
• Secure PostgreSQL with cnspec: for PostgreSQL servers you host
• Write Effective MQL: guide to authoring checks and queries