CockroachDB

View saved version →

---
name: analyze-range-distribution
description: Analyzes CockroachDB range distribution across tables and indexes using SHOW RANGES to identify range count, size patterns, leaseholder placement, and replication health. Use when investigating hotspots, uneven data distribution, range fragmentation, or validating zone configuration effects without DB Console access.
compatibility: Requires SQL access with admin role or ZONECONFIG system privilege. DETAILS option has high cost; use targeted queries with LIMIT. Production-safe for basic usage.
---

# Analyzing Range Distribution

Analyzes CockroachDB range distribution, leaseholder placement, and zone configuration compliance using `SHOW RANGES` and `SHOW ZONE CONFIGURATIONS` commands. Identifies range count anomalies, size imbalances, leaseholder hotspots, and replication issues - entirely via SQL without requiring DB Console access.

**Complement to profiling skills:** This skill analyzes range-level data distribution; for query performance patterns, see [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md). For schema change storage planning, see [analyzing-schema-change-storage-risk](../schema-change-storage-risk/SKILL.md).

## When to Use This Skill

- Identify tables/indexes with excessive range counts indicating fragmentation
- Detect range size imbalances or uneven data distribution across nodes
- Investigate leaseholder concentration causing read hotspots
- Validate zone configuration effects on range placement and replica distribution
- Diagnose range-level replication issues (under-replicated or unavailable ranges)
- Analyze range split patterns from high write volume
- SQL-only range analysis without DB Console access

**For schema change planning:** Use [analyzing-schema-change-storage-risk](../schema-change-storage-risk/SKILL.md) to estimate storage requirements before CREATE INDEX or ADD COLUMN operations.

## Prerequisites

- SQL connection to CockroachDB cluster
- Admin role OR `ZONECONFIG` system privilege
- Understanding of CockroachDB range architecture (default 512MB max size; verify with `SHOW ZONE CONFIGURATION FOR RANGE default`)
- Knowledge of cluster topology (node IDs, regions, availability zones)

**Check your privileges:**
```sql
SHOW SYSTEM GRANTS FOR <username>;  -- Should show admin or ZONECONFIG
```

See [permissions reference](references/permissions.md) for RBAC setup.

## Core Concepts

### Ranges: Units of Data Distribution

**Range:** Contiguous key space segment (default 512MB max size, configurable via zone config `range_max_bytes`)
**Raft group:** Each range replicated across nodes (default 3 replicas)
**Leaseholder:** Single replica handling reads and coordinating writes for a range

**Critical:** Ranges split automatically at `range_max_bytes` (default 512MB), but can fragment further due to load-based splitting during high write traffic.

### Leaseholders and Hotspots

**Leaseholder concentration:** Single node holding disproportionate leaseholders = read hotspot
**Load-based splitting:** CockroachDB splits ranges experiencing high QPS, increasing range count
**Hotspot symptoms:** High CPU on single node, slow reads on specific table/index

### Range Fragmentation

**Fragmentation:** Excessive range splits creating many small ranges (overhead from Raft coordination)
**Causes:** High write throughput, sequential inserts (timestamp-based primary keys), load-based splitting
**Symptoms:** High range count relative to data size, increased latency from Raft overhead

**Fragmentation metric:** Ranges per GB. With the 512MB default `range_max_bytes`, a fully-grown range covers 0.5 GB — so ~2 ranges/GB is the natural floor. Anything well above that (e.g., 10+ ranges/GB) suggests load-based splits or many small ranges; tune to your workload.

### Zone Configurations

**Zone config:** Replication and placement policies for databases, tables, or indexes
**Replication factor:** Number of replicas per range (default: 3)
**Constrai

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 2 — file references are metadata, not package contents.

View saved version →

---
name: audit-cloud-security
description: Audits the security posture of a CockroachDB cluster (Cloud or self-hosted) across network, authentication, authorization, encryption, audit logging, and backup dimensions. Use when assessing cluster security readiness, preparing for compliance reviews, or investigating security configuration gaps.
compatibility: Requires ccloud CLI authenticated via `ccloud auth login` and SQL access via cockroach sql with admin or VIEWACTIVITY privilege.
---

# 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

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 4 — file references are metadata, not package contents.

View saved version →

---
name: auditing-cis-benchmark
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.
compatibility: "CockroachDB self-hosted (all versions). Requires shell access to cluster nodes and SQL admin or VIEWACTIVITY privilege."
---

# Auditing CIS Benchmark Compliance

Assesses 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.

**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.

**Authoritative source:** This skill implements the benchmark defined at https://github.com/cockroachlabs/CIS-benchmarks-crdb

**Read-only audit:** All operations are read-only. No cluster state, OS configuration, or files are modified during the assessment.

## When to Use This Skill

- Preparing for a CIS benchmark compliance assessment or external audit
- Hardening a new self-hosted CockroachDB production deployment
- Validating security posture against industry-standard benchmarks
- Performing periodic compliance checks as part of security operations
- Mapping CockroachDB security controls to CIS Controls frameworks
- Responding to auditor requests for CIS benchmark evidence

## Prerequisites

**Access requirements:**

| Requirement | Purpose |
|-------------|---------|
| SSH/shell access to cluster nodes | OS-level checks (systemd, swap, THP, file descriptors, certs) |
| SQL access (admin or VIEWACTIVITY) | Cluster settings, user/role audit, logging config |
| Access to systemd service files | Service configuration verification |
| Access to certificate directory | Certificate permission and validity checks |

**Tools:**

| Tool | Required | Purpose |
|------|----------|---------|
| `cockroach` CLI | Yes | Version check, cert inspection, SQL access |
| `systemctl` | Yes | Service status, NTP verification |
| `openssl` | Recommended | Certificate validation and expiry checks |
| Standard Unix tools | Yes | `ps`, `ls`, `cat`, `grep`, `swapon`, `ulimit` |

## Audit Depth

This skill supports two audit depths. Choose based on your needs:

| Depth | When to Use | What It Does |
|-------|-------------|--------------|
| **Quick Scan** | Periodic checks, CI/CD gates, rapid triage | Runs one-liner shell/SQL commands per control, returns pass/fail |
| **Full Audit** | Compliance evidence, external auditor requests, thorough assessments | Multi-step procedures per control from the official CIS benchmark |

At 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.

## CIS Benchmark Structure

The CIS CockroachDB Benchmark v1.0.0 Level 1 profile contains 30 controls organized into six sections:

| Section | Domain | Controls | Automated | Manual |
|---------|--------|----------|-----------|--------|
| 1 | Installation and Patches | 1.1–1.7 (7) | 3 | 4 |
| 2 | System Hardening and Topology | 2.1–2.7 (7) | 5 | 2 |
| 3 | Logging and Monitoring | 3.1–3.4 (4) | 1 | 3 |
| 4 | User Access and Authorization | 4.1–4.4 (4) | 0 | 4 |

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 3 — file references are metadata, not package contents.

View saved version →

---
name: auditing-table-statistics
description: Audits optimizer table statistics for staleness, missing coverage, and data quality issues using SHOW STATISTICS. Use when diagnosing poor query performance, unexpected plan changes, or after bulk data changes to identify stale statistics requiring refresh via CREATE STATISTICS.
compatibility: Requires SQL access with any privilege on target tables (SELECT, INSERT, UPDATE, DELETE, or admin). Uses SHOW STATISTICS (production-safe, read-only).
---

# Auditing Table Statistics

Audits optimizer table statistics for staleness, missing column coverage, and row count drift to diagnose poor query performance caused by outdated or incomplete statistics. Uses `SHOW STATISTICS` for read-only SQL analysis of table-level and column-level statistics freshness, entirely without requiring DB Console access.

**Complement to profiling-statement-fingerprints:** This skill diagnoses optimizer statistics issues; for identifying historically slow queries, see [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md).

## Prerequisites

- SQL connection with any privilege on target tables
- Automatic statistics collection enabled (default): `sql.stats.automatic_collection.enabled = true`

**Related skills:** [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md) for historical query analysis, [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) for live triage.

## Core Concepts

**CockroachDB-specific defaults:**
- Automatic collection triggers at ~20% row count change (`sql.stats.automatic_collection.fraction_stale_rows`)
- Auto-collection covers index column groups (when `sql.stats.multi_column_collection.enabled = true`, the default); ad-hoc multi-column stats on non-indexed columns require manual `CREATE STATISTICS`
- Large tables (>10M rows) may have delayed auto-collection
- Staleness thresholds: refresh if >7 days (OLTP) or >30 days (OLAP), or >20-30% row count drift

See [references/statistics-thresholds.md](references/statistics-thresholds.md) for workload-specific guidance.

## Core Diagnostic Queries

### Query 1: Identify Tables with Stale or Missing Statistics

Finds tables with outdated statistics or no statistics at all, ranked by staleness.

```sql
WITH table_stats AS (
  SELECT
    table_catalog,
    table_schema,
    table_name,
    column_names,
    row_count,
    created,
    now() - created AS stats_age
  FROM [SHOW STATISTICS FOR TABLE database_name.*]  -- Replace database_name
  WHERE column_names = '{}'  -- Table-level stats only (empty array)
)
SELECT
  table_schema || '.' || table_name AS full_table_name,
  row_count,
  created AS stats_created_at,
  stats_age,
  CASE
    WHEN created IS NULL THEN 'Missing statistics'
    WHEN stats_age > INTERVAL '30 days' THEN 'Very stale (>30d)'
    WHEN stats_age > INTERVAL '7 days' THEN 'Stale (>7d)'
    ELSE 'Fresh'
  END AS staleness_status
FROM table_stats
WHERE stats_age > INTERVAL '7 days' OR created IS NULL  -- Adjust threshold
ORDER BY stats_age DESC NULLS FIRST
LIMIT 50;
```

**Customization:**
- Replace `database_name.*` with specific schema pattern (e.g., `mydb.public.*`)
- Adjust staleness threshold: `INTERVAL '7 days'` for OLTP, `'30 days'` for OLAP
- Increase `LIMIT` to see more tables

**Key columns:**
- `staleness_status`: Quick classification of statistics freshness
- `stats_age`: Exact time since last collection
- `row_count`: Last known table size

### Query 2: Audit Statistics for Specific Table

Shows all statistics for a single table, including table-level and per-column details.

```sql
SELECT
  column_names,
  row_count,
  distinct_count,
  null_count,
  created,
  now() - created AS stats_age,
  CASE
    WHEN histogram_id IS NOT NULL THEN 'Yes'
    ELSE 'No'
  END AS has_histogram
FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
ORDER BY
  CASE WHEN column_names = '{}' THEN 0 ELSE 1 END,  -- Table-level first
  created DESC;
```

**Customiz

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 2 — file references are metadata, not package contents.

View saved version →

---
name: benchmark-transactions
description: Guides benchmarking and comparing explicit multi-statement transactions versus single-statement CTE transactions in CockroachDB, with fair test methodology, contention analysis, and performance interpretation. Use when comparing transaction formulations, benchmarking CockroachDB workloads under contention, investigating retry pressure, or deciding whether to rewrite multi-step application flows into single SQL statements.
compatibility: "CockroachDB >= 22.1. Requires SQL access and a test cluster for benchmark execution. Do not run benchmarks against production workloads."
---

# Benchmarking Transaction Patterns

Guides users through benchmarking, explaining, and comparing two formulations of the same transactional business workflow in CockroachDB: explicit multi-statement transactions versus single-statement CTE transactions. Focuses on performance under contention, fair test methodology, and result interpretation.

**Complement to design skills:** For general transaction design principles, see [designing-application-transactions](../design-application-txns/SKILL.md). For SQL syntax and query patterns, see [cockroachdb-sql](../cockroachdb-sql/SKILL.md).

## Core Concept

Under contention, the transaction formulation itself is a primary performance lever. The **explicit model** (multi-statement `BEGIN`/`COMMIT`) keeps the transaction open across round trips, widening the contention window. The **CTE model** (single-statement) collapses the same logic into one atomic statement, reducing transaction duration and retries.

### Explicit Transaction Model

```sql
BEGIN;
SELECT balance FROM accounts WHERE id = $1;
-- Application decides whether transfer is allowed
UPDATE accounts SET balance = balance - $2 WHERE id = $1;
UPDATE accounts SET balance = balance + $2 WHERE id = $3;
INSERT INTO transfers (from_acct, to_acct, amount, created_at)
VALUES ($1, $3, $2, now());
COMMIT;
```

### CTE Transaction Model

CockroachDB rejects multiple mutations of the same table in a single statement by default (`sql.multiple_modifications_of_table.enabled`), so the debit and credit are folded into one `UPDATE` using `CASE`.

```sql
WITH funded AS (
  SELECT 1 FROM accounts WHERE id = $1 AND balance >= $2
), upd AS (
  UPDATE accounts
  SET balance = CASE WHEN id = $1 THEN balance - $2 ELSE balance + $2 END
  WHERE id IN ($1, $3) AND EXISTS (SELECT 1 FROM funded)
  RETURNING id
), ins AS (
  INSERT INTO transfers (from_acct, to_acct, amount, created_at)
  SELECT $1, $3, $2, now() WHERE (SELECT count(*) FROM upd) = 2
  RETURNING id
)
SELECT id FROM ins;
```

## Steps

### 1. Prepare the Benchmark Environment

Set up a dedicated test database and schema. Do not mix benchmark workloads with other traffic.

```sql
CREATE DATABASE IF NOT EXISTS bankbench;
USE bankbench;

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance DECIMAL(18,2) NOT NULL DEFAULT 0
);

CREATE TABLE transfers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  from_acct INT NOT NULL,
  to_acct INT NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

### 2. Seed the Test Data

Use multi-row UPSERT for efficient seeding. Single-row inserts distort setup cost.

```sql
INSERT INTO accounts (id, balance)
SELECT generate_series(1, 10000), 1000.00
ON CONFLICT (id) DO UPDATE SET balance = 1000.00;
```

### 3. Run the Explicit Transaction Benchmark

Execute with realistic concurrency. Example using `pgbench` (PostgreSQL-compatible):

```bash
# Create a pgbench script file: explicit_transfer.sql
# \set from_id random(1, 10000)
# \set to_id random(1, 10000)
# \set amount 10.00
# BEGIN;
# SELECT balance FROM accounts WHERE id = :from_id;
# UPDATE accounts SET balance = balance - :amount WHERE id = :from_id;
# UPDATE accounts SET balance = balance + :amount WHERE id = :to_id;
# INSERT INTO transfers (from_acct, to_acct, amount, created_at) VALUES (:from_id, :to_id, :amount, now());
# COMMIT;

pgbenc

Preview of the first 4,000 characters. Read full instructions →

View saved version →

---
name: cockroachdb-sql
description: Use when writing, generating, or optimizing SQL for CockroachDB, designing CockroachDB schemas, or when the user asks about CockroachDB-specific SQL patterns, type mappings, and distributed database best practices. Also use when encountering CockroachDB anti-patterns like missing primary keys, sequential ID hotspots, or incorrect type usage.
compatibility: Can work with or without connection to a database. Without connection it generates the SQL and gives instruction for connection.With connection it requires appropriate privilege on target database and tables (SELECT, INSERT, UPDATE, DELETE, or admin).
---

## CockroachDB SQL Skill  

Converts natural language questions into CockroachDB-compliant SQL queries, following CockroachDB best practices. Use it for schema design, writing queries and optimizing query.

## How to Apply this Skill

1. **Connection Detection** — already performed on skill invocation; reuse active connection.

2. **Parse Natural Language Intent**
   - Identify the operation type (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, etc.)

3. **Context Gathering**
   - Check for existing schema context in conversation
   - If connected to DB, query existing schema:
     - `SHOW TABLES;` to see existing tables
     - `SHOW CREATE TABLE table_name;` for existing structure
   - Ask clarifying questions if needed:
     - Table structure if not provided
     - Data types for columns
     - Index requirements
     - Multi-region needs
     - Performance characteristics

4. **Apply CockroachDB Rules**
   - Reference rules in `references/cockroachdb-rules/` 
   - Ensure compliance with CockroachDB best practices
   - **Determine rule category based on operation and apply the relavant rules**:
     * `00-fundamental-principles.md` - Always apply these first
     * `01-schema-design.md` - Table creation and structure
     * `02-dml-operations.md` - Data modification
     * `03-query-patterns.md` - Query construction
     * `04-optimization.md` - Performance, Optimization and anti-patterns
     * `05-operational.md` - Admin and maintenance
   - Validate against anti-patterns in 04-optimization.md 

5. **Validate against DB(MANDATORY)**
   - ALWAYS run EXPLAIN on every generated SQL query when connected to DB.
   - If EXPLAIN returns a parsing/syntax error, fix the query and re-run EXPLAIN until it passes.
   - Include the EXPLAIN output in the response.

## Response Behavior

### Initial Response

When skill is invoked:
1. **Check for an available connection** so queries run against the right cluster:
   - Check if a connection string is provided in the prompt (postgresql://...).
     - If provided, use `cockroach sql --url "<provided-url>" -e "SQL"` to run queries. Do not use psql.
   - Else check the COCKROACH_URL environment variable (`echo $COCKROACH_URL`).
     - If set, use `cockroach sql --url $COCKROACH_URL -e "SQL"` to run queries. Do not use psql.
   - Else check for cockroach-cloud MCP server availability.
   - If none of these are available or it is unclear which cluster to use, ask the user before running anything.

2. Focus exclusively on CockroachDB
3. Emphasize "natural language to CockroachDB SQL" not "database conversion"
4. Present CockroachDB-specific syntax even when a rule derives from PostgreSQL compatibility.

### Output Format
- Show generated SQL with explanatory comments
- List CockroachDB-specific features used
- Include performance considerations
- When optimizing, at each step 1- Explain the step's purpose. 2- Execute the step and report the outcome. 3- Summarize all findings and actions taken.
- Provide references used including the rules

## Examples

### Schema Design — UUID Primary Key (Avoid Hotspots)

```sql
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL,
  status STRING NOT NULL DEFAULT 'pending',
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  INDEX idx_orders_c

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 7 — file references are metadata, not package contents.

View saved version →

---
name: configure-private-network
description: Configures private network connectivity for CockroachDB Cloud clusters including AWS PrivateLink, GCP Private Service Connect, Azure Private Link, egress private endpoints, and VPC peering. Use when setting up private endpoints to eliminate public internet exposure, configuring egress to external services like Kafka, or establishing VPC peering.
compatibility: Requires CockroachDB Cloud Advanced or Standard plan. Private endpoints require cloud provider configuration (AWS, GCP, or Azure). VPC peering requires Advanced plan.
---

# Configuring Private Connectivity

Configures private network connectivity for CockroachDB Cloud clusters to eliminate public internet exposure for database traffic. Covers ingress private endpoints (AWS PrivateLink, GCP Private Service Connect, Azure Private Link), egress private endpoints for outbound connections to external services, and VPC peering.

## When to Use This Skill

- Setting up private endpoints to eliminate public internet exposure for database connections
- Configuring egress private endpoints for CDC changefeeds to Confluent Kafka or other external services
- Establishing VPC peering between a CockroachDB Cloud cluster and application VPCs
- Troubleshooting DNS resolution issues with private endpoints
- Resolving "stuck pending" or connection failure errors with private endpoints
- Automating private connectivity setup with Terraform

## Prerequisites

- **CockroachDB Cloud cluster** — Standard or Advanced plan (VPC peering requires Advanced)
- **ccloud CLI** authenticated with Cluster Admin role
- **Cloud provider access:**
  - **AWS:** IAM permissions to create VPC endpoints, modify DNS, and manage security groups
  - **GCP:** Permissions to create Private Service Connect endpoints and DNS records
  - **Azure:** Permissions to create private endpoints and manage DNS zones
- **Cluster ID and cloud provider details** from `ccloud cluster info`

**Verify access:**
```bash
ccloud auth whoami
ccloud cluster info <cluster-name> -o json
```

See [ccloud commands reference](references/ccloud-commands.md) for full command syntax.

## Configuration Decisions

Before proceeding, determine which connectivity types and cloud provider apply to the user's environment. Ask which options are relevant, then follow only the corresponding sections below.

**Decision 1 — Connectivity type(s) needed:**
- **Ingress private endpoints:** Applications connect to CockroachDB over a private network path (AWS PrivateLink, GCP Private Service Connect, Azure Private Link). Most common use case.
- **Egress private endpoints:** CockroachDB connects outbound to external services (e.g., Confluent Kafka for CDC) over a private path.
- **VPC peering:** Direct network connection between the application VPC and the CockroachDB Cloud VPC. Requires Advanced plan.
- **Combination:** Multiple connectivity types can be configured together.

**Decision 2 — Cloud provider:**
- **AWS:** Use AWS PrivateLink for ingress, AWS VPC peering for peering.
- **GCP:** Use GCP Private Service Connect for ingress, GCP VPC peering for peering.
- **Azure:** Use Azure Private Link for ingress. VPC peering is not available for Azure.

## Steps

### Part 1: Ingress Private Endpoints

> Follow this part only if the user selected **Ingress private endpoints** in Decision 1. Follow only the subsection (1.2, 1.3, or 1.4) matching the user's cloud provider from Decision 2.

Private endpoints allow applications in your VPC to connect to CockroachDB Cloud without traversing the public internet.

#### 1.1 Get the Private Endpoint Service

Get the private endpoint service information from the **Cloud Console** or **Cloud API**:

**Cloud Console:** Navigate to your cluster's **Networking > Private endpoint** tab. The service name/ID is displayed.

**Cloud API:**
```bash
curl "https://cockroachlabs.cloud/api/v1/clusters/<cluster-id>/networking/private-endpoint-services" \
  -H "Authorization: Bearer <api-k

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 2 — file references are metadata, not package contents.

View saved version →

---
name: configuring-audit-logging
description: Configures SQL audit logging on CockroachDB clusters to capture security-relevant events including authentication, privilege changes, and sensitive data access. Use when enabling audit logging for compliance, setting up role-based audit policies, or verifying audit configuration.
compatibility: Requires admin role for cluster setting changes. Role-based audit logging available on CockroachDB 22.2+.
---

# Configuring Audit Logging

Configures SQL audit logging on CockroachDB clusters to capture security-relevant events such as authentication attempts, privilege changes, DDL operations, and sensitive data access. Supports both cluster-wide audit settings and role-based audit policies for targeted logging.

## When to Use This Skill

- Enabling audit logging to meet SOC 2, HIPAA, or PCI DSS compliance requirements
- Setting up role-based audit policies for specific users or roles
- Verifying that audit logging is properly configured and capturing events
- Responding to a security audit finding about missing audit trails
- Investigating security incidents by reviewing audit log configuration

## Prerequisites

- **SQL access** with admin role (required to modify cluster settings)
- **CockroachDB version:** 22.2+ for role-based audit logging
- **Log export** configured for persistent audit trail (CockroachDB Cloud exports logs to your cloud provider)
- **Storage planning:** Audit logging increases log volume; plan for additional storage

**Check your access:**
```sql
-- Verify admin role
SELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();

-- Check CockroachDB version
SELECT version();
```

## Steps

### 1. Check Current Audit Configuration

```sql
-- User audit logging configuration
SHOW CLUSTER SETTING sql.log.user_audit;

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

-- All audit-related settings
SELECT variable, value
FROM [SHOW ALL CLUSTER SETTINGS]
WHERE variable LIKE '%audit%'
ORDER BY variable;
```

See [SQL queries reference](references/sql-queries.md) for additional audit-related queries.

### 2. Enable Admin Audit Logging

Admin audit logging captures all SQL statements executed by users with the admin role.

```sql
-- Enable admin audit logging
SET CLUSTER SETTING sql.log.admin_audit.enabled = true;
```

**What is captured:**
- All SQL statements executed by admin users
- DDL operations (CREATE, ALTER, DROP)
- Grant and revoke operations
- Cluster setting changes

### 3. Configure Role-Based Audit Logging

Role-based audit logging allows targeted logging for specific roles. This is more efficient than cluster-wide logging.

```sql
-- Enable audit logging for a specific role
-- Format: <role_name> <audit_mode>
-- Audit modes: ALL (all statements), READ (reads only), WRITE (writes only), NONE (disable)
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';
```

**Multiple roles:**
```sql
-- Audit multiple roles (newline-separated)
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL
security_admin ALL
app_service_account READ';
```

**Create purpose-specific audit roles:**
```sql
-- Create a role for users accessing sensitive data
CREATE ROLE sensitive_data_reader;
GRANT SELECT ON TABLE customers, payments, pii_table TO sensitive_data_reader;

-- Assign users to the audited role
GRANT sensitive_data_reader TO app_user;

-- Enable audit logging for this role
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';
```

### 4. Configure Slow Query Logging (Supplemental)

Slow query logging captures queries exceeding a latency threshold, which can indicate unauthorized scans or data exfiltration attempts.

```sql
-- Log queries taking longer than 1 second
SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '1s';

-- Log all queries (high overhead — use only for investigation)
-- SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '0';
```

### 5. Verify Audit Logging

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: configuring-ip-allowlists
description: Configures and hardens IP allowlists for CockroachDB Cloud clusters to restrict network access to authorized CIDR ranges. Use when tightening network security, removing overly permissive allowlist entries like 0.0.0.0/0, or setting up allowlists for a new cluster.
compatibility: Requires ccloud CLI with Cluster Admin or Cluster Operator role.
---

# Configuring IP Allowlists

Configures and hardens IP allowlists on CockroachDB Cloud clusters to restrict SQL and DB Console access to authorized CIDR ranges. Identifies overly permissive entries (such as `0.0.0.0/0`) and replaces them with specific, narrow ranges.

## When to Use This Skill

- Removing `0.0.0.0/0` (open to all) from the IP allowlist
- Restricting network access after initial cluster setup
- Adding office, VPN, or CI/CD CIDR ranges to the allowlist
- Reviewing and tightening existing allowlist entries
- Responding to a security audit finding about overly broad network access

## Prerequisites

- **ccloud CLI** installed and authenticated (`ccloud auth login`)
- **Cloud Console role:** Cluster Admin or Cluster Operator
- **Known CIDR ranges:** Office IPs, VPN egress IPs, CI/CD runner IPs, or other authorized sources
- **Cluster ID:** Available from `ccloud cluster list`

**Verify access:**
```bash
ccloud auth whoami
ccloud cluster list
```

## Steps

### 1. List Current Allowlist Entries

```bash
# List all IP allowlist entries for the cluster
ccloud cluster networking allowlist list <cluster-id> -o json
```

Review each entry. Flag any of these as overly permissive:
- `0.0.0.0/0` — Open to all IPv4 addresses
- `/8` ranges — 16 million+ addresses
- `/16` ranges — 65,000+ addresses
- Unknown or undocumented entries

See [ccloud commands reference](references/ccloud-commands.md) for full command syntax.

### 2. Understand Allowlist Limits

CockroachDB Cloud clusters have a maximum number of IP allowlist entries per cluster. If you need more entries than the limit allows:

- **Consolidate entries:** Use broader CIDR ranges where security permits (e.g., combine several `/32` entries into a `/24`)
- **Use private endpoints:** Switch to [private endpoints](../configure-private-network/SKILL.md) instead of allowlists for VPC-based access — private endpoints bypass the allowlist entirely
- **Request a limit increase:** Contact CockroachDB Cloud support if consolidation and private endpoints are not sufficient

### 3. Identify Required CIDR Ranges

Before modifying the allowlist, document all legitimate access sources:

| Source | CIDR | SQL Access | UI Access |
|--------|------|------------|-----------|
| Office network | `203.0.113.0/24` | Yes | Yes |
| VPN egress | `198.51.100.0/24` | Yes | Yes |
| CI/CD runners | `192.0.2.0/28` | Yes | No |
| Monitoring | `10.0.1.5/32` | Yes | No |

### 4. Add Specific CIDR Entries

```bash
# Add a specific CIDR range (CIDR is a positional argument)
ccloud cluster networking allowlist create <cluster-name> <cidr> \
  --sql \
  --ui \
  --name "<description>"
```

**Examples:**
```bash
# Office network — SQL and UI access
ccloud cluster networking allowlist create <cluster-name> 203.0.113.0/24 \
  --sql \
  --ui \
  --name "Office network"

# CI/CD runners — SQL only
ccloud cluster networking allowlist create <cluster-name> 192.0.2.0/28 \
  --sql \
  --name "CI/CD runners"

# Single IP — /32 for maximum specificity
ccloud cluster networking allowlist create <cluster-name> 198.51.100.42/32 \
  --sql \
  --ui \
  --name "Developer workstation"
```

### 5. Remove Overly Permissive Entries

```bash
# Delete the 0.0.0.0/0 entry (or other overly broad entries)
ccloud cluster networking allowlist delete <cluster-name> 0.0.0.0/0
```

**Important:** Only remove `0.0.0.0/0` after confirming your specific CIDR entries are in place and tested.

### 6. Verify the Updated Allowlist

```bash
# Confirm the final allowlist
ccloud cluster networking allowlist list <cluster-id> -o json
```

Test connectivity 

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: configuring-log-export
description: Configures log and metric export for CockroachDB Cloud clusters to external monitoring services including AWS CloudWatch, GCP Cloud Logging, and Datadog. Use when setting up log export for audit compliance, configuring metric export for monitoring, or troubleshooting log delivery issues.
compatibility: Requires CockroachDB Cloud Advanced plan. Requires ccloud CLI with Cluster Admin role and cloud provider IAM configuration.
---

# Configuring Log Export

Configures log and metric export for CockroachDB Cloud clusters to deliver cluster logs, audit logs, and performance metrics to external monitoring services. Supports AWS CloudWatch, GCP Cloud Logging, and third-party integrations like Datadog.

## When to Use This Skill

- Setting up log export to AWS CloudWatch or GCP Cloud Logging
- Configuring metric export to CloudWatch or Datadog
- Verifying that SQL audit logs are being exported to an external sink
- Troubleshooting log export errors or missing logs
- Estimating CloudWatch or logging service costs for CockroachDB log volume
- Configuring log export via Terraform

## Prerequisites

- **CockroachDB Cloud Advanced plan** — Log export is not available on Basic or Standard plans
- **ccloud CLI** authenticated with Cluster Admin role
- **Cloud provider setup:**
  - **AWS:** CloudWatch Logs group created, IAM role granting CockroachDB Cloud cross-account access
  - **GCP:** Cloud Logging API enabled, service account with Logs Writer role
- **Cluster ID:** Available from `ccloud cluster list`

**Verify access:**
```bash
ccloud auth whoami
ccloud cluster info <cluster-name> -o json
# Look for "plan": "ADVANCED"
```

## Configuration Decisions

Before proceeding, determine which export destinations apply to the user's environment. Ask which options are relevant, then follow only the corresponding sections below.

**Decision 1 — Log export destination:**
- **AWS CloudWatch:** Use when the cluster runs on AWS and logs should go to CloudWatch Logs. Requires IAM cross-account role setup.
- **GCP Cloud Logging:** Use when the cluster runs on GCP. Requires a service account with Logs Writer role.

**Decision 2 — Metric export destination:**
- **CloudWatch:** Use when metrics should go to AWS CloudWatch. Requires IAM role with `cloudwatch:PutMetricData` permission.
- **Datadog:** Use when metrics should go to Datadog. Requires a Datadog API key and site.
- **Skip:** No metric export needed at this time.

## Steps

### 1. Check Current Log Export Configuration

```bash
# Check if log export is currently configured
ccloud cluster info <cluster-name> -o json
# Look for "log_export_config" in the output
```

### 2. Set Up Log Export to AWS CloudWatch

> Follow this section only if the user selected **AWS CloudWatch** in Decision 1. Skip to Step 3 if using GCP Cloud Logging.

#### 2.1 Create a CloudWatch Log Group

```bash
# Create a log group in AWS (if it doesn't exist)
aws logs create-log-group \
  --log-group-name cockroachdb-<cluster-name> \
  --region <aws-region>

# Set retention policy (recommended)
aws logs put-retention-policy \
  --log-group-name cockroachdb-<cluster-name> \
  --retention-in-days 90 \
  --region <aws-region>
```

#### 2.2 Create an IAM Role for CockroachDB Cloud

See [cloud provider setup reference](references/cloud-provider-setup.md) for the complete IAM role policy.

The IAM role must:
- Trust the CockroachDB Cloud AWS account as an allowed principal
- Grant `logs:CreateLogStream`, `logs:PutLogEvents`, `logs:DescribeLogGroups`, `logs:DescribeLogStreams` permissions
- Be scoped to the specific log group

#### 2.3 Enable Log Export

```bash
# Enable log export to CloudWatch
ccloud cluster log-export create <cluster-id> \
  --log-group-name cockroachdb-<cluster-name> \
  --auth-principal <iam-role-arn> \
  --type AWS_CLOUDWATCH \
  --region <aws-region>
```

### 3. Set Up Log Export to GCP Cloud Logging

> Follow this section only if the user selected **GCP Cloud Loggin

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: configuring-sso-and-scim
description: Configures SSO authentication and SCIM 2.0 provisioning for CockroachDB across four distinct layers — Cloud Console SSO (SAML/OIDC), DB Console SSO (OIDC), SQL/Cluster SSO (JWT or LDAP/AD), and SCIM 2.0 automated provisioning. Use when enabling centralized identity management, setting up SSO for compliance, or automating user lifecycle management.
compatibility: Requires CockroachDB Cloud organization admin for Console SSO/SCIM. Requires cluster admin for DB Console and SQL SSO configuration. DB Console SSO requires Advanced or Enterprise plan (not available on Standard/Basic). LDAP/AD authentication is self-hosted only (not available on CockroachDB Cloud). SCIM 2.0 requires Enterprise plan.
---

# Configuring SSO and SCIM

Configures Single Sign-On (SSO) and SCIM 2.0 provisioning for CockroachDB across four distinct layers:

1. **Cloud Console SSO** — SAML or OIDC for the CockroachDB Cloud web console
2. **DB Console SSO** — OIDC for the DB Console web UI (Advanced/Enterprise only)
3. **SQL/Cluster SSO** — JWT-based or LDAP/AD authentication for SQL client connections
4. **SCIM 2.0** — Automated user provisioning on the Cloud Console

## Prerequisites

- **Console SSO/SCIM:** Organization Admin + `ccloud` CLI
- **DB Console/SQL SSO:** Cluster admin role + Advanced or Enterprise plan (DB Console SSO not on Standard/Basic)
- **LDAP/AD:** Self-hosted only (not available on CockroachDB Cloud)
- **SCIM 2.0:** Enterprise plan required

## Configuration Decisions

Before proceeding, determine which layers the user needs. Ask which of the following apply, then follow only the relevant Parts below.

**Decision 1 — Cloud Console SSO:**
- **SAML:** Best for enterprise IdPs with existing SAML infrastructure (Okta, Azure AD, PingOne)
- **OIDC:** Simpler setup, supports Google Workspace and modern IdPs natively
- **Skip:** Not needed

**Decision 2 — DB Console SSO:**
- **Enable (OIDC):** SSO for the DB Console web UI. Requires Advanced or Enterprise plan — not available on Standard or Basic
- **Skip:** Not needed or plan does not support it

**Decision 3 — SQL/Cluster SSO method:**
- **JWT-based:** Cloud-native approach using JWT tokens for SQL client connections. Works on both CockroachDB Cloud and self-hosted clusters
- **LDAP/AD:** Direct directory authentication against Active Directory or OpenLDAP. Self-hosted only (not available on CockroachDB Cloud)
- **Skip:** Not needed

**Decision 4 — SCIM 2.0 provisioning:**
- **Enable:** Automates user lifecycle (create/update/deactivate) between IdP and Cloud Console. Requires Enterprise plan
- **Skip:** Manage Cloud Console users manually

## Steps

### Part 1: Cloud Console SSO

Cloud Console SSO enables SAML or OIDC authentication for the CockroachDB Cloud web console.

#### 1.1 Check Current SSO Configuration

Check SSO status in the Cloud Console UI (Organization Settings > Authentication).

#### 1.2a Configure SAML SSO

Follow this section if the user selected **SAML** in Decision 1.

1. Navigate to **Organization Settings > Authentication** in the Cloud Console
2. Select **SAML** as the SSO provider type
3. Configure the SAML connection:
   - Enter the IdP metadata URL or upload the metadata XML
   - Copy the CockroachDB Cloud **ACS URL** and **Entity ID** to your IdP
   - Map IdP attributes to CockroachDB Cloud user fields (email, name)
4. In your IdP, create a SAML 2.0 application:
   - Set the **Single sign-on URL** to the CockroachDB Cloud ACS URL
   - Set the **Audience URI (SP Entity ID)** to the CockroachDB Cloud Entity ID
   - Set **Name ID format** to Email Address
   - Map attributes: `email`, `firstName`, `lastName`
5. Assign users or groups to the SAML application in the IdP

See [configuration steps reference](references/configuration-steps.md) for IdP-specific SAML instructions (Okta, Azure AD).

#### 1.2b Configure OIDC SSO

Follow this section if the user selected **OIDC** in Decision 1.

1. Navigate to **Organization Setti

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: design-application-txns
description: Guides application developers in designing correct and performant transaction patterns for CockroachDB, covering transaction lifetime, implicit vs explicit transactions, retry handling with exponential backoff, pushing invariants into SQL, selective pessimistic locking, set-based operations, connection pooling, prepared statements, keyset pagination, follower reads, and separating business logic from database logic. Use when building applications on CockroachDB, designing transaction workflows, handling retries, optimizing application-layer database interactions, or configuring connection pools.
compatibility: "CockroachDB >= 22.1. Works with or without a live database connection. With connection, requires appropriate privileges on target tables."
---

# Designing Application Transactions

Guides application developers through the design principles and implementation patterns needed to build correct, performant, and resilient applications on CockroachDB. Covers the full spectrum from transaction scoping and retry logic to connection pooling and observability.

**Complement to SQL skills:** For SQL syntax, schema design, and query optimization, see [cockroachdb-sql](../cockroachdb-sql/SKILL.md). For benchmarking transaction formulations under contention, see [benchmarking-transaction-patterns](../benchmark-transactions/SKILL.md).

## When to Use This Skill

- Designing transaction boundaries for a CockroachDB application
- Implementing client-side retry logic with exponential backoff
- Deciding between implicit and explicit transactions
- Choosing between optimistic and pessimistic concurrency control
- Replacing read-modify-write loops with atomic SQL
- Configuring connection pools (HikariCP, pgbouncer, etc.)
- Implementing keyset pagination instead of OFFSET/LIMIT
- Using follower reads for reporting and analytics queries
- Separating business orchestration from database transactions
- Using prepared statements for performance and security
- Selecting explicit column projections instead of SELECT *
- Testing application behavior under concurrency
- Monitoring application-level database performance

## Prerequisites

- Familiarity with CockroachDB's SERIALIZABLE isolation level
- Understanding of ACID transaction semantics
- Access to application source code for transaction design changes
- SQL connection to a CockroachDB cluster (for testing and validation)

## Steps

### 1. Keep Transactions Short-Lived

Transactions must include only the minimal set of SQL operations needed for one atomic state change. Do not place remote API calls, service-to-service requests, loops, expensive computation, or artificial waits inside a CockroachDB transaction.

Long-lived transactions increase intent lifetime, contention, and retry probability in CockroachDB's distributed, optimistic-concurrency architecture.

**Anti-pattern:**

```java
@Transactional
public void createOrder(Order order) {
    orderRepository.save(order);
    paymentGateway.charge(order); // external call inside TX
}
```

**Correct approach — split the logic:**

```java
@Transactional
public void createOrderRecord(Order order) {
    orderRepository.save(order);
}

// Outside the transaction
paymentGateway.charge(order);
```

**Why it matters:**
- Active intents block concurrent writers, reducing cluster throughput
- Competing transactions are more likely to encounter `40001` retry errors
- External work inside a retried transaction may run twice, causing duplicate side effects
- Long transactions tie up connections and memory, reducing concurrency

### 2. Use Implicit Transactions for Single Statements

CockroachDB automatically wraps each individual SQL statement as a transaction in autocommit mode. For single `INSERT`, `UPDATE`, `DELETE`, or `SELECT` statements, do not wrap in explicit `BEGIN`/`COMMIT`.

**Preferred:**

```sql
INSERT INTO orders (id, status)
VALUES (gen_random_uuid(), 'open');
```

**Avoid:**

```sql
BEGIN;
I

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: design-multi-region-apps
description: Guides developers in selecting and implementing multi-region patterns for CockroachDB applications, covering active-passive vs active-active architectures, REGIONAL BY ROW, GLOBAL tables, manual geo-partitioning with lease preferences, and live demo setup with validation queries. Use when designing multi-region database topologies, choosing between REGIONAL BY ROW and manual partitioning, building multi-region demos, or optimizing cross-region latency.
compatibility: "CockroachDB >= 22.1 with multi-region licensed features. Requires a multi-region cluster or cockroach demo with locality flags."
---

# Designing Multi-Region Applications

Guides developers through selecting the right multi-region pattern for their CockroachDB application and implementing it with proper validation. Covers the decision model for choosing between regular regional tables, `REGIONAL BY ROW`, `GLOBAL` tables, and manual geo-partitioning, plus a hands-on demo framework for comparing approaches.

**Complement to other skills:** For transaction design patterns, see [designing-application-transactions](../design-application-txns/SKILL.md). For SQL syntax and schema design, see [cockroachdb-sql](../cockroachdb-sql/SKILL.md).

## When to Use This Skill

- Deciding how to model multi-region read/write behavior in CockroachDB
- Choosing between active-active and active-passive architectures
- Evaluating `REGIONAL BY ROW` vs manual geo-partitioning
- Understanding `GLOBAL` table behavior and trade-offs
- Designing for local reads and writes in multiple regions
- Building or presenting a multi-region demo or workshop
- Validating leaseholder placement and zone configurations
- Optimizing cross-region transaction latency

**Do not use this skill** when the question is only about SQL syntax, indexing, or generic schema design with no multi-region decision involved.

## Prerequisites

- Understanding of CockroachDB range architecture and leaseholder concepts
- A **multi-region cluster** with nodes started using `--locality=region=...,zone=...` matching the regions used in the examples below. Without matching localities the DDL errors with `region "..." does not exist` and constraints like `+region=...` match no nodes. Quickest path locally:
  ```bash
  # 9-node demo with three regions, three AZs each — note --no-example-database
  cockroach demo --no-example-database --nodes=9 \
    --demo-locality=region=NA-NE,az=1:region=NA-NE,az=2:region=NA-NE,az=3:region=NA-MW,az=1:region=NA-MW,az=2:region=NA-MW,az=3:region=EU-DE,az=1:region=EU-DE,az=2:region=EU-DE,az=3
  ```
  For long-running clusters, see [setting-up-local-cluster](../setting-up-local-cluster/SKILL.md) and add `--locality=region=...,zone=...` to each `cockroach start` invocation.
- Knowledge of application write patterns (single-region vs multi-region)

## Pattern Selection

### Step 1: Identify the Application Write Model

Ask first: **is there one write home, or many?**

- If the application has **one primary region for read/write**, start with a primary-region / regular regional-table model or a manually configured active-passive design.
- If the application needs **low-latency read/write in multiple regions**, evaluate manual geo-partitioning or `REGIONAL BY ROW`.
- If the table is mostly **reference data** that should read fast everywhere and the write path is not the main focus, consider `GLOBAL` tables.

### Step 2: Choose the Pattern

#### A. Regular Regional Tables (Active-Passive)

**Use when:**
- The application has one primary region for RW
- Remote regions are secondary or read-mostly
- Simplicity matters more than region-local writes everywhere

**Characteristics:**
- All leaseholders stay in the active region
- Replicas in other regions provide resiliency and single-region-failure survival
- Indicative latency: ~20ms writes, ~2-5ms reads (local region)

**Recommendation:** Prefer the higher-level multi-region abstractions first unless the user 

Preview of the first 4,000 characters. Read full instructions →

View saved version →

---
name: enabling-cmek-encryption
description: Enables Customer-Managed Encryption Keys (CMEK) on CockroachDB Cloud clusters with the Advanced plan and Advanced Security Add-on to give organizations control over data-at-rest encryption keys via their cloud provider's KMS. Use when enabling CMEK for compliance, rotating encryption keys, or verifying CMEK configuration.
compatibility: Requires CockroachDB Cloud Advanced plan with Advanced Security Add-on and cloud provider KMS (AWS KMS, GCP Cloud KMS, or Azure Key Vault).
---

# Enabling CMEK Encryption

Enables Customer-Managed Encryption Keys (CMEK) on CockroachDB Cloud clusters so that data-at-rest encryption is controlled by keys in the organization's own cloud provider KMS (AWS KMS, GCP Cloud KMS, or Azure Key Vault). CMEK gives organizations full control over key lifecycle, rotation, and revocation.

## When to Use This Skill

- Enabling CMEK for regulatory or compliance requirements (SOC 2, HIPAA, PCI DSS)
- Verifying CMEK is properly configured after initial setup
- Rotating CMEK keys per organization key rotation policy
- Responding to a security audit finding about encryption at rest
- Understanding CMEK requirements and plan prerequisites

## Prerequisites

- **CockroachDB Cloud Advanced plan** — CMEK is not available on Basic or Standard plans
- **Advanced Security Add-on** — Must be enabled on the Advanced plan cluster
- **Cloud provider KMS:**
  - **AWS:** KMS key in the same region as the cluster, with appropriate IAM policy
  - **GCP:** Cloud KMS key ring and key in the same region, with appropriate IAM bindings
  - **Azure:** Key Vault with key in the same region, with appropriate access policies
- **ccloud CLI** authenticated with Cluster Admin role
- **Cross-account access:** The CockroachDB Cloud service account must have permission to use the KMS key

**Check plan eligibility:**
```bash
# Verify cluster plan type
ccloud cluster info <cluster-name> -o json
# Look for "plan": "ADVANCED" and Advanced Security Add-on status
```

## Configuration Decisions

Before proceeding, determine which cloud provider KMS the user will use. Ask which option applies to their environment, then follow only the corresponding subsection in Step 2.

**Decision 1 — Cloud provider KMS:**
- **AWS KMS:** Use when the CockroachDB Cloud cluster runs on AWS. Requires a symmetric encryption key in the same region as the cluster.
- **GCP Cloud KMS:** Use when the cluster runs on GCP. Requires a key ring and key in the same region.
- **Azure Key Vault:** Use when the cluster runs on Azure. Requires a Key Vault with a key in the same region.

## Steps

### 1. Verify Plan and Add-on Eligibility

```bash
ccloud cluster info <cluster-name> -o json
```

**If Standard plan:** CMEK is not available. The upgrade path is:
1. Upgrade to Advanced plan (contact CockroachDB sales or upgrade in Cloud Console)
2. Enable the Advanced Security Add-on

**If Advanced plan without Advanced Security Add-on:** Enable the add-on in the Cloud Console before proceeding.

### 2. Create a KMS Key in Your Cloud Provider

Follow only the subsection for the user's cloud provider (selected in Configuration Decisions above).

#### AWS KMS

```bash
# Create a symmetric encryption key in the same region as the cluster
aws kms create-key \
  --description "CockroachDB CMEK - <cluster-name>" \
  --key-usage ENCRYPT_DECRYPT \
  --origin AWS_KMS \
  --region <aws-region>
```

Grant the CockroachDB Cloud service account access to the key:
```json
{
  "Effect": "Allow",
  "Principal": {
    "AWS": "<cockroachdb-cloud-service-account-arn>"
  },
  "Action": [
    "kms:Encrypt",
    "kms:Decrypt",
    "kms:GenerateDataKey",
    "kms:DescribeKey"
  ],
  "Resource": "*"
}
```

#### GCP Cloud KMS

```bash
# Create a key ring
gcloud kms keyrings create cockroachdb-cmek \
  --location <gcp-region>

# Create a symmetric encryption key
gcloud kms keys create cmek-key \
  --keyring cockroachdb-cmek \
  --location <gcp-region> \
  --purpose

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: enforcing-password-policies
description: Configures and enforces password policies on CockroachDB clusters including minimum length, complexity requirements, and hash cost settings. Use when strengthening authentication requirements, setting up password policies for a new cluster, or meeting compliance password standards.
compatibility: Requires admin role for cluster setting changes.
---

# Enforcing Password Policies

Configures and enforces password policies on CockroachDB clusters by setting minimum password length and bcrypt hash cost. Ensures password strength meets organizational and compliance requirements.

## When to Use This Skill

- Strengthening password requirements to meet compliance standards (SOC 2, HIPAA, NIST 800-63B)
- Setting up password policies for a new production cluster
- Responding to a security audit finding about weak password policies
- Increasing bcrypt hash cost to improve resistance against brute-force attacks

## Prerequisites

- **SQL access** with admin role (required to modify cluster settings)
- **Understanding of impact:** Password policy changes affect new passwords only, not existing passwords

**Check your access:**
```sql
SELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();
```

## Steps

### 1. Check Current Password Policy Settings

```sql
-- Minimum password length
SHOW CLUSTER SETTING server.user_login.min_password_length;

-- Password hash cost (bcrypt rounds)
SHOW CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt;
```

See [SQL queries reference](references/sql-queries.md) for additional password-related queries.

### 2. Set Minimum Password Length

```sql
-- Set minimum password length to 12 characters (recommended)
SET CLUSTER SETTING server.user_login.min_password_length = 12;
```

**Recommended minimums by compliance framework:**

| Framework | Minimum Length | Recommendation |
|-----------|---------------|----------------|
| NIST 800-63B | 8 characters | 12+ recommended |
| SOC 2 | 8 characters | 12+ recommended |
| HIPAA | 8 characters | 12+ recommended |
| PCI DSS | 7 characters | 12+ recommended |
| Internal best practice | — | 14+ for admin accounts |

### 3. Configure Hash Cost

The bcrypt hash cost controls how computationally expensive password hashing is. Higher values make brute-force attacks slower but increase CPU during authentication.

```sql
-- Set bcrypt hash cost (default: 10, recommended: 12)
SET CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt = 12;
```

**Hash cost guidance:**

| Cost | Time per Hash (approx.) | Recommendation |
|------|------------------------|----------------|
| 10 | ~100ms | Default, acceptable for most |
| 12 | ~400ms | Recommended for production |
| 14 | ~1.5s | High security, slower logins |

**Trade-off:** Higher cost means slower password verification, which affects login latency. Cost 12 is a good balance.

### 4. Verify Enforcement

```sql
-- Confirm settings
SHOW CLUSTER SETTING server.user_login.min_password_length;
SHOW CLUSTER SETTING server.user_login.password_hashes.default_cost.crdb_bcrypt;
```

**Test enforcement:**
```sql
-- This should fail if min_password_length is 12
CREATE USER test_weak_password WITH PASSWORD 'short';
-- Expected: ERROR: password too short

-- This should succeed
CREATE USER test_strong_password WITH PASSWORD 'a-secure-password-123';
DROP USER test_strong_password;
```

### 5. Address Existing Users with Weak Passwords

Password policy changes only apply to new passwords. Existing users retain their old passwords until they change them.

**Options for enforcing password rotation:**
1. **Communicate the policy change** and ask users to update their passwords
2. **Expire existing passwords** by requiring a password change on next login (if supported by your application layer)
3. **Reset passwords administratively** for critical accounts:

```sql
-- Reset a user's password (forces them to use a new, policy-compliant 

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: hardening-user-privileges
description: Hardens CockroachDB user privileges by auditing and tightening role-based access control, reducing admin grants, restricting PUBLIC role permissions, and applying least-privilege principles. Use when reducing excessive privileges, cleaning up admin access, or implementing RBAC best practices.
compatibility: Requires admin role to modify grants and role membership.
---

# Hardening User Privileges

Audits and tightens CockroachDB role-based access control (RBAC) by identifying over-privileged users, reducing admin grants, restricting PUBLIC role permissions, creating purpose-specific roles, and applying least-privilege principles.

## When to Use This Skill

- Reducing the number of users with admin role
- Removing excessive PUBLIC role privileges (SELECT, INSERT, UPDATE, DELETE)
- Creating purpose-specific roles to replace broad admin grants
- Responding to a security audit finding about excessive privileges
- Implementing RBAC best practices for a production cluster
- Onboarding a cluster to a least-privilege access model

## Prerequisites

- **SQL access** with admin role (required to modify grants and role membership)
- **User inventory:** Understanding of which users/applications need which level of access
- **Application testing plan:** Revoking grants can break applications that depend on them

**Check your access:**
```sql
SELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();
```

## Steps

### 1. Audit Current Users and Roles

```sql
-- List all users and their role memberships
SELECT
  username,
  options,
  member_of
FROM [SHOW USERS]
ORDER BY username;

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

-- List all admin users
SELECT member AS admin_user
FROM [SHOW GRANTS ON ROLE admin]
WHERE is_admin = true
ORDER BY member;
```

See [SQL queries reference](references/sql-queries.md) for additional audit queries.

### 2. Identify Over-Privileged Users

**Admin role review:**
```sql
-- Admin users — each should have a documented reason for admin access
SELECT member AS admin_user
FROM [SHOW GRANTS ON ROLE admin]
WHERE is_admin = true
ORDER BY member;
```

Evaluate each admin user:
- **Keep admin:** Cluster operators, DBAs, automation accounts that genuinely need full access
- **Downgrade:** Developers, analysts, application service accounts that only need specific permissions

**PUBLIC role review:**
```sql
-- Check what PUBLIC can do (these apply to ALL users)
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;
```

**System privilege review:**
```sql
-- Users with sensitive system privileges
SELECT grantee, privilege_type
FROM [SHOW SYSTEM GRANTS]
WHERE privilege_type IN (
  'MODIFYCLUSTERSETTING',
  'CANCELQUERY',
  'CANCELSESSION',
  'VIEWACTIVITY',
  'CREATEDB',
  'CREATELOGIN'
)
ORDER BY privilege_type, grantee;
```

### 3. Create Purpose-Specific Roles

Replace broad admin grants with targeted roles. Database-level grants in
CockroachDB only support `CONNECT`, `CREATE`, `DROP`, `ZONECONFIG`, `BACKUP`,
`RESTORE`, and `ALL` — data-access privileges (`SELECT`, `INSERT`, `UPDATE`,
`DELETE`) live at the schema or table level. Pair `GRANT ... ON ALL TABLES IN
SCHEMA` (covers existing tables) with `ALTER DEFAULT PRIVILEGES` (covers
future tables created by the listed grantors) so new tables inherit the
intended access.

```sql
-- Read-only role for analysts
CREATE ROLE analyst_reader;
GRANT CONNECT ON DATABASE <app_db> TO analyst_reader;
GRANT USAGE ON SCHEMA <app_db>.public TO analyst_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA <app_db>.public TO analyst_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA <app_db>.public GRANT SELECT ON TABLES TO analyst_reader;

-- Application service role (read + write, no DDL)
CREATE ROLE app_service;
GRANT C

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: manage-certs-and-encryption
description: Manages TLS certificate and encryption key lifecycle across all tiers. Self-Hosted covers certificate expiry monitoring, node/CA/client cert rotation, and Kubernetes cert management. Advanced/BYOC covers managed TLS (no action) and CMEK (Customer-Managed Encryption Key) rotation in your KMS. Standard and Basic have fully managed TLS and encryption with no customer action. CMEK is only available on Advanced. Use when monitoring cert health, performing rotation, managing CMEK, or responding to key compromise.
compatibility: Self-Hosted requires SQL admin access and filesystem access for cert rotation. Advanced/BYOC requires Cloud Console for CMEK management. Standard and Basic certificates and encryption are fully managed.
---

# Managing Certificates and Encryption

Manages TLS certificate and encryption key lifecycle across all deployment tiers. Before providing procedures, this skill gathers context to determine whether the operator manages certificates directly (Self-Hosted), manages CMEK encryption keys (Advanced/BYOC), or has fully managed encryption (Standard/Basic).

## When to Use This Skill

- Monitoring certificate expiration (Self-Hosted)
- Performing scheduled certificate rotation (Self-Hosted)
- Managing CMEK encryption keys (Advanced/BYOC)
- Responding to key compromise (Self-Hosted, CMEK)
- Auditing encryption posture for compliance (all tiers)
- Adding DNS names or IPs to node certificates (Self-Hosted)

**For daily health checks:** Use [reviewing-cluster-health](../reviewing-cluster-health/SKILL.md).

---

## Step 1: Gather Context

### Required Context

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Deployment tier?** | Self-Hosted, Advanced, BYOC, Standard, Basic | Determines encryption management responsibility |
| **Reason?** | Routine monitoring, Scheduled rotation, Key compromise, Compliance audit, Add SAN entries | Determines urgency and procedure |

### Additional Context (by tier)

**If Self-Hosted:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Certificate type?** | CA, Node, Client, UI | Different rotation procedures per type |
| **Deployment platform?** | Bare metal/VMs, Kubernetes (Operator/Helm/manual) | Changes rotation tooling |
| **Certificate tooling?** | cockroach cert, openssl, HashiCorp Vault, cert-manager | Determines generation commands |
| **Is the CA being rotated?** | Yes, No | CA rotation requires combined CA approach |

**If Advanced or BYOC:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Is this about CMEK?** | Yes, No | CMEK is the customer's encryption responsibility; TLS is managed by CRL |
| **Cloud provider?** | AWS, GCP, Azure | Determines KMS service and CLI commands |
| **CMEK currently enabled?** | Yes, No | CMEK must be enabled at cluster creation |

**If Standard or Basic:** No context needed — TLS and encryption are fully managed. CMEK is not available on these tiers.

### Context-Driven Routing

| Tier + Scenario | Go To |
|-----------------|-------|
| Self-Hosted | [Self-Hosted Certificate Management](#self-hosted-certificate-management) |
| Advanced/BYOC + CMEK | [CMEK Key Management](#cmek-key-management) |
| Advanced/BYOC + TLS question | [Cloud TLS (Managed)](#cloud-tls-managed) |
| Standard | [Fully Managed Encryption](#fully-managed-encryption) |
| Basic | [Fully Managed Encryption](#fully-managed-encryption) |

---

## Self-Hosted Certificate Management

**Applies when:** Tier = Self-Hosted

### Monitor Certificate Expiry

No production-safe SQL view exposes certificate expiration. Use one of:

```bash
# Inspect certs locally on each node
cockroach cert list --certs-dir=<certs-dir>

# Or read a specific cert file
openssl x509 -in <certs-dir>/node.crt -noout -enddate

# Or scrape the per-node Prometheus endpoint (UNIX seconds for ca, node, client_ca, ui_ca)
curl -ks https://<node>:8080/_status/vars

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 4 — file references are metadata, not package contents.

View saved version →

---
name: managing-cluster-capacity
description: Manages CockroachDB cluster capacity across all tiers. Self-Hosted covers node decommissioning for permanent removal and adding nodes for expansion. Advanced/BYOC covers scaling node count and machine size via Cloud Console, API, or Terraform. Standard covers adjusting provisioned compute (vCPUs). Basic auto-scales — guidance covers spending limits and cost management. Use when scaling capacity up or down, permanently removing nodes, or managing costs.
compatibility: Self-Hosted requires CLI access and admin role. Advanced/BYOC requires Cloud Console with Cluster Admin role. Standard requires Cloud Console. Basic has no capacity management — spending limits only.
---

# Managing Cluster Capacity

Manages cluster capacity across all CockroachDB deployment tiers. What "capacity" means varies by tier — Self-Hosted manages individual nodes, Advanced/BYOC manage node count and machine size, Standard manages provisioned vCPUs, and Basic auto-scales with cost controls.

## When to Use This Skill

- Permanently removing a node from a cluster (Self-Hosted)
- Adding nodes to increase capacity (Self-Hosted)
- Scaling cluster node count or machine size (Advanced, BYOC)
- Adjusting provisioned compute (Standard)
- Managing costs on a serverless cluster (Basic)
- Replacing hardware or migrating infrastructure (Self-Hosted, BYOC)
- Replacing a failed or dead node (Self-Hosted)
- Managing storage utilization and disk pressure (Self-Hosted)

**For temporary maintenance (not capacity changes):** Use [performing-cluster-maintenance](../perform-cluster-maintenance/SKILL.md).
**For pre-operation health check:** Use [reviewing-cluster-health](../reviewing-cluster-health/SKILL.md).

---

## Step 1: Gather Context

### Required Context

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Deployment tier?** | Self-Hosted, Advanced, BYOC, Standard, Basic | Different capacity model per tier |
| **Direction?** | Scale up (add capacity), Scale down (reduce capacity) | Determines procedure |

### Additional Context (by tier)

**If Self-Hosted (scaling down):**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **How many nodes to remove?** | 1, multiple | Multi-node decommission should be done simultaneously |
| **Target node IDs?** | Node IDs from `cockroach node status` | Required for CLI commands |
| **Is the node alive or dead?** | Alive, Dead | Dead nodes use a different procedure |
| **Deployment platform?** | Bare metal, VMs, Kubernetes | Changes CLI and cleanup steps |
| **Current replication factor?** | 3, 5, custom | Must have enough nodes remaining |
| **Current node count?** | Number | Validates remaining capacity |
| **Storage utilization?** | Low (<60%), Medium (60-80%), High (>80%) | Determines urgency and whether storage maintenance is needed |

**If Advanced or BYOC:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Scale method?** | Cloud Console, API, Terraform | Determines procedure |
| **Current and target configuration?** | e.g., 5 nodes → 3 nodes, or 4 vCPU → 8 vCPU | Validates constraints |
| **Cloud provider?** (BYOC only) | AWS, GCP, Azure | Affects infrastructure verification |

**If Standard:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Current provisioned vCPUs?** | Number | Context for scaling decision |
| **Target vCPUs?** | Number | Validates workload will fit |

**If Basic:** Gather cost management goals — Basic auto-scales with no manual capacity control.

### Context-Driven Routing

| Tier | Go To |
|------|-------|
| Self-Hosted | [Self-Hosted Capacity Management](#self-hosted-capacity-management) |
| Advanced | [Advanced Scaling](#advanced-scaling) |
| BYOC | [BYOC Scaling](#byoc-scaling) |
| Standard | [Standard Compute Management](#standard-compute-management) |
| Basic | [Basic Cost Management](#basic-cost-management) |

---

##

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 2 — file references are metadata, not package contents.

View saved version →

---
name: managing-cluster-settings
description: Reviews, audits, and modifies CockroachDB cluster settings. Self-Hosted has full control over all settings and start flags. Advanced/BYOC can modify most SQL-level settings but infrastructure settings are managed by CRL. Standard has limited settings access — session variables are the primary tuning mechanism. Basic has minimal settings — use session variables and Cloud Console. Use when auditing configuration, tuning performance, or troubleshooting settings-related issues.
compatibility: Self-Hosted has full control with admin role. Advanced/BYOC can modify most settings. Standard has limited SET CLUSTER SETTING access. Basic has minimal access. All tiers support SHOW CLUSTER SETTING for visible settings.
---

# Managing Cluster Settings

Reviews, audits, and modifies CockroachDB cluster settings. Before providing procedures, this skill gathers context to determine which settings are available and which modification approach to recommend for the operator's tier.

## When to Use This Skill

- Auditing cluster configuration for production readiness
- Identifying settings that deviate from defaults
- Tuning performance, replication, or admission control
- Verifying settings after an upgrade or incident
- Understanding which settings are modifiable on your tier
- Managing enterprise license installation and renewal (Self-Hosted)

**For version management:** Use [upgrading-cluster-version](../upgrading-cluster-version/SKILL.md) — do not change settings during an upgrade.
**For health checks:** Use [reviewing-cluster-health](../reviewing-cluster-health/SKILL.md).

---

## Step 1: Gather Context

### Required Context

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Deployment tier?** | Self-Hosted, Advanced, BYOC, Standard, Basic | Determines which settings are viewable and modifiable |
| **Goal?** | Audit/review, Tune performance, Fix specific issue, Post-upgrade verification | Directs which queries and procedures to use |

### Additional Context (by tier)

**If Self-Hosted:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **SQL access level?** | admin, MODIFYCLUSTERSETTING, VIEWCLUSTERSETTING | Determines available operations |
| **Specific setting or area?** | e.g., "timeout", "replication", "gc" | Narrows search to relevant settings |
| **Currently mid-upgrade?** | Yes, No | Settings must NOT be changed during rolling upgrades |

**If Advanced or BYOC:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **SQL access level?** | admin, limited | Determines available operations |
| **Specific setting or area?** | e.g., "timeout", "rangefeed" | Narrows search |

**If Standard or Basic:** No additional context needed — settings access is limited. Session variables are the primary tuning mechanism.

### Context-Driven Routing

| Tier | Settings Access | Go To |
|------|----------------|-------|
| Self-Hosted | Full | [Self-Hosted Settings](#self-hosted-settings) |
| Advanced / BYOC | Most (some restricted) | [Advanced / BYOC Settings](#advanced--byoc-settings) |
| Standard | Limited | [Standard Settings](#standard-settings) |
| Basic | Minimal | [Basic Settings](#basic-settings) |

---

## Audit Queries (All Tiers)

These read-only queries work on Self-Hosted and Advanced/BYOC. Standard and Basic may have restricted visibility — see tier-specific sections below.

### Non-Default Settings

```sql
SELECT variable, value, setting_type, description
FROM [SHOW ALL CLUSTER SETTINGS]
WHERE value != default_value ORDER BY variable;
```

### Production-Critical Settings

```sql
SELECT variable, value FROM [SHOW ALL CLUSTER SETTINGS]
WHERE variable IN (
  'kv.rangefeed.enabled', 'sql.stats.automatic_collection.enabled',
  'server.time_until_store_dead', 'admission.kv.enabled',
  'cluster.preserve_downgrade_option',
  'sql.defaults.idle_in_transaction_session_timeout'
) ORDER BY variable;
```

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 5 — file references are metadata, not package contents.

View saved version →

---
name: managing-tls-certificates
description: Manages TLS certificates for CockroachDB clusters including CA certificate configuration, client certificate authentication, certificate rotation, and troubleshooting SSL/TLS connection errors. Use when setting up client certificate auth, resolving SSL connection failures, rotating certificates, or configuring mTLS for CDC changefeeds.
compatibility: Requires ccloud CLI for Cloud clusters. Requires admin access and cockroach cert CLI for self-hosted clusters.
---

# Managing TLS Certificates

Manages TLS certificates for CockroachDB clusters, covering CA certificate downloads, client certificate authentication setup, certificate rotation, and troubleshooting common SSL/TLS connection errors. Addresses both CockroachDB Cloud (always-on TLS) and self-hosted certificate lifecycle management.

## When to Use This Skill

- Troubleshooting SSL/TLS connection errors from application clients (DBeaver, TypeORM, psql, Go, Python, Java)
- Setting up client certificate authentication on CockroachDB Cloud
- Uploading a custom Client CA to a Cloud cluster
- Rotating or renewing certificates (Cloud or self-hosted)
- Configuring mTLS for CDC changefeeds to Kafka
- Downloading or locating the CA certificate for a Cloud cluster

## Prerequisites

**CockroachDB Cloud:**
- **ccloud CLI** authenticated (`ccloud auth login`)
- **Cloud Console access** for CA certificate download
- **Cluster Admin role** for client CA configuration

**Self-hosted:**
- **cockroach cert** CLI available
- **Admin access** to cluster nodes
- **OpenSSL** for certificate inspection and generation

**Verify access:**
```bash
# Cloud
ccloud auth whoami
ccloud cluster list

# Self-hosted — check existing certificates
cockroach cert list --certs-dir=<certs-directory>
```

## Configuration Decisions

Before proceeding, determine the user's deployment model. Ask which option applies, then follow only the relevant sections below.

**Decision 1 — Deployment model:**
- **CockroachDB Cloud:** TLS is always on and the cluster CA is managed by Cockroach Labs. Follow Part 1 for CA download, client certificate auth, and Cloud certificate rotation.
- **Self-hosted:** Full manual certificate lifecycle management (CA, node, and client certificates). Follow Part 2 for certificate creation, rotation, and management.

Parts 3 (Troubleshooting) and 4 (mTLS for CDC) apply to both deployment models.

## Steps

### Part 1: CockroachDB Cloud TLS

> Follow this part if the user selected **CockroachDB Cloud** in Decision 1.

CockroachDB Cloud enforces TLS on all connections. The cluster CA certificate is managed by Cockroach Labs.

#### 1.1 Download the CA Certificate

The CA certificate is required by clients to verify the cluster's identity.

```bash
# Download via ccloud CLI
ccloud cluster cert <cluster-id>

# Or download from the Cloud Console:
# Cluster > Connect > Download CA Cert
```

The CA certificate is also available at: `https://cockroachlabs.cloud/clusters/<cluster-id>/cert`

**Common CA cert locations after download:**
- macOS: `~/.postgresql/root.crt`
- Linux: `~/.postgresql/root.crt` or `/etc/cockroach-certs/ca.crt`
- Windows: `%APPDATA%\postgresql\root.crt`

#### 1.2 Configure Client Certificate Authentication

Client certificate auth provides mutual TLS (mTLS) — the client proves its identity via certificate instead of a password.

**Step 1: Upload a Client CA to the cluster**

The Client CA signs your client certificates. This is separate from the cluster's CA.

```bash
# Upload a Client CA certificate via ccloud CLI
ccloud cluster cert set-client-ca <cluster-id> --cert-file <client-ca.crt>
```

**Step 2: Create a client certificate signed by your Client CA**

```bash
# Generate a client key and certificate signing request
openssl genrsa -out client.<username>.key 2048
openssl req -new -key client.<username>.key \
  -out client.<username>.csr \
  -subj "/CN=<username>"

# Sign the CSR with your Client CA
openssl x509 -req -in clien

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 2 — file references are metadata, not package contents.

View saved version →

---
name: molt-fetch
description: Guide for using molt fetch to migrate data from PostgreSQL, MySQL, Oracle, or MSSQL to CockroachDB. Use when running molt fetch commands, configuring storage backends, handling fetch failures/resumption, or chaining fetch with verify.
compatibility: Requires molt binary. Source DB must be accessible. For Oracle, CGO and Oracle Instant Client required.
---

# molt fetch

Bulk data migration from source databases (PostgreSQL, MySQL, Oracle, MSSQL) to CockroachDB.

## Basic Structure

```bash
molt fetch \
  --source "<source-conn>" \
  --target "<crdb-conn>" \
  --bucket-path "s3://bucket/prefix"   # or --direct-copy or --local-path
  [options]
```

## Storage Backends (pick one)

| Option | When to use |
|--------|-------------|
| `--bucket-path "s3://..."` | AWS S3 (also `gs://` for GCS, `azure://` for Azure) |
| `--direct-copy` | No intermediate storage; fastest for accessible networks |
| `--local-path "/tmp/molt"` + `--local-path-listen-addr "0.0.0.0:9005"` | CRDB must reach the listen addr |

Cloud auth: pass `--use-implicit-auth` for IAM/ADC/managed identity, or set `AWS_ACCESS_KEY_ID`/`GOOGLE_APPLICATION_CREDENTIALS` env vars.

## Table Handling (`--table-handling`)

| Value | Behavior |
|-------|----------|
| `none` (default) | Append to existing tables |
| `drop-on-target-and-recreate` | Drop + recreate from source schema; enables auto schema creation. Destroys existing target tables and their data, so use only on fresh or empty targets |
| `truncate-if-exists` | Truncate before loading; errors if table missing |

## Import Mode

**IMPORT INTO** (default): Table goes OFFLINE during load. Highest throughput.

**COPY FROM** (`--use-copy`): Table stays ONLINE. Use with `--direct-copy`. Cannot use compression.

```bash
# Zero-downtime load
molt fetch --source "..." --target "..." --direct-copy --use-copy
```

## Key Flags

```bash
# Filtering
--table-filter "customers|orders"      # POSIX regex for tables to include
--table-exclusion-filter "temp_.*"     # exclude pattern
--schema-filter "public"               # PostgreSQL only

# Performance
--table-concurrency 4                  # parallel tables (default: 4)
--export-concurrency 4                 # export threads (default: 4)
--row-batch-size 100000                # rows per SELECT (default: 100k)

# Schema
--type-map-file "types.json"           # custom type mappings
--transformations-file "transforms.json"  # column exclusions, table aliases

# Logging
--log-file "migration.log"             # or "stdout"
--logging debug                        # info (default), debug, trace
--metrics-listen-addr "0.0.0.0:3030"  # Prometheus scrape endpoint
```

## Source-Specific Prerequisites

**MySQL**: GTID mode required (`gtid_mode=ON`, `enforce_gtid_consistency=ON`). `ONLY_FULL_GROUP_BY` must be off. Or use `--ignore-replication-check`.

**Oracle**: Binary must be built with `CGO_ENABLED=1 -tags="cgo source_all"`. Oracle Instant Client in `LD_LIBRARY_PATH`.

**PostgreSQL**: Replication privileges needed, or `--ignore-replication-check`.

## Common Workflows

### 1. Full migration with schema creation

`drop-on-target-and-recreate` drops any existing target tables first; run this only against a fresh or empty target.

```bash
molt fetch \
  --source "postgresql://<user>:<password>@pg:5432/db" \
  --target "postgresql://root@crdb:26257/db" \
  --bucket-path "s3://mybucket/migration" \
  --table-handling drop-on-target-and-recreate \
  --table-filter "customers|orders|payments" \
  --log-file migration.log
```

### 2. Resume after failure
```bash
# List available continuation tokens
molt fetch tokens --fetch-id "abc-123" --target "postgresql://root@crdb:26257/db"

# Resume all failed tables
molt fetch \
  --source "..." --target "..." \
  --bucket-path "s3://mybucket/migration" \
  --fetch-id "abc-123" \
  --non-interactive
```

## Error Recovery

| Error | Cause | Fix |
|-------|-------|-----|
| "GTID-based replication not enabled" | MySQL missing GT

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: molt-replicator
description: Guide for using the CockroachDB replicator to continuously replicate changes from PostgreSQL, MySQL, or Oracle to CockroachDB after an initial molt fetch data load. Use when setting up CDC replication, configuring pglogical/mylogical/oraclelogminer, or managing the fetch → replicator cutover workflow.
compatibility: Requires separate replicator binary (not part of molt). Staging CockroachDB database required. Source DB must support logical replication.
---

# molt replicator

Continuous change-data-capture (CDC) replication from source databases to CockroachDB. Run **after** `molt fetch` completes the initial bulk load.

> **Important**: replicator is a **separate binary** from `molt`. It is not invoked by `molt fetch`. The `data-load-and-replication` mode in molt fetch is deprecated — use replicator directly instead.

## Architecture

```
Source DB ──► [replicator] ──► Staging DB (_replicator schema) ──► Target CockroachDB
                  ▲
            Publication/
            Slot/BinLog/
            LogMiner
```

Replicator reads changes from the source, buffers them in a staging schema on the target CRDB cluster, and applies them to the target tables.

## Subcommands by Source

| Source | Command |
|--------|---------|
| PostgreSQL | `replicator pglogical` |
| MySQL | `replicator mylogical` |
| Oracle | `replicator oraclelogminer` |
| Kafka | `replicator kafka` |
| Cloud storage | `replicator objstore` |
| CockroachDB CDC | `replicator start` |

## Full Fetch → Replicator Workflow

### Step 1: Initial bulk load with molt fetch

`drop-on-target-and-recreate` drops any existing target tables first; run this only against a fresh or empty target.

```bash
molt fetch \
  --source "postgresql://<user>:<password>@source:5432/db" \
  --target "postgresql://root@crdb:26257/db" \
  --bucket-path "s3://mybucket/migration" \
  --table-handling drop-on-target-and-recreate
```

### Step 2: Create publication on source (PostgreSQL)

```sql
-- Run on source PostgreSQL:
CREATE PUBLICATION molt_fetch FOR ALL TABLES;
-- (molt fetch may have already created this; check first)
```

### Step 3: Create staging database on target

```sql
-- Run on target CockroachDB:
CREATE DATABASE _replicator;
```

### Step 4: Test connectivity

```bash
# preflight only takes --stagingConn and --targetConn (always required for the
# target; stagingConn required if the target is not CRDB)
replicator preflight \
  --stagingConn "postgresql://root@crdb:26257/_replicator" \
  --targetConn "postgresql://root@crdb:26257/db"
```

### Step 5: Start replicator

```bash
replicator pglogical \
  --publicationName "molt_fetch" \
  --sourceConn "postgresql://<user>:<password>@source:5432/db" \
  --stagingConn "postgresql://root@crdb:26257/_replicator" \
  --stagingSchema "_replicator.public" \
  --targetConn "postgresql://root@crdb:26257/db" \
  --targetSchema "public" \
  --metricsAddr "0.0.0.0:8080"
```

### Step 6: Monitor lag

```bash
curl http://localhost:8080/metrics | grep replicator_
# Watch for: mutations applied, unapplied mutations, lag
```

### Step 7: Cutover

1. When lag reaches ~0, redirect app writes to CockroachDB
2. Let replicator drain remaining changes
3. Confirm no new writes on source
4. Stop replicator
5. Decommission the source only after `molt verify` passes and a source backup is confirmed

## Source-Specific Setup

### PostgreSQL (pglogical)

**Source prerequisites:**
- User with `REPLICATION` privilege
- Logical replication enabled (`wal_level = logical`)
- Publication exists (created by `molt fetch` or manually)

```bash
replicator pglogical \
  --publicationName "molt_fetch" \
  --slotName "replicator" \
  --sourceConn "postgresql://..." \
  --stagingConn "postgresql://root@crdb:26257/_replicator" \
  --stagingSchema "_replicator.public" \
  --targetConn "postgresql://root@crdb:26257/db" \
  --targetSchema "public"
```

### MySQL (mylogical)

**Source prerequisites:**
- Binary logging enabled (`binlog_fo

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: molt-verify
description: Guide for using molt verify to compare source and target databases for schema and row-level consistency after a migration. Use when running verify commands, tuning concurrency/sharding, handling schema mismatches, or validating data integrity post-migration.
compatibility: Requires molt binary. Source and target DBs must be accessible. Oracle requires CGO build.
---

# molt verify

Compares source and target databases for schema (DDL) and row (data) consistency. Run after `molt fetch` to confirm migration integrity.

## Basic Structure

```bash
molt verify \
  --source "<source-conn>" \
  --target "<crdb-conn>" \
  [options]
```

## Verification Phases

**Phase 1 — Schema:** Compares table presence, columns, types, NOT NULL constraints, and primary key structure.

**Phase 2 — Rows** (default, `--rows=true`): Iterates source rows in PK order and compares against target. Reports missing, extraneous, and mismatched rows per shard.

## Modes

| Mode | Command | Use When |
|------|---------|----------|
| Full (default) | `molt verify --source "..." --target "..."` | Post-migration integrity check |
| Schema-only | `molt verify ... --rows=false` | Fast DDL check; no data I/O |

## Concurrency & Sharding

```bash
# Default: CPU-count tables in parallel, 1 shard/table, 20k rows/batch
molt verify --source "..." --target "..."

# Large tables: parallelize within a single table
molt verify --source "..." --target "..." \
  --concurrency 1 --concurrency-per-table 8 --row-batch-size 50000

# Rate-limited (minimize production impact)
molt verify --source "..." --target "..." \
  --rows-per-second 1000 --concurrency 2
```

Sharding splits a table's PK range across workers. Supported PK types: INT, FLOAT, DECIMAL, UUID. Falls back to a single full-scan for unsupported types.

## Common Workflows

### 1. Post-migration sanity check
```bash
molt verify \
  --source "postgresql://<user>:<password>@pg:5432/db" \
  --target "postgresql://root@crdb:26257/db"
```

### 2. Schema-only (CI gate)
```bash
molt verify \
  --source "..." --target "..." \
  --rows=false --non-interactive --log-file stdout
```

### 3. Filtered verification (subset of tables)
```bash
molt verify \
  --source "..." --target "..." \
  --table-filter "customers|orders"
```

### 4. Verify with column exclusions
```bash
# transformations.json: {"tables":[{"name":"users","excludedColumns":["temp_col"]}]}
molt verify \
  --source "..." --target "..." \
  --transformations-file transformations.json
```

## Source-Specific Prerequisites

**PostgreSQL**: No special requirements. Partition tables (child partitions) are not supported — remove them before verifying.

**MySQL**: Queries current database only. `ONLY_FULL_GROUP_BY` may affect queries; disable if issues arise.

**Oracle**: Binary must be built with `CGO_ENABLED=1 -tags="cgo source_all"`. Oracle Instant Client in `LD_LIBRARY_PATH`. Use `--source-cdb` for multi-tenant (CDB) setups. Selective data verification (`--filter-path`) is not supported.

## Output & Reporting

Each table prints a summary per shard:
```
truth rows seen: 10000, success: 9950, missing: 5, mismatch: 45, extraneous: 0
```

- **missing**: rows present on source but absent on target
- **extraneous**: rows on target with no match on source
- **mismatch**: rows present on both but values differ

Schema issues (missing/extra tables or columns, type mismatches, PK differences) are logged as warnings and do not stop row verification.

Prometheus metrics available at `--metrics-listen-addr` (default `127.0.0.1:3030`).

## Error Recovery

| Error | Cause | Fix |
|-------|-------|-----|
| `missing table X on target` | Table not migrated | Rerun fetch or check filters |
| `extraneous table X on target` | Unexpected table | Clean up or adjust `--table-filter` |
| `column type mismatch` | Type conversion issue | Check type mappings or use `--transformations-file` |
| `PRIMARY KEY does not match` | PK structure differs | Inspect schema c

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: monitoring-background-jobs
description: Monitors CockroachDB background job health by identifying failed, paused, and long-running jobs using SHOW JOBS and SHOW AUTOMATIC JOBS. Surfaces schema changes, backups/restores, automatic statistics collection, and SQL stats compaction jobs without DB Console access. Use when investigating schema change delays, failed backups, or automatic job issues.
compatibility: Requires SQL access with VIEWJOB privilege or CONTROLJOB role option for cluster-wide visibility. Uses SHOW JOBS and SHOW AUTOMATIC JOBS (production-safe).
---

# Monitoring Background Jobs

Monitors background job health by identifying failed, paused, and long-running jobs that are distinct from user queries. Uses SQL-only interfaces (SHOW JOBS and SHOW AUTOMATIC JOBS) to surface schema changes, backups/restores, automatic statistics collection, and SQL stats compaction without requiring DB Console access.

## Prerequisites

- SQL connection with `VIEWJOB` system privilege (read-only) or `CONTROLJOB` role option (control)
- Background jobs are excluded from `SHOW CLUSTER STATEMENTS` and from statement statistics surfaced in the DB Console SQL Activity page

**Related skills:** [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) for live queries, [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md) for historical query analysis.

## Key Interfaces

- `SHOW JOBS`: User-initiated + automatic jobs (last 12h default; retention configurable via the `jobs.retention_time` cluster setting, default 14 days)
- `SHOW AUTOMATIC JOBS`: Automatic jobs only (AUTO CREATE STATS, SCHEMA CHANGE GC, etc.)

See [job types reference](references/job-types.md) and [job states reference](references/job-states.md) for complete catalogs.

## Core Diagnostic Queries

### Query 1: Failed Jobs (Last 12 Hours)

Identify jobs that failed with error messages:

```sql
-- Failed jobs in last 12 hours
WITH j AS (SHOW JOBS)
SELECT
  job_id,
  job_type,
  description,
  created,
  finished,
  now() - created AS total_duration,
  error
FROM j
WHERE status = 'failed'
  AND created > now() - INTERVAL '12 hours'
ORDER BY created DESC
LIMIT 50;
```

**Key columns:**
- `error`: Failure reason (check for permission errors, disk space, network issues)
- `description`: Human-readable description of what the job was doing
- `total_duration`: How long the job ran before failing

**Common failure patterns:**
- Permission denied: User lacks required privileges
- Disk space: Backup destination full
- Network timeout: External storage unreachable
- Constraint violation: Restore conflicts with existing data

### Query 2: Long-Running Jobs

Find jobs running longer than expected threshold:

```sql
-- Jobs running longer than 1 hour
WITH j AS (SHOW JOBS)
SELECT
  job_id,
  job_type,
  description,
  status,
  running_status,
  created,
  now() - created AS running_for,
  fraction_completed,
  coordinator_id
FROM j
WHERE status = 'running'
  AND created < now() - INTERVAL '1 hour'
ORDER BY created
LIMIT 50;
```

**Key columns:**
- `running_for`: Total elapsed time since job started
- `fraction_completed`: Progress estimate (0.0 to 1.0, NULL if unavailable)
- `running_status`: Sub-state details (e.g., "waiting for MVCC GC")

**Customizable thresholds:**
- Schema changes: 30 minutes to several hours (depends on table size)
- Backups: 1-6+ hours (depends on data volume)
- Automatic jobs: Usually < 30 minutes

### Query 3: Paused Jobs

Identify jobs that are paused and may need attention:

```sql
-- Paused jobs needing resume
WITH j AS (SHOW JOBS)
SELECT
  job_id,
  job_type,
  description,
  created,
  now() - created AS paused_for,
  coordinator_id
FROM j
WHERE status = 'paused'
ORDER BY created
LIMIT 50;
```

**Action required:**
Resume with `RESUME JOB <job_id>` after verifying the pause reason.

**Common reasons for paused jobs:**
- Manual user pause for maintenance
- Resource constraints (cluster paused the job)
- Error requiring 

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 4 — file references are metadata, not package contents.

View saved version →

---
name: perform-cluster-maintenance
description: Manages planned cluster maintenance across all tiers. Self-Hosted covers node drain procedures for OS patching, hardware changes, and configuration updates. Advanced/BYOC covers maintenance window configuration, patch scheduling, deferral policies, and monitoring during CRL-managed maintenance. Standard and Basic maintenance is fully managed with no customer action. Use when planning maintenance, configuring maintenance windows, or preparing applications for maintenance events.
compatibility: Self-Hosted requires CLI access and SQL access. Advanced/BYOC requires Cloud Console with Cluster Admin role. Standard and Basic maintenance is fully managed.
---

# Performing Cluster Maintenance

Manages planned cluster maintenance across all deployment tiers. For Self-Hosted, this means draining and restarting individual nodes. For Advanced/BYOC, this means configuring and managing maintenance windows for CRL-applied patches. For Standard and Basic, maintenance is fully managed with no customer action required.

## When to Use This Skill

- Planning OS patching, hardware changes, or configuration updates (Self-Hosted)
- Configuring or modifying a maintenance window (Advanced, BYOC)
- Setting patch deferral policies (Advanced, BYOC)
- Monitoring during a CRL-managed maintenance event (Advanced, BYOC)
- Running pre-maintenance validation checks (Self-Hosted, Advanced, BYOC)
- Understanding how maintenance affects your application (all tiers)
- Preparing applications for maintenance events (all tiers)

**For permanent node removal:** Use [managing-cluster-capacity](../managing-cluster-capacity/SKILL.md).
**For pre-maintenance health check:** Use [reviewing-cluster-health](../reviewing-cluster-health/SKILL.md).
**For version upgrades:** Use [upgrading-cluster-version](../upgrading-cluster-version/SKILL.md).

---

## Step 1: Gather Context

### Required Context

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Deployment tier?** | Self-Hosted, Advanced, BYOC, Standard, Basic | Determines maintenance procedure |
| **Goal?** | Plan maintenance, Configure maintenance window, Defer a patch, Monitor during maintenance, Prepare application | Routes to the right procedure |

### Additional Context (by tier)

**If Self-Hosted:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Maintenance type?** | OS patching, Hardware change, Binary upgrade, Config change, Planned restart | Affects sequencing and post-maintenance steps |
| **Deployment platform?** | Bare metal, VMs, Kubernetes (Operator/Helm/manual) | Changes drain and restart commands |
| **Process manager?** | systemd, manual, container orchestrator | Changes stop/start commands |
| **Target node ID?** | Node ID | Required for drain command |
| **Long-running queries expected?** | Yes (increase drain timeout), No (default timeout) | Determines drain-wait parameter |

**If Advanced or BYOC:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Maintenance window configured?** | Yes (what schedule), No | Determines if window needs setup |
| **Patch pending?** | Yes, No, Don't know | Determines urgency |
| **Cloud provider?** (BYOC only) | AWS, GCP, Azure | For infrastructure-level monitoring |

**If Standard or Basic:** No context needed — maintenance is fully managed.

### Context-Driven Routing

| Tier | Go To |
|------|-------|
| Self-Hosted | [Self-Hosted Node Maintenance](#self-hosted-node-maintenance) |
| Advanced | [Advanced Maintenance Management](#advanced-maintenance-management) |
| BYOC | [BYOC Maintenance Management](#byoc-maintenance-management) |
| Standard | [Standard Maintenance](#standard-maintenance) |
| Basic | [Basic Maintenance](#basic-maintenance) |

---

## Self-Hosted Node Maintenance

**Applies when:** Tier = Self-Hosted

Self-Hosted operators manage all maintenance directly. The core operation is draining a node to saf

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 3 — file references are metadata, not package contents.

View saved version →

---
name: prepare-compliance-docs
description: Guides preparation of compliance documentation for CockroachDB Cloud deployments, covering SOC 2, PCI DSS, ISO 27001, HIPAA, and GDPR certifications. Use when responding to compliance questionnaires, preparing for audits, locating certification documents, or assessing cluster configuration for compliance readiness.
compatibility: Applicable to all CockroachDB Cloud plans. Some compliance features require Advanced plan with security add-ons.
---

# Preparing Compliance Documentation

Guides the preparation of compliance documentation for CockroachDB Cloud deployments by identifying available certifications, mapping security features to compliance controls, and providing a configuration checklist for compliance readiness. Covers SOC 2 Type II, PCI DSS, ISO 27001, HIPAA, and GDPR.

## When to Use This Skill

- Responding to customer security questionnaires about CockroachDB Cloud
- Preparing for SOC 2, PCI DSS, or HIPAA compliance audits
- Locating CockroachDB Cloud certification documents (SOC 2 reports, PCI AOC, ISO certificates)
- Assessing whether a cluster's configuration meets a specific compliance framework
- Understanding which compliance features are available on each CockroachDB Cloud plan

## Prerequisites

- **CockroachDB Cloud account** — Any plan
- **Cloud Console access** — For downloading compliance documents
- **Understanding of your compliance requirements** — Which frameworks apply to your organization

## Steps

### 1. Identify Available Certifications

CockroachDB Cloud maintains the following certifications and attestations:

| Certification | Type | Scope | Where to Find |
|--------------|------|-------|---------------|
| SOC 2 Type II | Attestation report | Cloud infrastructure and operations | Trust Center or request via support |
| PCI DSS | Certificate of Compliance (AOC) | Payment data processing | Trust Center or request via support |
| ISO 27001 | Certification | Information security management | Trust Center or request via support |
| HIPAA | BAA eligibility | Protected health information | Contact sales for BAA execution |
| GDPR | Compliance | EU personal data protection | DPA available on request |

**CockroachDB Cloud Trust Center:** The Trust Center is the primary location for downloading compliance documents. Access it via the Cloud Console or request documents through a support ticket.

### 2. Map Compliance Controls to CockroachDB Features

See [compliance matrix reference](references/compliance-matrix.md) for a detailed mapping of compliance controls to CockroachDB features.

#### SOC 2 — Key Controls

| Control Area | CockroachDB Feature | Configuration Required |
|-------------|---------------------|----------------------|
| Access Control | RBAC, SSO, SCIM | Configure roles, enable SSO |
| Encryption | TLS (always on), CMEK | Enable CMEK on Advanced plan |
| Audit Logging | SQL audit logging, log export | Enable audit logging + log export |
| Network Security | IP allowlists, private endpoints | Configure network restrictions |
| Availability | Multi-region, managed backups | Built-in on all plans |
| Change Management | Cluster versioning, Terraform | Use IaC for cluster management |

#### PCI DSS — Key Requirements

| Requirement | CockroachDB Feature | Notes |
|------------|---------------------|-------|
| Req 1: Network Security | IP allowlists, private endpoints | Restrict access to cardholder data environment |
| Req 3: Protect Stored Data | Encryption at rest (default), CMEK | CMEK provides key control |
| Req 4: Encrypt Transmission | TLS (always on) | Enforced by default |
| Req 7: Restrict Access | RBAC, least privilege | Use hardening-user-privileges skill |
| Req 8: Identify Users | SQL users, SSO, MFA | Enable SSO with MFA at IdP |
| Req 10: Track Access | Audit logging, log export | Enable and export audit logs |

#### HIPAA — Key Safeguards

| Safeguard | CockroachDB Feature | Notes |
|-----------|---------------------|-------

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: profile-stmt-fingerprints
description: Ranks and analyzes statement fingerprints using aggregated SQL statistics from crdb_internal.statement_statistics to identify slow, resource-intensive, or error-prone query patterns. Use when investigating historical performance trends, identifying optimization opportunities, or diagnosing recurring slowness without DB Console access.
compatibility: Requires SQL access with VIEWACTIVITY or VIEWACTIVITYREDACTED cluster privilege. Uses crdb_internal.statement_statistics (production-safe). Execution statistics fields are sampled and may be sparse.
---

# Profiling Statement Fingerprints

Analyzes historical statement performance patterns using aggregated SQL statistics to identify slow, resource-intensive, or error-prone query fingerprints. Uses `crdb_internal.statement_statistics` for time-windowed analysis of latency, CPU, contention, admission delays, and failure rates - entirely via SQL without requiring DB Console access.

**Complement to triaging-live-sql-activity:** This skill analyzes historical patterns; for immediate triage of currently running queries, see [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md).

## When to Use This Skill

- Identify slowest statement fingerprints over past hours/days/weeks
- Find queries with high CPU consumption, contention, or admission waits
- Investigate performance regressions or plan changes
- Locate full table scans or missing indexes via index recommendations
- Analyze resource consumption by application or database
- SQL-only historical analysis without DB Console access

**For immediate incident response:** Use [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) to triage currently running queries and cancel runaway work.
**For transaction-level analysis:** Use [profiling-transaction-fingerprints](../profile-txn-fingerprints/SKILL.md) to analyze retry patterns, commit latency, and statement composition at the transaction boundary.
**For background job monitoring:** Use [monitoring-background-jobs](../monitoring-background-jobs/SKILL.md) for long-running schema changes and automatic jobs excluded from statement statistics.

## Prerequisites

- SQL connection to CockroachDB cluster
- `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` cluster privilege for cluster-wide visibility
- Statement statistics collection enabled (default): `sql.stats.automatic_collection.enabled = true`

**Check collection status:**
```sql
SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;  -- Should return: true
```

See [triaging-live-sql-activity permissions reference](../triaging-live-sql-activity/references/permissions.md) for RBAC setup (same privileges).

## Core Concepts

### Statement Fingerprints vs Live Queries

**Statement fingerprint:** Normalized SQL pattern with parameterized constants (e.g., `SELECT * FROM users WHERE id = $1` vs `SELECT * FROM users WHERE id = 123`)

**Key differences:**
- **Time scope:** Historical hourly buckets vs real-time current state
- **Granularity:** Aggregated pattern statistics vs individual execution instances

### Time-Series Bucketing

**aggregated_ts:** Hourly UTC buckets (e.g., `2026-02-21 14:00:00` = 14:00-14:59 executions)
**Data retention:** Capped by row count, not time. `sql.stats.persisted_rows.max` (default 1,000,000) bounds the persisted statement+transaction rows; older buckets are compacted once the cap is reached. Effective wall-clock window depends on workload diversity.
**Best practice:** Always filter by time window: `WHERE aggregated_ts > now() - INTERVAL '24 hours'`

### Aggregated vs Sampled Metrics

| Metric Category | JSON Path | Scope | Use Case |
|-----------------|-----------|-------|----------|
| **Aggregated** | `statistics.statistics.*` | All executions | Latency, row counts, execution counts |
| **Sampled** | `statistics.execution_statistics.*` | Probabilistic sample governed by `sql.txn_stats.sample_rate` (default 0.01) | CPU, contention, admission 

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 3 — file references are metadata, not package contents.

View saved version →

---
name: profile-txn-fingerprints
description: Analyzes transaction fingerprints using aggregated statistics from crdb_internal.transaction_statistics to identify high-retry transactions, contention patterns, and commit latency issues. Provides historical transaction-level analysis to understand which statement combinations are causing retries, contention, or performance degradation. Use when investigating transaction retry storms, analyzing commit latency trends, or understanding statement composition of problematic transactions without DB Console access.
compatibility: Requires SQL access with VIEWACTIVITY or VIEWACTIVITYREDACTED cluster privilege. Uses crdb_internal.transaction_statistics (production-safe). Execution statistics fields are sampled and may be sparse.
---

# Profiling Transaction Fingerprints

Analyzes historical transaction performance patterns using aggregated SQL statistics to identify high-retry transactions, contention patterns, and commit latency issues. Uses `crdb_internal.transaction_statistics` for time-windowed analysis of retry behavior, commit latency, and statement composition - entirely via SQL without requiring DB Console access.

**Complement to profiling-statement-fingerprints:** This skill analyzes transaction-level patterns (groups of statements with retry behavior); for statement-level optimization, see [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md).

**Complement to triaging-live-sql-activity:** This skill analyzes historical transaction patterns; for immediate triage of currently active transactions, see [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md).

## When to Use This Skill

- Identify transactions with high retry counts
- Analyze commit latency trends for transaction fingerprints
- Find transactions with high contention at transaction boundary
- Understand statement composition of problematic transactions
- Investigate transaction retry storms or abort patterns
- SQL-only historical transaction analysis without DB Console access

**For immediate incident response:** Use [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) to triage currently active transactions and cancel runaway work.
**For statement-level optimization:** Use [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md) to analyze individual query patterns.

## Prerequisites

- SQL connection to CockroachDB cluster
- `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` cluster privilege for cluster-wide visibility
  - Same privilege requirements as profiling-statement-fingerprints
- Understanding of transaction performance concepts
- Transaction statistics collection enabled (default): `sql.stats.automatic_collection.enabled = true`

**Check transaction stats collection:**
```sql
SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;
-- Should return: true
```

See [triaging-live-sql-activity permissions reference](../triaging-live-sql-activity/references/permissions.md) for RBAC setup (same privileges).

## Core Concepts

### Transaction Fingerprints vs Live Transactions

**Transaction fingerprint:** Normalized transaction pattern grouping statements with parameterized constants.

**Key differences:**
- **Time scope:** Historical hourly buckets vs real-time current state
- **Granularity:** Aggregated retry/commit stats vs individual transaction instances
- **Relationship:** Transaction = collection of statement fingerprints

### Time-Series Bucketing

**aggregated_ts:** Hourly UTC buckets (e.g., `2026-02-21 14:00:00` = 14:00-14:59 executions)
**Data retention:** Capped by row count, not time. `sql.stats.persisted_rows.max` (default 1,000,000) bounds the persisted statement+transaction rows; older buckets are compacted once the cap is reached. Effective wall-clock window depends on workload diversity.
**Best practice:** Always filter by time window: `WHERE aggregated_ts > now() - INTERVAL '24 hours'`

### Aggregated vs Sampled Metrics

| Metric 

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 3 — file references are metadata, not package contents.

View saved version →

---
name: provision-prod-cluster
description: Guides initial CockroachDB cluster provisioning and production deployment. Self-Hosted covers cockroach start/init, Kubernetes deployment (Operator, Helm), hardware sizing, and production configuration. Advanced/BYOC covers Cloud Console, API, and Terraform provisioning with production settings. Standard covers cluster creation and provisioned compute selection. Basic covers cluster creation and spending limits. Use when creating a new cluster, preparing for production go-live, or validating deployment configuration.
compatibility: Self-Hosted requires CLI access and infrastructure provisioning. Advanced/BYOC requires Cloud Console with Org Admin role. Standard requires Cloud Console. Basic requires Cloud Console.
---

# Provisioning Cluster for Production

Guides CockroachDB cluster creation and production deployment configuration. Before providing procedures, this skill gathers context to deliver tier-appropriate provisioning steps and production hardening guidance.

## When to Use This Skill

- Creating a new CockroachDB cluster
- Preparing a development/staging cluster for production go-live
- Validating hardware and configuration for production readiness
- Choosing the right deployment tier and sizing

**For post-deployment health checks:** Use [reviewing-cluster-health](../reviewing-cluster-health/SKILL.md).
**For ongoing settings management:** Use [managing-cluster-settings](../managing-cluster-settings/SKILL.md).
**For capacity changes after deployment:** Use [managing-cluster-capacity](../managing-cluster-capacity/SKILL.md).

---

## Step 1: Gather Context

### Required Context

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Deployment tier?** | Self-Hosted, Advanced, BYOC, Standard, Basic | Completely different provisioning procedures |
| **Environment?** | Production, Staging, Development | Determines hardware sizing and configuration rigor |

### Additional Context (by tier)

**If Self-Hosted:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Platform?** | Bare metal, VMs (AWS/GCP/Azure), Kubernetes | Changes installation and start commands |
| **If Kubernetes?** | Operator (recommended), Helm, Manual StatefulSet | Determines deployment method |
| **Node count?** | 3 (minimum), 5, 9+ | Affects topology and replication |
| **Multi-region?** | Yes (how many regions), No | Requires locality flags and topology planning |
| **Expected workload?** | OLTP, mixed OLTP/analytics, write-heavy | Affects hardware sizing |
| **Security requirements?** | TLS required, encryption at rest, CMEK | Determines certificate and encryption setup |

**If Advanced or BYOC:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Provisioning method?** | Cloud Console, Cloud API, Terraform | Determines procedure |
| **Cloud provider?** | AWS, GCP, Azure | Affects region selection and networking |
| **Node count and size?** | e.g., 3 nodes x 8 vCPUs | Determines initial capacity |

**If Standard:** Gather expected workload size (vCPUs) and storage estimate.

**If Basic:** Gather expected usage pattern and monthly budget.

### Context-Driven Routing

| Tier | Go To |
|------|-------|
| Self-Hosted | [Self-Hosted Provisioning](#self-hosted-provisioning) |
| Advanced | [Advanced Provisioning](#advanced-provisioning) |
| BYOC | [BYOC Provisioning](#byoc-provisioning) |
| Standard | [Standard Provisioning](#standard-provisioning) |
| Basic | [Basic Provisioning](#basic-provisioning) |

---

## Self-Hosted Provisioning

**Applies when:** Tier = Self-Hosted

### Hardware Sizing

| Component | Minimum | Production Recommended |
|-----------|---------|----------------------|
| Nodes | 3 | 3+ (odd number per failure domain) |
| CPU | 4 vCPUs (non-burstable) | 8+ vCPUs |
| RAM | 16 GB | 32+ GB |
| Storage | 150 GB SSD | 500+ GB NVMe SSD |
| Network | 1 Gbps | 10 Gbps |

**Memory formula:** `--cache + --m

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 2 — file references are metadata, not package contents.

View saved version →

---
name: reviewing-cluster-health
description: Performs a comprehensive health check of a CockroachDB cluster. Gathers deployment context first, then provides tier-appropriate diagnostics. Self-Hosted uses SQL against node-level system tables and CLI. Advanced/BYOC use Cloud Console and SQL with node visibility. Standard monitors provisioned compute and workload via Cloud Console. Basic monitors Request Unit consumption and connectivity. Use for daily checks, pre-maintenance validation, post-incident verification, or production readiness assessment.
compatibility: Self-Hosted requires SQL access with admin or VIEWCLUSTERMETADATA privilege. Advanced/BYOC require Cloud Console and SQL connectivity. Standard requires Cloud Console and SQL. Basic requires Cloud Console.
---

# Reviewing Cluster Health

Performs a comprehensive health check of a CockroachDB cluster. Before running diagnostics, this skill gathers deployment context to provide the right queries and tools for the operator's tier.

## When to Use This Skill

- Daily or shift-start operational health checks
- Before starting maintenance (Self-Hosted, Advanced, BYOC)
- After incidents to confirm recovery
- Verifying production readiness
- Monitoring capacity and performance

**For live query issues:** Use [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md).
**For background jobs:** Use [monitoring-background-jobs](../monitoring-background-jobs/SKILL.md).
**For range analysis:** Use [analyzing-range-distribution](../analyze-range-distribution/SKILL.md).

---

## Step 1: Gather Context

### Required Context

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Deployment tier?** | Self-Hosted, Advanced, BYOC, Standard, Basic | Determines available diagnostics and operator responsibilities |
| **Reason for health check?** | Daily check, Pre-maintenance, Post-incident, Pre-upgrade | Prioritizes which dimensions to check first |

### Additional Context (by tier)

**If Self-Hosted:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Access available?** | SQL + CLI, SQL only | Determines which tools can be used |
| **Cloud provider?** | AWS, GCP, Azure, On-Premises | Affects infrastructure-level checks |
| **Kubernetes deployment?** | Yes (Operator, Helm, manual), No | Changes CLI commands and monitoring |
| **Node count and regions?** | e.g., 9 nodes, 3 regions | Sets expectations for query results |

**If Advanced or BYOC:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Cloud provider?** (BYOC only) | AWS, GCP, Azure | For infrastructure-level monitoring in your cloud account |

**If Standard:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Current provisioned vCPUs?** | Number | Context for compute utilization assessment |

**If Basic:** No additional context needed.

### Context-Driven Routing

| Tier | Go To |
|------|-------|
| Self-Hosted | [Self-Hosted Health Check](#self-hosted-health-check) |
| Advanced | [Advanced Health Check](#advanced-health-check) |
| BYOC | [BYOC Health Check](#byoc-health-check) |
| Standard | [Standard Health Check](#standard-health-check) |
| Basic | [Basic Health Check](#basic-health-check) |

---

## Self-Hosted Health Check

**Applies when:** Tier = Self-Hosted

Self-Hosted node-level health is read primarily through `cockroach node status` (CLI) and the DB Console. Cluster settings and jobs are read through public SQL (`SHOW ALL CLUSTER SETTINGS`, `SHOW JOBS`). The `crdb_internal` virtual tables for cluster topology, storage, and certificates are not for production use — see the [docs](https://www.cockroachlabs.com/docs/stable/crdb-internal) for the production-safe table list.

### Check 1: Node Liveness, Version, and Replication

```bash
cockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>
```

Key columns:
- `is_live` — `false` requires immediate 

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: schema-change-storage-risk
description: Estimates storage requirements for CockroachDB online schema change backfills using SHOW RANGES WITH DETAILS, KEYS, INDEXES. Use before CREATE INDEX, ADD COLUMN with INDEX/UNIQUE, ALTER PRIMARY KEY, CREATE MATERIALIZED VIEW, CREATE TABLE AS, REFRESH, or SET LOCALITY on tables with large per-index footprints, to avoid mid-backfill disk exhaustion.
compatibility: Requires SQL access. SHOW RANGES WITH DETAILS computes span_stats on demand and is expensive on tables with many ranges; target specific tables. Mirrors official guidance at https://www.cockroachlabs.com/docs/stable/online-schema-changes#estimate-your-storage-capacity-before-performing-online-schema-changes.
---

# Analyzing Schema Change Storage Risk

Estimates the storage headroom needed to safely run online schema changes.
Mirrors the [official guidance](https://www.cockroachlabs.com/docs/stable/online-schema-changes#estimate-your-storage-capacity-before-performing-online-schema-changes):
some operations may temporarily require up to **3× the size of the affected
table or index** while the schema change is in flight.

For ongoing range-distribution monitoring, see
[analyzing-range-distribution](../analyze-range-distribution/SKILL.md).

## When to Use This Skill

Run a quick estimate before issuing any of these operations on a table whose
indexes are large (multiple GB per index, or many ranges per index):

- `CREATE INDEX`
- `ADD COLUMN` with `INDEX` or `UNIQUE`
- `ALTER PRIMARY KEY`
- `CREATE MATERIALIZED VIEW`
- `CREATE TABLE AS`
- `REFRESH MATERIALIZED VIEW`
- `ALTER TABLE ... SET LOCALITY` (when the locality change rewrites data)

Tables whose indexes are small (kilobytes to a few megabytes) carry trivial
storage risk; estimation is unnecessary.

## Background

### How much temporary space does a backfill actually need?

The honest answer depends on the operation:

- **`CREATE INDEX` / `ADD COLUMN ... UNIQUE`**: needs roughly **1× the size of
  the new index** — the indexed columns plus the primary key columns, written
  into a fresh index span. This is typically a small fraction of the table.
  Worst-case headroom is bounded by the size of that one index.
- **`ALTER PRIMARY KEY`**: rewrites the primary index and any secondary indexes
  whose definitions depend on the old PK. Old data sticks around until GC, so
  peak on-disk usage during the change can approach the size of the table again.
- **All bulk-ingest backfills**: extra MVCC versions and pre-compaction SSTs
  add overhead until Pebble compacts and GC runs.

The official docs round these up into a single conservative recommendation:
plan for up to **3× the size of the affected table or index** as free space.
That figure is a safety bound, not a precise prediction. For most
`CREATE INDEX` operations the real cost is much smaller; for
`ALTER PRIMARY KEY` on a large table it is the right ballpark.

### What happens if the cluster runs out of disk mid-backfill?

Backfills bulk-ingest data via `AddSSTable`, which checks the per-store
remaining capacity before each ingestion. If the remaining fraction falls
below `kv.bulk_io_write.min_capacity_remaining_fraction` (default `0.05`,
i.e. 5%), the ingest is rejected with `InsufficientSpaceError`. Both the
legacy and declarative schema changers translate that error into a job pause
request, so the schema change halts rather than wedging the cluster. To
resume, free space (e.g. drop unused indexes, expand storage) and resume the
paused job.

This is a *reactive* safety net, not a planning tool — by the time it fires,
foreground writes on the affected store may already be unhealthy.

## Estimating Capacity

### Step 1 — Check free space per store

The minimum free space across stores is what bounds the schema change, not the
total cluster free space (replicas are spread across nodes).

No production-safe SQL view exposes per-store capacity. Use the DB Console
**Overview** → **Storage** page (sorts per-store usage), or 

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: setting-up-local-cluster
description: Downloads and starts a local CockroachDB cluster for development using the official binary. Use when a developer needs a local CockroachDB instance, when no cluster is available, or when setting up a new development environment.
---

# Setting Up a Local CockroachDB Cluster

Guides you through downloading, installing, and starting a local CockroachDB cluster for development. Uses the official binary -- no Docker or external runtime dependencies required.

## When to Use This Skill

- Developer asks to "set up CockroachDB" or "start a local database"
- No CockroachDB cluster is reachable
- Developer wants to build an app with CockroachDB from scratch
- Setting up a new development environment

## Prerequisites

- macOS (Intel or Apple Silicon), Linux (Intel or ARM), or Windows (Intel)
- `curl` or `wget` available for downloading the binary
- ~500 MB disk space per node
- Ports 26257-26259 (SQL), 26357-26359 (RPC), and 8080-8082 (DB Console) available for a 3-node cluster

## Step 1: Detect Platform and Download

Detect the OS and architecture, then download the appropriate binary.

### Download URLs

Base URL: `https://binaries.cockroachdb.com/`

| OS | Architecture | Filename Pattern |
|----|-------------|-----------------|
| Linux | Intel (amd64) | `cockroach-v{VERSION}.linux-amd64.tgz` |
| Linux | ARM (arm64) | `cockroach-v{VERSION}.linux-arm64.tgz` |
| macOS | Intel (amd64) | `cockroach-v{VERSION}.darwin-10.9-amd64.tgz` |
| macOS | Apple Silicon (arm64) | `cockroach-v{VERSION}.darwin-11.0-arm64.tgz` |
| Windows | Intel (amd64) | `cockroach-v{VERSION}.windows-6.2-amd64.zip` |

Replace `{VERSION}` with the desired release (e.g., `25.4.9`). See [CockroachDB Releases](https://www.cockroachlabs.com/docs/releases/) for the latest GA version.

### Installation

```bash
# Example: macOS Apple Silicon, v25.4.9
curl -fsSL https://binaries.cockroachdb.com/cockroach-v25.4.9.darwin-11.0-arm64.tgz | tar xz
mkdir -p ~/.cockroachdb/bin
cp cockroach-v25.4.9.darwin-11.0-arm64/cockroach ~/.cockroachdb/bin/
export PATH="$HOME/.cockroachdb/bin:$PATH"
```

If `cockroach` is already on PATH, skip the download.

## Step 2: Start the Cluster

A 3-node cluster is recommended for development because it exercises replication, range distribution, leaseholder balancing, and survival goals -- core CockroachDB capabilities that a single node cannot demonstrate.

### 3-Node Cluster (Recommended)

```bash
# Start 3 nodes with separate SQL, RPC, and HTTP ports.
# Use $HOME instead of ~ in --store / --log-dir / --pid-file: tilde does not
# expand inside --flag=~/... in bash or zsh.
cockroach start --insecure --listen-addr=localhost:26357 --sql-addr=localhost:26257 \
  --http-addr=localhost:8080 --store=$HOME/.cockroachdb/data/node1 \
  --log-dir=$HOME/.cockroachdb/logs/node1 \
  --pid-file=$HOME/.cockroachdb/data/node1/cockroach.pid \
  --join=localhost:26357,localhost:26358,localhost:26359 --background

cockroach start --insecure --listen-addr=localhost:26358 --sql-addr=localhost:26258 \
  --http-addr=localhost:8081 --store=$HOME/.cockroachdb/data/node2 \
  --log-dir=$HOME/.cockroachdb/logs/node2 \
  --pid-file=$HOME/.cockroachdb/data/node2/cockroach.pid \
  --join=localhost:26357,localhost:26358,localhost:26359 --background

cockroach start --insecure --listen-addr=localhost:26359 --sql-addr=localhost:26259 \
  --http-addr=localhost:8082 --store=$HOME/.cockroachdb/data/node3 \
  --log-dir=$HOME/.cockroachdb/logs/node3 \
  --pid-file=$HOME/.cockroachdb/data/node3/cockroach.pid \
  --join=localhost:26357,localhost:26358,localhost:26359 --background

# Initialize the cluster (only needed on first start)
cockroach init --insecure --host=localhost:26357
```

### Single-Node (Lightweight)

For minimal resource usage when full cluster capabilities are not needed:

```bash
cockroach start-single-node --insecure --listen-addr=localhost:26257 \
  --http-addr=localhost:8080 --store=$HOME/.cockroachdb/data/node1 \
  --log

Preview of the first 4,000 characters. Read full instructions →

View saved version →

---
name: triaging-live-sql-activity
description: Diagnoses live CockroachDB cluster performance issues by identifying long-running queries, busy sessions, and active transactions using SQL-only interfaces. Use when users report cluster slowness, high CPU, or need to find runaway queries and their source applications without DB Console access.
compatibility: Requires SQL access with VIEWACTIVITY or VIEWACTIVITYREDACTED cluster privilege for cluster-wide visibility.
---

# Triaging Live SQL Activity

Diagnoses live cluster performance issues by identifying currently active long-running queries, busy sessions, and active transactions. Uses SQL-only interfaces (SHOW statements and `crdb_internal` views) to provide immediate triage without requiring DB Console, HTTP endpoints, or Prometheus access.

## When to Use This Skill

- Users report "the cluster is slow right now"
- High CPU or memory usage on cluster nodes
- Need to identify runaway queries or stuck transactions
- Want to find which applications/users are consuming resources
- Require immediate triage without DB Console access
- Need to generate SQL to cancel problematic sessions/queries

**For historical performance analysis:** Use [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md) to analyze query patterns over time, identify slow fingerprints, and investigate trends without needing live queries.
**For transaction-level analysis:** Use [profiling-transaction-fingerprints](../profile-txn-fingerprints/SKILL.md) to analyze historical transaction retry patterns, commit latency trends, and statement composition.
**For background job monitoring:** Use [monitoring-background-jobs](../monitoring-background-jobs/SKILL.md) to monitor schema changes, backups, and automatic jobs that don't appear in SHOW CLUSTER STATEMENTS.

## Prerequisites

**Required SQL access:**
- Connection to any CockroachDB node
- For cluster-wide visibility: `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` privilege
  - `VIEWACTIVITYREDACTED`: Redacts constants in other users' queries (recommended for privacy)
  - `VIEWACTIVITY`: Shows full query text for all users
  - Without these: Only see your own sessions/queries
- Basic understanding of SQL query execution
- (Optional) `CANCELQUERY` / `CANCELSESSION` privileges for cancellation operations

**Check your privileges:**
```sql
SHOW SYSTEM GRANTS FOR <username>;
```

See [permissions reference](references/permissions.md) for detailed RBAC setup.

## Core Diagnostic Approach

CockroachDB provides SQL-only interfaces for live activity triage:

| Interface | Purpose | Cluster-wide? |
|-----------|---------|---------------|
| `SHOW CLUSTER STATEMENTS` | Currently executing queries | Yes (with VIEWACTIVITY) |
| `SHOW CLUSTER SESSIONS` | Active client sessions | Yes (with VIEWACTIVITY) |
| `crdb_internal.cluster_transactions` | In-progress transactions | Yes (with VIEWACTIVITY) |

**Triage workflow:**
1. Identify long-running queries (> 5-10 minutes)
2. Correlate to sessions and applications
3. Check transaction retry counts (high retries = contention)
4. Drill down by app/user/client
5. (Optional) Cancel runaway work

**Safety:** All diagnostic queries are read-only. Cancellation is opt-in with explicit warnings.

## Core Diagnostic Queries

### Long-Running Queries

Identify queries running longer than a specified threshold:

```sql
-- Queries running longer than 5 minutes
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT
  query_id,
  node_id,
  session_id,
  user_name,
  client_address,
  application_name,
  start,
  now() - start AS running_for,
  substring(query, 1, 200) AS query_preview,
  distributed,
  phase
FROM q
WHERE start < now() - INTERVAL '5 minutes'
ORDER BY start
LIMIT 50;
```

**Key columns:**
- `running_for`: How long the query has been executing
- `query_preview`: First 200 characters (protects against massive queries)
- `phase`: execution phase (preparing, executing, etc.)
- `distributed`: whether query spans multiple nodes

**Cust

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 2 — file references are metadata, not package contents.

View saved version →

---
name: upgrading-cluster-version
description: Guides CockroachDB version upgrades with tier-appropriate procedures. Self-Hosted covers manual rolling binary replacement with finalization control. Advanced/BYOC covers Console-initiated major upgrades, maintenance windows for patches, and release channel selection. Standard and Basic upgrades are fully automatic with no customer action required. Use when planning, executing, or monitoring a version upgrade.
compatibility: Self-Hosted requires CLI and SQL access with admin role. Advanced/BYOC requires Cloud Console with Cluster Admin role. Standard/Basic upgrades are automatic — SQL access needed only for post-upgrade verification.
---

# Upgrading Cluster Version

Guides CockroachDB version upgrades end-to-end. Before providing procedures, this skill gathers deployment context to deliver only the steps relevant to the operator's tier.

## When to Use This Skill

- Planning or executing a version upgrade (Self-Hosted)
- Initiating a major version upgrade via Cloud Console (Advanced, BYOC)
- Configuring how patches are applied (Advanced, BYOC)
- Verifying upgrade completion (all tiers)
- Deciding whether to finalize or roll back (Self-Hosted)

**For pre-upgrade health check:** Use [reviewing-cluster-health](../reviewing-cluster-health/SKILL.md).
**For node drain during Self-Hosted upgrade:** Use [performing-cluster-maintenance](../perform-cluster-maintenance/SKILL.md).
**For maintenance window configuration:** Use [performing-cluster-maintenance](../perform-cluster-maintenance/SKILL.md).

---

## Step 1: Gather Context

### Required Context

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Deployment tier?** | Self-Hosted, Advanced, BYOC, Standard, Basic | Completely different upgrade procedures per tier |
| **Current version?** | e.g., v24.2.5 | Validates upgrade path and compatibility |

### Additional Context (by tier)

**If Self-Hosted:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Upgrade type?** | Major (e.g., 24.2→24.3), Patch (e.g., 24.2.5→24.2.8) | Major requires finalization; patches do not |
| **Target version?** | e.g., v24.3.1 | Confirms version is available and sequential |
| **Deployment platform?** | Bare metal, VMs, Kubernetes (Operator/Helm/manual) | Changes binary replacement and restart procedure |
| **Process manager?** | systemd, manual, container orchestrator | Changes stop/start commands |
| **Node count?** | Number | Affects upgrade sequencing |

**If Advanced or BYOC:**

| Question | Options | Why It Matters |
|----------|---------|----------------|
| **Upgrade type?** | Major version, Patch | Major requires Console initiation; patches use maintenance windows |
| **Cloud provider?** (BYOC only) | AWS, GCP, Azure | For infrastructure-level monitoring during upgrade |
| **Release channel?** | Regular, Innovation | Innovation gives latest features, shorter support window |

**If Standard or Basic:** No additional context needed — upgrades are fully automatic.

### Context-Driven Routing

| Tier | Go To |
|------|-------|
| Self-Hosted | [Self-Hosted Upgrade](#self-hosted-upgrade) |
| Advanced | [Advanced Upgrade](#advanced-upgrade) |
| BYOC | [BYOC Upgrade](#byoc-upgrade) |
| Standard | [Standard Upgrade](#standard-upgrade) |
| Basic | [Basic Upgrade](#basic-upgrade) |

---

## Self-Hosted Upgrade

**Applies when:** Tier = Self-Hosted

### Major vs Patch

| Aspect | Major (e.g., 24.2→24.3) | Patch (e.g., 24.2.5→24.2.8) |
|--------|--------------------------|------------------------------|
| Finalization required | Yes | No |
| Rollback possible | Before finalization | Always (binary swap) |
| Auto-finalization | Enabled by default (disable recommended) | N/A |

### Pre-Upgrade Validation

```bash
# All nodes live, version-consistent, fully replicated
cockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>
```

In the output: every node should show `is_l

Preview of the first 4,000 characters. Read full instructions →