YugabyteDB YSQL uses the PostgreSQL Audit Extension v1.7 ([pgaudit](https://github.com/pgaudit/pgaudit/blob/1.7.0/README.md)) to provide detailed session and/or object audit logging via YugabyteDB YB-TServer logging. The goal of YSQL audit logging is to provide you with the 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. ## Enable audit logging To enable audit logging, first configure audit logging for the cluster. This is done in one of the following ways: - At cluster startup. Use the [--ysql_pg_conf_csv](../../../reference/configuration/yb-tserver/#ysql-pg-conf-csv) YB-TServer flag. Database administrators can use `ysql_pg_conf_csv` to configure audit logging using [pgaudit flags](#customize-audit-logging). Provide the options as comma-separated values. Use double quotation marks to enclose any settings that include commas or single quotation marks. For example: ```sh --ysql_pg_conf_csv="log_line_prefix='%m [%p %l %c] %q[%C %R %Z %H] [%r %a %u %d] '","pgaudit.log='all, -misc'",pgaudit.log_parameter=on,pgaudit.log_relation=on,pgaudit.log_catalog=off,suppress_nonpg_logs=on ``` These [configuration parameters](../../../reference/configuration/yb-tserver/#postgresql-configuration-parameters) are set when the YugabyteDB cluster is restarted, and they apply for all users and for every session. For clusters with replication factor 3 or greater, this can be done with a rolling restart, without interrupting application workloads. - Per session. Use the [SET](../../../api/ysql/the-sql-language/statements/cmd_set/) command in a running session. The `SET` command essentially changes the run-time configuration parameters. For example, `SET pgaudit.log='DDL'` `SET` only affects the value used by the current session. For more information, see the [PostgreSQL documentation](https://www.postgresql.org/docs/15/sql-set.html). ### Create the extension After configuring the YB-TServer and starting the cluster, create the pgaudit extension by executing the following statement in ysqlsh: ```sql CREATE EXTENSION IF NOT EXISTS pgaudit; ``` You only need to run this statement on a single node, and it will apply across your cluster. ## Customize audit logging By default, audit logging includes the statement text for all statements in the classes of statements that you specify using the `pgaudit.log` flag. You can customize YSQL audit logging using the pgaudit flags, as per the following table. | Option | Description | Default | | :----- | :----- | :------ | | pgaudit.log | Specifies which classes of statements are logged by session audit logging, as follows:You can specify multiple classes using a comma-separated list. Subtract classes by prefacing the class with a minus (`-`) sign. | none | | pgaudit.log_catalog | Log statements for the PostgreSQL system catalog relations in `pg_catalog`. These system catalog tables record system (as opposed to user) activity, such as metadata lookups and from third-party tools performing lookups.
These statements aren't required for typical auditing and you can disable this option to reduce noise in the log. | ON | | pgaudit.log_client | Enable this option to echo log messages directly to clients such as [ysqlsh](../../../api/ysqlsh/) and psql. Log messages are printed directly to the shell, which can be helpful for debugging.
When enabled, you can set the level of logs that are output using `pgaudit.log_level`. | OFF | | pgaudit.log_level | Sets the [severity level](https://www.postgresql.org/docs/16/runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS) of logs written to clients when `pgaudit.log_client` is on. Use this setting for debugging and testing.
Values: DEBUG1 .. DEBUG5, INFO, NOTICE, WARNING, LOG.
ERROR, FATAL, and PANIC are not allowed.
`pgaudit.log_level` only applies when `pgaudit.log_client` is on; otherwise the default LOG level is used. | LOG | | pgaudit.log_parameter | Include the parameters that were passed with the statement in the logs. When parameters are present, they are included in CSV format after the statement text. | OFF | | pgaudit.log_relation | Create separate log entries for each relation (TABLE, VIEW, and so on) referenced in a SELECT or DML statement. This is a shortcut for exhaustive logging without using [object audit logging](../object-audit-logging-ysql/). | OFF | | pgaudit.log_rows | Include the rows retrieved or affected by a statement. The rows field is included after the parameter field. | OFF | | pgaudit.log_statement | Include the statement text and parameters. Depending on requirements, an audit log might not require this and it makes the logs less verbose. | ON | | pgaudit.log_statement_once | Ordinarily, statement text (and, if enabled, parameters) are included with every log entry. Enable this setting to only include statement text and parameters for the first entry for a statement or sub-statement combination. This makes for less verbose logging, but can make it more difficult to determine the statement that generated a log entry. | OFF | | pgaudit.role | Specifies the master role to use for object audit logging. To define multiple audit roles, grant the roles to the master role; this allows multiple groups to be in charge of different aspects of audit logging. | None | ## Examples {{% explore-setup-single-new %}} Using ysqlsh, connect to the database and enable the pgaudit extension on the YugabyteDB cluster as follows: ```sql \c yugabyte yugabyte; CREATE EXTENSION IF NOT EXISTS pgaudit; ``` ### Basic audit logging In ysqlsh, execute the following commands: ```sql SET pgaudit.log='DDL'; SET pgaudit.log_client=ON; SET pgaudit.log_level=notice; ``` #### Create a table and verify the log As `pgaudit.log='DDL'` is configured, `CREATE TABLE` YSQL statements are logged and the corresponding log is shown in ysqlsh: ```sql CREATE TABLE employees (empno int, ename text, address text, salary int, account_number text); ``` ```output 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 );", CREATE TABLE ``` Notice that audit logs are generated for DDL statements. ### Advanced audit logging For this example, start a new cluster with the following audit logging configuration: ```shell --ysql_pg_conf_csv="log_line_prefix='%m [%p %l %c] %q[%C %R %Z %H] [%r %a %u %d] ',pgaudit.log='all',pgaudit.log_parameter=on,pgaudit.log_relation=on,pgaudit.log_catalog=off,suppress_nonpg_logs=on" ``` Enable the pgaudit extension on any node as follows: ```sql CREATE EXTENSION IF NOT EXISTS pgaudit; CREATE TABLE IF NOT EXISTS my_table ( h int, r int, v int, primary key(h,r)); ``` Start two sessions and execute transactions concurrently as follows:
Client 1 Client 2
```sql BEGIN; INSERT INTO my_table VALUES (5,2,2); ```
```sql BEGIN; INSERT INTO my_table VALUES (6,2,2); COMMIT; ```
```sql INSERT INTO my_table VALUES (7,2,2); COMMIT; ```
Your PostgreSQL log should include interleaved output similar to the following: ```output 2022-12-08 14:11:24.190 EST [93243 15 639235e1.16c3b] [cloud1 datacenter1 rack1 node1] [127.0.0.1(49823) ysqlsh yugabyte yugabyte] LOG: AUDIT: SESSION,6,1,MISC,BEGIN,,,begin;, 2022-12-08 14:11:34.309 EST [93243 16 639235e1.16c3b] [cloud1 datacenter1 rack1 node1] [127.0.0.1(49823) ysqlsh yugabyte yugabyte] LOG: AUDIT: SESSION,7,1,WRITE,INSERT,TABLE,public.my_table,"INSERT INTO my_table VALUES (5,2,2);", 2022-12-08 14:11:38.294 EST [92937 8 639233f7.16b09] [cloud1 datacenter1 rack1 node1] [127.0.0.1(49633) ysqlsh yugabyte yugabyte] LOG: AUDIT: SESSION,6,1,MISC,BEGIN,,,begin;, 2022-12-08 14:11:42.976 EST [92937 9 639233f7.16b09] [cloud1 datacenter1 rack1 node1] [127.0.0.1(49633) ysqlsh yugabyte yugabyte] LOG: AUDIT: SESSION,7,1,WRITE,INSERT,TABLE,public.my_table,"INSERT INTO my_table VALUES (6,2,2);", 2022-12-08 14:11:46.596 EST [92937 10 639233f7.16b09] [cloud1 datacenter1 rack1 node1] [127.0.0.1(49633) ysqlsh yugabyte yugabyte] LOG: AUDIT: SESSION,8,1,MISC,COMMIT,,,COMMIT;, 2022-12-08 14:11:52.317 EST [93243 17 639235e1.16c3b] [cloud1 datacenter1 rack1 node1] [127.0.0.1(49823) ysqlsh yugabyte yugabyte] LOG: AUDIT: SESSION,8,1,WRITE,INSERT,TABLE,public.my_table,"INSERT INTO my_table VALUES (7,2,2);", 2022-12-08 14:11:54.374 EST [93243 18 639235e1.16c3b] [cloud1 datacenter1 rack1 node1] [127.0.0.1(49823) ysqlsh yugabyte yugabyte] LOG: AUDIT: SESSION,9,1,MISC,COMMIT,,,commit;, ``` Sorting by session identifier and timestamp, and including the node information for uniqueness in the cluster, you can group the transactions: ```output cloud1 datacenter1 rack1 node1 639233f7.16b09 2022-12-08 14:11:38.294 SESSION,6,1,MISC,BEGIN,,,begin;, cloud1 datacenter1 rack1 node1 639233f7.16b09 2022-12-08 14:11:42.976 SESSION,7,1,WRITE,INSERT,TABLE,public.my_table,"INSERT INTO my_table VALUES (6,2,2);", cloud1 datacenter1 rack1 node1 639233f7.16b09 2022-12-08 14:11:46.596 SESSION,8,1,MISC,COMMIT,,,COMMIT;, cloud1 datacenter1 rack1 node1 639235e1.16c3b 2022-12-08 14:11:24.190 SESSION,6,1,MISC,BEGIN,,,begin;, cloud1 datacenter1 rack1 node1 639235e1.16c3b 2022-12-08 14:11:34.309 SESSION,7,1,WRITE,INSERT,TABLE,public.my_table,"INSERT INTO my_table VALUES (5,2,2);", cloud1 datacenter1 rack1 node1 639235e1.16c3b 2022-12-08 14:11:52.317 SESSION,8,1,WRITE,INSERT,TABLE,public.my_table,"INSERT INTO my_table VALUES (7,2,2);", cloud1 datacenter1 rack1 node1 639235e1.16c3b 2022-12-08 14:11:54.374 SESSION,9,1,MISC,COMMIT,,,commit;, ``` ## Learn more - [Audit logging in YugabyteDB](https://www.youtube.com/watch?v=ecYN9Z5_Hzc) - [pgaudit GitHub project](https://github.com/pgaudit/pgaudit/) - [PostgreSQL Extensions](../../../additional-features/pg-extensions/)