Skip to main content
Table-based SQL audit logging gives you detailed information about queries being executed against your system. This feature is especially useful when you want to log all queries that are run against a table containing personally identifiable information (PII). It consists of using the ALTER TABLE... EXPERIMENTAL_AUDIT command to enable the SENSITIVE_ACCESS logging channel per table. This page provides an example of SQL audit logging in CockroachDB, including:
  • How to turn audit logging on and off.
  • Where the audit log files live.
  • What the audit log files look like.
Note that enabling SQL audit logs can negatively impact performance. As a result, we recommend using SQL audit logs for security purposes only. For more details, see Performance considerations.
For the best visibility into security-related events on your cluster, we recommend configuring SENSITIVE_ACCESS together with the USER_ADMIN, PRIVILEGES, and SESSIONS logging channels. To learn more, see Logging Use Cases.
This feature is in preview and subject to change. To share feedback and/or issues, contact Support.

​
File format

Audit log messages, like all log messages, consist of two sections:
  • A payload that contains notable events structured in JSON. These can include information such as the application name, full text of the query (which may contain PII), user account that triggered the event, number of rows produced (e.g., for SELECT ) or processed (e.g., for INSERT or UPDATE ), status of the query, and more. For more information on the possible event types logged to the SENSITIVE_ACCESS channel, see Notable Event Types.
  • An envelope that contains event metadata (e.g., severity, date, timestamp, channel). Depending on the log format you specify when configuring logs, the envelope can be formatted either as JSON or as a flat prefix to the message.

​
File storage location

By default, audit logs are prefixed cockroach-sql-audit and are stored in the same directory as the other logs generated by CockroachDB. To store the audit log files in a specific directory, configure the SENSITIVE_ACCESS channel with a custom dir path.
If your deployment requires particular lifecycle and access policies for audit log files, point SENSITIVE_ACCESS to a directory that has permissions set so that only CockroachDB can create/delete files.

​
Performance considerations

To ensure non-repudiation in audit logs, we recommend enabling auditable for the SENSITIVE_ACCESS channel. CockroachDB will then synchronously log all of the activity of every user on a cluster in a way that is durable to system failures. Note that every query that causes a logging event must access the disk of the node on which audit logging is enabled. As a result, enabling auditable on a logging channel negatively impacts performance, and we recommend using this setting for security purposes only. For debugging and troubleshooting on production clusters, the most performant way to log all queries is to enable the SQL_EXEC logging channel. For details, see Logging Use Cases.

​
Example

​
Step 1. Create sample tables

Use the statements below to create:
  • A customers table which contains PII such as name, address, etc.
  • An orders table with a foreign key into customers, which does not expose any PII
Later, we’ll show how to turn on auditing for the customers table.

​
Step 2. Turn on auditing for the customers table

We turn on auditing for a table using the EXPERIMENTAL_AUDIT subcommand of ALTER TABLE.
This directs SQL audit events for the customers table into the SENSITIVE_ACCESS logging channel.
To turn on auditing for more than one table, issue a separate ALTER statement for each table.

​
Step 3. Populate the customers table

Now that we have auditing turned on, let’s add some customer data:
Now let’s verify that our customers were added successfully:

​
Step 4. Check the audit log

By default, events in the SENSITIVE_ACCESS channel are output to a log file that is prefixed cockroach-sql-audit and stored in the same directory as the other logs generated by CockroachDB. To store the audit log files in a specific directory, configure the SENSITIVE_ACCESS channel with a custom dir path. Like the other log files, it’s rotated according to the max-file-size setting. When we look at the audit log for this example, we see the following lines showing every command we’ve run so far, as expected.
The above example shows the default crdb-v2 log format. This can be changed to another format (e.g., JSON). For details, see Configure Logs.
For descriptions of all SQL audit event types and their fields, see Notable Event Types.

​
Step 5. Populate the orders table

Unlike the customers table, orders doesn’t have any PII, just a Product ID and a delivery status. Let’s populate the orders table with some placeholder data using CREATE SEQUENCE:
Evaluate the below a few times to generate data; note that this would error if SELECT returned multiple results, but it doesn’t in this case.
Let’s verify that our orders were added successfully:

​
Step 6. Check the audit log again

Because we used a SELECT against the customers table to generate the placeholder data for orders, those queries will also show up in the audit log. Note that two log entries are created for each query: one entry for the SELECT subquery, and one entry for the foreign key check on customer_id. Since the customers table is read twice with each query, the TableName and TxnCounter values will be duplicated across entries:
The above example shows the default crdb-v2 log format. This can be changed to another format (e.g., JSON). For details, see Configure Logs. For descriptions of all SQL audit event types and their fields, see Notable Event Types.

​
See also