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:

Credentials:

See permissions reference for detailed privilege requirements.

Security Audit Dimensions

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:

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

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

Severity Adjustments by Environment

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

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

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:

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