Configure Audit Logging in YSQL

• YSQL
• YCQL

YugabyteDB YSQL uses PostgreSQL Audit Extension (pgAudit) to provide detailed session and/or object audit logging via YugabyteDB TServer logging.

The goal of the YSQL audit logging is to provide YugabyteDB users with capability to produce audit logs often required to comply with government, financial, or ISO certifications. An audit is an official inspection of an individual’s or organization’s accounts, typically by an independent body.

Enabling Audit Logging

Step 1. Enable audit logging on YB-TServer

This can be done in one of the following ways.

Option A: Using --ysql_pg_conf TServer flag

1. Database administrators can leverage ysql_pg_conf to set appropriate values for pgAudit configuration.
2. Eg. ysql_pg_conf="pgaudit.log='DDL',pgaudit.log_level=notice"
3. These configuration values are set when the YugabyteDB cluster is created and hence are picked up for all users and for every session.

Option B: Using YugabyteDB SET command

1. An alternative suggestion is to use the YB SET command, which essentially changes the run-time configuration parameters.
2. Eg. SET pgaudit.log='DDL'
3. SET only affects the value used by the current session. A detailed description of the set command is illustrated in this webpage.

Step 2. Load the pgAudit extension

Enable Audit logging in YugabyteDB clusters by creating the pgaudit extension. Executing the below statement in a YSQL shell will enable Audit logging.

yugabyte=# CREATE EXTENSION IF NOT EXISTS pgaudit;
CREATE EXTENSION

Customizing Audit Logging

YSQL Audit logging can be further customized by configuring the pgAudit flags as described below.

Option	Values notes
pgaudit.log	Specifies which classes of statements will be logged by session audit logging. READ: SELECT and COPY when the source is a relation or a query. WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation. FUNCTION: Function calls and DO blocks. ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE. DDL: All DDL that is not included in the ROLE class. MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET. MISC_SET: Miscellaneous SET commands, e.g. SET ROLE. ALL: Include all of the above. Multiple classes can be provided using a comma-separated list and classes can be subtracted by prefacing the class with a - sign. The default is none.
pgaudit.log_catalog	ON: Session logging would be enabled in the case for all relations in a statement that are in pg_catalog. OFF: Vice Versa! Disabling this setting will reduce noise in the log from tools. The default is ON.
pgaudit.log_client	ON: Log messages will be visible to a client process such as psql. Useful for debugging. OFF: Vice Versa! Note that pgaudit.log_level is only enabled when pgaudit.log_client is ON. The default is OFF.
pgaudit.log_level	Values: DEBUG1 .. DEBUG5, INFO, NOTICE, WARNING, LOG. Log level that will be used for log entries (ERROR, FATAL, and PANIC are not allowed). This setting is used for testing. Note that pgaudit.log_level is only enabled when pgaudit.log_client is ON; otherwise the default will be used. The default is LOG.
pgaudit.log_parameter	ON: Audit logging includes the parameters that were passed with the statement. When parameters are present they will be included in CSV format after the statement text. The default is OFF.
pgaudit.log_relation	ON: Session audit logging creates separate log entries for each relation (TABLE, VIEW, etc.) referenced in a SELECT or DML statement. This is a useful shortcut for exhaustive logging without using object audit logging. The default is OFF.
pgaudit.log_statement_once	ON: Specifies whether logging will include the statement text and parameters with the first log entry for a statement/substatement combination or with every entry. Disabling this setting will result in less verbose logging but may make it more difficult to determine the statement that generated a log entry. The default is OFF.
pgaudit.role	Specifies the master role to use for object audit logging. Multiple audit roles can be defined by granting them to the master role. This allows multiple groups to be in charge of different aspects of audit logging. There is no default.

Example

Use these steps to configure audit logging in a YugabyteDB cluster with bare minimum configurations.

1. Enable audit logging

Start the YugabyteDB Cluster with the following Audit logging Configuration.

```
--ysql_pg_conf="pgaudit.log='DDL',pgaudit.log_level=notice,pgaudit.log_client=ON"
```



Alternatively, go to a YSQL shell and execute the following commands.


```
SET pgaudit.log='DDL'; 
SET pgaudit.log_client=ON;
SET pgaudit.log_level=notice;
```

2. Load pgAudit extension

Open the YSQL shell (ysqlsh), specifying the yugabyte user and prompting for the password.

```
$ ./ysqlsh -U yugabyte -W
```



When prompted for the password, enter the yugabyte password. You should be able to login and see a response like below.


```
ysqlsh (11.2-YB-2.5.0.0-b0)
Type "help" for help.

yugabyte=#
```

Enable pgaudit extension on the YugabyteDB cluster.

Connect to the database using the following: `yugabyte=> \c yugabyte yugabyte;`

Create the `pgAudit` extension.
```
You are now connected to database "yugabyte" as user "yugabyte".

yugabyte=# CREATE EXTENSION IF NOT EXISTS pgaudit;
CREATE EXTENSION

```

3. Create a table, verify log

Since pgaudit.log='DDL' is configured, CREATE TABLE YSQL statements will be logged and the corresponding log will be shown in the ysql client.

yugabyte=# create table employees ( empno int, ename text, address text, 
  salary int, account_number text );
NOTICE:  AUDIT: SESSION,2,1,DDL,CREATE TABLE,TABLE,public.employees,
"create table employees ( empno int, ename text, address text, salary int, 
account_number text );",<not logged>
CREATE TABLE

Notice that audit logs get generated for DDL statements.