Auditing Updates in PostgreSQL

A database is a system that provides leverage for dealing with persistent data, and part of this functionality is the ability to execute functions in response to INSERT, UPDATE, and DELETE. These actions may be consistency checks or automatic normalization or de-normalization of data. Triggers can also be used to provide a record of updates so that the reason for the modification of data itself can be obtained.

Tracking Changes

Suppose we have a table that tracks open tickets

CREATE TABLE tickets (
  id serial PRIMARY KEY,
  description text,
  asignee varchar,
  status varchar -- 'open' or 'closed'
);

If the state changes in the table in an unexpected way, then naturally the code that updated the table should be reviewed. But what do we do if you are not able to discern the source of a change? We can log particular state changes using a trigger.

Installing the Trigger

First create a table that has contains field for the data we're interested in. Capturing the application_name is always a good idea, but critical if you're suing a proxy such as PgBouncer which will obscure the source IP address.

CREATE TABLE ticket_updates (
  time timestamp with time zone NOT NULl,
  application_name varchar NOT NULL,
  remote_addr inet,
  query text NOT NULL
);

The trigger could record every updated, but in this case we only want to record the context for the case where the ticket was not closed, and after an update it is set to closed

CREATE FUNCTION log_ticket_update()
  RETURNS trigger AS
$$
BEGIN
  IF OLD.state_id != 'closed' AND NEW.state_id = 'closed' THEN
    INSERT
      INTO ticket_updates VALUES(
        now(),
        current_setting('application_name'),
        inet_client_addr(),
        current_query()
      );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ticket_update_trigger
  AFTER UPDATE ON tickets
  FOR EACH ROW
  EXECUTE PROCEDURE log_ticket_update();