Update to CockroachDB

{
  "description": "Audits a self-hosted CockroachDB cluster against the CIS CockroachDB Benchmark v1.0.0 Level 1 controls. Supports two audit depths — quick automated scans and full CIS audit procedures. Produces a structured PASS/FAIL/MANUAL report covering installation, system hardening, logging, user access, data protection, and CockroachDB settings. Use when preparing for CIS compliance assessments, hardening self-hosted deployments, or validating security posture against industry benchmarks.",
  "included_files": [
    {
      "relative_path": "references/cis-controls.md",
      "size_in_bytes": 26495
    },
    {
      "relative_path": "references/sample-report.md",
      "size_in_bytes": 7567
    },
    {
      "relative_path": "references/sql-queries.md",
      "size_in_bytes": 5037
    }
  ],
  "name": "auditing-cis-benchmark",
  "skill_md_contents": "---\nname: auditing-cis-benchmark\ndescription: Audits a self-hosted CockroachDB cluster against the CIS CockroachDB Benchmark v1.0.0 Level 1 controls. Supports two audit depths — quick automated scans and full CIS audit procedures. Produces a structured PASS/FAIL/MANUAL report covering installation, system hardening, logging, user access, data protection, and CockroachDB settings. Use when preparing for CIS compliance assessments, hardening self-hosted deployments, or validating security posture against industry benchmarks.\ncompatibility: \"CockroachDB self-hosted (all versions). Requires shell access to cluster nodes and SQL admin or VIEWACTIVITY privilege.\"\n---\n\n# Auditing CIS Benchmark Compliance\n\nAssesses a self-hosted CockroachDB cluster against the CIS CockroachDB Benchmark v1.0.0 Level 1 profile. Evaluates 30 controls across six domains: installation and patches, system hardening and topology, logging and monitoring, user access and authorization, data protection, and CockroachDB settings. Produces a structured report with PASS, FAIL, and MANUAL REVIEW findings, including CIS Controls v7/v8 mappings with Implementation Group coverage.\n\n**Scope:** Self-hosted CockroachDB deployments only. For CockroachDB Cloud clusters, use [auditing-cloud-cluster-security](../audit-cloud-security/SKILL.md) instead — Cloud clusters have managed controls that supersede many CIS self-hosted checks.\n\n**Authoritative source:** This skill implements the benchmark defined at https://github.com/cockroachlabs/CIS-benchmarks-crdb\n\n**Read-only audit:** All operations are read-only. No cluster state, OS configuration, or files are modified during the assessment.\n\n## When to Use This Skill\n\n- Preparing for a CIS benchmark compliance assessment or external audit\n- Hardening a new self-hosted CockroachDB production deployment\n- Validating security posture against industry-standard benchmarks\n- Performing periodic compliance checks as part of security operations\n- Mapping CockroachDB security controls to CIS Controls frameworks\n- Responding to auditor requests for CIS benchmark evidence\n\n## Prerequisites\n\n**Access requirements:**\n\n| Requirement | Purpose |\n|-------------|---------|\n| SSH/shell access to cluster nodes | OS-level checks (systemd, swap, THP, file descriptors, certs) |\n| SQL access (admin or VIEWACTIVITY) | Cluster settings, user/role audit, logging config |\n| Access to systemd service files | Service configuration verification |\n| Access to certificate directory | Certificate permission and validity checks |\n\n**Tools:**\n\n| Tool | Required | Purpose |\n|------|----------|---------|\n| `cockroach` CLI | Yes | Version check, cert inspection, SQL access |\n| `systemctl` | Yes | Service status, NTP verification |\n| `openssl` | Recommended | Certificate validation and expiry checks |\n| Standard Unix tools | Yes | `ps`, `ls`, `cat`, `grep`, `swapon`, `ulimit` |\n\n## Audit Depth\n\nThis skill supports two audit depths. Choose based on your needs:\n\n| Depth | When to Use | What It Does |\n|-------|-------------|--------------|\n| **Quick Scan** | Periodic checks, CI/CD gates, rapid triage | Runs one-liner shell/SQL commands per control, returns pass/fail |\n| **Full Audit** | Compliance evidence, external auditor requests, thorough assessments | Multi-step procedures per control from the official CIS benchmark |\n\nAt Step 0, confirm which depth the user wants. Both depths can be combined — run the quick scan first to identify failures, then run the full audit on those failures for evidence collection.\n\n## CIS Benchmark Structure\n\nThe CIS CockroachDB Benchmark v1.0.0 Level 1 profile contains 30 controls organized into six sections:\n\n| Section | Domain | Controls | Automated | Manual |\n|---------|--------|----------|-----------|--------|\n| 1 | Installation and Patches | 1.1–1.7 (7) | 3 | 4 |\n| 2 | System Hardening and Topology | 2.1–2.7 (7) | 5 | 2 |\n| 3 | Logging and Monitoring | 3.1–3.4 (4) | 1 | 3 |\n| 4 | User Access and Authorization | 4.1–4.4 (4) | 0 | 4 |\n| 5 | Data Protection | 5.1–5.3 (3) | 0 | 3 |\n| 6 | CockroachDB Settings | 6.1–6.5 (5) | 1 | 4 |\n\nSee [CIS controls reference](references/cis-controls.md) for full control definitions with both quick scan commands and full audit procedures.\n\n## Assessment Workflow\n\n### Step 0: Confirm Audit Scope\n\nBefore starting, confirm with the user:\n\n1. **Audit depth** — Quick scan, full audit, or both\n2. **Target nodes** — Which nodes to audit (all nodes recommended)\n3. **CockroachDB service account** — Username running CockroachDB (default: `cockroach`)\n4. **Certificate directory** — Path to TLS certificates (default: `/var/lib/cockroach/certs`)\n5. **Data directory** — Path to CockroachDB data store (default: `/var/lib/cockroach`)\n6. **Log directory** — Path to logs (default: `/var/lib/cockroach/logs` or `<store>/logs`)\n7. **Enterprise license** — Whether Enterprise features (EAR) are available\n\nRecord these values — they parameterize the checks below.\n\n### Step 1: Installation and Patches (Controls 1.1–1.7)\n\nThese controls verify binary integrity, systemd service management, TLS initialization, version uniformity, upgrade processes, and encryption at rest.\n\n**Key checks:**\n- 1.1: Binary downloaded from official source, SHA-256 verified (Manual)\n- 1.2: `systemctl is-enabled cockroach.service` → `enabled` (Automated)\n- 1.3: No `--insecure` flag; certs directory exists with valid CA-signed certs (Automated)\n- 1.4: `ps aux | grep -c insecure` → `0` (Automated)\n- 1.5: All nodes run same version — quick scan via `cockroach version`, full audit via DB Console Node List and `crdb_internal.gossip_nodes` (Automated)\n- 1.6: Rolling upgrade runbook exists and has been tested (Manual)\n- 1.7: `--enterprise-encryption` configured with external KMS, keys not on disk (Manual)\n\n### Step 2: System Hardening and Topology (Controls 2.1–2.7)\n\nThese controls verify OS-level configuration: time synchronization, process isolation, network segmentation, memory settings, file descriptors, and privilege restrictions.\n\n**Key checks (run on each node):**\n- 2.1: NTP/chrony active, offsets below `--max-offset` (Automated)\n- 2.2: One CockroachDB process per host (Automated)\n- 2.3: Dedicated subnet with restricted ingress on ports 26257/8080 (Manual)\n- 2.4: `swapon --show | wc -l` → `0` (Automated)\n- 2.5: THP set to `madvise` or `never` (Automated)\n- 2.6: File descriptor limit ≥ 15,000 for cockroach user (Automated)\n- 2.7: cockroach user has no sudo privileges (Automated)\n\n### Step 3: Logging and Monitoring (Controls 3.1–3.4)\n\nThese controls verify logging configuration, rotation, authentication event capture, and monitoring/alerting.\n\n**Key checks:**\n- 3.1: Log directory exists, owned by cockroach, permissions 700 (Manual)\n- 3.2: Log rotation configured via CockroachDB config or logrotate (Manual)\n- 3.3: Both `server.auth_log.sql_connections.enabled` AND `server.auth_log.sql_sessions.enabled` = `true` (Automated)\n- 3.4: Monitoring system scraping metrics, alerts configured (Manual)\n\n### Step 4: User Access and Authorization (Controls 4.1–4.4)\n\nThese controls verify host-based authentication, password security, root user hardening, and centralized identity management.\n\n> ⚠️ **Control 4.2 (Full Audit only):** Inspecting stored password hashes requires `SET allow_unsafe_internals = true`. This is a break-glass, audit-only operation — run only in a dedicated admin session by authorized operators, and reset immediately after with `SET allow_unsafe_internals = false`. Do not expose this to application workloads.\n\n**Key checks (all SQL):**\n- 4.1: HBA has specific IP ranges, strong auth methods, reject catch-all (Manual)\n- 4.2: `password_encryption` = `scram-sha-256`, stored hashes are SCRAM format (Manual)\n- 4.3: Root restricted to specific hosts via HBA with cert method, activity audited (Manual)\n- 4.4: OIDC/LDAP enabled, identity mapping configured, local accounts minimized (Manual)\n\n### Step 5: Data Protection (Controls 5.1–5.3)\n\nThese controls verify backup encryption, recovery testing, and multi-region data localization.\n\n**Key checks:**\n- 5.1: Backups use `kms` or `encryption_passphrase`, or storage-layer encryption enforced (Manual)\n- 5.2: Recovery procedures tested quarterly with documented results (Manual)\n- 5.3: Multi-region tables use `REGIONAL BY ROW` or zone constraints for data residency (Manual)\n\n### Step 6: CockroachDB Settings (Controls 6.1–6.5)\n\nThese controls verify certificate security, setting redaction, audit logging, session timeouts, and OCSP.\n\n**Key checks:**\n- 6.1: Private key permissions `0600`, certs valid 90+ days (Automated)\n- 6.2: Debug bundles use `--redact`, no secrets in logs (Manual)\n- 6.3: `sql.log.user_audit` non-empty, `sql.log.admin_audit.enabled` = `true`, security channels configured (Manual)\n- 6.4: `sql.defaults.idle_in_session_timeout` set (e.g., `15m`) (Manual)\n- 6.5: `security.ocsp.mode` = `strict` or `lax` (Manual)\n\n## Report Format\n\nGenerate a markdown report following the structure in [sample report](references/sample-report.md).\n\n**Status markers:**\n- `[PASS]` — Control is satisfied\n- `[FAIL]` — Control is not satisfied, remediation required\n- `[MANUAL]` — Requires human review; automated check cannot determine compliance\n- `[N/A]` — Control does not apply (e.g., EAR without Enterprise license)\n\n## Relationship to Other Security Skills\n\n| CIS Section | Related Remediation Skills |\n|-------------|---------------------------|\n| 1 Installation | [managing-tls-certificates](../managing-tls-certificates/SKILL.md), [enabling-cmek-encryption](../enabling-cmek-encryption/SKILL.md) |\n| 3 Logging | [configuring-audit-logging](../configuring-audit-logging/SKILL.md), [configuring-log-export](../configuring-log-export/SKILL.md) |\n| 4 User Access | [hardening-user-privileges](../hardening-user-privileges/SKILL.md), [enforcing-password-policies](../enforcing-password-policies/SKILL.md), [configuring-sso-and-scim](../configuring-sso-and-scim/SKILL.md) |\n| 5 Data Protection | [preparing-compliance-documentation](../prepare-compliance-docs/SKILL.md) |\n| 6 Settings | [managing-tls-certificates](../managing-tls-certificates/SKILL.md), [configuring-audit-logging](../configuring-audit-logging/SKILL.md) |\n\nFor a broader security posture assessment (covering Cloud and self-hosted), use [auditing-cloud-cluster-security](../audit-cloud-security/SKILL.md).\n\n## Safety Considerations\n\n- **All operations are read-only.** No cluster settings, OS configuration, or files are modified.\n- **Shell commands use read-only tools:** `ps`, `ls`, `cat`, `grep`, `systemctl is-enabled/is-active`, `swapon --show`, `ulimit`, `openssl x509`, `openssl verify`.\n- **SQL queries use SHOW and SELECT only.** No DDL or DML statements.\n- **One exception (4.2):** The full audit procedure for password hash inspection requires `SET allow_unsafe_internals = true` — this is a read-only break-glass operation that must be reset immediately after (`SET allow_unsafe_internals = false`). Only authorized operators should run this step.\n- **No secrets are logged.** Certificate private keys and passwords are not included in report output.\n- **Privilege check:** Some SQL queries require admin or VIEWACTIVITY privilege. The report notes any permission gaps.\n\n## References\n\n**Skill references:**\n- [CIS controls reference](references/cis-controls.md) — All 30 controls with quick scan commands and full audit procedures\n- [SQL queries for CIS audit](references/sql-queries.md) — All SQL queries used in the assessment\n- [Sample audit report](references/sample-report.md) — Example report with findings\n\n**Authoritative source:**\n- [CIS CockroachDB Benchmark (official repo)](https://github.com/cockroachlabs/CIS-benchmarks-crdb)\n\n**Related skills:**\n- [auditing-cloud-cluster-security](../audit-cloud-security/SKILL.md) — Broader security posture assessment\n- [configuring-audit-logging](../configuring-audit-logging/SKILL.md) — SQL audit logging setup\n- [hardening-user-privileges](../hardening-user-privileges/SKILL.md) — RBAC tightening\n- [enforcing-password-policies](../enforcing-password-policies/SKILL.md) — Password policy enforcement\n- [managing-tls-certificates](../managing-tls-certificates/SKILL.md) — TLS certificate management\n- [enabling-cmek-encryption](../enabling-cmek-encryption/SKILL.md) — Encryption at rest\n- [configuring-sso-and-scim](../configuring-sso-and-scim/SKILL.md) — SSO and SCIM provisioning\n\n**Official CockroachDB documentation:**\n- [Security Overview](https://www.cockroachlabs.com/docs/stable/security-reference/security-overview.html)\n- [Recommended Production Settings](https://www.cockroachlabs.com/docs/stable/recommended-production-settings.html)\n- [Releases](https://www.cockroachlabs.com/docs/releases/)\n- [Security Advisories](https://www.cockroachlabs.com/docs/advisories/)"
}