PostgreSQL - Trigger-based Auditing

Sometimes we need an auditing solution for some tables in our databases and face some of the following requirements:

• Auditing from application side is not an option for us as we are using a third-party application, so, we don´t have direct access to modify the application code and that third party application doesn´t provide those auditing capabilities.
• We need to access the auditing records from inside the server, by using SQL commands.
• We have huge transactional activity in some tables that we don´t need to audit, we just need to audit some subset of tables.
• We need a standard solution to work between different cloud providers or on-prem without configuration changes.

Proposal

In this scenario, we should consider trigger-based auditing with specific permission policy.

We can create triggers in the tables we want to audit, so we can collect the auditing data properly in separate table/tables with following benefits:

• Trigger-based auditing don´t need any application side change to be applied.
• Trigger-based auditing can be enabled for selected tables, no need to enable auditing for whole server/tables.
• Trigger-based auditing is a transparent solution that should work across different cloud providers or on-prem configurations (as far as hstore extension is available, see "Preparation" section).
• Trigger-based auditing will allow us to access the audited data using standard SQL commands.

Implementation

We´ll use Audit trigger 91plus , git code can be found here: audit-trigger 

Preparation

Hstore extension

This solution requires hstore extension, so, in our Azure Database for PostgreSQL Flexible server we should allow that extension to be used: PostgreSQL extensions in Azure Database for PostgreSQL - Flexible Server . Apart from that, the provided script will create a schema called audit with some functions, table, view that will help in the triggers creation and to maintain the auditing records.

Creating schema, table, view and auditing functions

We need to execute the contents of audit-trigger  in the database we want to enable our trigger-based auditing solution.

Usage

Here we have some usage examples: Audit trigger 91plus , pasting here some of them:

- Auditing table "target_table_name":

SELECT audit.audit_table('target_table_name');

"The table will now have audit events recorded at a row level for every insert/update/delete, and at a statement level for truncate. Query text will always be logged."

- Canceling auditing on table "target_table_name":

DROP TRIGGER audit_trigger_row on target_table_name;
DROP TRIGGER audit_trigger_stm on target_table_name;

- Auditing table "target_table_name" , without query text, without some columns (version_col, changed_by, changed_timestamp):

"If you want finer control use audit.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean, excluded_cols text[]) or CREATE TRIGGER the audit trigger yourself. For example:

SELECT audit.audit_table('target_table_name', 'true', 'false', '{version_col, changed_by, changed_timestamp}'::text[]);

... would create audit triggers on target_table_name that record each row change but omit the query text (the 'false' argument) and omit the version_col, changed_by and changed_timestamp columns from logged rows. An UPDATE that only changes ignored columns won't result in an audit record being created at all."

- Check tables with auditing enabled:

select * from audit.tableslist;

schema | auditedtable
--------+--------------
 public | test

Here we are auditing table test in public schema.

- Geeting the audited data:

select * from audit.logged_actions;

event_id | schema_name | table_name | relid | session_user_name |       action_tstamp_tx        |       action_tstamp_stm       |       action_tstamp_clk       | transaction_id | application_name | client_addr  | client_port |        client_query        | action |   row_data   | changed_fields | statement_only
----------+-------------+------------+-------+-------------------+-------------------------------+-------------------------------+-------------------------------+----------------+------------------+--------------+-------------+----------------------------+--------+--------------+----------------+----------------
        1 | public      | test       | 25422 | frpardil          | 2023-07-31 07:57:42.266933+00 | 2023-07-31 07:57:42.266933+00 | 2023-07-31 07:57:42.269395+00 |        3070917 | psql             | 47.61.102.31 |       53673 | update test set col='321'; | U      | "col"=>"123" | "col"=>"321"   | f

Here we can observe the query used: update test set col='321', previous value on that column: "col"=>"123" and change applied: "col"->"321".

Limitations

Here we´ll show some limitations or concerns to consider before using this triggers-based auditing:

• Performance: triggers will create an overhead for each DML activity executed on the audited tables.
• Locking: Trigger-based auditing uses a single table to save all auditing records, this could have some locking issues for high auditing activity.
• Testing, we should test trigger-based auditing before applying it to any production environment to measure the performance impact.
• Only DML activity, the trigger-based auditing is limited to DML activity (insert, update, delete), we can´t audit SELECT statements using this method.