A simple, customizable table audit system for PostgreSQL implemented using triggers. This repo has the following changes:
- Merged with /pull/32 to use json instead of hstore
- Added script for use with PostGIS to convert GeoJSON back into WKB
PostGIS 3.0 now automatically converts geometry into GeoJSON. We did not want that, so we created a script that automatically converts GeoJSON back into WKB.
- Install the auditing schema with the following command:
psql -h <host> -p <port> -U <user> -d <db> -f audit.sql --single-transaction- If auditing tables with PostGIS (3.0+) geometry, run the following command:
psql -h <host> -p <port> -U <user> -d <db> -f audit-postgis.sql --single-transaction- Run
SELECT audit.audit_table('schema.table')to begin auditing a table
Note: If inserts are taking too long, you can try to run the
audit.trim_table()function to remove any audit logs older than one year
The audit.audit_table function allows the user to pass various options to customize the behavior:
| argument | type | default | |
|---|---|---|---|
target_table |
regclass | Table name, schema qualified if not on search_path | |
audit_rows |
bool | True | Record each row change, or only audit at a statement level |
audit_query_text |
bool | True | Record the text of the client query that triggered the audit event? |
ignored_cols |
text[] | [] | Columns to exclude from update diffs, ignore updates that change only ignored cols. |
If using Postgres 9.5 or a similar older version, I have modified the audit script to work with that. You can find it on the 9.5 branch.