configuring-audit-logging

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:

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

-- 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 for additional audit-related queries.

2. Enable Admin Audit Logging

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

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