Audit trigger 91plus

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(New audit trigger for Pg 9.1+, uses hstore)

Revision as of 07:04, 16 July 2012

Library Snippets

Generic audit trigger function (enhanced)

Works with PostgreSQL

9.1+

Written in

PL/pgSQL

Depends on

Nothing


Here is an example of a generic trigger function used for recording changes to tables into an audit log table. It records quite a bit more detail than the older Audit trigger and does so in a more structured manner.

Row values are recorded as hstore fields rather than as flat text. This allows much more sophisticated querying against the audit history and allows the audit system to record only changed fields for updates.

Auditing can be done coarsely at a statement level or finely at a row level. Control is per-audited-table.

The information recorded is:

  • Change type - Insert, Update, Delete or Ttruncate.
  • client IP/port if not a UNIX socket
  • Session user name ("real" user name, not "set role" name)
  • transaction, statement, and wall clock timestamps
  • Top-level statement that caused the change
  • The row value before the change (or after in the case of INSERT)
  • In the case of UPDATE, the new values of any changed columns. The new value can be reconstructed using row_value || changed_fields
  • The transaction ID of the tx that made the change
  • The application_name
  • Target schema and table, by OID and name.

This trigger can not track:

  • SELECTs
  • DDL like ALTER TABLE
  • Changes to system catalogs

Changes by the table owner and superusers are tracked, but can be trivially tampered with.

If you want this audit log to be trustworthy, your app should run with a role that has at most USAGE to the audit schema and SELECT rights to audit.logged_actions. Most importantly, your app must not connect with a superuser role and must not own the tables it uses. Create your app's schema with a different user to the one your app runs as, and GRANT your app the minimum rights it needs.

Contents

The trigger

You can obtain the latest version of the audit trigger from GitHub.

A copy - hopefully up to date - appears below.

 

Basic usage

SELECT audit.audit_table('target_table_name');

The table will now have audit events recorded at a row level for every insert/update/delete, and at a statement level for truncate. Query text will always be logged.

If you want finer control use audit.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) or CREATE TRIGGER the audit trigger yourself.

Ignoring some changes

In PostgreSQL 9.0 and above, you can add an optional WHEN clause to invocations of the trigger. This is great for auditing, as it lets you exclude some changes without even paying the cost of invoking the trigger. A common case is to ignore a frequently updated field that isn't worth auditing:

CREATE TRIGGER tablename_audit_insert_delete
AFTER INSERT OR DELETE ON sometable FOR EACH ROW
EXECUTE PROCEDURE audit.if_modified_func();
 
CREATE TRIGGER tablename_audit_update_selective
AFTER UPDATE ON sometable FOR EACH ROW
WHEN ( OLD.ignored_column IS DISTINCT FROM NEW.ignored_column )
EXECUTE PROCEDURE audit.if_modified_func();

... but of course, you can do a lot more. You'll often need to separate your INSERT, DELETE and UPDATE trigger definitions rather than doing them all in one trigger definition, though.

Note the use of IS DISTINCT FROM rather than =. Think about the effect of NULL.

Time zone concerns

TIMESTAMP WITH TIME ZONE fields are always stored in UTC and are displayed in local time by default. You can control display using AT TIME ZONE in queries, or with SET timezone = 'UTC' as a per-session GUC. See the Pg docs.

Loosely based on Audit trigger by bricklen, completely rewritten by ringerc for more audit detail, hstore logging, and more.

Personal tools