PostgreSQL Server Programming - Second Edition

Auditing changes

If you need to know who did what to the data and when it was done, one way to find out is to log every action that is performed in an important table. In PostgreSQL 9.3, you can also audit the data definition language (DDL) changes to the database using event triggers. We will learn more about this in the later chapters.

There are at least two equally valid ways to perform data auditing:

• Using auditing triggers
• Allowing tables to be accessed only through functions and auditing inside these functions

Here, we will take a look at a minimal number of examples for both the approaches.

First, let's create the tables:

CREATE TABLE salaries( emp_name text PRIMARY KEY, salary integer NOT NULL ); CREATE TABLE salary_change_log( changed_by text ...