Top
Enterprise Postgres 14 SP1 Security Operation Guide
FUJITSU Software

6.7 View Audit Logs Using SQL

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.

  1. 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)
  2. 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;
  3. 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.

  4. 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              ...