# SQL Audit Logging

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

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.

For a detailed description of the audit log file format, see Audit log file format on the `ALTER TABLE ... EXPERIMENTAL_AUDIT` reference page.

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, on the `ALTER TABLE ... EXPERIMENTAL_AUDIT` reference page.

Warning:

This is an experimental feature. The interface and output are subject to change.

Tip:

To learn about other SQL query logging options, see SQL logging.

## 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 audit logs for the `customers` table.

    > CREATE TABLE customers (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name STRING NOT NULL,
        address STRING NOT NULL,
        national_id INT NOT NULL,
        telephone INT NOT NULL,
        email STRING UNIQUE NOT NULL
    );

    > CREATE TABLE orders (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        product_id INT NOT NULL,
        delivery_status STRING check (delivery_status='processing' or delivery_status='in-transit' or delivery_status='delivered') NOT NULL,
        customer_id UUID NOT NULL REFERENCES customers (id)
    );

## Step 2. Turn on auditing for the `customers` table

We turn on auditing for a table using the `EXPERIMENTAL_AUDIT` subcommand of `ALTER TABLE`.

    > ALTER TABLE customers EXPERIMENTAL_AUDIT SET READ WRITE;

Note:

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:

    > INSERT INTO customers (name, address, national_id, telephone, email) VALUES (
        'Pritchard M. Cleveland',
        '23 Crooked Lane, Garden City, NY USA 11536',
        778124477,
        12125552000,
        'pritchmeister@aol.com'
    );

    > INSERT INTO customers (name, address, national_id, telephone, email) VALUES (
        'Vainglorious K. Snerptwiddle III',
        '44 Straight Narrows, Garden City, NY USA 11536',
        899127890,
        16465552000,
        'snerp@snerpy.net'
    );

Now let's verify that our customers were added successfully:

    > SELECT * FROM customers;

                       id                  |               name               |                    address                     | national_id |  telephone  |         email
    ---------------------------------------+----------------------------------+------------------------------------------------+-------------+-------------+------------------------
      859c6aa1-ae36-49c8-9f12-7a952b4e6915 | Vainglorious K. Snerptwiddle III | 44 Straight Narrows, Garden City, NY USA 11536 |   899127890 | 16465552000 | snerp@snerpy.net
      90810df2-d3c1-4038-8462-132f4df5112b | Pritchard M. Cleveland           | 23 Crooked Lane, Garden City, NY USA 11536     |   778124477 | 12125552000 | pritchmeister@aol.com
    (2 rows)

## Step 4. Check the audit log

By default, the active audit log file is prefixed `cockroach-sql-audit` and is stored in CockroachDB's standard log directory. To store the audit log files in a specific directory, pass the `--sql-audit-dir` flag to `cockroach start`. Like the other log files, it's rotated according to the `--log-file-max-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.

    I180321 20:54:21.381565 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 2 exec "cockroach sql" {"customers"[76]:READWRITE} "ALTER TABLE customers EXPERIMENTAL_AUDIT SET READ WRITE" {} 4.811 0 OK
    I180321 20:54:26.315985 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 3 exec "cockroach sql" {"customers"[76]:READWRITE} "INSERT INTO customers(\"name\", address, national_id, telephone, email) VALUES ('Pritchard M. Cleveland', '23 Crooked Lane, Garden City, NY USA 11536', 778124477, 12125552000, 'pritchmeister@aol.com')" {} 6.319 1 OK
    I180321 20:54:30.080592 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 4 exec "cockroach sql" {"customers"[76]:READWRITE} "INSERT INTO customers(\"name\", address, national_id, telephone, email) VALUES ('Vainglorious K. Snerptwiddle III', '44 Straight Narrows, Garden City, NY USA 11536', 899127890, 16465552000, 'snerp@snerpy.net')" {} 2.809 1 OK
    I180321 20:54:39.377395 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 5 exec "cockroach sql" {"customers"[76]:READ} "SELECT * FROM customers" {} 1.236 2 OK

Note:

For reference documentation of the audit log file format, see `ALTER TABLE ... EXPERIMENTAL_AUDIT`.

Tip:

Log files can be accessed using the Admin UI, which displays them in JSON format.

1. Access the Admin UI and then click Advanced Debug in the left-hand navigation.

2. Under Raw Status Endpoints (JSON), click Log Files to view the JSON of all collected logs.

3. Copy one of the log filenames. Then click Specific Log File and replace the `cockroach.log` placeholder in the URL with the filename.

## Step 5. Populate the `orders` table

Unlike the `customers` table, `orders` doesn't have any PII, just a Product ID and a delivery status. (Note the use of the `CHECK` constraint as a workaround for the as-yet-unimplemented `ENUM` - see SQL feature support for more information.)

Let's populate the `orders` table with some placeholder data using `CREATE SEQUENCE`:

    > CREATE SEQUENCE product_ids_asc START 1 INCREMENT 1;

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.

    > INSERT INTO orders (product_id, delivery_status, customer_id) VALUES (
        nextval('product_ids_asc'),
        'processing',
        (SELECT id FROM customers WHERE name ~ 'Cleve')
    );

Let's verify that our orders were added successfully:

    > SELECT * FROM orders ORDER BY product_id;

                       id                  | product_id | delivery_status |             customer_id
    ---------------------------------------+------------+-----------------+---------------------------------------
      77fa8340-8a65-4ab2-8191-ed87fc049b33 |          1 | processing      | 90810df2-d3c1-4038-8462-132f4df5112b
      36c8b00d-01f0-4956-bb0e-6e9219f49bae |          2 | processing      | 90810df2-d3c1-4038-8462-132f4df5112b
      5eebf961-1e4c-41a4-b6c6-441c3d5ef595 |          3 | processing      | 90810df2-d3c1-4038-8462-132f4df5112b
      2952402e-0cde-438f-a1fb-09e30be26748 |          4 | processing      | 90810df2-d3c1-4038-8462-132f4df5112b
      a9bf61ee-2c8c-4f77-b684-d943e1a46093 |          5 | processing      | 90810df2-d3c1-4038-8462-132f4df5112b
    (5 rows)

## 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 as follows:

    I180321 21:01:59.677273 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 7 exec "cockroach sql" {"customers"[76]:READ, "customers"[76]:READ} "INSERT INTO orders(product_id, delivery_status, customer_id) VALUES (nextval('product_ids_asc'), 'processing', (SELECT id FROM customers WHERE \"name\" ~ 'Cleve'))" {} 5.183 1 OK
    I180321 21:04:07.497555 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 8 exec "cockroach sql" {"customers"[76]:READ, "customers"[76]:READ} "INSERT INTO orders(product_id, delivery_status, customer_id) VALUES (nextval('product_ids_asc'), 'processing', (SELECT id FROM customers WHERE \"name\" ~ 'Cleve'))" {} 5.219 1 OK
    I180321 21:04:08.730379 351 sql/exec_log.go:163  [n1,client=127.0.0.1:60754,user=root] 9 exec "cockroach sql" {"customers"[76]:READ, "customers"[76]:READ} "INSERT INTO orders(product_id, delivery_status, customer_id) VALUES (nextval('product_ids_asc'), 'processing', (SELECT id FROM customers WHERE \"name\" ~ 'Cleve'))" {} 5.392 1 OK

Note:

For reference documentation of the audit log file format, see `ALTER TABLE ... EXPERIMENTAL_AUDIT`.

Tip:

Log files can be accessed using the Admin UI, which displays them in JSON format.

1. Access the Admin UI and then click Advanced Debug in the left-hand navigation.

2. Under Raw Status Endpoints (JSON), click Log Files to view the JSON of all collected logs.

3. Copy one of the log filenames. Then click Specific Log File and replace the `cockroach.log` placeholder in the URL with the filename.

## Known limitations

SQL audit logs can only be accessed in the DB Console if they are stored in the same directory as the main CockroachDB log files.

For more information, see `EXPERIMENTAL_AUDIT`.

## See also

- `ALTER TABLE ... EXPERIMENTAL_AUDIT`
- `cockroach start` logging flags
- SQL FAQ - generating unique row IDs
- `CREATE SEQUENCE`
- SQL Feature Support
- Authentication logs
- Slow query logs
- SQL logging