Security Best Practices

Security is critical for any production database deployment. This guide covers essential security practices for CockroachDB clusters.

Security Overview

CockroachDB provides multiple layers of security:

Authentication: Verify identity of users and nodes
Encryption: Protect data in transit and at rest
Authorization: Control access to database objects
Network Security: Restrict network access to the cluster
Auditing: Track database operations for compliance

By default, CockroachDB clusters are insecure. You must explicitly enable security features for production deployments.

Authentication

TLS Certificates for Node Communication

Secure inter-node communication with TLS 1.3:

Create CA certificate

# Create CA certificate and key
cockroach cert create-ca \
  --certs-dir=certs \
  --ca-key=my-safe-directory/ca.key

Store the CA key (ca.key) in a secure location, separate from the certificates directory. You’ll need it to create additional certificates.

Create node certificates

# Create certificate for each node
cockroach cert create-node \
  localhost \
  node1.example.com \
  node1.internal \
  *.example.com \
  --certs-dir=certs \
  --ca-key=my-safe-directory/ca.key

# Copy certificates to each node
scp certs/ca.crt certs/node.crt certs/node.key node1:/certs/

Certificate must include:

Node’s hostname and IP address
Any DNS names or wildcards used to access the node
localhost if connecting locally

Create client certificates

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

# Create certificates for other users
cockroach cert create-client \
  myuser \
  --certs-dir=certs \
  --ca-key=my-safe-directory/ca.key

Start secure cluster

cockroach start \
  --certs-dir=certs \
  --advertise-addr=node1.example.com:26257 \
  --join=node1.example.com:26257,node2.example.com:26257,node3.example.com:26257 \
  --background

Omit the --insecure flag to require TLS for all connections. This enables secure mode.

SQL User Authentication

Password authentication (SCRAM-SHA-256)

CockroachDB uses SCRAM-SHA-256 for secure password authentication:

-- Create user with password
CREATE USER myuser WITH PASSWORD 'securepassword';

-- Change user password
ALTER USER myuser WITH PASSWORD 'newsecurepassword';

-- Connect with password
cockroach sql --url "postgresql://myuser:[email protected]:26257/mydb?sslmode=require"

Password requirements:

Enforce strong passwords in your application
Rotate passwords regularly
Store passwords securely (never in source code)
Use password managers for credential management

Certificate authentication

Use client certificates for authentication:

# Connect with client certificate
cockroach sql \
  --certs-dir=certs \
  --host=node1.example.com:26257

# Application connection with certificate
postgresql://[email protected]:26257/mydb?sslmode=require&sslcert=certs/client.myuser.crt&sslkey=certs/client.myuser.key&sslrootcert=certs/ca.crt

Benefits:

No passwords to manage
Strong cryptographic authentication
Suitable for service accounts and automation

Single Sign-On (SSO)

Integrate with enterprise identity providers:

-- Enable SSO for DB Console
SET CLUSTER SETTING server.oidc_authentication.enabled = true;
SET CLUSTER SETTING server.oidc_authentication.provider_url = 'https://accounts.google.com';
SET CLUSTER SETTING server.oidc_authentication.client_id = 'your-client-id';
SET CLUSTER SETTING server.oidc_authentication.client_secret = 'your-client-secret';
SET CLUSTER SETTING server.oidc_authentication.redirect_url = 'https://cockroach.example.com:8080/oidc/v1/callback';

Supported protocols:

OpenID Connect (OIDC)
GSSAPI with Kerberos (Enterprise)
SAML (via third-party proxy)

Encryption

Encryption in Transit

All network communication should use TLS:

Enable TLS for all connections

# Clients must use sslmode=require or higher
psql "postgresql://[email protected]:26257/mydb?sslmode=require"

# Verify TLS connection
SHOW session_id;
-- Check that connection uses encryption

SSL modes:

disable: No encryption (insecure, dev only)
allow: Opportunistic encryption (insecure)
prefer: Use encryption if available (insecure)
require: Require encryption (secure)
verify-ca: Require encryption and verify CA (more secure)
verify-full: Require encryption and verify hostname (most secure)

Always use sslmode=require or higher in production. Lower modes are vulnerable to man-in-the-middle attacks.

Encryption at Rest

Infrastructure-level encryption

All major cloud providers offer encryption at rest:

AWS: Amazon EBS encryption with KMS
GCP: Persistent disk encryption with Cloud KMS
Azure: Managed disk encryption with Key Vault

# AWS example: Create encrypted EBS volume
aws ec2 create-volume \
  --size 100 \
  --encrypted \
  --kms-key-id arn:aws:kms:us-east-1:123456789012:key/12345678-1234-1234-1234-123456789012 \
  --availability-zone us-east-1a

CockroachDB Enterprise Encryption at Rest

CockroachDB Enterprise provides application-level encryption:

# Enable encryption at rest with key files
cockroach start \
  --certs-dir=certs \
  --enterprise-encryption=path=cockroach-data,key=aes-128.key,old-key=plain \
  --advertise-addr=node1.example.com:26257 \
  --join=node1.example.com:26257,node2.example.com:26257,node3.example.com:26257

Features:

AES-128, AES-192, or AES-256 encryption
Key rotation support
Per-store encryption keys
Integration with key management systems

Authorization

Role-Based Access Control (RBAC)

Create roles

-- Create roles for different access levels
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin_role;

-- Create users and assign roles
CREATE USER analyst WITH PASSWORD 'password';
GRANT readonly TO analyst;

CREATE USER developer WITH PASSWORD 'password';
GRANT readwrite TO developer;

CREATE USER dba WITH PASSWORD 'password';
GRANT admin_role TO dba;

Grant privileges to roles

-- Grant database-level privileges
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT CONNECT, CREATE ON DATABASE mydb TO readwrite;
GRANT ALL ON DATABASE mydb TO admin_role;

-- Grant table-level privileges
GRANT SELECT ON TABLE users, orders TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE users, orders TO readwrite;
GRANT ALL ON TABLE users, orders TO admin_role;

-- Grant schema-level privileges
GRANT USAGE ON SCHEMA public TO readonly;
GRANT USAGE, CREATE ON SCHEMA public TO readwrite;

Implement least privilege

-- Application service account (limited privileges)
CREATE USER app_service WITH PASSWORD 'password';
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_service;
GRANT SELECT ON TABLE products TO app_service;
GRANT USAGE ON SCHEMA public TO app_service;

-- Read-only replica user
CREATE USER readonly_replica WITH PASSWORD 'password';
GRANT SELECT ON DATABASE mydb TO readonly_replica;

-- Backup service account
CREATE USER backup_service WITH PASSWORD 'password';
GRANT SYSTEM BACKUP TO backup_service;

Grant only the minimum privileges required for each user or service to function. This limits damage if credentials are compromised.

Default Privileges

Set privileges automatically for new objects:

-- Grant default privileges on future tables
ALTER DEFAULT PRIVILEGES 
  FOR ROLE developer 
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

ALTER DEFAULT PRIVILEGES 
  FOR ROLE developer 
  GRANT SELECT ON TABLES TO readonly;

-- Grant default privileges in specific schema
ALTER DEFAULT PRIVILEGES 
  IN SCHEMA analytics 
  GRANT SELECT ON TABLES TO analyst_role;

Network Security

IP Allowlisting

Configure host-based authentication

Restrict connections by IP address:

# Create HBA configuration file
cat > hba.conf <<EOF
# Allow local connections
host all all 127.0.0.1/32 cert-password
host all all ::1/128 cert-password

# Allow specific application servers
host all app_user 10.0.1.0/24 password
host all app_user 10.0.2.0/24 password

# Allow admin from office network
host all admin 203.0.113.0/24 cert-password

# Deny all other connections
host all all all reject
EOF

# Start node with HBA config
cockroach start \
  --certs-dir=certs \
  --accept-sql-without-tls \
  --hba-conf=hba.conf

HBA rules:

Rules are evaluated in order (first match wins)
Use cert-password for certificate or password auth
Use cert for certificate-only auth
Use password for password-only auth
Use reject to explicitly deny

Firewall Configuration

Restrict network access at the infrastructure level:

# AWS Security Group example
aws ec2 authorize-security-group-ingress \
  --group-id sg-123456 \
  --protocol tcp \
  --port 26257 \
  --source-group sg-app-servers  # Only from application servers

aws ec2 authorize-security-group-ingress \
  --group-id sg-123456 \
  --protocol tcp \
  --port 8080 \
  --cidr 203.0.113.0/24  # DB Console from office network only

# Allow inter-node communication
aws ec2 authorize-security-group-ingress \
  --group-id sg-123456 \
  --protocol tcp \
  --port 26257 \
  --source-group sg-123456

Ports to protect:

26257: SQL and inter-node (PostgreSQL wire protocol)
8080: DB Console HTTP interface
26258: Node status and metrics

Private Network Connectivity

VPC Peering

Connect application VPC to database VPC:

# AWS VPC Peering
aws ec2 create-vpc-peering-connection \
  --vpc-id vpc-app \
  --peer-vpc-id vpc-database

# Update route tables
aws ec2 create-route \
  --route-table-id rtb-app \
  --destination-cidr-block 10.1.0.0/16 \
  --vpc-peering-connection-id pcx-123456

AWS PrivateLink / GCP Private Service Connect

Private connectivity without VPC peering:

AWS PrivateLink: Expose CockroachDB through VPC endpoint
GCP Private Service Connect: Connect via private service connection
Azure Private Link: Use private endpoints

Benefits:

No IP conflicts between VPCs
Simplified network management
Enhanced security

Audit Logging

Track database operations for compliance and security:

-- Enable SQL audit logging for sensitive tables
ALTER TABLE users EXPERIMENTAL_AUDIT SET READ WRITE;
ALTER TABLE financial_records EXPERIMENTAL_AUDIT SET READ WRITE;

-- Enable audit logging for entire database
ALTER DATABASE mydb EXPERIMENTAL_AUDIT SET READ WRITE;

-- View audit log settings
SHOW EXPERIMENTAL_AUDIT FOR TABLE users;

-- Disable audit logging
ALTER TABLE users EXPERIMENTAL_AUDIT SET OFF;

Audit Log Configuration

# Configure audit log sinks in logging configuration
sinks:
  file-groups:
    sensitive:
      channels:
        - SENSITIVE_ACCESS
      dir: /var/log/cockroach/audit
      max-file-size: 100mib
      max-group-size: 1gib
      file-defaults:
        format: json
        redactable: false

What gets logged:

All SQL statements affecting audited tables
User who executed the statement
Timestamp of execution
Source IP address
Application name