Change the Format of Audit Log Entries for YSQL

Jim Knicely

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

Audit records are written to a Postgres log file in the directory defined in the log_directory server parameter. 
				
					yugabyte=# SHOW log_directory;
            log_directory
-------------------------------------
 /root/var/data/yb-data/tserver/logs
(1 row)

You can get a list of all of the Postgres log files from within YSQLSH.

				
					yugabyte=# \! ls /root/var/data/yb-data/tserver/logs/post*
/root/var/data/yb-data/tserver/logs/postgresql-2023-04-06_184105.log
/root/var/data/yb-data/tserver/logs/postgresql-2023-04-07_000000.log
/root/var/data/yb-data/tserver/logs/postgresql-2023-04-07_125534.log

The format of the audit records is defined in another server parameter named log-line-prefix.

				
					yugabyte=# SHOW log_line_prefix; -- This is the default!
 log_line_prefix
-----------------
 %m [%p]
(1 row)

In addition to all of the options available in PostgreSQL, YugabyteDB supports the following additional options for the log_line_prefix  parameter:

  • %C = cloud name
  • %R = region / data center name
  • %Z = availability zone / rack name
  • %U = cluster UUID
  • %N = node and cluster name
  • %H = current hostname

One of my recommended formats is:

      %m [%p %l %c] %q[%C %R %Z %H] [%r %a %u %d]

Unfortunately, the log_line_prefix  parameter can not be changed by altering either the database or the session.

				
					yugabyte=# ALTER DATABASE yugabyte SET log_line_prefix = '%m [%p %l %c] %q[%C %R %Z %H] [%r %a %u %d]';
ERROR:  parameter "log_line_prefix" cannot be changed now

yugabyte=# SET log_line_prefix = '%m [%p %l %c] %q[%C %R %Z %H] [%r %a %u %d]';
ERROR:  parameter "log_line_prefix" cannot be changed now
It must be set via the YB-TServer flag ysql_pg_conf_csv.

You can do this in the YugabyteDB Anywhere GUI or from the command line.

Here is an example of starting a YugabyteDB database with the yugabyted utility.  

				
					[root@localhost ~]# yugabyted start --advertise_address=198.xx.xx.xxx --tserver_flags=ysql_pg_conf_csv={"log_line_prefix='%m [%p %l %c] %q[%C %R %Z %H] [%r %a %u %d] '"}

It is recommended to make the last character of  log_line_prefix be a space, to provide visual separation from the rest of the log line. A punctuation character can be used too.

Now I can view the new value of log_line_prefix in YSQLSH.

				
					yugabyte=# SHOW log_line_prefix; -- This is the updated value!
               log_line_prefix
----------------------------------------------
 %m [%p %l %c] %q[%C %R %Z %H] [%r %a %u %d]
(1 row)

Here is a sample of a couple of audit log records having the above formatting options defined above:

				
					2023-04-05 12:45:02.325 UTC [61972 1 642eeba2.f214] [cloud1 datacenter1 rack1 localhost] [198.xx.xx.xxx(52766) ysqlsh bryan yugabyte]LOG:  AUDIT: SESSION,1,1,WRITE,INSERT,,,"INSERT INTO test VALUES (1), (2), (3);",<not logged>
2023-04-05 12:47:20.244 UTC [61972 2 642eeba2.f214] [cloud1 datacenter1 rack1 localhost] [198.xx.xx.xxx(52766) ysqlsh bryan yugabyte]LOG:  AUDIT: SESSION,2,1,WRITE,DELETE,,,DELETE FROM test WHERE c1 = 2;,<not logged>

Have Fun!