Audit trails

From a medico-legal point of view it is important to track who changed what data when. GNUMed uses database level facilities to achieve that goal. Advantages are:

  • SQL query parsing is left to the database engine
  • the backend provides uniform auditing across any variety of clients
  • auditing data is moved local to the original data which provides for reasonable speed

Drawbacks are:

  • audit trail data cannot be transferred remotely right away in a particularly convenient way
  • this audit scheme is not foolproof against manipulation


On INSERT the committer (CURRENT_USER) and the current timestamp is added to the table data. Row versioning is initialized to 0. No data is transferred to the corresponding audit trail table.


The original row version, row values, committer, timestamp and table OID are logged in the corresponding audit trail table. The current timestamp, committer and action (DELETE or UPDATE) are added. For updates, the row version in the original table is incremented and the timestamp and committer are updated to the current values.


Currently SELECTs are not audited/logged. While some legislation may require this it does not make sense technically, will slow down the database considerably and is better left to middleware or the frontend. The main argument against SELECT logging at the database level is that the intended purpose of it would be to enable later investigation of unauthorized disclosure. However, a logged SELECT in no way proves any disclosure to any specific person. A missing SELECT does not contribute any useful evidence against disclosure either. Unauthorized disclosure is largely a social-control/policy issue. There's no such thing as "unauthorized access". If access occurred it was (technically) authorized (barring any software bugs). Such security breaches must be prevented by other means.

How to add an audit trail to a table

Any table that is supposed to be audited must be marked for auditing by calling the plpgsql function:

add_table_for_audit (table_name)

Most tables in the standard Gnumed schema are marked for auditing.

During bootstrapping audit trail tables are auto-generated for all the marked tables. It is also possible to manually create audit trail tables in which case they need not contain all the columns of the respective audited table (but they must not have any extra columns). In case of manually setting up audit trail tables make sure they inherit from the root table "audit_trail". The table that is audited manually must inherit from "audit_fields".

During bootstrapping all marked tables that have a corresponding audit trail table are prepared for auditing. The necessary functions and triggers are automatically generated and inserted into the database. The audit trail tables will have the name of the original audited table prefixed with "log_".

Please read the source in server/bootstrap/ and server/bootstrap/boostrap*.py to learn how to manually create audit trails.

Audit trail handling

Over time the audit trail tables may grow quite large. They may be dumped and cleaned out regularly. The dump file should be notarized digitally and backed up. Preferably an unsigned online copy of the audit trail tables should be moved to another server. It is useful to have the complete revision history online for reference but this is not speed-critical.

  • audit inheritance at gnumed-devel here
  • overview of auditing at gnumed-devel here
    • not quite correct anymore
  • audit "limbo" records discussed here

--- This topic: Gnumed > WebHome > DevelopmentMain > DevelopmentReference > DevelRefMisc > AuditTrails
Topic revision: 11 Feb 2011, SebastianHilbert
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback