Distributed SQL Database

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.

SQL Audit Channels Distributed Tracing crdb_internal Tables
Start Free Trial View Configuration

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

1 Basic 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

2 SSL/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

3 CockroachDB 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

4 Log 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

Recommended

CockroachDB native audit logging with configurable channels and targets

Advantages:

  • Granular audit channels
  • Multiple output targets
  • Low performance overhead
  • Cluster-wide configuration
Setup: Configure via cluster settings or cockroach.yaml

crdb_internal Tables

Query internal system tables for session and query information

Advantages:

  • No configuration required
  • Real-time data
  • Rich metadata
Setup: Query crdb_internal.node_queries, crdb_internal.cluster_sessions

CloudWatch Logs

Ship audit logs to AWS CloudWatch for CockroachDB on AWS

Advantages:

  • Centralized AWS logging
  • Integration with AWS services
  • Long-term retention
Setup: Configure Fluent Bit or CloudWatch agent

Setup Instructions

1

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;
2

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.

3

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
4

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;"
5

Configure in DB Audit

Add the CockroachDB connection in the DB Audit dashboard.

  1. Navigate to Connections in DB Audit
  2. Click Add Connection
  3. Select CockroachDB as the database type
  4. Enter your connection details and credentials
  5. Select your preferred log collection method
  6. Test the connection and save

Data Collected

SQL Statements

All SELECT, INSERT, UPDATE, DELETE queries with parameters

User Sessions

Login/logout, session duration, client addresses

Schema Changes

CREATE, ALTER, DROP operations across all nodes

Privilege Changes

GRANT, REVOKE, role modifications

Transaction IDs

Distributed transaction tracking with full correlation

Errors

Query failures, constraint violations, permission denied

Node Information

Node ID, region, locality for each operation

Performance Metrics

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

SOC 2

Access logging and change tracking

HIPAA

PHI access auditing

PCI DSS

Cardholder data monitoring

GDPR

Personal data access trails

Next Steps

Configure Alerts

Set up real-time alerts for suspicious database activity.

Create Policies

Define audit policies and compliance rules for your CockroachDB clusters.

SIEM Integration

Forward audit events to your security operations platform.

Ready to Audit Your CockroachDB Clusters?

Start monitoring your distributed SQL databases in minutes with comprehensive audit logging.

Start Free Trial Contact Sales