Cyan Audit is a PostgreSQL utility providing comprehensive and easily-searchable logs of DML (INSERT/UPDATE/DELETE) activity in your database.
This is a fork of CyanAudit to work with PostgreSQL 12.0 and above. Tested with 17.0.
With Cyan Audit you can:
- Log any table with a PK, regardless of schema.
- Search logs by querying a simple view.
- Toggle logging on a column-by-column basis using an easy config table.
- Attribute every operation to a specific application user.
- Label any operation with a human-readable description.
- Back up and restore logs with confidence using supplied Perl scripts.
- Rotate & drop old logs automatically using a supplied Perl script.
- Keep years of logs online comfortably with automatic archival to your cheap tablespace.
- Effectively "undo" any recorded transaction by playing its operations in reverse.
- Save time with a design focused on ease of setup and maintenance.
Cyan Audit:
- is written entirely in SQL and PL/pgSQL (except Perl cron scripts).
- is Trigger-based.
- supports PostgreSQL 9.6 and newer.
- has been production tested since 2012.
Here is the doc/cyanaudit.md:
Cyan Audit provides an easy-to-use SQL-searchable log of who changed your data and when. It is a stable, powerful and mature DML logging extension for PostgreSQL 9.6+.
Cyan Audit is written entirely in pl/pgsql and is trigger-based, so it does not require admin privileges at the cluster or system levels.
Install Cyan Audit:
$ Execute sql statemens from `cyanaudit.sql` on your server.
Turn on logging for schemas public
and app_schema
:
app_db# select cyanaudit.fn_update_audit_fields('public');
app_db# select cyanaudit.fn_update_audit_fields('app_schema');
Search the logs by querying the view vw_audit_log
:
app_db=# select recorded, txid, table_name, column_name, pk_vals, op, old_value, new_value
app_db-# from cyanaudit.vw_audit_log
app_db-# where recorded > now() - interval '5 min' -- less than 5 min ago
app_db-# and pk_vals[1] = '7' -- the modified row's pk value is '7'
app_db-# and table_name = 'employees'
app_db-# and column_name = 'family_name';
recorded | txid | table_name | column_name | pk_vals | op | old_value | new_value
---------------------------+---------+------------+-------------+---------+----+-----------+-----------
2016-02-06 16:07:32.63177 | 1901268 | employees | family_name | {7} | U | Riley | Chase
(1 row)
The audit log view looks like this:
| Column | Description
|-------------|---------------------------------------------------------------
| recorded | clock_timestamp of each logged operation.
| uid | UID of application user, set with SELECT fn_set_current_uid(uid).
| user_email | Derived from uid (see Final Configuration Steps below).
| txid | Indexed for easy lookup.
| table_name | Affected table (schema-qualified if not in search_path)
| column_name | Column whose values are given in old_value and new_value.
| pk_vals[] | affected row's pk values (after update) cast as text.
| op | operation ('I', 'U', or 'D')
| old_value | NULL on 'I'. Never NULL on 'D'. IS DISTINCT FROM new_value.
| new_value | NULL on 'D'. Never NULL on 'I'. IS DISTINCT FROM old_value.
With \pset format wrapped
, these columns fit nicely across a ~200 col display
Disable logging for a particularly noisy column:
UPDATE cyanaudit.tb_audit_field
SET enabled = false
WHERE table_schema = 'app_schema'
AND table_name = 'customers'
AND column_name = 'last_modified';
Disable logging for the current session:
SET cyanaudit.enabled = 0;
Play back the inverse of the DML logged in the last transaction:
SELECT cyanaudit.fn_undo_transaction( cyanaudit.fn_get_last_txid() );
Shorthand for above:
SELECT cyanaudit.fn_undo_last_transaction();
Set uid value to 42 for all subsequent activity in the current session:
SELECT cyanaudit.fn_set_current_uid( 42 );
Set the description for all future DML in this transaction:
SELECT cyanaudit.fn_set_transaction_label( 'Change last name' );
Set the description for all past, unlabled DML in this transaction:
SELECT cyanaudit.fn_label_transaction( 'User disabled due to inactivity' );
Set the label for all unlabled DML in this session's last logged transaction:
SELECT cyanaudit.fn_label_last_transaction( 'User enabled' );
Un-set uid and last txid, turn logging back on:
DISCARD ALL;
Tell Cyan Audit how to populate vw_audit_log.user_email
based on logged uids:
UPDATE cyanaudit.tb_config SET value = 'users' where name = 'user_table'
UPDATE cyanaudit.tb_config SET value = 'user' where name = 'user_table_uid_col'
UPDATE cyanaudit.tb_config SET value = 'email_address' where name = 'user_table_email_col''
Enable setting uid automatically when current_user
matches users.username
:
UPDATE cyanaudit.tb_config SET value = 'username' WHERE name = 'user_table_username_col';
Set the tablespace to which rotated logs will be moved:
ALTER DATABASE mydb SET cyanaudit.archive_tablespace = 'big_n_slow';
Cause all sessions to reload settings by forcing reconnect (optional):
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
AND datname = current_database();
Re-scan for schema changes in all tracked schemas:
SELECT cyanaudit.fn_update_audit_fields();
Set up :logwhere
alias by customizing this line & adding to your .psqlrc
:
\set logwhere 'select recorded, uid, user_email, txid, description, table_schema, table_name, column_name, pk_vals, op, old_value, new_value from cyanaudit.vw_audit_log where'
Use the :logwhere
alias (previous step) to see all activity from the last 5 minutes:
app_db# :logwhere recorded > now() - interval '5 min';
Cyan Audit's logs are divided (sharded) into partitions, which are created every
time you run cyanaudit_log_rotate.pl
. If you ran it at 2016-01-10 09:00, it
would create a new partition called cyanaudit.tb_audit_event_20160110_0900
.
Cron to rotate logs weekly, dropping archive tables over 10 in quantity, over 20GB in size, and over 30 days in age:
0 0 * * 0 /usr/pgsql-X.X/bin/cyanaudit_log_rotate.pl -U postgres -d app_db -n 10 -s 20 -a 30
Cron to back up logs nightly (skips tables already having current backup):
5 0 * * * /usr/pgsql-X.X/bin/cyanaudit_dump.pl -U postgres -d app_db /mnt/backups/cyanaudit/app_db/
Restore a couple backup files to an existing Cyan Audit installation:
# /usr/pgsql/9.3/bin/cyanaudit_restore.pl -U postgres -d app_db \
/mnt/backups/cyanaudit/app_db/tb_audit_event_20180101_1200.gz \
/mnt/backups/cyanaudit/app_db/tb_audit_event_20180102_1200.gz
If you wish to reinstall Cyan Audit without dropping it, simply re-run the install script and it will automatically re-install the same version you currently have installed.
Cyan Audit lives entirely in the cyanaudit schema, and can be dropped as follows:
psql> DROP SCHEMA cyanaudit CASCADE
Cyan Audit's scripts can be removed as follows:
Sometimes a completely fresh install might be needed (please report the bug if so). In this case, it will involve a little bit of downtime, but you can back up all of your logs, remove, reinstall and restore the logs, as follows:
- Rotate logs using
cyanaudit_log_rotate.pl
- Back up all logs using
cyanaudit_dump.pl
- Shut down database access (e.g. by turning off pgbouncer)
- Run
cyanaudit_dump.pl
again to catch the last bit of logs - Install new cyanaudit scripts by executing the sql statemens from
cyanaudit.sql
- Create backup of
cyanaudit.tb_audit_field
(for the enabled values) andcyanaudit.tb_config
for the new installation:
CREATE TABLE public.tb_audit_field_backup AS SELECT * FROM cyanaudit.tb_audit_field;
CREATE TABLE public.tb_cyanaudit_config_backup AS SELECT * FROM cyanaudit.tb_config;
DROP SCHEMA cyanaudit CASCADE;
- Execute sql statemens from
cyanaudit.sql
select fn_update_audit_fields('public')
# (Also run this for any other schema being logged)- Restore the configs from your backups of tb_audit_field and tb_config:
UPDATE cyanaudit.tb_audit_field af SET enabled = afb.enabled FROM public.tb_audit_field_backup afb WHERE afb.table_schema = af.table_schema AND afb.table_name = af.table_name AND afb.column_name = af.column_name; UPDATE cyanaudit.tb_config c SET value = ccb.value FROM public.tb_cyanaudit_config_backup ccb WHERE ccb.name = c.name;
- Re-enable database access (e.g. restart pgbouncer)
- TEST THE SYSTEM. Log in. See if things are being logged.
- Restore old logs using
cyanaudit_restore.pl
, remembering to restore only as much logs as are normally kept on the server (not the whole history!)
-
When using with pgbouncer or other connnection poolers, you must use session-level pooling (not statement-level or transaction-level) for
fn_set_current_uid()
andfn_label_last_txid()
to have any effect. Additionally, you must have the pooler issue aDISCARD ALL
command to reset the persistent server connection after a client disconnects. -
The first time you run
fn_update_audit_fields()
, it will hold an exclusive lock on all of your tables until the function returns. On a test database with about 3500 columns, this took 12 seconds. Please make sure you run this at a time when it is acceptable for your tables to be locked for up to a minute. -
When Cyan Audit finds a new column (e.g. during
fn_update_audit_fields()
), it will decide the default value forenabled
as follows:If any column on same table is enabled, then true. Else If we know of fields on this table but all are inactive, then false. Else If we know of no fields in this table, then: If any field in same schema is enabled, then true. Else If we know of fields in this schema but all are inactive, then false. Else true
-
When querying
vw_audit_log
, being as specific as possible about yourtable_name
andcolumn_name
will greatly speed up search results.
-
Cyan Audit is released under the PostgreSQL license. Please see the accompanying LICENSE file for more details.
-
Cyan Audit is written and maintained by Moshe Jacobson -- moshe@neadwerx.com
-
Development sponsored by Nead Werx, Inc. -- http://www.neadwerx.com