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_fieldsinstead ofrow_datato 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 = deleteU = updateT = 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 [email protected]:m-martinez/pg-audit-json
cd pg-audit-json
make installIt 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=pguserUsage
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 = '[email protected]';| 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
- https://www.postgresql.org/docs/current/static/extend-pgxs.html
- https://www.postgresql.org/docs/current/static/extend-extensions.html
- http://manager.pgxn.org/howto
Releasing
Remember to update the version tags in the following files:
- META.json
- pg-audit-json.control