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.