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

• UPDATE STATEMENT

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

Let’s check the table logged_action in schema audit

• DELETE STATEMENT

DELETE FROM public.custom WHERE id='2506'

Let’s check the table logged_action in schema audit

• TRUNCATE STATEMENT

TRUNCATE TABLE public.custom

Let’s check the table logged_action in schema audit

• 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

• 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

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
Find Us @
Facebook
Telegram
Twitter
LinkedIn