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 settings 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 settings, 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 connection/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. 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.
The pg_proaudit extension is a built-in extension included into
Postgres Pro Standard. To enable pg_proaudit, complete the following
steps:
Add pg_proaudit to the
shared_preload_libraries variable in the
postgresql.conf file:
shared_preload_libraries = 'pg_proaudit'
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;
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 settings and event
triggers. For convenience, the pg_proaudit extension should be
created in each database for which you are going to log security events.
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_object(event_type text,
object_type text)
pg_proaudit_set_object(event_type text,
object_oid oid)
Enables logging for events with the specified parameters. When the
pg_proaudit_set_object() 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:
event_type — type of the event that needs
to be logged, including SQL operator names, as well as
CONNECT and DISCONNECT events.
When set to ALL, 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, as well as
CREATE, ALTER,
DROP. For the full list of possible
event_type values, see Section F.39.3.1.
object_type — type of the object for which
security events need to be logged. For example, specify
FOREIGN TABLE object type for the SELECT
event to log all attempts to access foreign tables. Use the
NULL value if event_type is
set to CONNECT, DISCONNECT,
SET, or RESET, and the
ROLE value for all events that reference user
actions, such as CREATE USER or
DROP USER.
object_oid — identifier of the object
(OID) for which security events need to be logged.
pg_proaudit_set_role(event_type text,
role_oid oid)
Enables logging of events caused by the specified DBMS user. When
the pg_proaudit_set_role() 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:
event_type — type of the event that
needs to be logged. When set to ALL, enables
logging for all commands executed by the role_oid
user. For the list of possible values, see Section F.39.3.1.
role_oid — identifier of the
user (OID) for which actions need to be logged.
pg_proaudit_reset_object(event_type text,
object_type text)
pg_proaudit_reset_object(event_type text,
object_oid oid)
Disables logging of events with specified parameters.
To save the changes in the pg_proaudit.conf file,
call the pg_proaudit_save() function.
Arguments:
event_type — type of the event that needs
to be logged, including SQL operator names, as well as
CONNECT and DISCONNECT events.
When set to ALL, 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, as well as
CREATE, ALTER,
DROP. For the full list of possible
event_type values, see Section F.39.3.1.
object_type — type of the object for which
security events need to be logged. For example, specify
FOREIGN TABLE object type for the SELECT
event to log all attempts to access foreign tables. Use the
NULL value if event_type is
set to CONNECT, DISCONNECT,
SET, or RESET, and the
ROLE value for all events that reference user
actions, such as CREATE USER or
DROP USER.
object_oid — identifier of the object (OID)
for which security events need to be logged.
pg_proaudit_reset_role(event_type text,
role_oid oid)
Disables logging of events caused by the specified DBMS user. To save
information about the canceled events in the pg_proaudit.conf file,
call the pg_proaudit_save() function.
Arguments:
event_type — type of the event that
needs to be logged. When set to ALL, enables
logging for all commands executed by the role_oid
user. For the list of possible values, see Section F.39.3.1.
role_oid — identifier of the
user (OID) for which actions need to be logged.
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()
Disables logging of all events. To save information about the canceled
events in the pg_proaudit.conf file, call the
pg_proaudit_save() function.
pg_proaudit_save()
Saves logging settings 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.
This view displays the current pg_proaudit settings,
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 — 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.
The pg_proaudit extension can log the following security events:
CONNECT
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
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
GRANT
INSERT
REASSIGN OWNED
REFRESH MATERIALIZED VIEW
REINDEX
RESET
REVOKE
SECURITY LABEL
SELECT
SET
UPDATE
TRUNCATE TABLE
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.
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: 1day
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
pg_proaudit.syslog_facility (enum)
Specifies the logging category in syslog. This parameter is
used if pg_proaudit.log_destination contains
the syslog value. Possible values are:
LOCAL0, LOCAL1,
LOCAL2, LOCAL3,
LOCAL4, LOCAL5,
LOCAL6, LOCAL7.
Default: LOCAL0
pg_proaudit.syslog_ident (string)
Specifies the program name used to identify
pg_proaudit messages in syslog logs.
Default: pg_proaudit
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:
Install the file_fdw and create an external
server:
CREATE EXTENSION file_fdw; CREATE SERVER pg_proauditlog FOREIGN DATA WRAPPER file_fdw;
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.
As an example, let's set up logging for the following security events:
connections/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 | 1.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_object ('CONNECT', null);
SELECT pg_proaudit_set_object ('DISCONNECT', null);
SELECT pg_proaudit_set_object ('ALL', 'table');
SELECT pg_proaudit_set_role ('ALL', 'postgres'::regrole);
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_object ('ALL', 'public.app_table'::regclass);
Check that event logging is configured as expected:
postgres=# SELECT * FROM pg_proaudit_settings; db_name | event_type | object_type | object_name | role_name ---------+------------+-------------+-----------------+----------- postgres | DISCONNECT | | 0 | 0 postgres | CONNECT | | 0 | 0 postgres | ALL | TABLE | 0 | 0 postgres | ALL | | public.app_table| 0 postgres | ALL | | 0 | postgres (5 rows)
Save these configuration settings 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';
-[ RECORD 1 ]--------------------------------------------
when | 24.11.16 16:31:16
role_name | postgres
session_pid | 26513
event_type | CREATE TABLE
query_text | CREATE TABLE app_table (id int, name text);
-[ RECORD 2 ]--------------------------------------------
when | 24.11.16 16:31:53
role_name | postgres
session_pid | 26513
event_type | INSERT
query_text | INSERT INTO app_table VALUES (1, 'first');
-[ RECORD 3 ]--------------------------------------------
when | 24.11.16 16:31:54
role_name | postgres
session_pid | 26513
event_type | SELECT
query_text | SELECT * FROM app_table;
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.