By using file_fdw of an additional module, audit logs can be accessed using SQL. This section describes how to view audit logs, using Session Audit Logging output to a dedicated log file as an example.
Install file_fdw
Execute CREATE EXTENSION to install file_fdw as an extension.
$ psql =# CREATE EXTENSION file_fdw; =# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------------------- file_fdw | 1.0 | public | foreign-data wrapper for flat file access pgaudit | 1.0 | public | provides auditing functionality plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (3 rows)
Create an external server
Use CREATE SERVER to create an external server managed by file_fdw.
$ psql =# CREATE SERVER auditlog FOREIGN DATA WRAPPER file_fdw;
Create an audit log table.
Use CREATE FOREIGN TABLE to define the table columns of audit logs, CSV file name and format.
$ psql =# CREATE FOREIGN TABLE auditlog ( header text, class text, sql_start_time timestamp with time zone, remote_host_name text, backend_process_id text, application_name text, session_user_name text, database_name text, virtual_transaction_id text, statement_id text, substatement_id text, command_tag text, sqlstate text, object_type text, object_name text, error_message text, sql text, parameter text ) SERVER auditlog OPTIONS ( filename '/database/inst1/pgaudit_log/pgaudit-2022-03-12.log', format 'csv' );
Note
If an audit log file is rotated and multiple audit log files exist, it is necessary to create a table for each audit log file.
View audit logs
Use SELECT and view the audit logs.
$ psql =# SELECT * FROM auditlog; header | class | sql_start_time | remote_host_name | backend_process_id ... ----------------+---------+------------------------+------------------+------------------- ... AUDIT: SESSION | WRITE | 2022-03-12 19:00:49+09 | [local] | 19944 ... AUDIT: SESSION | READ | 2022-03-12 19:00:58+09 | [local] | 19944 ...