Usage

The Audit Service provides database auditing. This is done by two endpoints: one to manage audit rules and one to search audited database changes (deletion, insertion and modification).

Prerequisites for usage

Currently, the Audit Service only works with postgres databases version 12+. For a table to be eligible for auditing, it has to have one unique field named id (preferably a primary key) of type integer not bigger than bigint.

Configuration

The Audit Service is configured by a set of audit rules. Audit administrators may add, modify and delete rules from the currently configured rule set. They may also view the rules currently defined. An audit administrator is a user that has the authority AUDIT_ADMIN. Users are administrated by the eitco user management service.

Audit Rules

An audit rule consists of the following components:

  • tableName

    • the name of the table to audit

  • auditTableName

    • the name of the table to log database changes to - optional, defaults to default_audit_log

  • readAccess

    • specifies read access rights to the audit table (see below) - optional

  • deleteUserIdField

    • specifies that the user ID of a delete operation should be read from a specific field in the updated table (see below) - optional

  • deleteUserIdSetting

    • specifies that the user ID of a delete operation should be read from a local setting (see below) - optional

  • updateUserIdField

    • specifies that the user ID of an update operation should be read from a specific field in the updated table (see below) - optional

  • updateUserIdSetting

    • specifies that the user ID of an update operation should be read from a local setting (see below) - optional

  • indexConfiguration

    • this triple of booleans specifies whether json indexes should be created for the fields diff_old, diff_new and current in the audit table.

  • defaultAuditEventsEnabled

    • enables or disables the default audit events, which are audited by a trigger for insert, update and delete operations on the audited table

If a rule with table name my_table and audit table name my_table_log exists, the Audit Service will assure that every change in the table my_table will result in an entry in my_table_log tracking the changes to this table. The Audit Service will create the audit table automatically if it does not already exist.

Specifying read access

Optionally, a rule may specify which users have read access to the audit table. This is only possible if the access to the audited table is secured by ACLs. ACLs (access control lists) are administrated by the eitco user management access control service.

It is done by two components:

  • aclIdField

    • the name of the field in the audited table that holds the id of the acl that is attached to the database row

  • readAccessRight

    • the numerical value of the right a user must at least have to have read access on a database row

If no read access is specified, only users with the authority AUDITOR may view changes to the tabled audited. If an access rule is specified, only users with at least the readAccessRight specified may read such changes - independent of whether they have the authority AUDITOR or not.

The Audit Service provides a REST API to search for changes of tables being audited.

Supplying the user id for the log entries

There are two ways to get the user id for the log entry: By reading it from a specific field contained in the updated table or by reading a local setting of the current transaction. The administrator who creates the audit rule in use can specify how to read the user ID for each audited table.

When the user id should be read from a field contained in the audited table, the name of that field must be set in the deleteUserIdField property (for delete operations) and/or the updateUserIdField (for all other operations) property of the audit rule. The name of the field must match the regular expression [a-zA-Z][a-zA-Z0-9_]*.

When the user id should be read from a local setting, the name of the setting must be set in the deleteUserIdSetting property (for delete operations) and/or the updateUserIdSetting property (for all other operations) of the audit rule. The name of the setting must match the regular expression [a-zA-Z][a-zA-Z0-9.]*. The application creating the audit rule is responsible of assuring that the configured setting is present in the current transaction. On postgres, such a setting can be set by executing the following statement prior to the update/delete statement:

set local "user_id" = 12345

If deleteUserIdField and deleteUserIdSetting (or updateUserIdField and updateUserIdSetting) are set to null in the audit rule, the system will try to read the user id from a setting called authentication.user.id. There is a Spring Boot Starter that automatically configures the DatabaseAccessJooq utility class to set this setting:

Maven
<dependency>
   <groupId>de.eitco.commons</groupId>
   <artifactId>cmn-user-management-authentication-db-context</artifactId>
   <version></version>
</dependency>
Gradle
compile 'de.eitco.commons:cmn-user-management-authentication-db-context:'

Json and indexes

When logging a change in an audited table, the changes are stored in the fields diff_old, diff_new and current. These fields have the type jsonb. Searches in json fields of big tables can reduce performance. For these cases the audit service is able to create gin indexes for these fields. To configure the audit service to do this, create (or update) the corresponding rule with a corresponding indexConfiguration. This configuration consists of three booleans that specify whether a gin index should be created for each of the fields above. Additionally, the behaviour of the search endpoint will change and generate different queries when querying on equality of elements of this field.

As an example, assume that the table auditee is audited to the table audit_log. An index is configured for the field current. When that was configured the audit service created an index for the field current with the following query:

CREATE INDEX IF NOT EXISTS idx_audit_log_diff_old ON audit_log USING GIN (current)

Also, an eql-query like the following

                    searchService.where()
                        .contextReference("current", "field")
                        .equalTo().value("value")
                        .holds()
                        .count()

will be translated to the following sql condition

select count(*) from audit_log where (current @> json_build_object(field, value))

If the index was not defined, the query would be translated as follows:

select count(*) from audit_log where current->>field = value
A side effect of the first translation is that value can be of any type, while in the first translation the ->> operator will extract the field 'field' as text.

More specific indexes

The approach above works well for queries checking for equality of elements in a json field. However, there are cases where this does not suffice. In those cases it is advisable to create an appropriate index manually. Postgresqls indexes on expressions come handy here

Say for example, we have a table users with a field first_name which is audited to the table user_audit. Say further that searches in user_audit often checks for changes of the first name starting with an arbitrary string (let’s assume this string to be 'ch'). Those queries could look like this in eql:

                    searchService.where()
                        .contextReference("diff_new", "first_name")
                        .like().value("ch*")
                        .holds()
                        .order().ascendingBy("id").from(0).pageSize(100),

These queries generate sql where clauses like this:

where diff_new->>first_name like ch% escape '\' order by "id" asc offset 0 rows fetch next 100 rows only

Even if the gin index on field diff_new was defined, this query would not profit from it. However, a postgres expression index on diff_new->>first_name could improve performance. It is created as follows:

create index json_functional on user_audit ((diff_new ->> 'first_name') varchar_pattern_ops)

Modification

Database modification

Each audited event is represented as a DatabaseModification. It contains the following fields:

  • table: The name of the audited table

  • entityId: The ID of the entity that was modified

  • userId: The ID of the user who caused the event (might be null if user information was not available)

  • timestamp: The time and date of the event in UTC

  • type: The type of the event (INSERT, UPDATE, DELETE, TRUNCATE, CUSTOM)

  • customType: The type of the custom event or null if the event is a default event

  • currentValues: A JSON representation of the current state of the entity

  • diffNew: The new values of the entities properties. Only available for UPDATE events.

  • diffOld: The old values of the entities properties. Only available for UPDATE events.

Custom events

The AuditResourceClient provides a method to send custom audit events to the service. These events will be added to the audit table defined by the audit rule matching the audited table supplied in the custom event. Custom events can be used to audit operations that do not require a change on the database, for example when a download or view of an entity must be audited.

A custom audit event must not use one of the default database modification types defined in the enumeration DatabaseModificationType when the default audit events are enabled for the audited table.