qhb_audit

It is an extension for auditing QHB actions.

The extension consists of three main components

The native dynamic library of the extension libqhb_audit.so.
Event queue in a special area of shared memory.
Dedicated background process qhb_audit_worker to process the queue.

The extension's work cycle:

The server process performs the operation and generates an event.
The extension places the event in a queue in shared memory.
The background process extracts the event from the queue and either writes it to a special file — the audit log, or passes it to an external HTTP agent.

Configuration

The extension is connected by setting parameters in the configuration file qhb.conf. To automatically load the extension when the instance starts, you need to add the following string to qhb.conf:

shared_preload_libraries = 'libqhb_audit'

If shared_preload_libraries is already set, then you need to add the libqhb_audit value to it, separated by commas, for example:

shared_preload_libraries = 'pg_store_plans,pg_stat_statements,libqhb_audit'

The extension (or rather its background process) receives events from the database server through a queue located in the QHB shared memory area. This imposes restrictions on the maximum size of the queue and the memory allocated for it. If the queue size is exceeded, a warning about this will appear in the main server log.

The general parameters are read only when the extension is initialized (when the DBMS starts):

qhb_audit.queue_memory: The size of shared memory allocated for the internal message queue. The default is 10 MB.
qhb_audit.queue_capacity: maximum size of the internal message queue. The default is 1000 events.

If you can not restart the server, updating the parameters is allowed using the qhb_ctl reload command.

Parameters

Parameters for Recording Events to a File

qhb_audit.csv_file

Path to the file where events will be written. This parameter can be changed "on the fly".

If the parameter value is not specified, events are not written to the file. If the specified name does not exist in the file system, a new file will be created.

If the specified name indicates a file in which writing is not permitted or to a file that cannot be created, an error message will be written to the server log, and no events will be writing to the file.

If a relative path to the file is specified, the file is assumed to be in the PGDATA directory. Intermediate directories, if specified, are not created automatically; if they do not exist in the file system, such a path will be invalid.

If the specified name points to a directory, the message is displayed

qhb_audit: CSV output path is a directory '<name>', ignoring

and events will not be written to the file.

When log rotation is enabled (see below), the value of the csv_file parameter has the meaning of a "base" name: new files for rotation are generated based on it (see examples below).

qhb_audit.csv_file_rotation

Event file rotation mode. This parameter can be changed "on the fly".

Rotation prevents the event file from growing uncontrollably by splitting it into multiple files and overwriting them over time "in a circle". Rotation file names are generated based on the value of the qhb_audit.csv_file parameter. Rotation files are created in the same directory that specified in csv_file. This directory is not created automatically, but must exist at start time. If a relative path is specified, rotation files (as well as a single file without rotation) are created at the specified path relative to the PGDATA directory.

If this parameter is not specified or has an empty value, rotation is not performed.

If an incorrect value is specified, the entire extension is blocked and a message is displayed

incorrect value of parameter csv_file_rotation

Allowed parameter values:

off — rotation is disabled, everything is written to a single file;
day — daily rotation in 24 files;
week — weekly rotation in 7 files;
month — monthly rotation in 28-31 files;
year — yearly rotation in 12 files.

Approximate file names generated for each mode with the "base" name audit.csv. A distinguishing suffix is appended to the file name (without extension), the extension is preserved.

Rotation mode	Parameter	File names
daily	day	audit_00h_of_day.csv ... audit_23h_of_day.csv
weekly	week	audit_Mon_of_week.csv audit_Tue_of_week.csv audit_Wed_of_week.csv audit_Thu_of_week.csv audit_Fri_of_week.csv audit_Sat_of_week.csv audit_Sun_of_week.csv
monthly	month	audit_01d_of_month.csv ... audit_31d_of_month.csv
yearly	year	audit_January_of_year.csv audit_February_of_year.csv audit_March_of_year.csv audit_April_of_year.csv audit_May_of_year.csv audit_June_of_year.csv audit_July_of_year.csv audit_August_of_year.csv audit_September_of_year.csv audit_October_of_year.csv audit_November_of_year.csv audit_December_of_year.csv

Files from the previous rotation mode remain and can be deleted manually. The same applies to external tables for accessing audit data via SQL queries (see below).

Preliminary Steps for Setting Up Access to Audit Log Data Via SQL Queries

To access audit data via SQL queries, the file_fdw extension is used. To do this, the file_fdw extension is installed, the main table qhb_audit_log is created, and a log rotation scheme is selected. Depending on the selected scheme, the given script options, where the path to the data directory PGDATA and the directory of audit log files is qualified, are taken as a basis.

The scripts below assume that the audit files will be located in the audit directory of the PGDATA data directory, and the affix of the file name will also be audit. To do this, you can specify in qhb_audit.csv_file either the absolute path <PGDATA>/audit/audit.csv (here <PGDATA> should be replaced with the actual path to the data directory), or specify a path relative to the current data directory, for example audit/audit.csv. If necessary, edit the scripts below to match the actual location of the files.

Setting the PGDATA Variable and Creating an audit Directory Inside PGDATA

Here and in all scripts below, you need to replace <PGDATA> with the real path to the PGDATA directory.

Create a directory for audit log files.

export PGDATA=<PGDATA>

sudo -u qhb mkdir $PGDATA/audit

We make audit data available at the system level only to user qhb:

sudo -u qhb chmod 700 $PGDATA/audit

Creating file_fdw Extension and Registering Foreign Server

Create a file_fdw extension and a foreign server (alternatively, you can do this in the qhb database).

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

Creating the Main Table

This table describes all log fields, and all external log tables that match the selected rotation scheme will inherit from it when they are created.

CREATE TABLE qhb_audit_log
(
  event_id 	bigint,
  event_ts      timestamp,
  event_name    text,
  event_type  	text,        
  user_name     text,
  db_name       text,
  table_name    text,
  query_text  	text,
  event_level 	text
);

If Rotation is Not Needed

If there is no need for rotation, you should just create one external table for the file specified in qhb_audit.csv_file. In the following command, you need to replace <PGDATA> with the actual path to the data directory:

export PGDATA=<PGDATA>

In the following command, you need to replace <file.csv> with the actual file name from qhb_audit.csv_file:

sudo -u qhb touch $PGDATA/audit/<file.csv>

In the command for creating an external table, when specifying filename, you need to replace <PGDATA> with the actual path to the data directory, and <file.csv> with the actual file name from qhb_audit.csv_file:

CREATE FOREIGN TABLE qhb_audit_log_csv() INHERITS (qhb_audit_log) SERVER qhb_audit_log OPTIONS (filename '<PGDATA>/audit/<file.csv>',  FORMAT 'csv', HEADER 'true', DELIMITER ',' );