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.
| 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 |
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
> 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:
> make installcheck
To track a user table, use the audit.audit_table function as the ONWER of the
audit.log table. Here are a few exapmles:
> -- 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}");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 be trigger audit events |
appliation_user_name |
The effective applicaiton user |
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
Remember to update the version tags in the following files:
- META.json
- pg-audit-json.control