pg_proaudit Extensionpg_proaudit Extension
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.
Legacy-format logging rules that contain OIDs instead of object names are only partially supported and may be processed incorrectly.
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 can be written in the CEF (Common Event
Format) and CSV (Comma-Separated Values) formats. Each event
is logged on a separate line that contains the following fields:
date and time of the event
current user name
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
additional information like error message in case of FAILURE
or connection parameters and the application name for the
AUTHENTICATE event; unfinished authentication attempts
are marked as [EOF. No credentials provided]
text of the SQL command
parameters of the command (for example, for PREPARE)
session user name
unique ID of the event (in the UUIDv7 format)
transaction ID (XID)
virtual transaction ID (VXID)
number of affected rows
An event contains the information about both session_user
and current_user of the session.
Therefore, it will be possible to identify the user, even if they execute
the SET ROLE command to change user identifier.
The XID and VXID values can be zero if an event does not relate to a transaction (e. g. the DISCONNECT event).
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.
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:
Add pg_proaudit to the
shared_preload_libraries variable in the
postgresql.conf file:
shared_preload_libraries = 'pg_proaudit'
Restart 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;
In each database for which you are going to log security events, 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.
pg_proaudit Extension #
To properly uninstall pg_proaudit, complete the
following steps:
Delete the pg_proaudit extension using the
following query:
DROP EXTENSION pg_proaudit;
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.
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_DDL_NONTEMP,
ALL_DML, ALL_DML_NONTEMP,
ALL_MOD, ALL_PROC,
ALL_ROLE, and MISC.
For the full list of possible event_type values,
see Section F.42.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,
MATERIALIZED VIEW, CATALOG RELATION,
and CATALOG FUNCTION.
To log security events for system catalog objects, it is required
to enable the pg_proaudit.log_catalog_access
configuration parameter. Otherwise, these events will not be logged,
even if the CATALOG RELATION,
CATALOG FUNCTION, or ALL
object type is specified in the rule.
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 user who has the privileges of the role.
This means that at least one of the user session
attributes, session_user
or current_user, should either
be equal to the role_name,
or be directly or indirectly a member of this role.
When an empty string or NULL is specified, enables logging of actions
caused by any user. When set to
current_role,
the logging rule is established for the user current role.
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.
If the rule that you want to remove is configured to log
the DISCONNECT event type, then disconnect events
may still be logged after the rule is removed. For additional
information, see Section F.42.3.1.
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.42.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.
When a logging rule is configured for DISCONNECT
event type, and a user authenticates in a database, then
the pg_proaudit extension checks whether
the corresponding disconnection event satisfies the rule and must be
logged.
If the event must be logged, then the removal of the logging rule has no effect on the event. This event will be logged no matter if the logging rule exists at the moment of disconnect.
Consider the following example:
An administrator configures the following logging rule:
SELECT pg_proaudit_set_rule ('postgres', 'DISCONNECT', null, null, null, 'Any disconnect from the postgres DB');
A user connects to the postgres database
and goes through authentication.
For the rule in effect, a corresponding disconnection event will be logged later.
The administrator removes the logging rule, while the user is still connected to the database:
SELECT pg_proaudit_remove_rule('postgres', 'DISCONNECT', null, null, null);
The user disconnects from the database.
The corresponding disconnection event is logged despite the removal of the logging rule.
Comparison of string representations of names for database, user, role and other objects is case-insensitive even if the object name in the database is enclosed in quotes. These string representations are stored as lower-case strings.
Strings representing object names need to be passed to functions
pg_proaudit_set_rule and pg_proaudit_remove_rule
as regular strings: a single quote needs repeating, and other characters are passed
as is, without escaping or wrapping.
For example, rules for tables table1 and
"TaBlE1" will be the same, and their names can be passed
in upper-case or lower-case characters. The same holds for roles. As for
databases, before creating a rule, pg_proaudit
checks whether this database actually exists, and this check is case-sensitive.
The following examples illustrate creation of "weird" object names and specifying correct audit rules for them.
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:
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.
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 except stored procedures and functions.
ALL_DDL_NONTEMP: same as
ALL_DDL but the scope is limited to the objects
that are not contained in
pg_temp_
temporary schemas.
nnn
ALL_DML: SELECT,
INSERT, UPDATE,
DELETE, TRUNCATE
for any table type; EXECUTE for functions
and stored procedures.
ALL_DML_NONTEMP: same as
ALL_DML but the scope is limited to the objects
that are not contained in
pg_temp_
temporary schemas.
nnn
ALL_MOD: INSERT,
UPDATE, DELETE,
TRUNCATE for any table type.
ALL_PROC: CREATE,
ALTER, DROP for any function
and stored procedure.
ALL_ROLE: CREATE,
ALTER, DROP for
USER, ROLE,
GROUP, PROFILE, as well as
execution of the GRANT command.
MISC: all events not related to any of the other
classes mentioned above.
The following specific security events are supported:
ANALYZE
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 PROFILE
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
CHECKPOINT
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 PROFILE
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
DISCARD ALL
DISCARD PLANS
DISCARD SEQUENCES
DISCARD TEMP
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 PROFILE
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
FETCH
GRANT
INSERT
MOVE
PREPARE
REASSIGN OWNED
REFRESH MATERIALIZED VIEW
REINDEX
RESET
REVOKE
SECURITY LABEL
SELECT
SET
UPDATE
TRUNCATE TABLE
VACUUM
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.
pg_proaudit.log_destination (string)
#Defines the method for logging security events. Possible values are:
csvlog — log security events in a
CSV file.
ceflog — log security events in a
CEF file. CEF is an open
text-based format designed for log records. It contains a
standard header and a variable extension formatted as key-value
pairs.
syslog — log security events in
syslog.
cefsyslog — log security events in
the CEF format in
syslog.
You can specify one or more values separated by commas. Note, however,
that syslog and cefsyslog are
mutually exclusive. If both are specified, cefsyslog
takes precedence and syslog is ignored.
Default: csvlog
pg_proaudit.log_catalog_access (boolean)
#
Specifies whether to log access to system catalog objects in the
pg_catalog schema.
When set to off, no events will be logged, even if
there are rules for the CATALOG RELATION,
CATALOG FUNCTION, or ALL object types.
It is not recommended to set the parameter to on,
if you do not plan to log events for system catalog objects. Doing
otherwise may impact Postgres Pro performance,
even if there are no rules for the aforementioned object types.
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
and CEF log files. This can be an absolute path or
a relative path to the cluster data directory (PGDATA).
This parameter is used if the csvlog or
ceflog value is specified in the
pg_proaudit.log_destination
parameter.
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 the csvlog or
ceflog value is specified in the
pg_proaudit.log_destination
parameter.
Default: postgresql-%Y-%m-%d_%H%M%S.log
pg_proaudit.log_rotation_size (integer)
#
Sets the maximum size of a log file, in kilobytes. When this size is
achieved, pg_proaudit creates a new file for
logging security events. If set to 0, disables
size-based creation of new log files. This parameter is used if the
csvlog or ceflog value is specified
in the pg_proaudit.log_destination
parameter.
Default: 10MB
pg_proaudit.log_rotation_age (integer)
#
Sets the maximum lifetime of a log file, in minutes. After this
timeframe is elapsed, pg_proaudit creates a new
file for logging security events. If set to 0, disables
time-based creation of new log files. This parameter is used if the
csvlog or ceflog value is specified
in the pg_proaudit.log_destination
parameter.
Default: 1 day
pg_proaudit.log_rows (boolean)
#
Specifies whether to log the number of rows affected by an SQL command.
This parameter is used if the csvlog
or ceflog value is specified in the
pg_proaudit.log_destination
parameter.
Default: off
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 the csvlog or
ceflog value is specified in the
pg_proaudit.log_destination
parameter.
Default: off
pg_proaudit.max_rules_count (integer)
#Specifies the maximum number of rules allowed. For the parameter changes to take effect, the database server must be restarted.
Default: 500
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,
current_usr_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,
session_usr_name text,
uuid text,
xid text,
vxid 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:
authentications/disconnections to the postgres
database
all actions of the user if at least one of the user session
attributes, session_user
or current_user, is either explicitly
set to the postgres role, or is directly
or indirectly a member of this role
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, current_usr_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, session_usr_name text, uuid text, xid text, vxid 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, current_usr_name,
session_pid, event_type, query_text, session_usr_name
FROM pg_proaudit_log
WHERE object_name = 'public.app_table';
when | current_usr_name | session_pid | event_type | query_text | session_usr_name
-------------------+------------------+-------------+--------------+---------------------------------------------+------------------
27.09.23 12:44:27 | postgres | 2010 | CREATE TABLE | CREATE TABLE app_table (id int, name text); | postgres
27.09.23 12:45:55 | postgres | 2010 | INSERT | INSERT INTO app_table VALUES (1, 'first'); | postgres
27.09.23 12:46:00 | postgres | 2010 | SELECT | SELECT * FROM app_table; | postgres
(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.