Native PostgreSQL Audit Trail

Last modified 02 Dec 2021 13:42 +01:00
Since 4.4
This functionality is available since version 4.4.
This page documents the audit trail based on the new Native PostgreSQL Repository introduced in midPoint 4.4. For the old Generic SQL audit trail see this page.

Configuration overview

SQL auditing is configured in config.xml inside midPoint home directory, also known as midpoint.home.

See Auditing for more general information about auditing and some other configuration options, not specific for the SQL trail implementation.

SQL audit trail for the Native Repository is enabled in audit configuration element by adding auditService element containing auditServiceFactoryClass element with the value com.evolveum.midpoint.repo.sqale.audit.SqaleAuditServiceFactory.

For example, the audit section may look like this (the first auditService is not related to the repository):

<audit>
    <auditService>
        <auditServiceFactoryClass>com.evolveum.midpoint.audit.impl.LoggerAuditServiceFactory</auditServiceFactoryClass>
    </auditService>
    <auditService>
        <auditServiceFactoryClass>com.evolveum.midpoint.repo.sqale.audit.SqaleAuditServiceFactory</auditServiceFactoryClass>
    <auditService>
</audit>

Without further configuration, audit uses the same datasource as the main repository (in the repository element). The audit tables (see below) must be part of the repository database in that case.

To enable separate database connection settings, use jdbcUrl in the auditService element. See the options in the following sections for more details.

Configuration options

Many repository options can be used for the SQL audit trail - these are mentioned in the table below. Some of them are relevant only if separate data source with its own connection pool is used for the SQL audit. Few options do not depend on the database connection and can be tweaked separately - these are named in the second table below.

All the options for the concrete audit trail implementation (or audit service) are placed directly under auditService element.

Basic connection options

You may skip this table, if you want to use the same database settings and the same connection pool for both main repository and the SQL audit - this is perfectly acceptable solution for most deployments.

Option Description Default

jdbcUrl

URL for JDBC connection. When this (or non-recommended dataSource) is present, separate connection pool will be initialized for the SQL audit. See Connecting to the Database from PostgreSQL JDBC Driver documentation for more.

jdbc:postgresql://localhost:5432/midpoint

jdbcUsername

Username for JDBC connection. Can be empty, if username is not needed or provided in JDBC URL or otherwise.

Example: midaudit

jdbcPassword

Password for JDBC connection. Can be empty, if password is not needed or provided in JDBC URL or otherwise.

Example: password

database

Ignored by the Native repository and cannot be changed - do not use it.

postgresql

driverClassName

Ignored by the Native repository and cannot be changed - do not use it.

org.postgresql.Driver

dataSource

Uses JNDI DataSource loading, when this option is defined in configuration. This is only relevant for WAR deployment which is not recommended anyway. jdbcUrl, jdbcUsername, jdbcPassword, and driverClassName is ignored and should not be used. Example: <dataSource>java:comp/env/jdbc/midaudit</dataSource>

WARNING: This is obsolete functionality that is no longer supported or maintained. It is relevant only for WAR deployments on Tomcat and even there we recommend using explicit configuration using options above.

It is possible to connect to the database without specifying password or username or both. Simply skip configuration elements jdbcUsername and jdbcPassword. If everything is configured as expected, connection will be successful, otherwise JDBC driver will throw an exception and midPoint will not start.

There are more connection pool settings, see their description in the repository configuration document. Again, these are only relevant if separate connection setup is used. You may try to lower minPoolSize and monitor the usage of the connections to adjust maxPoolSize later too.

If the same database server is used for the repository and SQL audit, it is important to count the maxPoolSize for audit to the total possible count of used connections and avoid going over PostgreSQL max_connections setting.

Other SQL audit configuration options

Default values of these options are taken from repository element, and only if not specified there, the value in the Default column is used.

Option Description Default

fullObjectFormat

Property specifies format (language) used to store serialized object representation into ma_audit_delta table (delta and fullResult columns). Supported values are json and xml. This is safe to change any time, objects are read properly regardless of the format they are stored in.

json

iterativeSearchByPagingBatchSize

The size of the "page" for iterative search, that is the maximum number of results returned by a single iteration. This is a rather internal setting and the default value is reasonable balance between query overhead and time to process the results.

It can be raised if the iterative search overhead (executing the select) is too high compared to the time used for processing the page results.

100

createMissingCustomColumns

Specifies whether midPoint should create missing columns for custom properties during the startup.

false

There are no options for compression as this is left to PostgreSQL. This also makes the inspection of the values in the columns easier.

Audit tables

Audit logs are stored in the tables structured as shown below. You can find current schema script for audit in git, or in the distribution packages in the file doc/config/sql/native-new/postgres-new-audit.sql.

native tables audit
Figure 1. Native PostgreSQL audit tables

ma_audit_event is the main table representing each audit event. See Audit record structure for detailed logical descritiption of the events.

  • Each record can have multiple deltas associated with it, these are stored in ma_audit_delta.

  • References are stored in ma_audit_ref table, multiple references for the same recordId can be stored, even under the same name. Conceptually, this stores multimap (name → references) for each record.

  • Audit event changedItem and resourceOid are stored as arrays of TEXT directly in ma_audit_event table.

  • Item property, which is a multimap (name → strings), is stored in JSONB column directly in ma_audit_event table.

  • Custom properties (customColumnProperty) are stored each in dedicated column of TEXT type. See section Custom columns below for details.

Upgrading

Partitioning

Custom column

EXPERIMENTAL

This feature is experimental. It means that it is not intended for production use. The feature is not finished. It is not stable. The implementation may contain bugs, the configuration may change at any moment without any warning and it may not work at all. Use at your own risk. This feature is not covered by midPoint support. In case that you are interested in supporting development of this feature, please consider purchasing midPoint Platform subscription.

It would be more flexible to have an extension container in the audit event record. Current implementation requires DB changes for each custom property and allows only a single string as value for each property.

When we need some other information in the audit table, we can add custom columns to the table in the database. We can use new column to tag some special audit records for reporting.

Custom columns is configured in three steps:

  1. Creating the TEXT columns in ma_audit_event table in the database using SQL command.

  2. Adding the configuration for each custom column to config.xml file in the midPoint home directory.

  3. Adding the configuration how the audit event record property is created to the system configuration object.

1. Creating the column in ma_audit_event:
ALTER TABLE ma_audit_event ADD custFoo TEXT;

CREATE INDEX ma_audit_event_custFoo_idx ON ma_audit_event (custFoo);

If the search by the property is required (which is likely), the index is needed. In order to avoid conflicting column names, it is recommended to name the columns as 'custXXX' where `XXX `represents the name of the custom property.

2. Adding the configuration in config.xml file:
<configuration>
    <midpoint>
        ...
        <audit>
            <auditService>
                <auditServiceFactoryClass>com.evolveum.midpoint.audit.impl.LoggerAuditServiceFactory</auditServiceFactoryClass>
            </auditService>
            <auditService>
                <auditServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlAuditServiceFactory</auditServiceFactoryClass>
                <customColumn>
                    <columnName>custFoo</columnName>
                    <eventRecordPropertyName>foo</eventRecordPropertyName>
                </customColumn>
            </auditService>
        </audit>
        ...
    </midpoint>
</configuration>

Multiple customColumn elements can be added. After adding of this configuration is needed restart of midpoint.

While not recommended, it is possible to let midPoint create the missing custom columns - if the connection allows it. Just add the following line inside the auditService element:

<createMissingCustomColumns>true</createMissingCustomColumns>

This creates the missing column, but it does not create the index. It can be useful in experimental environments, but definitely not recommended for production ones, including UAT, etc.

3. Setting up the custom property initialization - modify the system configuration object:
<systemConfiguration>
    ...
    <audit>
        <eventRecording>
            <property>
                <name>foo</name>
                <expression>
                    <path>$target/extension/foo</path>
                </expression>
            </property>
        </eventRecording>
    </audit>
    ...
</systemConfiguration>

We can use a script in expression for obtaining some special information. In the script we can use variable target, which represent target object of audit record, and auditRecord, which represent audit record itself (type AuditEventRecord).

An example with custom column is available in the samples.

Audit migration from other database

Configuration examples

Both examples also configure the audit service for writing the audit trail to a log file (factory class LoggerAuditServiceFactory).

Basic SQL audit setup

This setup uses the same setup and set of connections as the main repository. The audit tables (prefixed ma_) must be present in the same database as the main repository.

config.xml
<configuration>
    <midpoint>
        ...
        <repository>
            <type>sqale</type>
            <database>postgresql</database>
            <jdbcUrl>jdbc:postgresql://192.168.56.33:5432/midpoint</jdbcUrl>
            <jdbcUsername>midpoint</jdbcUsername>
            <jdbcPassword>password</jdbcPassword>
        </repository>
        <audit>
            <auditService>
                <auditServiceFactoryClass>com.evolveum.midpoint.audit.impl.LoggerAuditServiceFactory</auditServiceFactoryClass>
            </auditService>
            <auditService>
                <auditServiceFactoryClass>com.evolveum.midpoint.repo.sqale.audit.SqaleAuditServiceFactory</auditServiceFactoryClass>
            </auditService>
        </audit>
...

SQL audit using its own database

Here we use different database for the audit by specifying jdbcUrl and other related options. Couple of notes to the example below:

  • You can use the same JDBC URL, username and password to use the same database, but with separate connection pool. This probably does not make much sense, adjusting connection pool in the repository is more flexible, but it is possible.

  • Example below uses the same database server, which is probably not ideal, if you decide for this scenario. Separate databases allow for some flexibility, but separate database servers are better.

  • Example below shows maxPoolSize and fullObjectFormat overrides. Normally these values are taken from the main repository section - and if not present there, from the defaults.

    • These defaults for the main repository may be unnecessarily generous for the audit connection pool, although the default settings do release unused physical connections.

    • fullObjectFormat is shown only for demonstration purposes, do not change it unless you have specific needs.

config.xml
<configuration>
    <midpoint>
        ...
        <repository>
            <type>sqale</type>
            <database>postgresql</database>
            <jdbcUrl>jdbc:postgresql://192.168.56.33:5432/midpoint</jdbcUrl>
            <jdbcUsername>midpoint</jdbcUsername>
            <jdbcPassword>password</jdbcPassword>
        </repository>
        <audit>
            <auditService>
                <auditServiceFactoryClass>com.evolveum.midpoint.audit.impl.LoggerAuditServiceFactory</auditServiceFactoryClass>
            </auditService>
            <auditService>
                <auditServiceFactoryClass>com.evolveum.midpoint.repo.sqale.audit.SqaleAuditServiceFactory</auditServiceFactoryClass>
                <jdbcUrl>jdbc:postgresql://192.168.56.33:5432/midaudit?ApplicationName=audit</jdbcUrl>
                <jdbcUsername>midaudit</jdbcUsername>
                <jdbcPassword>password</jdbcPassword>
                <maxPoolSize>4</maxPoolSize>
                <fullObjectFormat>xml</fullObjectFormat>
            </auditService>
        </audit>
...