The pg_proaudit extension is a built-in extension included into Postgres Pro Standard. To enable pg_proaudit, complete the following steps:

To properly uninstall pg_proaudit, complete the following steps:

To configure security event logging, pg_proaudit provides an SQL interface that consists of several functions and the pg_proaudit_settings view.

pg_proaudit_set_rule(db_name text, event_type text, object_type text, object_name text, role_name text, comment text)

Creates the logging rule with the specified parameters. When the pg_proaudit_set_rule() function completes, security event logging starts immediately, but the pg_proaudit.conf file is not updated. To save the changes in the pg_proaudit.conf file, call the pg_proaudit_save() function.

Arguments:

pg_proaudit_remove_rule(db_name text, event_type text, object_type text, object_name text, role_name text)

Removes the specific logging rule with the set parameters. To save the changes in the pg_proaudit.conf file, call the pg_proaudit_save() function.

Arguments:

pg_proaudit_show()

Returns logged events in a table view. This function is used by the pg_proaudit_settings view.

pg_proaudit_reload()

Reads logging configuration from the pg_proaudit.conf file. You must call this function if the pg_proaudit.conf file was modified manually using the operating system facilities.

pg_proaudit_reset()

Removes all logging rules. To save information about the canceled logging in the pg_proaudit.conf file, call the pg_proaudit_save() function.

pg_proaudit_save()

Saves logging rules from memory into the pg_proaudit.conf file. The pg_proaudit.conf file is located in the cluster data directory (PGDATA). You cannot change the pg_proaudit.conf file location.

create table "TAbLe''123"();
SELECT pg_proaudit_set_rule(current_database(), null, null, 'public.TabLe''''123', null, 'tst cmnt');

create table "taBlE""123"();
SELECT pg_proaudit_set_rule(null, null, null, 'public.taBlE"123', null);

create schema "(BIG!) Schema";
create table "(BIG!) Schema"."My Tab.lE"();
SELECT pg_proaudit_set_rule(null, null, null, '(BIG!) Schema.My Tab.lE', null);

create role "Some '@#$%' person" with login;
SELECT pg_proaudit_set_rule(null, 'authenticate', null, null, 'Some ''@#$%'' person');

create role "Some '@#$%' person" with superuser;
\c - "Some '@#$%' person"
SELECT pg_proaudit_set_rule(null, 'disconnect', null, null, current_user);

create database " D B 1";
SELECT pg_proaudit_set_rule(' D B 1', 'authenticate', null, null, null);

create database " D b 2";
\c " D b 2"
create extension pg_proaudit;
SELECT pg_proaudit_set_rule(current_database(), 'disconnect', null, null, null);

This view displays the current pg_proaudit rules, even if they have not been saved into the pg_proaudit.conf file yet. The pg_proaudit_settings view consists of the following columns:

You can configure the pg_proaudit extension to log classes of security events and specific events by specifying the respective value in the event_type argument of the pg_proaudit_set_rule() function.

The following classes of security events are supported:

The following specific security events are supported:

The pg_proaudit extension provides several configuration parameters for managing security event log files. These parameters can be set in the postgresql.conf configuration file, or with the help of the ALTER SYSTEM command. For the changes to take effect, call the pg_reload_conf() function or restart the database server. For additional configuration, the syslog_ident and syslog_facility configuration parameters can be used.

Security event log files are text files that can be viewed by the operating system facilities. To access log files using SQL, you can use the file_fdw extension — a foreign data wrapper for accessing files on the database server. To use this method, complete the following steps:

Make sure that the pg_proaudit.log_destination parameter contains the csvlog value, which enables writing security event logs to CSV files.

As an example, let's set up logging for the following security events:

All events must be logged in the CSV format and stored for a week. It is required to set up SQL access to the security event log. To complete the scenario, do the following:

In psql, check that the preliminary setup of the pg_proaudit extension is complete in the postgres database:

SHOW shared_preload_libraries;
 shared_preload_libraries
--------------------------
 pg_proaudit

\dx pg_proaudit
                   List of installed extensions
    Name     | Version | Schema |           Description
-------------+---------+--------+---------------------------------
 pg_proaudit | 2.0     | public | provides auditing functionality

Add the following lines to the postgresql.conf configuration file:

pg_proaudit.log_destination = 'csvlog'
pg_proaudit.log_directory = 'audit'
pg_proaudit.log_filename = 'audit-%u.csv'
pg_proaudit.log_rotation_age = 1440
pg_proaudit.log_rotation_size = 0
pg_proaudit.log_truncate_on_rotation = on
pg_proaudit.log_command_text = on

For the changes to take effect, run the following query:

SELECT pg_reload_conf();

Check that the following parameters are set as expected:

SHOW pg_proaudit.log_destination;
SHOW pg_proaudit.log_directory;
SHOW pg_proaudit.log_filename;
SHOW pg_proaudit.log_rotation_age;
SHOW pg_proaudit.log_rotation_size;
SHOW pg_proaudit.log_truncate_on_rotation;
SHOW pg_proaudit.log_command_text;

Suppose your PGDATA environment variable points to the cluster data directory. Since the pg_proaudit.log_directory defines a relative path to the log files, they will be located in the $PGDATA/audit directory. Let's create an empty file for each day of the week and make them available to their owner only:

touch $PGDATA/audit/audit-1.csv
touch $PGDATA/audit/audit-2.csv
touch $PGDATA/audit/audit-3.csv
touch $PGDATA/audit/audit-4.csv
touch $PGDATA/audit/audit-5.csv
touch $PGDATA/audit/audit-6.csv
touch $PGDATA/audit/audit-7.csv
chmod 600 $PGDATA/audit/audit-*.csv

Create a table for reading log entries:

CREATE TABLE pg_proaudit_log (
  log_time timestamp(3) with time zone,
  role_name text,
  database_name text,
  session_pid text,
  error_severity text,
  session_line_num bigint,
  session_line_subcommand_num bigint,
  event_type text,
  object_type text,
  object_name text,
  status text,
  error_message text,
  query_text text,
  query_args text
);

Install the file_fdw extension and create an external server:

CREATE EXTENSION file_fdw;
CREATE SERVER pg_proauditlog FOREIGN DATA WRAPPER file_fdw;

Now let's create seven child foreign tables for the pg_proaudit_log table, for each day of the week:

CREATE FOREIGN TABLE pg_proaudit_log_1 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-1.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_2 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-2.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_3 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-3.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_4 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-4.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_5 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-5.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_6 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-6.csv',  FORMAT 'csv');
CREATE FOREIGN TABLE pg_proaudit_log_7 () INHERITS (pg_proaudit_log) SERVER pg_proauditlog
   OPTIONS (filename '/path_to_PGDATA/audit/audit-7.csv',  FORMAT 'csv');

To set up logging for the required security events, connect to the postgres database and execute the following commands:

SELECT pg_proaudit_set_rule (current_database(), 'AUTHENTICATE', null, null, null, 'Any authentication in the current DB');
SELECT pg_proaudit_set_rule (current_database(), 'DISCONNECT', null, null, null, 'Any disconnect from the current DB');
SELECT pg_proaudit_set_rule (current_database(), 'ALL', 'TABLE', null, null, 'Any operations with any table in the current DB');
SELECT pg_proaudit_set_rule (current_database(), 'ALL', null, null, 'postgres', 'Any operation by "postgres" user in the current DB');

Create the app_table table and enable logging for all operations on this table:

CREATE TABLE app_table (id int, name text);
SELECT pg_proaudit_set_rule (current_database(), 'ALL', null, 'public.app_table', null);

Check that event logging is configured as expected:

SELECT * FROM pg_proaudit_settings;
 db_name  |  event_type  | object_type |   object_name    | role_name |                      comment
----------+--------------+-------------+------------------+-----------+----------------------------------------------------
 postgres | authenticate | ALL         |                  |           | Any authentication in the current DB
 postgres | disconnect   | ALL         |                  |           | Any disconnect from the current DB
 postgres | ALL          | table       |                  |           | Any operations with any table in the current DB
 postgres | ALL          | ALL         |                  | postgres  | Any operation by "postgres" user in the current DB
 postgres | ALL          | ALL         | public.app_table |           |
(5 rows)

Save these logging rules into the pg_proaudit.conf file, so that they are not lost after the server restart:

SELECT pg_proaudit_save();

Let's run several queries on the app_table table:

INSERT INTO app_table VALUES (1, 'first');
SELECT * FROM app_table;

Check the log entries for the app_table table:

SELECT to_char(log_time, 'DD.MM.YY HH24:MI:SS') AS when, role_name,
       session_pid, event_type, query_text
FROM   pg_proaudit_log
where  object_name = 'public.app_table';
       when        | role_name | session_pid |  event_type  |                 query_text
-------------------+-----------+-------------+--------------+---------------------------------------------
 27.09.23 12:44:27 | postgres  | 2010        | CREATE TABLE | CREATE TABLE app_table (id int, name text);
 27.09.23 12:45:55 | postgres  | 2010        | INSERT       | INSERT INTO app_table VALUES (1, 'first');
 27.09.23 12:46:00 | postgres  | 2010        | SELECT       | SELECT * FROM app_table;
(3 rows)

We have set up a weekly rotation of log files, with the log file switched once a day. It means that queries to the pg_proaudit_log table will return only those security events that have happened in the latest week. Older events will be automatically removed at log file rotation. To define additional access constraints for specific log entries, you can create separate views based on queries to the pg_proaudit_log table and grant read rights to such views using built-in Postgres Pro access control methods.