sql-audit

Tracking changes is the whole goal here. When someone inserts, updates, or deletes a record, those changes should be traced and attributable for the user. This serves the dual purposes of auditing who makes changes and providing a way to revert them.

Limitations

1. Only Postgres is currently supported, only version 10.12 is tested.
2. To make it easy to track changes against a specific record, the primary key of that record is recorded separately from the rest of the data. Currently, this requires that all audited tables have a primary key column called pk which is type integer. There are plans to make this more flexible.
3. Your audit will only be as safe as the permissions on your database! Make sure only a secure audit account has access to the audit schema/table/trigger. The trigger will execute with the permissions of the user that creates it.
4. Only tables in the public schema of the connected database will be audited.

How To

1. cargo install sql-audit-cli

2. Create an audit.toml with content like this:

   database = "postgres://username:password@host/database"
   exclude_tables = ["test_2"] # Tables to not audit
   

   DO NOT CHECK THIS FILE IN as it obviously contains database credentials of the secure audit account. Future versions should make this config easier (e.g. prompting for credentials).

3. Run sql-audit from the command line in the directory containing this file.

How it Works

1. A new schema called sql_audit will be created in the database with the table audit.
2. A function will be created called sql_audit_trigger.
3. All tables in the public schema of the connected database will be audited except those listed in the exclude_tables list in audit.toml.
4. All "audited" tables (defined above) will get a TRIGGER on INSERT, UPDATE, and DELETE. Those triggers insert data into the audit table created for each row with the following columns:

   1. id: a surrogate key

   2. ts: the time of the change

   3. table_name: the table that the change took place on

   4. pk: the pk of the row which the trigger ran on

   5. operation: "INSERT", "UPDATE", "DELETE" depending on the action that triggered the trigger.

   6. db_user: The database user which made the change

   7. app_user: An optional string for storing data about a logged in user (to the app) that made the change. More details in features section.

   8. request_id: An optional arbitrary string representing a specific web request. More details in features.

   9. new_val: A JSON representation of the entire new row (only populated on insert or update).

   10. new_val: A JSON representation of the entire old row (only populated on update or delete).

Features

Store app_user

This setting only lasts for the duration of a transaction.

The point of app_user is to be able to track which users make changes at the application level instead of the database level. Often, an application will use a single set of database credentials regardless of which user is making a web request. Recording the application-level user is therefore often more useful than the database user alone. However, this can be set by whoever is making the query, so only trust this value as much as you trust the db_user.

Using this is purely at the discretion of the consuming application. Set it by calling sql_audit::set_local_app_user from Rust or using SELECT set_config('sql_audit.app_user', $1, true) where $1 is whatever you want to store here. Using a param and binding is strongly recommended since the user name / email is usually some sort of user-provided input. You don't want to run arbitrary SQL with the permissions of the audit user!

Store request_id

This setting only lasts for the duration of a transaction.

This field is here for tracing purposes. When a user makes a web request, you will often log some unique request ID for tracking bugs/changes/whatever. You can also store that unique ID here to track database changes back to a specific web request (or the other way around).

Using this is purely at the discretion of the consuming application. Set it by calling sql_audit::set_local_app_user from Rust or using SELECT set_config('sql_audit.request_id', $1, true) where $1 is whatever you want to store here.