Database Design: Audit Log, Tracking Changes to Column Data Value in PostgreSQL (PART 2)

2 months ago SETHA THAY 281
Database Design: Audit Log, Tracking Changes to Column Data Value in PostgreSQL (PART 2)

In part 1, we have introduced about audit log that you can tracking changes to the column data value and configuration steps of how you can achieve it. In this blog post, you will learn more about basic usage, experiments were done by applying audit log into the database, and some significant query language for data type hstore so that you can easily query the data changes as a report for your management or audit team.

Basic Usage

  • To attach the audit log trigger to the table you want to record log run the following statement
SELECT audit.audit_table('schemaName.tableName');

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.

  • Cancel audit log of a table
DROP TRIGGER audit_trigger_row ON target_table_name; 
DROP TRIGGER audit_trigger_stm ON target_table_name;
  • Finding all tables which have the audit log
SELECT * FROM audit.tableslist;

Experiment

  • INSERT STATEMENT
INSERT INTO custom (name, date, country, currency_code, credit_card_full, credit_card_provider) VALUES ('Setha Thay','1989-03-01','Cambodia','KH','JCB 16 digit Amy Eaton 3158008305030451 07/24 CVC: 293','JCB 15 digit');

Let’s check the table logged_action in schema audit

database-design-audit-log-tracking-changes-to-column-data-value-changes-insert-statement-1

database-design-audit-log-tracking-changes-to-column-data-value-changes-insert-statement-2

  • UPDATE STATEMENT
UPDATE custom SET country='South Korea' WHERE name='Setha Thay';

Let’s check the table logged_action in schema audit

database-design-audit-log-tracking-changes-to-column-data-value-changes-update-statement-1

database-design-audit-log-tracking-changes-to-column-data-value-changes-update-statement-2

  • DELETE STATEMENT
DELETE FROM public.custom WHERE id='2506'

Let’s check the table logged_action in schema audit

database-design-audit-log-tracking-changes-to-column-data-value-changes-delete-statement

  • TRUNCATE STATEMENT
TRUNCATE TABLE public.custom

Let’s check the table logged_action in schema audit

database-design-audit-log-tracking-changes-to-column-data-value-changes-truncate-statement

  • BULK INSERT
INSERT INTO custom (name, date, country, currency_code, credit_card_full, credit_card_provider) VALUES ('Mr. XBox','1990-03-01','Cambodia','KH','JCB 16 digit Amy Eaton 3158008305030451 07/24 CVC: 293','JCB 15 digit'), ('Ms. Lesley.','1989-03-01','Cambodia','KH','JCB 16 digit Amy Eaton 3158008305030451 07/24 CVC: 293','JCB 15 digit');

Let’s check the table logged_action in schema audit

database-design-audit-log-tracking-changes-to-column-data-value-changes-bulk-insert-1

database-design-audit-log-tracking-changes-to-column-data-value-changes-bulk-insert-2

  • UPDATE STATEMENT AFFECT MULTIPLE ROWS
UPDATE custom SET currency_code='WON' WHERE date= '1989-03-01' AND country='Cambodia'

Let’s check the table logged_action in schema audit

database-design-audit-log-tracking-changes-to-column-data-value-changes-update-multiple-rows-1

database-design-audit-log-tracking-changes-to-column-data-value-changes-update-multiple-rows-2

Query hstore

The hstore module implements hstore data type for storing key-value pairs in a single value. The hstore data type is very useful in many cases, such as semi-structured data or rows with many attributes that are rarely queried.

  • Create a table with hstore data type
create table books(
  id serial primary key,
  title varchar(255),
  attr hstore
);
  • Insert data into the table
INSERT INTO books (title, attr)
VALUES(
'PostgreSQL Cheat Sheet',
'
"paperback" => "5",
"publisher" => "postgresqltutorial.com",
"language"=> "English",
"ISBN-13" => "8283-98484",
"weight" => "1 ounces"
');
  • Query value for a specific key
SELECT attr->'ISBN-13' AS isbn FROM books;
  • Use value in the WHERE clause
SELECT attr->'weight' AS weight
FROM books
WHERE attr->'ISBN-13' = '8283-98484';
  • Add key-value pairs to existing rows
UPDATE books SET attr = attr || '"freeshipping" => "yes"' :: hstore;
  • Update existing key-value pair
UPDATE books SET attr = attr || '"freeshipping" => "no"' :: hstore;
  • Remove existing key-value pair
UPDATE books SET attr = delete(attr, 'freeshipping');
  • Check for a specific key in hstore column
SELECT title, attr->'publisher' as publisher, attr FROM books WHERE attr ? 'publisher';
  • Check for a key-value pair
SELECT title FROM books WHERE attr @> '"weight" => "11.2 ounces"' :: hstore;
  • Query rows that contain multiple specified keys
SELECT title FROM books WHERE attr ?& ARRAY['languge', 'weight'];
  • Get all keys from an hstore column
SELECT akeys(attr) FROM books;
  • Get all values from an hstore column
SELECT avals(attr) FROM books
  • Convert hstore data to JSON
SELECT title, hstore_to_json(attr) json FROM books;
  • Convert hstore data to sets
SELECT title, (EACH(attr)).* FROM books;

Query hstore with Table logged_action

  • Count number of changes by record ID
SELECT COUNT(*) FROM audit.logged_actions WHERE action='U' AND row_data->'id'='2507'
  • Field and value changes by record ID
SELECT event_id, action, skeys(changed_fields) as field, svals(changed_fields) as new_value FROM audit.logged_actions WHERE action='U' AND row_data->'id'='2507';
  • Field and value change by record ID, Old Value Vs New Value
SELECT event_id, action, skeys(changed_fields) as field, svals(slice(row_data, akeys(changed_fields))) as old_value, svals(changed_fields) as new_value FROM audit.logged_actions WHERE action='U' AND row_data->'id'='2507';
  • Field and value change by transaction ID
SELECT transaction_id,table_name, action, row_data FROM audit.logged_actions WHERE transaction_id=898
  • Field and value change by transaction ID, Old Value Vs New Value
SELECT transaction_id,table_name, action, row_data, hstore_to_json(slice(row_data, akeys(changed_fields))) as old_value, hstore_to_json(changed_fields) FROM audit.logged_actions WHERE transaction_id=898
  • Join Master Table for Data Display

Problem statement: we have 2 tables tb_clients and tb_addresses. Table tb_clients has a field called “address” which is stored id that references to table tb_addresses. If the user happens to change the id of address inside tb_clients, the audit log will log only id changes without the description of the address. In this regard, we have to use the join operation to get the description of the address to show to the user.
There are two ways to solve the above problem: CTE (Common Table Expression and Sub Query)

  • CTE (Common Table Expression)
WITH cte_audit AS ( 

SELECT event_id, action, skeys(changed_fields) as field, 
svals(slice(row_data, akeys(changed_fields))) as old_value, 
svals(changed_fields) as new_value FROM audit.logged_actions 
WHERE action='U' AND row_data->'id'='1' AND table_name='tb_clients' 

) 

SELECT cte_audit.event_id,cte_audit.action,cte_audit.field, 
CASE cte_audit.field WHEN 'address' THEN ad1.address ELSE cte_audit.old_value END AS old_value, 
CASE cte_audit.field WHEN 'address' THEN ad2.address ELSE cte_audit.new_value END AS new_value 

FROM cte_audit 

LEFT JOIN tb_addresses as ad1 ON cte_audit.old_value = ad1.id::text 
LEFT JOIN tb_addresses as ad2 ON cte_audit.new_value = ad2.id::text;
  • Sub Query
SELECT cte_audit.event_id,cte_audit.action,cte_audit.field, 
CASE cte_audit.field WHEN 'address' THEN ad1.address ELSE cte_audit.old_value END AS old_value, 
CASE cte_audit.field WHEN 'address' THEN ad2.address ELSE cte_audit.new_value END AS new_value 

FROM 
(SELECT event_id, action, skeys(changed_fields) as field, 
svals(slice(row_data, akeys(changed_fields))) as old_value, 
svals(changed_fields) as new_value FROM audit.logged_actions 
WHERE action='U' AND row_data->'id'='1' AND table_name='tb_clients') 
AS cte_audit 

LEFT JOIN tb_addresses as ad1 ON cte_audit.old_value = ad1.id::text 
LEFT JOIN tb_addresses as ad2 ON cte_audit.new_value = ad2.id::text;

There are some notes you need to remember if you apply this technique

  • Table audit log (logged_actions) record only transaction ID auto-generated by PostgreSQL without any meaningful description
  • Field session_user_name in table logged_actions can only log users who connect to the database (user configuration in application) but not the real users who logged in to the application. To avoid this problem, applications have to send created_by, created_date, updated_by, updated_date when inserting and updating records in order for the trigger to log the data into table logged_actions
  • Field “application_name” will record in table “logged_actions” as “PostgreSQL JDBC Driver” as a default configuration in JDBC. If we want to specify the application, we have to change JDBC configuration as below: jdbc:postgresql://localhost:5435/MyDB?ApplicationName=MyApp

 

THANK YOU
Facebook: https://web.facebook.com/nestcode.co
Telegram: https://t.me/nestcode168
Twitter: https://twitter.com/code_nest
LinkedIn: https://www.linkedin.com/company/nest-code


About author

Author Profile

SETHA THAY

Coding lover. I am willing to share my knowledge and 10 years experiences in the IT field. Please feel free to feedback on my post if you found any problems. Thank You.


DigitalOcean Referral Badge

Scroll to Top