auditing-cloud-cluster-security

Auditing Cloud Cluster Security

Assesses the security posture of a CockroachDB Cloud cluster by examining network access controls, authentication and SSO configuration, user authorization, encryption, audit logging, and backup status. Produces a structured PASS/WARN/FAIL report with remediation links for each finding. Supports both CockroachDB Cloud and self-hosted clusters — checks that don't apply to the deployment model are marked N/A.

Read-only audit: All operations are read-only. No cluster state is modified during the assessment.

When to Use This Skill

• Preparing for SOC 2, HIPAA, or other compliance reviews
• Conducting periodic security posture assessments
• Onboarding a new production cluster and validating security baseline
• Investigating security configuration gaps after an incident
• Reviewing cluster security before a major release or customer onboarding

Prerequisites

Tools:

Tool	Cloud	Self-Hosted	Purpose
ccloud CLI	Required	N/A	Cluster metadata, network config, CMEK
cockroach sql	Required	Required	SQL security checks
openssl (v3+)	Recommended	Recommended	TLS/PQC cipher probing (-starttls postgres)
sslyze	Optional	Optional	Comprehensive TLS enumeration (--starttls postgres)

Credentials:

Credential	Cloud	Self-Hosted
ccloud auth login session	Required	N/A
SQL connection string	Required (from ccloud cluster sql --url)	Required (user provides)
DB username/password	Required (admin or VIEWACTIVITY)	Required (admin or VIEWACTIVITY)
TLS certificates directory	N/A (managed)	Required for cert expiry checks
CA certificate file	N/A	Required for openssl TLS probing

See permissions reference for detailed privilege requirements.

Security Audit Dimensions

Dimension	Tool	Checks
Network Security	ccloud	IP allowlists, private endpoints
Authentication & SSO	ccloud + sql	Cloud Console SSO, Database SSO (Cluster SSO), SCIM 2.0 provisioning, auto user provisioning
Authorization	sql	Users, roles, admin grants, PUBLIC privileges
Encryption	ccloud + sql	CMEK status, TLS settings
Audit Logging	sql	Audit log config, session logging
Backup & Recovery	ccloud + sql	Managed backup status, self-managed backup schedules
Cryptographic Posture	sql + openssl + sslyze	TLS version, PQC hybrid cipher support, encryption key size
Cluster Context	ccloud + user input	Deployment model, environment, compliance, data sensitivity
Cluster Configuration	ccloud	Version, plan, regions

Assessment Workflow

Step 0: Verify Prerequisites

Run the following checks to determine which tools are available. The audit proceeds regardless — missing tools degrade specific checks rather than blocking the audit.

Cloud clusters:

# Verify ccloud authentication
ccloud auth whoami

# Verify cluster access
ccloud cluster list -o json

Both deployment models:

# Verify SQL connectivity
cockroach sql --url "<connection-string>" -e "SELECT current_user();"

# Check openssl version (v3+ recommended for PQC probes)
openssl version

# Check sslyze availability
which sslyze && sslyze --version

Report tool availability before proceeding:

Tool	Status	Impact if Missing
ccloud	Available / Missing	Network, CMEK, and managed backup checks skipped (Cloud only)
cockroach sql	Available / Missing	All SQL-based checks skipped — audit severely limited
openssl (v3+)	Available / Missing	TLS cipher and PQC probing degraded
sslyze	Available / Missing	Comprehensive TLS enumeration unavailable; falls back to openssl

If cockroach sql is unavailable, warn the user that the audit will be limited to ccloud-only checks and recommend resolving connectivity before continuing. For missing optional tools (openssl, sslyze), note which checks will produce incomplete results and proceed.

Step 1: Gather Cluster Metadata and Confirm Audit Context

Cloud clusters:

# List clusters and identify target
ccloud cluster list -o json

# Get cluster details (use cluster name or ID)
ccloud cluster info <cluster-name> -o json

Self-hosted clusters:

# Gather metadata via SQL and cockroach CLI
cockroach node status --certs-dir=<certs-dir> --host=<host>
cockroach sql --url "<connection-string>" -e "SELECT version();"

Record: cluster ID, plan type (Basic/Standard/Advanced or self-hosted), cloud provider, regions, CockroachDB version. See ccloud commands reference for Cloud CLI syntax.

Confirm audit context: Present auto-detected metadata (cluster name, version, provider, regions, plan) and ask the user to confirm and provide:

1. Deployment model: CockroachDB Cloud / self-hosted
2. Environment: production / staging / development / sandbox
3. Compliance frameworks: SOC 2, HIPAA, PCI DSS, ISO 27001, GDPR, or none
4. Data sensitivity: PII/PHI, financial/payment, internal business, public/non-sensitive

Defaults (if user confirms without changes): Cloud, production, no compliance, internal business data. Deployment model determines check applicability (below). Environment and compliance calibrate severity (see Severity Adjustments).

Check Applicability by Deployment Model

Check	Cloud	Self-Hosted
IP allowlists (ccloud)	Yes (all tiers)	N/A — managed externally via firewall/VPC
Ingress Private Endpoints (ccloud)	Yes (Standard+, Advanced)	N/A
Egress Private Endpoints (ccloud)	Yes (Advanced)	N/A
HBA configuration (SQL)	Yes	Yes — primary network-level auth control
Cloud Console SSO	Yes	N/A
SCIM 2.0	Yes	N/A
Database SSO (OIDC)	Yes	Yes
Database SSO (LDAP/AD)	Yes	Yes
Users & Roles (SQL)	Yes	Yes
Privileges (SQL)	Yes	Yes
CMEK (ccloud)	Yes	N/A — check Enterprise Encryption instead
Enterprise Encryption	N/A	Yes — verify encryption-at-rest via store config
TLS	Always PASS (enforced)	Check — verify certs, expiry, config
TLS 1.3 / PQC / Key Size	Yes (INFO)	Yes (INFO)
Audit Logging (SQL)	Yes	Yes
Managed Backups (ccloud)	Yes (automatic)	N/A
Self-Managed Backups (SQL)	Optional (if managed backups present)	Yes — verify backup schedules exist and are running

Skip N/A checks for the detected deployment model and mark them as [N/A] in the report rather than PASS/FAIL.

Step 2: Assess Network Security

Cloud clusters: Check all three layers based on cluster tier:

# IP allowlists (all tiers)
ccloud cluster networking allowlist list <cluster-id> -o json

# Ingress private endpoints (Standard+, Advanced) — via Cloud Console or API
# Cloud Console: Networking > Private endpoint tab
# API: GET /api/v1/clusters/{cluster_id}/networking/private-endpoint-connections

# Egress private endpoints (Advanced only) — via Cloud Console or API
# Cloud Console: Networking > Egress tab
# API: GET /api/v1/clusters/{cluster_id}/networking/egress-endpoints

-- HBA configuration (all tiers)
SHOW CLUSTER SETTING server.host_based_authentication.configuration;

Evaluate (Cloud):

• FAIL if 0.0.0.0/0 is in the IP allowlist (open to all traffic)
• WARN if allowlist contains broad CIDR ranges (e.g., /8 or /16)
• WARN if no private endpoints configured on Advanced plan
• INFO if private endpoints not available on current tier
• PASS if allowlist contains only specific, narrow CIDR ranges or private endpoints are configured

Self-hosted clusters: Check HBA configuration as the primary network-level auth control:

SHOW CLUSTER SETTING server.host_based_authentication.configuration;

Evaluate (self-hosted):

• WARN if HBA configuration is empty or default — network security may be managed externally (firewalls, security groups, VPCs), so this is WARN not FAIL
• PASS if HBA rules restrict connections by IP, subnet, or auth method

Step 3: Check SSO and SCIM Configuration

For self-hosted clusters, skip Cloud Console SSO and SCIM checks (N/A). Database SSO checks still apply — check OIDC and/or LDAP/AD configuration.

Cloud Console SSO (Cloud Console UI > Organization Settings > Authentication — not via ccloud CLI):

• FAIL if SSO is not configured
• PASS if SAML or OIDC SSO is enabled and enforced

Database SSO (Cluster SSO) — OIDC:

-- Check if Cluster SSO is enabled for SQL authentication
SHOW CLUSTER SETTING server.oidc_authentication.enabled;
SHOW CLUSTER SETTING server.oidc_authentication.provider_url;

• FAIL if server.oidc_authentication.enabled is false
• PASS if enabled with a valid provider URL

Database SSO — LDAP/AD (Cloud and self-hosted):

-- Check if LDAP authentication is configured via HBA
SHOW CLUSTER SETTING server.host_based_authentication.configuration;
-- Look for ldap auth method entries in the HBA configuration

• PASS if HBA contains entries with ldap auth method
• INFO if LDAP is not configured (OIDC may be used instead)

SCIM 2.0 (Cloud Console UI > Organization Settings > Authentication > SCIM):

• FAIL if SCIM endpoint is not enabled; PASS if enabled and connected to an IdP

Auto user provisioning on Database:

-- Check if SQL users are automatically provisioned from SSO identities
SHOW CLUSTER SETTING server.identity_map.configuration;

• FAIL if identity mapping is not configured
• PASS if identity mapping routes IdP identities to SQL users

Step 4: Audit Users and Roles

-- List all users and their roles
SELECT
  username,
  options,
  member_of
FROM [SHOW USERS]
ORDER BY username;

See SQL queries reference for additional role audit queries.

Step 5: Check Privileges

-- Count admin role members
SELECT COUNT(*) AS admin_count
FROM [SHOW GRANTS ON ROLE admin];

-- Check PUBLIC role privileges on the current database
-- Note: SHOW GRANTS FOR public is scoped to the current database.
-- Run this query from each application database to get full coverage.
SELECT
  database_name,
  schema_name,
  object_name,
  object_type,
  privilege_type
FROM [SHOW GRANTS FOR public]
WHERE privilege_type NOT IN ('USAGE')
  AND schema_name = 'public'
ORDER BY database_name, object_name;

Important: SHOW GRANTS FOR public is scoped to the current database. Run SHOW DATABASES; and repeat the query from each database for full coverage.

Evaluate:

• FAIL if more than 5 users have admin role
• FAIL if PUBLIC has SELECT, INSERT, UPDATE, or DELETE on application tables
• WARN if admin count is between 3 and 5
• PASS if admin count is 1-2 and PUBLIC has minimal grants

Step 6: Verify Encryption

CMEK status (Cloud clusters):

# Check CMEK configuration (Advanced plan with Advanced Security Add-on)
ccloud cluster info <cluster-name> -o json
# Look for cmek_config in the output

Evaluate by plan type (Cloud):

• Standard plan: INFO — "Upgrade to Advanced plan with Advanced Security Add-on to enable CMEK"
• Advanced plan without Advanced Security Add-on: INFO — "Add Advanced Security Add-on to enable CMEK"
• Advanced plan with Advanced Security Add-on, CMEK not enabled: FAIL — CMEK not enabled despite plan supporting it
• Advanced plan with Advanced Security Add-on, CMEK enabled: PASS

Enterprise Encryption (self-hosted — skip CMEK, check this instead):

# Enterprise Encryption is configured via --enterprise-encryption flag at node startup
cockroach node status --certs-dir=<certs-dir> --host=<host> --format=records

SHOW CLUSTER SETTING enterprise.encryption.type;

• FAIL if not enabled and cluster stores sensitive data
• WARN if encryption status cannot be determined
• PASS if enabled with AES-256

TLS (Cloud): Always PASS — enforced on all connections.

TLS (self-hosted): Verify certificate validity and expiry (NOT auto-PASS):

cockroach cert list --certs-dir=<certs-dir>
openssl x509 -in <certs-dir>/node.crt -noout -enddate

• FAIL if any certificate expires within 30 days
• WARN if any certificate expires within 90 days
• PASS if all certificates valid with 90+ days remaining

Remediation (self-hosted): managing-tls-certificates

Cryptographic posture (both Cloud and self-hosted — informational only):

# Primary: sslyze with STARTTLS postgres (if available)
sslyze <host>:26257 --starttls postgres

# Supplementary: openssl with STARTTLS postgres
openssl s_client -connect <host>:26257 -starttls postgres -showcerts -tlsextdebug 2>&1

# PQC probe: offer ML-KEM hybrid, check if server accepts
openssl s_client -connect <host>:26257 -starttls postgres -groups X25519MLKEM768:x25519 2>&1

• INFO — TLS version (should be TLS 1.3; note if TLS 1.2 only)
• INFO — PQC hybrid cipher support (e.g., X25519MLKEM768) — emerging, not yet a FAIL condition
• INFO — Encryption key size (check for AES-256; note if AES-128)

Note: CockroachDB uses PostgreSQL wire protocol, so openssl s_client requires -starttls postgres to negotiate TLS correctly. Without this flag, the connection will fail. sslyze similarly requires --starttls postgres.

Step 7: Check Audit Logging

-- Check audit log configuration
SHOW CLUSTER SETTING sql.log.user_audit;

-- Check admin audit logging
SHOW CLUSTER SETTING sql.log.admin_audit.enabled;

Evaluate:

• FAIL if sql.log.user_audit is empty and sql.log.admin_audit.enabled is false
• WARN if only admin audit is enabled but user audit is not configured
• PASS if both user and admin audit logging are configured

Step 8: Assess Backup Status

Cloud clusters: Managed backups are automatic — always PASS. Optionally check for self-managed schedules:

ccloud cluster info <cluster-name> -o json  # Look for backup_config

SHOW SCHEDULES;  -- Check for additional self-managed backup schedules

Self-hosted clusters: No managed backups — verify self-managed backup schedules:

SHOW SCHEDULES;
SELECT id, label, schedule_status, next_run, created
FROM [SHOW SCHEDULES]
WHERE label ILIKE '%backup%' OR command @> '{"backup":{}}';

• FAIL if no backup schedules exist
• WARN if schedules exist but show errors or haven't run recently
• PASS if schedules are active and running

Remediation (self-hosted):

CREATE SCHEDULE 'nightly-full-backup'
  FOR BACKUP INTO 'gs://bucket/backups'
  RECURRING '@daily'
  WITH SCHEDULE OPTIONS first_run = 'now';

Pass/Warn/Fail Criteria

Check	PASS	WARN	FAIL
IP Allowlist	Specific CIDRs only	Broad ranges (/8, /16)	0.0.0.0/0 present
Cloud Console SSO	SSO enabled + enforced	SSO enabled, not enforced	Not configured
Database SSO	Cluster SSO enabled	—	Not configured
SCIM 2.0	SCIM enabled + connected	—	Not enabled
DB Auto User Provisioning	Identity mapping configured	—	Not configured
Admin Users	1-2 admins	3-5 admins	6+ admins
PUBLIC Privileges	No data grants	USAGE-only grants	SELECT/INSERT/UPDATE/DELETE
CMEK (Standard)	N/A	—	— (INFO: upgrade path)
CMEK (Advanced + Security Add-on)	CMEK enabled	—	Not enabled
Enterprise Encryption (self-hosted)	AES-256 enabled	Cannot determine	Not enabled (sensitive data)
TLS (self-hosted)	Certs valid 90+ days	Certs expire in 30-90 days	Certs expire within 30 days
TLS 1.3 / PQC / Key Size	—	—	— (INFO only)
HBA (self-hosted)	Restrictive rules	Empty/default	—
Audit Logging	User + admin audit on	Admin audit only	Disabled
Password Policy	min length >= 12	min length 8-11	min length < 8
Backups (Cloud)	N/A	—	— (INFO: managed)
Backups (self-hosted)	Schedules active	Schedules have errors	No schedules exist

Severity Adjustments by Environment

Severity is calibrated for production by default. Non-production environments downgrade some findings. Compliance requirements override downgrades.

Check	Production	Staging	Development	Sandbox
IP allowlist 0.0.0.0/0 (Cloud)	FAIL	FAIL	WARN	WARN
No private endpoints on Advanced (Cloud)	WARN	WARN	INFO	INFO
Empty HBA conf (self-hosted)	WARN	WARN	INFO	INFO
SSO not configured	FAIL	FAIL	INFO	INFO
SCIM not enabled	FAIL	FAIL	INFO	INFO
Database SSO disabled	FAIL	FAIL	INFO	INFO
Admin count 6+	FAIL	FAIL	WARN	WARN
CMEK not enabled (Cloud)	FAIL	FAIL	WARN	WARN
Enterprise Encryption not enabled (self-hosted)	FAIL	FAIL	WARN	WARN
Audit logging disabled	FAIL	FAIL	WARN	INFO
No backup schedules (self-hosted)	FAIL	FAIL	WARN	INFO

Compliance overrides — these checks cannot be downgraded when a compliance framework is specified (compliance takes precedence over environment):

Framework	Non-Downgradable Checks
SOC 2	SSO, audit logging, admin users, password policy
HIPAA	SSO, CMEK/Enterprise Encryption, audit logging, encryption, password policy, backups
PCI DSS	IP allowlist/HBA, CMEK/Enterprise Encryption, audit logging, admin users, password policy, backups
ISO 27001	SSO, audit logging, admin users
GDPR	Audit logging, encryption

Annotations: *(downgraded from FAIL — development cluster)* or *(PCI DSS compliance — cannot downgrade)*

Report Format

Save each audit report to reports/security-audit-<cluster-name>-<YYYY-MM-DD>-<sequence>.md (gitignored, local-only). The reports/ directory is not committed to version control — it serves as a local log for historical comparison and remediation tracking.

Generate a markdown report with the following structure:

# Security Audit Report — <Cluster Name>

**Date:** YYYY-MM-DD
**Cluster ID:** <cluster-id>
**Plan:** Standard | Advanced | Self-hosted
**CockroachDB Version:** vXX.X.X
**Regions:** us-east-1, us-west-2
**Deployment:** CockroachDB Cloud | Self-hosted
**Environment:** production | staging | development | sandbox
**Compliance:** SOC 2, PCI DSS | (none specified)
**Data Sensitivity:** PII/PHI | financial | internal | public

## Summary

| Status | Count |
|--------|-------|
| PASS   | X     |
| WARN   | X     |
| FAIL   | X     |
| INFO   | X     |
| N/A    | X     |

## Findings

### Network Security
- [PASS|WARN|FAIL|N/A] IP allowlist: <details>
- [PASS|WARN|INFO|N/A] Private endpoints: <details>
- [PASS|WARN|N/A] HBA configuration: <details>

### Authentication & SSO
- [PASS|FAIL|N/A] Cloud Console SSO: <details>
- [PASS|FAIL] Database SSO (OIDC): <details>
- [PASS|INFO] Database SSO (LDAP/AD): <details>
- [PASS|FAIL|N/A] SCIM 2.0 provisioning: <details>
- [PASS|FAIL] Auto user provisioning: <details>

### Authorization
- [PASS|WARN|FAIL] Admin user count: X users with admin role
- [PASS|FAIL] PUBLIC role privileges: <details>

### Encryption
- [PASS|FAIL|INFO|N/A] CMEK: <details>
- [PASS|FAIL|WARN|N/A] Enterprise Encryption: <details>
- [PASS|FAIL|N/A] TLS: <details>
- [INFO] Cryptographic posture: TLS version, PQC support, key size

### Audit Logging
- [PASS|WARN|FAIL] Audit log configuration: <details>

### Backup & Recovery
- [PASS|INFO|N/A] Managed backups: <details>
- [PASS|WARN|FAIL|N/A] Self-managed backups: <details>

### Cluster Configuration
- [INFO] Version: vXX.X.X
- [INFO] Plan: Standard | Advanced | Self-hosted
- [INFO] Regions: <list>

Status markers: [PASS], [WARN], [FAIL], [INFO], [N/A]. Use [N/A] for checks that don't apply to the deployment model. Append severity adjustment annotations when applicable (see Severity Adjustments).

Remediation

For each finding, the corresponding remediation skill can be used independently:

Finding	Remediation Skill
Open IP allowlist	configuring-ip-allowlists
SSO not configured / SCIM not enabled	configuring-sso-and-scim
CMEK not enabled	enabling-cmek-encryption
Audit logging disabled	configuring-audit-logging
Excessive admin privileges	hardening-user-privileges
Weak password policy	enforcing-password-policies
TLS/certificate issues	managing-tls-certificates
No private connectivity	configuring-private-connectivity
Log export not configured	configuring-log-export
Compliance gaps	preparing-compliance-documentation

For each FAIL finding, offer: "Explain how to fix this" (step-by-step guidance) or "Help me fix this now" (interactive remediation).

Safety Considerations

• All operations are read-only. No cluster settings, users, roles, or network configurations are modified during the audit.
• SQL queries use SHOW and SELECT only. No DDL or DML statements are executed.
• ccloud commands are read-only. Only list, info, and auth subcommands are used.
• No secrets are logged. Connection strings and tokens are not included in the report output.
• Privilege check: The audit may produce incomplete results if the executing user lacks admin or VIEWACTIVITY privilege. The report notes any permission gaps.

References

Skill references:

• Sample audit report — Example report with findings and remediation links
• SQL queries for security auditing
• ccloud CLI commands
• RBAC and privileges setup

Remediation skills:

• configuring-ip-allowlists — Network access hardening
• enabling-cmek-encryption — Customer-managed encryption keys
• configuring-audit-logging — SQL audit logging
• hardening-user-privileges — RBAC tightening
• enforcing-password-policies — Password strength enforcement
• configuring-sso-and-scim — SSO and SCIM provisioning
• managing-tls-certificates — TLS certificate management
• configuring-private-connectivity — Private endpoints and VPC peering
• configuring-log-export — Log and metric export
• preparing-compliance-documentation — Compliance readiness and documentation

Official CockroachDB Documentation:

• CockroachDB Cloud Security Overview
• Managing IP Allowlists
• Cloud Console SSO
• Cluster SSO (Database SSO)
• SCIM Provisioning
• CMEK Overview
• Audit Logging
• Security Reference: Authorization