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](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; ``` **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:** ```sql -- 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):** ```sql -- 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:** ```sql -- 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 ```sql -- List all users and their roles SELECT username, options, member_of FROM [SHOW USERS] ORDER BY username; ``` See [SQL queries reference](references/sql-queries.md) for additional role audit queries. ### Step 5: Check Privileges ```sql -- 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):** ```bash # 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-at-Rest is configured at node start via the `--enterprise-encryption` flag and is not exposed as a SQL cluster setting. Confirm it by: - Inspecting the node's startup arguments (process command line / systemd unit / Kubernetes pod spec) for `--enterprise-encryption=...` - Checking the per-node Prometheus endpoint: `curl -ks https://<node>:8080/_status/vars | grep '^rocksdb_encryption_'` - The DB Console **Advanced Debug** → **Stores** view reports the active encryption type per store - **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): ```bash 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](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-security-and-governance/managing-tls-certificates/SKILL.md) **Cryptographic posture (both Cloud and self-hosted — informational only):** ```bash # 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 ```sql -- 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: