O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Auditing changes

If you need to know who did what to the data and when it was done, one way to do that is to log every action that is performed on an important table.

There are at least two equally valid ways of doing the auditing:

  • Use auditing triggers
  • Allow tables to be accessed only through functions, and do the auditing inside these functions

Here, we will take a look at minimal examples of 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 DEFAULT CURRENT_USER, changed_at timestamp DEFAULT CURRENT_TIMESTAMP, salary_op text, emp_name text, old_salary integer, new_salary integer ); REVOKE ALL ON salary_change_log FROM PUBLIC; ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required