CIS CockroachDB Benchmark

This recommendation ensures that SCRAM-SHA-256 authentication with host-based rules is enforced on the CockroachDB distributed SQL database. Enforcing this requirement establishes a minimum security standard and prevents insecure configurations.

Without this enforcement, the CockroachDB distributed SQL database may accept insecure configurations that weaken overall security posture. Mandating this control ensures consistent protection against common attack vectors.

# Check cluster authentication:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW CLUSTER SETTING server.user_login.password_encryption;"

# Verify HBA rules:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW CLUSTER SETTING server.host_based_authentication.configuration;"

# Set scram-sha-256 authentication:
cockroach sql --certs-dir=/var/lib/cockroach/certs << 'SQL'
SET CLUSTER SETTING server.user_login.password_encryption = 'scram-sha-256';

-- Configure HBA rules:
SET CLUSTER SETTING server.host_based_authentication.configuration = '
  host all all 0.0.0.0/0 cert-scram-sha-256
  host all all ::0/0 cert-scram-sha-256
';
SQL

This recommendation verifies that least-privilege roles with explicit grants replace admin access on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# List all users and roles:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SELECT username, is_role FROM system.users;"

# Check role memberships:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SELECT * FROM system.role_members;"

# Create roles with least privilege:
cockroach sql --certs-dir=/var/lib/cockroach/certs << 'SQL'
-- Create application role:
CREATE ROLE app_role;
GRANT SELECT, INSERT, UPDATE ON DATABASE app_db TO app_role;

-- Create read-only role:
CREATE ROLE readonly_role;
GRANT SELECT ON DATABASE app_db TO readonly_role;

-- Create users with roles:
CREATE USER app_user WITH PASSWORD NULL;
GRANT app_role TO app_user;

-- Revoke unnecessary admin grants:
REVOKE admin FROM non_admin_user;

-- Remove default public grants:
REVOKE ALL ON DATABASE defaultdb FROM public;
SQL

This recommendation verifies that mutual TLS is configured for inter-node and client connections on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check TLS certificates:
ls -la /var/lib/cockroach/certs/

# Verify node certificate:
openssl x509 -in /var/lib/cockroach/certs/node.crt -noout -text | grep -E 'Issuer|Subject|Not After'

# Generate certificates:
mkdir -p /var/lib/cockroach/certs /var/lib/cockroach/my-safe-directory

# Create CA:
cockroach cert create-ca --certs-dir=/var/lib/cockroach/certs --ca-key=/var/lib/cockroach/my-safe-directory/ca.key

# Create node certs:
cockroach cert create-node \
  localhost $(hostname) \
  node1.cockroach.example.com \
  --certs-dir=/var/lib/cockroach/certs \
  --ca-key=/var/lib/cockroach/my-safe-directory/ca.key

# Create client cert for root:
cockroach cert create-client root \
  --certs-dir=/var/lib/cockroach/certs \
  --ca-key=/var/lib/cockroach/my-safe-directory/ca.key

# Start node with TLS:
cockroach start --certs-dir=/var/lib/cockroach/certs --listen-addr=:26257 --http-addr=:8080

This recommendation verifies that encryption at rest is enabled with AES-256 store encryption on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check encryption at rest:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW CLUSTER SETTING storage.encryption.key;"

# Verify store encryption:
ls -la /var/lib/cockroach/cockroach-data/

# Enable encryption at rest:
cockroach start \
  --certs-dir=/var/lib/cockroach/certs \
  --store=path=/var/lib/cockroach/cockroach-data,attrs=ssd \
  --enterprise-encryption=path=/var/lib/cockroach/cockroach-data,key=/var/lib/cockroach/keys/aes-256.key,old-key=plain \
  --listen-addr=:26257 \
  --http-addr=:8080

# Generate AES key:
mkdir -p /var/lib/cockroach/keys
openssl rand -out /var/lib/cockroach/keys/aes-256.key 32
chmod 600 /var/lib/cockroach/keys/aes-256.key

This recommendation verifies that multi-region replication with locality constraints is configured on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check cluster locality:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SELECT node_id, locality FROM crdb_internal.kv_node_status;"

# Check replication zones:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW ZONE CONFIGURATION FOR RANGE default;"

# Configure replication and locality:
cockroach start \
  --locality=region=us-east-1,zone=us-east-1a \
  --certs-dir=/var/lib/cockroach/certs \
  --listen-addr=:26257

# Set replication factor:
cockroach sql --certs-dir=/var/lib/cockroach/certs << 'SQL'
ALTER RANGE default CONFIGURE ZONE USING
  num_replicas = 5,
  gc.ttlseconds = 90000,
  constraints = '{+region=us-east-1: 2, +region=us-west-2: 2, +region=eu-west-1: 1}';
SQL

This recommendation verifies that automated full and incremental backup schedules are active on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check changefeed configuration:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SELECT * FROM [SHOW CHANGEFEED JOBS];" | head -10

# Check backup schedules:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SELECT * FROM [SHOW SCHEDULES] WHERE schedule_name LIKE '%backup%';"

# Configure automated backups:
cockroach sql --certs-dir=/var/lib/cockroach/certs << 'SQL'
-- Full backup daily to S3:
CREATE SCHEDULE daily_backup
  FOR BACKUP INTO 's3://cockroach-backups/daily?AUTH=implicit'
  WITH revision_history
  RECURRING '@daily'
  WITH SCHEDULE OPTIONS first_run = 'now';

-- Incremental every hour:
CREATE SCHEDULE hourly_incremental
  FOR BACKUP INTO LATEST IN 's3://cockroach-backups/daily?AUTH=implicit'
  WITH revision_history
  RECURRING '@hourly'
  WITH SCHEDULE OPTIONS first_run = 'now';
SQL

This recommendation verifies that SQL audit logging and authentication event logging are enabled on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check SQL audit logging for specific tables:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW CLUSTER SETTING sql.log.all_statements.enabled;"

# Check audit table:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW EXPERIMENTAL_AUDIT;" 2>/dev/null

# Enable audit logging:
cockroach sql --certs-dir=/var/lib/cockroach/certs << 'SQL'
-- Enable slow query logging:
SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '100ms';

-- Enable statement logging for sensitive tables:
ALTER TABLE sensitive_data EXPERIMENTAL_AUDIT SET READ WRITE;

-- Log all authentication events:
SET CLUSTER SETTING server.auth_log.sql_connections.enabled = true;
SET CLUSTER SETTING server.auth_log.sql_sessions.enabled = true;
SQL

This recommendation verifies that diagnostic telemetry is disabled and stats collection is configured on the CockroachDB distributed SQL database. Disabling or removing unnecessary components reduces the attack surface and limits potential vectors for exploitation.

Running unnecessary components on the CockroachDB distributed SQL database increases the attack surface and the risk of exploitation. Disabling or removing them follows the principle of least functionality and reduces exposure to known vulnerabilities.

# Check SQL stats configuration:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;"

# Check diagnostic reporting:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW CLUSTER SETTING diagnostics.reporting.enabled;"

# Configure monitoring and disable unnecessary telemetry:
cockroach sql --certs-dir=/var/lib/cockroach/certs << 'SQL'
-- Disable diagnostic reporting:
SET CLUSTER SETTING diagnostics.reporting.enabled = false;
SET CLUSTER SETTING diagnostics.reporting.send_crash_reports = false;

-- Enable stats collection:
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = true;
SET CLUSTER SETTING sql.stats.automatic_collection.min_stale_rows = 500;

-- Configure statement diagnostics:
SET CLUSTER SETTING sql.stats.persisted_rows.max = 10000;
SQL

This recommendation verifies that connection limits and statement timeouts are configured on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check cluster settings for memory and connections:
cockroach sql --certs-dir=/var/lib/cockroach/certs << 'SQL'
SHOW CLUSTER SETTING sql.defaults.results_buffer.size;
SHOW CLUSTER SETTING server.max_connections_per_gateway;
SHOW CLUSTER SETTING kv.snapshot_rebalance.max_rate;
SQL

# Configure resource limits:
cockroach sql --certs-dir=/var/lib/cockroach/certs << 'SQL'
-- Limit max connections:
SET CLUSTER SETTING server.max_connections_per_gateway = 1000;

-- Configure memory budget:
SET CLUSTER SETTING sql.distsql.temp_storage.workmem = '128MiB';

-- Limit query result buffer:
SET CLUSTER SETTING sql.defaults.results_buffer.size = '16KiB';

-- Set statement timeout:
SET CLUSTER SETTING sql.defaults.statement_timeout = '60s';
SET CLUSTER SETTING sql.defaults.idle_in_session_timeout = '300s';
SET CLUSTER SETTING sql.defaults.idle_in_transaction_session_timeout = '60s';
SQL

This recommendation verifies that admission control is enabled for overload protection on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check admission control:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW CLUSTER SETTING admission.kv.enabled;"

# Check CPU overload:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SELECT * FROM crdb_internal.node_metrics WHERE name LIKE 'admission%' LIMIT 10;"

# Configure admission control:
cockroach sql --certs-dir=/var/lib/cockroach/certs << 'SQL'
-- Enable admission control:
SET CLUSTER SETTING admission.kv.enabled = true;
SET CLUSTER SETTING admission.sql_kv_response.enabled = true;
SET CLUSTER SETTING admission.sql_sql_response.enabled = true;

-- Configure rate limiting per tenant:
SET CLUSTER SETTING sql.defaults.transaction_rows_read_err = 100000;
SET CLUSTER SETTING sql.defaults.transaction_rows_written_err = 50000;
SQL

This recommendation verifies that CockroachDB is running a current supported version on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check CockroachDB version:
cockroach version

# Check nodes:
cockroach node status --certs-dir=/var/lib/cockroach/certs | head -10

# Upgrade CockroachDB (rolling):
# 1. Download new version:
VER=24.1.0
curl -O https://binaries.cockroachdb.com/cockroach-v${VER}.linux-amd64.tgz
tar xzf cockroach-v${VER}.linux-amd64.tgz

# 2. Stop node, replace binary, restart:
systemctl stop cockroachdb
cp cockroach-v${VER}.linux-amd64/cockroach /usr/local/bin/
systemctl start cockroachdb

# 3. After all nodes upgraded, finalize:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SET CLUSTER SETTING version = crdb_internal.node_executable_version();"

This recommendation verifies that CockroachDB runs as a dedicated non-root user with strict systemd controls on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check CockroachDB process user:
ps aux | grep cockroach | grep -v grep | awk '{print $1}'

# Check file permissions:
ls -la /var/lib/cockroach/ /etc/cockroach/

# Run as dedicated user:
useradd --system --no-create-home --shell /usr/sbin/nologin cockroach
mkdir -p /var/lib/cockroach/cockroach-data
chown -R cockroach:cockroach /var/lib/cockroach
chmod 700 /var/lib/cockroach/my-safe-directory

cat > /etc/systemd/system/cockroachdb.service << 'EOF'
[Unit]
Description=CockroachDB Server
After=network.target

[Service]
Type=notify
User=cockroach
Group=cockroach
ExecStart=/usr/local/bin/cockroach start \
  --certs-dir=/var/lib/cockroach/certs \
  --store=/var/lib/cockroach/cockroach-data \
  --listen-addr=:26257 \
  --http-addr=:8080 \
  --join=node1:26257,node2:26257,node3:26257
Restart=always
RestartSec=10
NoNewPrivileges=true
ProtectHome=true
ProtectSystem=strict
ReadWritePaths=/var/lib/cockroach
LimitNOFILE=65535

[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload && systemctl enable --now cockroachdb

This recommendation verifies that safe node decommission procedure is followed with rebalance verification on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check node decommission safety:
cockroach node status --certs-dir=/var/lib/cockroach/certs

# Check under-replicated ranges:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SELECT * FROM crdb_internal.ranges WHERE array_length(replicas, 1) < 3 LIMIT 5;"

# Decommission node safely:
cockroach node decommission <node_id> --certs-dir=/var/lib/cockroach/certs

# Wait until fully decommissioned:
cockroach node status --certs-dir=/var/lib/cockroach/certs --decommission

# Monitor rebalancing:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "
  SELECT store_id, range_count, used, available
  FROM crdb_internal.kv_store_status;"

This recommendation verifies that point-in-time restore procedure is documented and tested on the CockroachDB distributed SQL database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the CockroachDB distributed SQL database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check cluster health:
cockroach node status --certs-dir=/var/lib/cockroach/certs

# Verify backup history:
cockroach sql --certs-dir=/var/lib/cockroach/certs -e "SHOW BACKUP LATEST IN 's3://cockroach-backups/daily?AUTH=implicit';" 2>/dev/null | head -10

# Create point-in-time restore procedure:
cat > /usr/local/bin/cockroach-restore.sh << 'SCRIPT'
#!/bin/bash
set -e
CERTS=/var/lib/cockroach/certs
BACKUP_URI="s3://cockroach-backups/daily?AUTH=implicit"

# List available backups:
cockroach sql --certs-dir=$CERTS -e "SHOW BACKUPS IN '${BACKUP_URI}';"

# Restore to point in time:
# cockroach sql --certs-dir=$CERTS -e "
#   RESTORE FROM LATEST IN '${BACKUP_URI}'
#   AS OF SYSTEM TIME '-1h'
#   WITH detached;
# "

echo "Review available backups above. Uncomment RESTORE command with desired timestamp."
SCRIPT
chmod +x /usr/local/bin/cockroach-restore.sh