CockroachDB audit logging isn’t just a checkbox for compliance; it’s a granular, event-driven ledger that captures who did what and when to your data, enabling you to reconstruct a complete history of access and modification.
Let’s see it in action. Imagine a scenario where we need to track all UPDATE statements on a critical users table.
First, we enable audit logging. We can configure this globally or per database. For our example, let’s do it globally for simplicity, but remember you can scope it down.
# Example: Enabling audit logging globally
# Connect to your CockroachDB cluster using the cockroach CLI
cockroach sql --certs-dir=/path/to/certs --host=your-node-address --user=root
# Then execute the following SQL command:
SET CLUSTER SETTING audit.event_log.enabled = 'true';
SET CLUSTER SETTING audit.event_log.log_statement_unsafe = 'true'; # If you want to log the full SQL statement
SET CLUSTER SETTING audit.event_log.log_transaction_unsafe = 'true'; # If you want to log the full transaction
Now, let’s define a specific audit policy to capture UPDATE statements on the users table. We’ll log the user, the timestamp, the statement itself, and the affected table.
-- Create a table to store audit logs (or configure to log to file)
-- For demonstration, we'll create a simple table. In production, you'd likely
-- configure this to log to a file or a dedicated logging system.
CREATE TABLE audit_log (
timestamp TIMESTAMPTZ DEFAULT now(),
user_name STRING,
statement TEXT,
table_name STRING
);
-- Create an audit policy
CREATE AUDIT POLICY update_users_policy
FOR EVENT UPDATE
ON TABLE users
AS ($user, $statement, $table)
DO LOG ($user, $statement, $table)
TO audit_log;
With this policy in place, any UPDATE statement executed on the users table will trigger an entry in our audit_log table.
Let’s simulate an update:
-- Assume you have a 'users' table with some data
CREATE TABLE users (id INT PRIMARY KEY, name STRING, email STRING);
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
-- Now, let's perform an update as a specific user (e.g., 'app_user')
-- In a real scenario, 'app_user' would have specific privileges.
UPDATE users SET email = 'alice.smith@example.com' WHERE id = 1;
Now, let’s query our audit_log table:
SELECT * FROM audit_log;
You’d see an entry similar to this:
timestamp | user_name | statement | table_name
----------------------------+-----------+-------------------------------------+------------
2023-10-27 10:30:00.123456+00 | app_user | UPDATE users SET email = 'alice.smith@example.com' WHERE id = 1 | users
This gives us a clear trail: app_user modified the users table with a specific UPDATE statement at a precise time.
The mental model for CockroachDB audit logging revolves around three core components:
- Event Types: What actions are you interested in? CockroachDB supports a wide range of events, including
SELECT,INSERT,UPDATE,DELETE,CREATE TABLE,DROP TABLE,GRANT,REVOKE,LOGIN,LOGOUT, and more. You can also log DDL, DML, or specific statements. - Audit Policies: These are the rules you define. A policy specifies which events you want to capture, on which objects (tables, databases, or the entire cluster), and what data to extract from those events (e.g., the user, the statement, the affected rows’ primary keys).
- Log Destination: Where do the audit logs go? You can configure CockroachDB to write logs to the standard CockroachDB log files (which can then be ingested by external logging systems like Splunk, Elasticsearch, etc.) or, as shown in the example, to a specific table within the database itself for easier querying and analysis.
The key to leveraging audit logging effectively for compliance lies in understanding the AS and DO clauses of the CREATE AUDIT POLICY statement. The AS clause defines the variables that will be available within the policy’s scope for that specific event. For example, $user represents the user executing the statement, $statement is the raw SQL string (if log_statement_unsafe is enabled), and $table is the name of the table involved. The DO clause then dictates what to do with these variables – in our case, LOG them to the audit_log table.
A common pitfall is enabling audit logging without defining specific, targeted policies. If you simply enable audit.event_log.enabled = 'true', you’ll get a deluge of logs that are difficult to parse and analyze for compliance purposes. The real power comes from creating granular policies that focus only on the events and objects relevant to your compliance requirements, like tracking all modifications to sensitive data tables or all administrative privilege changes.
Once you’ve established a robust audit logging strategy, the next logical step for compliance and security is to implement Role-Based Access Control (RBAC) to ensure that only authorized users can perform sensitive operations, further limiting the attack surface and making your audit trails more meaningful.