CockroachDB Connector
Comprehensive audit logging for CockroachDB distributed SQL databases. Monitor queries, schema changes, and access patterns across your entire cluster with native PostgreSQL wire protocol support.
Native Deep Integration
DB Audit leverages CockroachDB's built-in audit logging channels and crdb_internal tables for comprehensive cluster-wide visibility without impacting performance.
Cluster-Wide Auditing
Monitor all nodes in your CockroachDB cluster with unified audit log collection and aggregation.
Distributed Transactions
Track distributed transactions across nodes with full transaction ID correlation and timing.
PostgreSQL Compatible
Leverage PostgreSQL wire protocol compatibility for familiar audit patterns and tooling.
Prerequisites
Supported Versions
- CockroachDB 21.1 and later
- CockroachDB Serverless
- CockroachDB Dedicated
- CockroachDB Self-Hosted
Network Requirements
- Port 26257 (SQL) - Required
- Port 8080 (Admin UI) - Optional
- TLS encryption (recommended)
- Load balancer access for multi-node
Configuration Reference
1Basic Connection
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| name | string | Yes | - | A unique, descriptive name for this connection (e.g., "cockroachdb-prod-cluster") |
| db_type | select | Yes | cockroachdb | Database type - select "CockroachDB" |
| host | string | Yes | - | CockroachDB hostname or load balancer address (e.g., "cockroach.example.com" or "localhost") |
| port | number | Yes | 26257 | CockroachDB SQL port (default: 26257) |
| database_name | string | Yes | - | Target database name (e.g., "defaultdb", "myapp") |
| username | string | Yes | - | CockroachDB user with audit privileges (e.g., "dbaudit_reader") |
| password | password | Yes | - | Password for the CockroachDB user |
2SSL/TLS Configuration
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| ssl_mode | select | No | verify-full | SSL connection mode: disable, allow, prefer, require, verify-ca, verify-full (CockroachDB recommends verify-full) |
| ssl_ca_cert | textarea | No | - | CA certificate for verifying the CockroachDB cluster certificate |
| ssl_client_cert | textarea | No | - | Client certificate for certificate-based authentication |
| ssl_client_key | password | No | - | Client private key for certificate-based authentication |
3CockroachDB Cloud Configuration
| Field | Type | Required | Description |
|---|---|---|---|
| cluster_id | string | No | CockroachDB Cloud cluster ID (for CockroachDB Serverless/Dedicated) |
| api_key | password | No | CockroachDB Cloud API key for management operations |
| cloud_organization | string | No | CockroachDB Cloud organization ID |
4Log Collection Configuration
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| log_collection_type | select | Yes | native_audit | Log collection method: native_audit (SQL audit logging), cloudwatch (for AWS deployments), custom_query |
| audit_log_dir | string | No | - | Directory path for file-based audit logs (e.g., "/var/log/cockroach/audit") |
| log_channels | multiselect | No | - | Audit channels to monitor: SQL_EXEC, SQL_PERF, SESSIONS, USER_ADMIN, PRIVILEGES, SENSITIVE_ACCESS |
| poll_interval | number | No | 30 | How often to poll for new audit events (in seconds) |
Log Collection Methods
SQL Audit Logging
RecommendedCockroachDB native audit logging with configurable channels and targets
Advantages:
- Granular audit channels
- Multiple output targets
- Low performance overhead
- Cluster-wide configuration
crdb_internal Tables
Query internal system tables for session and query information
Advantages:
- No configuration required
- Real-time data
- Rich metadata
CloudWatch Logs
Ship audit logs to AWS CloudWatch for CockroachDB on AWS
Advantages:
- Centralized AWS logging
- Integration with AWS services
- Long-term retention
Setup Instructions
Create Audit User
Create a dedicated CockroachDB user for DB Audit with appropriate privileges.
-- Create a dedicated audit user
CREATE USER dbaudit_reader WITH PASSWORD 'secure_password';
-- Grant necessary privileges
GRANT SELECT ON DATABASE defaultdb TO dbaudit_reader;
GRANT SELECT ON crdb_internal.* TO dbaudit_reader;
GRANT SELECT ON system.* TO dbaudit_reader;
-- Grant access to audit log views
GRANT VIEWACTIVITY ON CLUSTER TO dbaudit_reader;
GRANT VIEWACTIVITYREDACTED ON CLUSTER TO dbaudit_reader;
Enable SQL Audit Logging
Configure CockroachDB cluster settings to enable audit logging.
-- Enable SQL audit logging via cluster settings
SET CLUSTER SETTING server.auth_log.sql_connections.enabled = true;
SET CLUSTER SETTING server.auth_log.sql_sessions.enabled = true;
-- Configure audit log channels
SET CLUSTER SETTING sql.log.user_audit.enabled = true;
-- Set audit log format
SET CLUSTER SETTING server.logging.format = 'json';
-- Configure sensitive access logging
SET CLUSTER SETTING sql.log.unstructured_entries.enabled = true;
Tip: For production environments, consider using the cockroach.yaml configuration file for more granular audit log channel control.
Configure Audit Log Channels (Optional)
For advanced configurations, use the cockroach.yaml file to specify audit channels and sinks.
# cockroach.yaml audit configuration
logging:
sinks:
file-groups:
audit:
channels: [SQL_EXEC, SQL_PERF, SESSIONS, USER_ADMIN, PRIVILEGES, SENSITIVE_ACCESS]
filter: INFO
format: json
max-file-size: 10MiB
max-group-size: 100MiB
fluent-servers:
security-events:
channels: [SQL_EXEC, SENSITIVE_ACCESS]
address: localhost:24224
format: json-fluent
Test Connection
Verify the connection and audit user permissions using the cockroach CLI.
# Test connection to CockroachDB
cockroach sql --url "postgresql://dbaudit_reader@localhost:26257/defaultdb?sslmode=verify-full&sslrootcert=ca.crt"
# Verify audit user permissions
cockroach sql --execute "SELECT * FROM crdb_internal.node_queries LIMIT 1;"
# Check cluster settings
cockroach sql --execute "SHOW CLUSTER SETTING sql.log.user_audit.enabled;"
Configure in DB Audit
Add the CockroachDB connection in the DB Audit dashboard.
- Navigate to Connections in DB Audit
- Click Add Connection
- Select CockroachDB as the database type
- Enter your connection details and credentials
- Select your preferred log collection method
- Test the connection and save
Data Collected
All SELECT, INSERT, UPDATE, DELETE queries with parameters
Login/logout, session duration, client addresses
CREATE, ALTER, DROP operations across all nodes
GRANT, REVOKE, role modifications
Distributed transaction tracking with full correlation
Query failures, constraint violations, permission denied
Node ID, region, locality for each operation
Rows read/written, execution time, contention events
Troubleshooting
Connection refused error
Ensure CockroachDB is accepting connections on port 26257 and the load balancer (if used) is properly configured.
cockroach node status --host=localhost:26257
Certificate verification failed
CockroachDB requires TLS by default. Ensure you have the correct CA certificate and it's properly configured.
# Download cluster CA certificate
cockroach cert create-ca --certs-dir=certs --ca-key=certs/ca.key
Missing privileges for crdb_internal
The audit user needs VIEWACTIVITY privilege to query internal tables.
GRANT VIEWACTIVITY ON CLUSTER TO dbaudit_reader;
Audit logs not appearing
Verify that SQL audit logging is enabled in cluster settings.
SHOW CLUSTER SETTING sql.log.user_audit.enabled;
SHOW CLUSTER SETTING server.auth_log.sql_connections.enabled;
Compliance Support
Access logging and change tracking
PHI access auditing
Cardholder data monitoring
Personal data access trails
Ready to Audit Your CockroachDB Clusters?
Start monitoring your distributed SQL databases in minutes with comprehensive audit logging.