Working with Postgres Audit Triggers

SUMMARY: This article takes a close look at audit triggers in PostgreSQL. It explains what an audit trigger does and walks through the steps of how to create one.

           1. What is an audit trigger?

                    a. Example

                    b. Limitations

 

In this post, we are going to look at Audit triggers and how we can use them in PostgreSQL databases.

What is an audit trigger? 

Audit trigger works with PostgreSQL 8.4+ and can be written in PL/pgSQL (Procedural language/PostgreSQL), which is a procedural language where you can perform more complex tasks—e.g., easy computation—as compared to SQL, and also make use of loops, functions, and triggers. 

To create a trigger in PostgreSQL we need to use the CREATE FUNCTION syntax. We declare the trigger as a function without any arguments and a return type of <trigger>. 

With the help of audit trigger, we can track changes to a table like data insertion, updates, or deletions. In short, we can say that auditing data changes within a database will store the old and new records, the user who made the change, and a timestamp (date/time).

This information is important for any organization to track down who did what and when, and to provide a history of data/information for various internal auditing purposes. 

Example 

We can see any insert, update, or deletion of a row in the table “foo” recorded (for auditing purposes) with current time and user information in the “foo_audit” table using trigger. It will also record the type of operation (INSERT or UPDATE or DELETE) performed by the user.

This example has been performed on 

OS = Centos 7 x64 

PostgreSQL version = 11.5

 

1. Connect to psql terminal  

/usr/bin/psql -U postgres postgres -p 5432 

psql.bin (11.5.12)

Type "help" for help.

postgres=# 

 

2. CREATE TABLE “foo” containing -first_name, last_name, and salary. 

postgres=# create table foo

                           (

                            first_name varchar(20),

                            last_name varchar(20),

                            salary int

                           );

CREATE TABLE

 

3. CREATE TABLE “foo_audit” to store the information from data changes. 

postgres=# create table foo_audit

                  (

                  op  char(1)   NOT NULL,

                 stamp  timestamp NOT NULL,

                 user_id char(20)    NOT NULL,

                 first_name  varchar(20),

                 last_name  varchar(20), 

                 salary int

                 );

CREATE TABLE

 

4. CREATE TRIGGER for storing data changes (auditing) into table “foo_audit”.

postgres=# create or replace function foo_audit_information() returns trigger  

                 as  

                 $foo_audit$

                 begin

                 if (TG_OP = 'DELETE') THEN

                 insert into foo_audit SELECT 'D', now(), user, OLD.*;

                 elsif (TG_OP = 'UPDATE') THEN

                 insert into foo_audit SELECT 'U', now(), user, NEW.*;

                 elsif (TG_OP = 'INSERT') THEN

                 insert into foo_audit SELECT 'I', now(), user, NEW.*;

                 end if;

                 return null;

                 end;

                 $foo_audit$ 

                 language plpgsql;

CREATE FUNCTION

 

TG_OP is a special variable that indicates the type of operation—i.e., INSERT, UPDATE, DELETE—that triggered it.

 

5. CREATE TRIGGER for calling the trigger function. 

postgres=# create trigger foo_audit_trigger

                  after insert or update or delete on foo

                  for each row 

                  execute procedure foo_audit_information();

CREATE TRIGGER

 

6. Show the table and trigger information. 

postgres=# \d foo

                         Table "public.foo"

   Column   |         Type          | Collation | Nullable | Default 

------------+-----------------------+-----------+----------+---------

 first_name | character varying(20)  |           |          | 

 last_name  | character varying(20) |           |          | 

 salary     | integer               |            |           | 

Triggers:

    foo_audit_trigger AFTER INSERT OR DELETE OR UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE foo_audit_information()

 

7. Perform an INSERT operation in table “foo”.

postgres=# insert into foo values ('first_name','last_name',1);

INSERT 0 1

 

8. Check the “foo_audit” table information.

 

postgres=# select * from foo_audit ;

 op |          stamp           |       user_id        | first_name | last_name | salary 

----+--------------------------+----------------------+------------+-----------+--------

 I  | 06-NOV-19 20:16:49.04633 | postgres| first_name | last_name |  1

(1 row)

 

9. Perform an UPDATE  operation in table “foo”.

postgres=# update foo set salary=10;

UPDATE 1





postgres=# select * from foo_audit;

 op |           stamp           |       user_id        | first_name | last_name | salary 

----+---------------------------+----------------------+------------+-----------+--------

 I  | 06-NOV-19 20:16:49.04633  | postgres | first_name | last_name | 1

 U  | 06-NOV-19 20:17:20.257599 | postgres| first_name | last_name |10

(2 rows)

 

10. Perform a DELETE operation in table “foo”.

postgres=# delete from foo;

DELETE 1



 postgres=# select * from foo_audit;

 op |           stamp           |       user_id        | first_name | last_name | salary 

----+---------------------------+----------------------+------------+-----------+--------

 I  | 06-NOV-19 20:16:49.04633  |postgres| first_name | last_name |  1

 U  | 06-NOV-19 20:17:20.257599 |postgres| first_name | last_name |10

 D  | 06-NOV-19 20:17:27.395814 |postgres| first_name | last_name |10

(3 rows)

 

I =Insert

U=Update

D=Delete 

 

Limitations

—We cannot audit SELECT activity through the audit trigger, but we can check SELECT activities on the database using database logs.

—We cannot audit DDL statements and system tables.

 

Hope it helps!