Postgres Pro
Downloads
F.37. pg_proaudit
Prev UpAppendix F. Additional Supplied ModulesHome Next

F.37. pg_proaudit

F.37.1. Installing the pg_proaudit Extension
F.37.2. Uninstalling the pg_proaudit Extension
F.37.3. Functions to Configure Security Event Logging
F.37.4. pg_proaudit_settings View
F.37.5. Security Events
F.37.6. Security Event Log Configuration Parameters
F.37.7. Viewing Security Event Log
F.37.8. Examples

The pg_proaudit extension enables detailed logging of various security events.

pg_proaudit works in parallel with the standard PostgreSQL logging solutions (logging collector) and does not depend on them. Security event log of the pg_proaudit extension is stored separately from the server log. At the Postgres Pro Standard startup, pg_proaudit launches a special background process to log security events.

Logging rules are stored in the pg_proaudit.conf configuration file located in the cluster data directory (PGDATA). It is a text file that can be edited directly using operating system facilities. To modify the file using SQL, you can use several pg_proaudit functions. The pg_proaudit_settings view displays the current pg_proaudit rules, even if they have not been saved into the pg_proaudit.conf file yet.

All the logged events belong to the following classes:

  • DDL commands for creating, changing, and deleting DBMS objects (databases, tablespaces, schemas, tables, views, sequences, languages, functions)

  • access control commands for database objects (GRANT, REVOKE)

  • DML commands for access to database objects (INSERT, UPDATE, DELETE, SELECT, TRUNCATE for tables and/or views, EXECUTE for functions)

  • database authentication/disconnection events

  • all commands executed by a particular user

Security events can be logged both in the centralized logging solution of the operating system (syslog) and in the standard file-system files. Event logs can be written both into the syslog and into the files simultaneously. For clear identification, all pg_proaudit records in the syslog are marked with AUDIT. Event log files are written in the CSV format. Each event is logged on a separate line that contains the following fields:

  • date and time of the event

  • username

  • database name

  • server process ID (PID)

  • severity level: INFO or ERROR

  • serial number of the command in a session

  • subcommand number in complex commands (CREATE TABLE ... AS SELECT ...)

  • operator name

  • object type

  • object name

  • operator execution results: SUCCESS or FAILURE

  • error message in case of FAILURE

  • text of the SQL command

  • parameters of the command (for example, for PREPARE)

You can define a directory to store security log files and set up log file rotation. pg_proaudit can switch to a new log file either after the specified time interval, or when the specified size of the log file is exceeded. This enables you to define a workflow for cleaning up security event logs.

Postgres Pro user with the SUPERUSER attribute should grant access to the pg_proaudit extension and security event log files only to the user with the information security administrator role.

F.37.1. Installing the pg_proaudit Extension

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

  1. Add pg_proaudit to the shared_preload_libraries variable in the postgresql.conf file: 

    shared_preload_libraries = 'pg_proaudit'

  2. Reload the database server for the changes to take effect. To verify that the pg_proaudit library was installed correctly, you can run the following command: 

    SHOW shared_preload_libraries;

  3. Create the pg_proaudit extension using the following query: 

    CREATE EXTENSION pg_proaudit;

    The pg_proaudit extension adds several functions for managing the pg_proaudit.conf file, the pg_proaudit_settings view that displays the current pg_proaudit rules and event triggers. For convenience, the pg_proaudit extension should be created in each database for which you are going to log security events.

F.37.2. Uninstalling the pg_proaudit Extension

To properly uninstall pg_proaudit, complete the following steps:

  1. Delete the pg_proaudit extension using the following query: 

    DROP EXTENSION pg_proaudit;

  2. Remove pg_proaudit from the shared_preload_libraries variable in the postgresql.conf file.

    Skip this step if you have several databases in the cluster and you want to remove the extension only for one of them. In this case, it is recommended to remove logging rules related to the corresponding database prior to uninstalling the extension.

F.37.3. Functions to Configure Security Event Logging

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:

  • db_name — name of the database for which the logging rule is established. An empty string or NULL specified in this argument means that events are logged for all databases where the pg_proaudit extension is created. When set to current_database(), events for the current database are logged.

  • event_type — type of the event that needs to be logged, including SQL operator names, as well as AUTHENTICATE and DISCONNECT events. When set to ALL, as well as when an empty string or NULL is specified, enables logging for all events available for the specified object type. For example, for the TABLE object type, the ALL keyword enables logging for commands SELECT, INSERT, UPDATE, DELETE, TRUNCATE, COPY, as well as CREATE, ALTER, DROP. You can also set up logging of event classes by specifying the following values: ALL_DDL, ALL_DML, ALL_MOD, ALL_PROC, and ALL_ROLE. For the full list of possible event_type values, see Section F.37.5.

  • object_type — type of the object for which security events need to be logged. When set to ALL, as well as when an empty string or the NULL is specified, enables logging of events for all object types. For example, specify FOREIGN TABLE object type for the SELECT event to log all attempts to access foreign tables. Use NULL if event_type is set to AUTHENTICATE, DISCONNECT, SET, or RESET, and the ROLE value for all events that reference user actions, such as CREATE USER or DROP USER. The following object types are supported: COMPOSITE TYPE, DATABASE, EVENT TRIGGER, FUNCTION, INDEX, PREPARED STATEMENT, ROLE, SEQUENCE, SCHEMA, TABLE, FOREIGN TABLE, TOAST TABLE, TABLESPACE, VIEW, and MATERIALIZED VIEW.

  • object_name — name of the object for which the logging rule is established. When an empty string or NULL is specified, enables logging of events for all object names.

  • role_name — name of the role for which the logging rule is established. If specified, allows logging the actions caused by the specified DBMS user or the user who is directly or indirectly a member of this role. When an empty string or NULL is specified, enables logging of events for all role names. When set to current_role, events for the current role are logged.

  • comment — comment to describe the created logging rule. This argument does not affect the rule execution and is not reflected in the log. 

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:

  • db_name — name of the database for which the logging rule needs to be removed.

  • event_type — type of the event for which the logging rule needs to be removed. For the full list of possible event_type values, see Section F.37.5.

  • object_type — type of the object for which the logging rule needs to be removed.

  • object_name — name of the object for which the logging rule needs to be removed.

  • role_name — name of the role for which the logging rule needs to be removed. 

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.

F.37.4. pg_proaudit_settings View

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:

  • db_name (text) — name of the database for which to log security events.

  • event_type (text) — event type to log.

  • object_type (text) — type of the object for which security events are to be logged.

  • object_name (text) — name of the object for which security events are to be logged.

  • role_name (text) — the role on behalf of which logged actions are performed.

  • comment (text) — comment to describe the created logging rule.

F.37.5. Security Events

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:

  • ALL_DDL: CREATE, ALTER, DROP for any database object.

  • ALL_DML: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, EXECUTE for any table type as well as functions and stored procedures.

  • ALL_MOD: INSERT, UPDATE, DELETE, TRUNCATE for any table type.

  • ALL_PROC: CREATE, MODIFY, DROP for any function and stored procedure.

  • ALL_ROLE: CREATE, ALTER, DROP for USER, ROLE, GROUP, as well as execution of the GRANT command.

The following specific security events are supported:

  • AUTHENTICATE

  • DISCONNECT

  • ALTER AGGREGATE

  • ALTER COLLATION

  • ALTER CONVERSION

  • ALTER DATABASE

  • ALTER DEFAULT PRIVILEGES

  • ALTER DOMAIN

  • ALTER EVENT TRIGGER

  • ALTER EXTENSION

  • ALTER FOREIGN DATA WRAPPER

  • ALTER FOREIGN TABLE

  • ALTER FUNCTION

  • ALTER INDEX

  • ALTER LANGUAGE

  • ALTER LARGE OBJECT

  • ALTER MATERIALIZED VIEW

  • ALTER OPERATOR

  • ALTER OPERATOR CLASS

  • ALTER OPERATOR FAMILY

  • ALTER POLICY

  • ALTER ROLE, ALTER USER, ALTER GROUP

  • ALTER RULE

  • ALTER SCHEMA

  • ALTER SEQUENCE

  • ALTER SERVER

  • ALTER SYSTEM

  • ALTER TABLE

  • ALTER TABLESPACE

  • ALTER TEXT SEARCH CONFIGURATION

  • ALTER TEXT SEARCH DICTIONARY

  • ALTER TEXT SEARCH PARSER

  • ALTER TEXT SEARCH TEMPLATE

  • ALTER TRIGGER

  • ALTER TYPE

  • ALTER USER MAPPING

  • ALTER VIEW

  • CLUSTER

  • COMMENT

  • COPY

  • CREATE ACCESS METHOD

  • CREATE AGGREGATE

  • CREATE CAST

  • CREATE COLLATION

  • CREATE CONVERSION

  • CREATE DATABASE

  • CREATE DOMAIN

  • CREATE EVENT TRIGGER

  • CREATE EXTENSION

  • CREATE FOREIGN DATA WRAPPER

  • CREATE FOREIGN TABLE

  • CREATE FUNCTION

  • CREATE INDEX

  • CREATE LANGUAGE

  • CREATE MATERIALIZED VIEW

  • CREATE OPERATOR

  • CREATE OPERATOR CLASS

  • CREATE OPERATOR FAMILY

  • CREATE POLICY

  • CREATE ROLE, CREATE USER, CREATE GROUP

  • CREATE RULE

  • CREATE SCHEMA

  • CREATE SEQUENCE

  • CREATE SERVER

  • CREATE TABLE, CREATE TABLE AS, SELECT INTO

  • CREATE TABLESPACE

  • CREATE TEXT SEARCH CONFIGURATION

  • CREATE TEXT SEARCH DICTIONARY

  • CREATE TEXT SEARCH PARSER

  • CREATE TEXT SEARCH TEMPLATE

  • CREATE TRANSFORM

  • CREATE TRIGGER

  • CREATE TYPE

  • CREATE USER MAPPING

  • CREATE VIEW

  • DEALLOCATE

  • DELETE

  • DO

  • DROP ACCESS METHOD

  • DROP AGGREGATE

  • DROP CAST

  • DROP COLLATION

  • DROP CONVERSION

  • DROP DATABASE

  • DROP DOMAIN

  • DROP EVENT TRIGGER

  • DROP EXTENSION

  • DROP FOREIGN DATA WRAPPER

  • DROP FOREIGN TABLE

  • DROP FUNCTION

  • DROP INDEX

  • DROP LANGUAGE

  • DROP MATERIALIZED VIEW

  • DROP OPERATOR

  • DROP OPERATOR CLASS

  • DROP OPERATOR FAMILY

  • DROP OWNED

  • DROP POLICY

  • DROP ROLE, DROP USER, DROP GROUP

  • DROP RULE

  • DROP SCHEMA

  • DROP SEQUENCE

  • DROP SERVER

  • DROP TABLE

  • DROP TABLESPACE

  • DROP TEXT SEARCH CONFIGURATION

  • DROP TEXT SEARCH DICTIONARY

  • DROP TEXT SEARCH PARSER

  • DROP TEXT SEARCH TEMPLATE

  • DROP TRANSFORM

  • DROP TRIGGER

  • DROP TYPE

  • DROP USER MAPPING

  • DROP VIEW

  • EXECUTE

  • GRANT

  • INSERT

  • PREPARE

  • REASSIGN OWNED

  • REFRESH MATERIALIZED VIEW

  • REINDEX

  • RESET

  • REVOKE

  • SECURITY LABEL

  • SELECT

  • SET

  • UPDATE

  • TRUNCATE TABLE

F.37.6. Security Event Log Configuration Parameters

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 reload the database server. For additional configuration, the syslog_ident and syslog_facility configuration parameters can be used.

pg_proaudit.log_destination (string)

Defines the method for logging security events. Possible values are:

  • csvlog — log security events in a CSV file.

  • syslog — log security events in syslog.

You can specify one or more values separated by commas.

Default: csvlog

pg_proaudit.log_catalog_access (boolean)

Specifies whether to log access to system catalog objects in the pg_catalog schema.

Default: off

pg_proaudit.log_command_text (boolean)

Specifies whether to log the SQL command text for security events.

Default: on

pg_proaudit.log_directory (string)

Specifies the path to the directory that stores CSV log files. This can be an absolute path, or a relative path to the cluster data directory (PGDATA). This parameter is used if pg_proaudit.log_destination contains the csvlog value.

Default: pg_proaudit

pg_proaudit.log_filename (string)

Defines the filenames of the created security event log files. The filename template can contain %-escapes, similar to the ones listed in the strftime specification of the Open Group (http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html). This parameter is used if pg_proaudit.log_destination contains the csvlog value.

Default: postgresql-%Y-%m-%d_%H%M%S.log

pg_proaudit.log_rotation_size (integer)

Sets the maximum size of the CSV log file, in kilobytes. When this size is achieved, pg_proaudit creates a new file for logging security events. This parameter is used if pg_proaudit.log_destination contains the csvlog. If set to 0, disables size-based creation of new log files.

Default: 10MB

pg_proaudit.log_rotation_age (integer)

Sets the maximum lifetime of a log file, in minutes. After this timeframe has elapsed, pg_proaudit creates a new file for logging security events. This parameter is used if pg_proaudit.log_destination contains the csvlog value. If set to 0, disables time-based creation of new log files.

Default: 1 day

pg_proaudit.log_truncate_on_rotation (boolean)

Specifies whether to truncate log files when logging is switched to an existing log file. If set to off, pg_proaudit appends new log entries to the end of the file. This parameter is used if pg_proaudit.log_destination contains the csvlog value.

Default: off

F.37.7. Viewing Security Event Log

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:

  1. Install the file_fdw and create an external server: 

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

  2. Create a foreign table, specifying the columns and the absolute path to the log file. The actual log file location is determined by the pg_proaudit.log_directory and pg_proaudit.log_filename parameters. 

    CREATE FOREIGN 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 )
SERVER pg_proauditlog
OPTIONS (filename 'absolute_file_path_to_log_file.csv', FORMAT 'csv' );

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

F.37.8. Examples

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

  • authentications/disconnections to the postgres database

  • all actions of the postgres user

  • creating, updating, and deleting any tables

  • all operations on the app_table table that belongs to the public schema

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 |             |                  |           | Any authentication in the current DB
 postgres | disconnect   |             |                  |           | Any disconnect from the current DB
 postgres | ALL          | table       |                  |           | Any operations with any table in the current DB
 postgres | ALL          |             |                  | postgres  | Any operation by "postgres" user in the current DB
 postgres | 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.

Prev Up Next
F.36. pg_pathman Home F.38. pgpro_pwr — workload reports
epub pdf