Skip to content

Simple, easily customised trigger-based auditing for PostgreSQL (Postgres). See also pgaudit.

License

Notifications You must be signed in to change notification settings

m-martinez/pg-audit-json

Repository files navigation

Build Status

A simple, customizable table audit system for PostgreSQL implemented using triggers and JSONB for storing diffs. Additionally, if any column is also a JSON type, a recursive diff will be generated for changed fields.

This trigger is a fork of 2ndQuadrant's audit trigger implementation.

Significant changes made from original work:

  • Usage of JSONB instead of HSTORE
  • Slight table/column name differences
  • INSERT values are stored in the changed_fields instead of row_data to indicate that a new record is an entire change.

Audit Table Reference

Column Type Not Null Description
id BIGINT Unique identifier for each auditable event
schema_name TEXT Database schema audited table for this event is in
table_name TEXT Non-schema-qualified table name of table event occured in
relid OID Table OID. Changes with drop/create.
session_user_name TEXT Login / session user whose statement caused the audited event
current_user_name TEXT Effective user that cased audited event (if authorization level changed)
action_tstamp_tx TIMESTAMP Transaction start timestamp for tx in which audited event occurred
action_tstamp_stm TIMESTAMP Statement start timestamp for tx in which audited event occurred
action_tstamp_clk TIMESTAMP Wall clock time at which audited event's trigger call occurred
transaction_id BIGINT Identifier of transaction that made the change.
Unique when paired with action_tstamp_tx.
client_addr INET IP address of client that issued query. Null for unix domain socket.
client_port INTEGER Port address of client that issued query.
Undefined for unix socket.
client_query TEXT Top-level query that caused this auditable event.
May be more than one.
application_name TEXT Client-set session application name when this audit event occurred.
application_user TEXT Client-set session application user when this audit event occurred.
This is useful if the application uses its own user-management and authorization system.
action ENUM Action type
I = insert
D = delete
U = update
T = truncate
row_data JSONB Record value. Null for statement-level trigger.
For INSERT this is null becuase there was nothing there before.
For DELETE and UPDATE it is the old tuple.
changed_fields JSONB New values of fields for INSERT or those changed by UPDATE (i.e a diff).
Null for DELETE.
statement_only BOOLEAN t if audit event is from an FOR EACH STATEMENT trigger
f for FOR EACH ROW

Installation

Requirements:

  • PostgreSQL Server 9.6+ (including developer header files)

To install:

git clone git@github.com:m-martinez/pg-audit-json
cd pg-audit-json
make install

It is highly recommended that you only install this extension using a postgres administrative account and not the account an application will be using to interact the database.

In your postgres shell, activate the extension using:

CREATE EXTENSION "pg-audit-json";

To run the tests (replace PGHOST and PGUSER with your settings):

make installcheck PGHOST=pgserver PGUSER=pguser

Usage

Tracking a database table

To track a user table, use the audit.audit_table function as the OWNER of the audit.log table. Here are a few examples:

-- A simple table
SELECT audit.audit_table('mytable');

-- A schema-qualified table
SELECT audit.audit_table('myschema.mytable');

-- Ignore columns "foo" and "bar"
SELECT audit.audit_table('mytable', true, true, '{foo,bar}');

Setting application runtime variables

This extension allows you to define two optional settings in your application runtime, which can be set as follows:

SET LOCAL audit.application_name = 'my.fancy.app';
SET LOCAL audit.application_user_name = 'jdoe@foo.com';
Setting Description
application_name The name of the application that will trigger audit events
appliation_user_name The effective application user

pg_dump

This extension is configured to allow pg_dump of the audit log data for situations where you would like to keep backups of application data.

Upgrading

If you already have this plugin installed in your system and would like to install any recent updates, do either the following:

-- Use the latest
ALTER EXTENSION "pg-audit-json" UPDATE;

-- Or if you want to be more specific
ALTER EXTENSION "pg-audit-json" UPDATE TO '1.0.1';

Contributing

This project provides and editorconfig to conform to a coding style.

More information about PostgreSQL extensions

Releasing

Remember to update the version tags in the following files:

  • META.json
  • pg-audit-json.control

Credits