Update to CockroachDB

{
  "description": "Configures SQL audit logging on CockroachDB clusters to capture security-relevant events including authentication, privilege changes, and sensitive data access. Use when enabling audit logging for compliance, setting up role-based audit policies, or verifying audit configuration.",
  "included_files": [
    {
      "relative_path": "references/sql-queries.md",
      "size_in_bytes": 4028
    }
  ],
  "name": "configuring-audit-logging",
  "skill_md_contents": "---\nname: configuring-audit-logging\ndescription: Configures SQL audit logging on CockroachDB clusters to capture security-relevant events including authentication, privilege changes, and sensitive data access. Use when enabling audit logging for compliance, setting up role-based audit policies, or verifying audit configuration.\ncompatibility: Requires admin role for cluster setting changes. Role-based audit logging available on CockroachDB 22.2+.\n---\n\n# Configuring Audit Logging\n\nConfigures 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.\n\n## When to Use This Skill\n\n- Enabling audit logging to meet SOC 2, HIPAA, or PCI DSS compliance requirements\n- Setting up role-based audit policies for specific users or roles\n- Verifying that audit logging is properly configured and capturing events\n- Responding to a security audit finding about missing audit trails\n- Investigating security incidents by reviewing audit log configuration\n\n## Prerequisites\n\n- **SQL access** with admin role (required to modify cluster settings)\n- **CockroachDB version:** 22.2+ for role-based audit logging\n- **Log export** configured for persistent audit trail (CockroachDB Cloud exports logs to your cloud provider)\n- **Storage planning:** Audit logging increases log volume; plan for additional storage\n\n**Check your access:**\n```sql\n-- Verify admin role\nSELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();\n\n-- Check CockroachDB version\nSELECT version();\n```\n\n## Steps\n\n### 1. Check Current Audit Configuration\n\n```sql\n-- User audit logging configuration\nSHOW CLUSTER SETTING sql.log.user_audit;\n\n-- Admin audit logging\nSHOW CLUSTER SETTING sql.log.admin_audit.enabled;\n\n-- All audit-related settings\nSELECT variable, value\nFROM [SHOW ALL CLUSTER SETTINGS]\nWHERE variable LIKE '%audit%'\nORDER BY variable;\n```\n\nSee [SQL queries reference](references/sql-queries.md) for additional audit-related queries.\n\n### 2. Enable Admin Audit Logging\n\nAdmin audit logging captures all SQL statements executed by users with the admin role.\n\n```sql\n-- Enable admin audit logging\nSET CLUSTER SETTING sql.log.admin_audit.enabled = true;\n```\n\n**What is captured:**\n- All SQL statements executed by admin users\n- DDL operations (CREATE, ALTER, DROP)\n- Grant and revoke operations\n- Cluster setting changes\n\n### 3. Configure Role-Based Audit Logging\n\nRole-based audit logging allows targeted logging for specific roles. This is more efficient than cluster-wide logging.\n\n```sql\n-- Enable audit logging for a specific role\n-- Format: <role_name> <audit_mode>\n-- Audit modes: ALL (all statements), READ (reads only), WRITE (writes only), NONE (disable)\nSET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';\n```\n\n**Multiple roles:**\n```sql\n-- Audit multiple roles (newline-separated)\nSET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL\nsecurity_admin ALL\napp_service_account READ';\n```\n\n**Create purpose-specific audit roles:**\n```sql\n-- Create a role for users accessing sensitive data\nCREATE ROLE sensitive_data_reader;\nGRANT SELECT ON TABLE customers, payments, pii_table TO sensitive_data_reader;\n\n-- Assign users to the audited role\nGRANT sensitive_data_reader TO app_user;\n\n-- Enable audit logging for this role\nSET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';\n```\n\n### 4. Configure Slow Query Logging (Supplemental)\n\nSlow query logging captures queries exceeding a latency threshold, which can indicate unauthorized scans or data exfiltration attempts.\n\n```sql\n-- Log queries taking longer than 1 second\nSET CLUSTER SETTING sql.log.slow_query.latency_threshold = '1s';\n\n-- Log all queries (high overhead — use only for investigation)\n-- SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '0';\n```\n\n### 5. Verify Audit Logging\n\n```sql\n-- Confirm settings are active\nSHOW CLUSTER SETTING sql.log.user_audit;\nSHOW CLUSTER SETTING sql.log.admin_audit.enabled;\n\n-- Execute a test statement to generate an audit event\nSELECT 1;\n```\n\n**Verify log delivery:**\nOn 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.\n\n```bash\n# On CockroachDB Cloud, check log export configuration\nccloud cluster info <cluster-name> -o json\n# Look for log_export_config section\n```\n\n## Safety Considerations\n\n**Performance impact:** Audit logging increases CPU and I/O overhead. The impact depends on the audit scope:\n\n| Audit Scope | Performance Impact | Recommendation |\n|-------------|-------------------|----------------|\n| Admin audit only | Minimal | Safe for all environments |\n| Role-based audit (targeted roles) | Low to moderate | Recommended for production |\n| Cluster-wide all-statement logging | High | Use only during investigations |\n| Slow query logging (threshold > 0) | Minimal | Safe for all environments |\n| Slow query logging (threshold = 0) | Very high | Never use in production |\n\n**Storage impact:** Audit logs increase log volume. Plan for:\n- Admin audit: ~1-5% increase in log volume\n- Role-based audit: Proportional to query volume of audited roles\n- All-statement logging: 10x+ increase in log volume\n\n**Recommendations:**\n- Start with admin audit logging (minimal overhead, high value)\n- Add role-based auditing for sensitive data access roles\n- Avoid cluster-wide all-statement logging in production\n- Configure log rotation and retention policies\n\n## Rollback\n\n```sql\n-- Disable user audit logging\nSET CLUSTER SETTING sql.log.user_audit = '';\n\n-- Disable admin audit logging\nSET CLUSTER SETTING sql.log.admin_audit.enabled = false;\n\n-- Reset slow query threshold to default\nRESET CLUSTER SETTING sql.log.slow_query.latency_threshold;\n```\n\n## References\n\n**Skill references:**\n- [SQL queries for audit logging](references/sql-queries.md)\n\n**Related skills:**\n- [auditing-cloud-cluster-security](../audit-cloud-security/SKILL.md) — Run a full security posture audit\n- [hardening-user-privileges](../hardening-user-privileges/SKILL.md) — Create purpose-specific roles for targeted auditing\n\n**Official CockroachDB Documentation:**\n- [SQL Audit Logging](https://www.cockroachlabs.com/docs/stable/sql-audit-logging.html)\n- [Role-Based Audit Logging](https://www.cockroachlabs.com/docs/stable/role-based-audit-logging.html)\n- [Cluster Settings](https://www.cockroachlabs.com/docs/stable/cluster-settings.html)\n- [Log Export (CockroachDB Cloud)](https://www.cockroachlabs.com/docs/cockroachcloud/export-logs.html)\n"
}