Configure audit logging in YSQL
For production, use the latest stable version (v2024.1).
YugabyteDB YSQL uses the PostgreSQL Audit Extension v1.3.2 (pgaudit) 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 YB-TServer flag.
Database administrators can use
ysql_pg_conf_csv
to configure audit logging using pgaudit flags.Provide the options as comma-separated values. Use double quotation marks to enclose any settings that include commas or single quotation marks. For example:
--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 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 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.
Create the extension
After configuring the YB-TServer and starting the cluster, create the pgaudit extension by executing the following statement in ysqlsh:
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:
- ) 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 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 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. | OFF |
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
Before you start
The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.
Using ysqlsh, connect to the database and enable the pgaudit extension on the YugabyteDB cluster as follows:
\c yugabyte yugabyte;
CREATE EXTENSION IF NOT EXISTS pgaudit;
Basic audit logging
In ysqlsh, execute the following commands:
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:
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 are generated for DDL statements.
Advanced audit logging
For this example, start a new cluster with the following audit logging configuration:
--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:
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 |
---|---|
|
|
|
|
|
Your PostgreSQL log should include interleaved output similar to the following:
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;,<none>
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);",<none>
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;,<none>
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);",<none>
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;,<none>
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);",<none>
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;,<none>
Sorting by session identifier and timestamp, and including the node information for uniqueness in the cluster, you can group the transactions:
cloud1 datacenter1 rack1 node1 639233f7.16b09 2022-12-08 14:11:38.294 SESSION,6,1,MISC,BEGIN,,,begin;,<none>
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);",<none>
cloud1 datacenter1 rack1 node1 639233f7.16b09 2022-12-08 14:11:46.596 SESSION,8,1,MISC,COMMIT,,,COMMIT;,<none>
cloud1 datacenter1 rack1 node1 639235e1.16c3b 2022-12-08 14:11:24.190 SESSION,6,1,MISC,BEGIN,,,begin;,<none>
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);",<none>
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);",<none>
cloud1 datacenter1 rack1 node1 639235e1.16c3b 2022-12-08 14:11:54.374 SESSION,9,1,MISC,COMMIT,,,commit;,<none>