Role-based SQL Audit Logging

Role-based SQL audit logging gives you detailed information about queries being executed against your system by specific users or roles. An event of type is recorded when an executed query belongs to a user whose role membership corresponds to a role that is enabled to emit an audit log via the . The event is logged in the logging channel.

This feature is in and subject to change. To share feedback and/or issues, contact Support.

This page shows how to enable role-based SQL audit logging in CockroachDB, including:

• How to configure audit logging.
• Where the audit log files are stored.
• What the audit log files look like.

Logging, in general, can negatively impact performance. Log only what you require to limit impact to your workload.

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, refer to .

Configure Logging

Prerequisites

You must have:

• or MODIFYCLUSTERSETTING to configure the cluster setting,
• an , and
• your , particularly the location of audit logs.

Syntax of audit settings

sql.log.user_audit

With the , you can set an audit logging configuration using a table-like syntax, as shown in the Examples. Each row in the configuration represents an audit setting and must be separated by a new line. An audit setting is comprised of two columns separated by a space:

• USER/ROLE: the name of the user or role you want to audit.
  • The special USER/ROLE value ALL applies the audit setting to all users.
• STATEMENT_FILTER: the statement filter for the role. The following keywords are valid:
  • ALL will enable audit logging for all SQL statements for the given audit setting.
  • NONE will exclude audit logging for all SQL statements for the given audit setting.

Once sql.log.user_audit is set, the default behavior is for role-based SQL audit logging to take effect immediately within user sessions.

sql.log.user_audit.reduced_config.enabled

When enabled, the computes a “reduced” audit configuration based on the user’s current and the current value for the sql.log.user_audit cluster setting. The “reduced” audit configuration is computed at the first SQL event emitted by the user, after the setting is enabled. When the cluster setting is enabled, CockroachDB computes the audit configuration once at start, instead of at each SQL event. However with the setting enabled, changes to the audit configuration (user role memberships or cluster setting configuration) are not reflected within a user session. To reflect the configuration changes in auditing behavior, users need to start a new session.

Matching order

The order in which the audit settings in the configuration are specified is important, as shown in the example of a user with multiple roles. When determining whether to emit a log, the first audit setting that matches a user is the audit setting that gets applied.

For each statement executed, CockroachDB gets the user’s role memberships and iterates through the audit settings, looking for a matching audit setting by role or username. At the first audit setting match, CockroachDB stops iteration. If auditing is enabled (the STATEMENT_FILTER is set to ALL) for the matching audit setting, the statement is logged as an audit event. If the STATEMENT_FILTER is set to NONE, the statement is not logged.

Validate setting

You can check the value of the sql.log.user_audit setting by running the command:

SHOW CLUSTER SETTING sql.log.user_audit;

File format

Audit log messages, like all , 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 details on the information logged, refer to common fields of .
• An envelope that contains event metadata (e.g., severity, date, timestamp, channel). Depending on the log format you specify when , the envelope can be formatted either as JSON or as a flat prefix to the message.

File storage location

By , audit logs are prefixed cockroach-sql-audit and are stored in the as the other logs generated by CockroachDB.

To store the audit log files in a specific directory, 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.

Examples

Exclude one role from logging

With the audit settings in this example,

• Users with the username or role service_account_role will not emit audit logs for any statements they issue.
• All remaining users will emit audit logs for any statements they issue. ALL is used twice. The first ALL refers to all USER/ROLE s. The second ALL refers to logging all actions/statements.

SET CLUSTER SETTING sql.log.user_audit = '
    service_account_role NONE
    ALL ALL
';

Include only one role for logging

With the audit settings in this example,

• Users with the username or role test_role will emit audit logs for all statements they issue.
• Users with the username or role another_role will not emit audit logs for any statements they issue. In principle, you can achieve this by simply omitting another_role from the configuration entirely. It is included here as a basis of comparison to the next example - User with multiple roles.
• All remaining users will not emit audit logs for any statements they issue.

SET CLUSTER SETTING sql.log.user_audit = '
    test_role       ALL
    another_role    NONE
    ALL             NONE
';

User with multiple roles

With the audit settings in this example,

• Users with the username or role test_role will not emit audit logs for any statements they issue.
• Users with the username or role another_role will emit audit logs for all statements they issue.
• All remaining users will not emit audit logs for any statements they issue.

SET CLUSTER SETTING sql.log.user_audit = '
    test_role       NONE
    another_role    ALL
    ALL             NONE
';

Grant an existing user, test_user, both roles test_role and another_role:

GRANT test_role to test_user;
GRANT another_role to test_user;

If test_user executes a statement, an audit log will not be emitted. In this case, CockroachDB would match test_user to role test_role, which is listed first and has the STATEMENT_FILTER set to NONE. Even though test_user is a member of role another_role which has STATEMENT_FILTER set to ALL, CockroachDB does not match test_user to that role because it is listed second.

See also