Session-level audit logging in YSQL

• YSQL

Session logging is enabled on a per user session basis.

To enable session logging for all DML and DDL statements and log all relations in DML statements, you would enter the following commands:

set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;

Enable session logging for all commands except MISC and raise audit log messages as NOTICE.

Session-level example

In this example, session audit logging is used for logging DDL and SELECT statements. Note that the insert statement is not logged because the WRITE class is not enabled.

SQL statements are shown below.

Setup

Connect to the database using ysqlsh and enable the pgaudit extension on the YugabyteDB cluster as follows:

\c yugabyte yugabyte;
CREATE EXTENSION IF NOT EXISTS pgaudit;

Enable session audit logging

Enable session audit logging in the YugabyteDB cluster as follows:

SET pgaudit.log = 'read, ddl';

Perform statements

Run some statements as follows:

CREATE TABLE account
(
    id int,
    name text,
    password text,
    description text
);

INSERT INTO account (id, name, password, description)
             VALUES (1, 'user1', 'HASH1', 'blah, blah');

SELECT * FROM account;

Verify output

You should see output similar to the following in the logs:

2020-11-09 19:19:09.262 UTC [3710] LOG:  AUDIT: SESSION,1,1,DDL,CREATE
TABLE,TABLE,public.account,"create table account
        (
            id int,
            name text,
            password text,
            description text
        );",<not logged>
2020-11-09 19:19:19.619 UTC [3710] LOG:  AUDIT: SESSION,2,1,READ,SELECT,,,"select *
            from account;",<not logged>