auditing-cloud-cluster-security

What this skill does

# 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](references/permissions.md) 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:**
```bash
# Verify ccloud authentication
ccloud auth whoami

# Verify cluster access
ccloud cluster list -o json
```

**Both deployment models:**
```bash
# 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:**
```bash
# 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:**
```bash
# 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](references/ccloud-commands.md) 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:

```bash
# 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
```

```sql
-- 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:

```sql
SHOW CLUSTER SETTING server.host_based_authentication.configuration;
`