django-simple-trigger-audit 0.0.6
A Django app to keep audit log based on PostgreSql triggers
License: BSD-3-Clause

===========================
django-simple-trigger-audit
===========================

Creates an audit log of the modifications made to models, by using triggers.

* Only PostgreSql supported.
* Models to audit are configured in ``AppConfig``
* Triggers are applied automatically when running ``python manage.py migrate``
* The whole row is saved by the trigger by using PostgreSql's ``row_to_json(NEW)``

Next versions will add support to:

* associate logged in user to audit log (work in progress)
* use table as initial storage (to take advantage of TX and triggers), but move
  data to cloud to keep the table small.


Quick start
-----------

1. Add "simple_trigger_audit" to your INSTALLED_APPS setting like this::

    INSTALLED_APPS = [
        ...
        'simple_trigger_audit',

        'your.app1.App1Config',
        'your.app2.App2Config',
        'your.app3.App3Config',
    ]

2. Register the models you want to audit in your ``AppConfig`` instances::

    from django.apps import AppConfig
    class MyAppConfig(AppConfig):
        name = "myapp"
        trigger_audit_models = (
            'Model1',
            'Model2',
            'Model3',
        )

3. Run ``python manage.py migrate`` to create the triggers.

4. Optional: configure middleware to store the logged in in user in audit entries ::

    MIDDLEWARE = [
        ...
        "simple_trigger_audit.middleware.TriggerAuditMiddleware",
    ]


5. Optional: use the Django Admin to list and search ``TriggerAuditEntry``::

    from simple_trigger_audit.admin import TriggerAuditEntryAdmin
    from simple_trigger_audit.models import TriggerAuditEntry

    admin.register(TriggerAuditEntry, TriggerAuditEntryAdmin)

Security of audit entries
+++++++++++++++++++++++++

DBA should correctly configure permissions, in a way that the user used to
connect to the database from Django has permissions to INSERT rows in the
``trigger_audit_entries`` table, but NO permissions to UPDATE / DELETE them.

If security is not important and you just want to avoid deleting the audit
entries by accident, a solution could be
https://docs.djangoproject.com/en/3.1/topics/db/multi-db/#automatic-database-routing


How it works
------------

#. A trigger is executed with each insert/update/delete operation

   * the database trigger is created by ``python manage.py migrate``.
   * only for the tables associated to the models that are explicitly
     specified in your ``AppConfig``.
   * a pre-save and pre-delete signal is used to track which user is associated
     with each request / transaction: ``set_config()``` is used to set a config
     associated to the current transaction, and that value can be read from
     the trigger.

#. The trigger creates a new row in ``trigger_audit_entries`` table containing:

   * ``object_table``: table where the modification happened (one of your models)
   * ``object_pk``: primary key of your model
   * ``object_payload``: JSON representation of the whole row (after modification)
   * ``audit_entry_created``: timestamp
   * ``audit_txid_current``: PostgreSql TXID in which the modification occurred
     (not used at the moment, but will be used soon to associate the Django user
     that caused that modification)
   * ``audit_operation``: operation: ``INSERT``, ``UPDATE``, ``DELETE``
   * ``audit_request_info``: stores information about user that originated
     the audit entry.


Trigger
+++++++

The solution is very simple in terms of code running in PostgreSQL: just a trigger
that calls a function.

The trigger just invokes the function for each ``INSERT``, ``UPDATE``, ``DELETE`` ::

    CREATE TRIGGER trigger_audit_entry_creator_trigger
        AFTER INSERT OR UPDATE OR DELETE ON {table_name}
        FOR EACH ROW EXECUTE FUNCTION trigger_audit_entry_creator_func();

The function just serializes the row into a JSON and insert it in the audit table::

    CREATE FUNCTION trigger_audit_entry_creator_func_v1() RETURNS TRIGGER AS $scr$
        DECLARE
            object_pk       integer;
            object_payload  varchar;
            request_info    text;
        BEGIN
            request_info := current_setting('django_simple_trigger.request_info', TRUE);

            IF (TG_OP = 'INSERT') THEN
                object_pk       = NEW.id;
                object_payload  = row_to_json(NEW);
            ELSIF (TG_OP = 'UPDATE') THEN
                object_pk       = OLD.id;
                object_payload  = row_to_json(NEW);
            ELSIF (TG_OP = 'DELETE') THEN
                object_pk       = OLD.id;
                object_payload  = row_to_json(OLD);
            ELSE
                RAISE EXCEPTION 'Unexpected TG_OP = %', TG_OP;
            END IF;

            INSERT INTO trigger_audit_entries_v1 (
                    object_table,
                    object_pk,
                    object_payload,
                    audit_entry_created,
                    audit_txid_current,
                    audit_operation,
                    audit_version,
                    audit_request_info
                )
                SELECT
                    TG_TABLE_NAME,
                    object_pk,
                    object_payload,
                    now(),
                    txid_current(),
                    TG_OP,
                    1,
                    request_info;
            RETURN NULL;
        END;
    $scr$ LANGUAGE plpgsql;

Test
----

Tested on:

* Python 3.7, 3.8, 3.9
* Django 2.2, 3.0, 3.1
* PostgreSql 11, 12, 13 (it doesn't work with PostgreSql 10)

Results of ``make tox``::

    py37-django22-postgres11: commands succeeded
    py37-django22-postgres12: commands succeeded
    py37-django22-postgres13: commands succeeded
    py37-django30-postgres11: commands succeeded
    py37-django30-postgres12: commands succeeded
    py37-django30-postgres13: commands succeeded
    py37-django31-postgres11: commands succeeded
    py37-django31-postgres12: commands succeeded
    py37-django31-postgres13: commands succeeded
    py38-django22-postgres11: commands succeeded
    py38-django22-postgres12: commands succeeded
    py38-django22-postgres13: commands succeeded
    py38-django30-postgres11: commands succeeded
    py38-django30-postgres12: commands succeeded
    py38-django30-postgres13: commands succeeded
    py38-django31-postgres11: commands succeeded
    py38-django31-postgres12: commands succeeded
    py38-django31-postgres13: commands succeeded
    py39-django22-postgres11: commands succeeded
    py39-django22-postgres12: commands succeeded
    py39-django22-postgres13: commands succeeded
    py39-django30-postgres11: commands succeeded
    py39-django30-postgres12: commands succeeded
    py39-django30-postgres13: commands succeeded
    py39-django31-postgres11: commands succeeded
    py39-django31-postgres12: commands succeeded
    py39-django31-postgres13: commands succeeded
    congratulations :)