Database cleanup

Many things that are stored in the database should be cleaned up on a regular basis to prevent the database from growing to an unsustainable size. If you are using the default, internal database, this is done automatically. However, if you are using an externally managed database you will need to opt in for our automatic database cleanup.

The automatic cleanup

As stated above, the automatic cleanup is opt-in if you are using an external database. See information on how to configure it below.

The automatic database cleanup that PAS performs does five things:

• Removes legacy events from the EVENT table if they are older than the configured data retention (see configuration below).
• Removes modern events from the AUDIT_EVENTS table if they are older than the configured data retention (see configuration below).
• Removes expired OneID / OneTouch assignments from the ASSIGNMENTS table
• Removes expired authentication tokens from the TOKENS table
• Removes devices from the DEVICES table that no longer have an associated entry in the TOKENS table, and are older than the expired threshold

These table cleanups have associated audit logs so you can track when cleanups happen, and what they deleted in the process. Find more details in the audit log documentation tree: Audit log namespace: Database.

Two ways to configure

There are two ways to configure data retention:

1. New way is by setting auditLogDataRetentionDays and daysBeforeExpiredCleanup.
2. The old way is via the dataretention and expiration grace period parameters.

The do the same thing, but please use the new way.

The queries that are run

To accomplish the cleanup described above, the following queries are run:

• delete from event where date < ?; -- where the parameter is the current date, minus the amount of days set in the dataretention parameter (see below).
• delete from audit_events where date < ?; -- where the parameter is the current date, minus the amount of days set in the dataretention parameter (see below).
• delete from assignments where expires < ?; -- where the parameter is the current date, minus the amount of days set in the expiration grace period parameter (see below).
• delete from tokens where expires < ?; -- where the parameter is the current date, minus the amount of days set in the expiration grace period parameter (see below).
• delete from devices d where created < ? and not exists (select 1 from tokens t where t.device_ref = d.id); -- where the parameter is the current date, minus the amount of days set in the expiration grace period parameter (see below).

How to configure

This is configured in the boot.json file, in the com.phenixidentity~phenix-store-mpl section, at the automaticDbCleanupConfig attribute. See Example below:

{
    "name": "com.phenixidentity~phenix-store-mpl",
    "config": {
        "user": "phenixid",
        "password": "******",
        "encryption.key": "********",
        "export_start": "04:00",
        "driver_class": "com.mysql.cj.jdbc.Driver",
        "automaticDbCleanupConfig": {
            "enabled": "true",
            "enableAuditLogCleanup": "true",
            "auditLogDataRetentionDays": "90",
            "daysBeforeExpiredCleanup": "1"
        },
        "is_server": "false",
        "url": "jdbc:mysql://127.0.0.1:3306/phenixid"
    }
}

The available configuration parameters are:

Name	Description	Default value
enabled	Whether or not to enable automatic database cleaning	false (true if internal database)
enableAuditLogCleanup	Whether or not old audit logs / events should be cleaned up	false (true if internal database)
auditLogDataRetentionDays	How many days audit logs / events should be kept before cleanup	60 (if nothing is configured, tries to read from old dataretention parameter, then defaults to 60.)
daysBeforeExpiredCleanup	How many days expired tokens / assignments should be kept before cleanup	1

Tags

Database Cleanup