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

3. Configure Role-Based Audit Logging

Role-based audit logging allows targeted logging for specific roles. This is more efficient than cluster-wide logging.

-- Enable audit logging for a specific role
-- Format: <role_name> <audit_mode>
-- Audit modes: ALL (all statements), READ (reads only), WRITE (writes only), NONE (disable)
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';

Multiple roles:

-- Audit multiple roles (newline-separated)
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL
security_admin ALL
app_service_account READ';

Create purpose-specific audit roles:

-- Create a role for users accessing sensitive data
CREATE ROLE sensitive_data_reader;
GRANT SELECT ON TABLE customers, payments, pii_table TO sensitive_data_reader;

-- Assign users to the audited role
GRANT sensitive_data_reader TO app_user;

-- Enable audit logging for this role
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';

4. Configure Slow Query Logging (Supplemental)

Slow query logging captures queries exceeding a latency threshold, which can indicate unauthorized scans or data exfiltration attempts.

-- Log queries taking longer than 1 second
SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '1s';

-- Log all queries (high overhead — use only for investigation)
-- SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '0';

5. Verify Audit Logging

-- Confirm settings are active
SHOW CLUSTER SETTING sql.log.user_audit;
SHOW CLUSTER SETTING sql.log.admin_audit.enabled;

-- Execute a test statement to generate an audit event
SELECT 1;

Verify log delivery: On CockroachDB Cloud, audit logs are exported to your configured log sink (cloud provider logging service). Check your log export destination to verify events are being captured.

# On CockroachDB Cloud, check log export configuration
ccloud cluster info <cluster-name> -o json
# Look for log_export_config section

Safety Considerations

Performance impact: Audit logging increases CPU and I/O overhead. The impact depends on the audit scope:

Audit Scope	Performance Impact	Recommendation
Admin audit only	Minimal	Safe for all environments
Role-based audit (targeted roles)	Low to moderate	Recommended for production
Cluster-wide all-statement logging	High	Use only during investigations
Slow query logging (threshold > 0)	Minimal	Safe for all environments
Slow query logging (threshold = 0)	Very high	Never use in production

Storage impact: Audit logs increase log volume. Plan for:

• Admin audit: ~1-5% increase in log volume
• Role-based audit: Proportional to query volume of audited roles
• All-statement logging: 10x+ increase in log volume

Recommendations:

• Start with admin audit logging (minimal overhead, high value)
• Add role-based auditing for sensitive data access roles
• Avoid cluster-wide all-statement logging in production
• Configure log rotation and retention policies

Rollback

-- Disable user audit logging
SET CLUSTER SETTING sql.log.user_audit = '';

-- Disable admin audit logging
SET CLUSTER SETTING sql.log.admin_audit.enabled = false;

-- Reset slow query threshold to default
RESET CLUSTER SETTING sql.log.slow_query.latency_threshold;

References

Skill references:

• SQL queries for audit logging

Related skills:

• auditing-cloud-cluster-security — Run a full security posture audit
• hardening-user-privileges — Create purpose-specific roles for targeted auditing

Official CockroachDB Documentation:

• SQL Audit Logging
• Role-Based Audit Logging
• Cluster Settings
• Log Export (CockroachDB Cloud)