Skip to content

Ensure the PostgreSQL Audit Extension (pgAudit) is enabled

Description

The PostgreSQL Audit Extension (pgAudit) provides detailed session and/or object audit logging via the standard PostgreSQL logging facility. The goal of pgAudit is to provide PostgreSQL users with the capability to produce audit logs often required to comply with government, financial, or ISO certifications.

Rationale

Basic statement logging can be provided by the standard logging facility with log_statement = all. This is acceptable for monitoring and other uses but does not provide the level of detail generally required for an audit. It is not enough to have a list of all the operations performed against the database, it must also be possible to find particular statements that are of interest to an auditor. The standard logging facility shows what the user requested, while pgAudit focuses on the details of what happened while the database was satisfying the request.

When logging SELECT and DML statements, pgAudit can be configured to log a separate entry for each relation referenced in a statement. No parsing is required to find all statements that touch a particular table. In fact, the goal is that the statement text is provided primarily for deep forensics and should not be required for an audit.

Applies To

  • Databases

Tags

This rule is applied when the following tags are present:

Tag With Value
secureclouddb/provider aws
secureclouddb/service rds
secureclouddb/engine postgres

Default Rule

const { getServerSetting } = module

/**
 * @param {Object} databaseSettings - database settings object
 * @returns {boolean} true if the pgAudit extension is installed and enabled
 */
function validate(databaseSettings) {
    const expectedExtension = 'pgaudit'
    const expectedStatus = 'installed'

    // check if the extension is installed
    const isInstalled =
        databaseSettings.serverExtensions &&
        databaseSettings.serverExtensions.extensions &&
        databaseSettings.serverExtensions.extensions[expectedExtension] &&
        databaseSettings.serverExtensions.extensions[expectedExtension].status.toLowerCase() === expectedStatus

    // check if the extension is enabled
    const settingSharedPreloadLibraries = 'shared_preload_libraries'
    const settingPgauditLog = 'pgaudit.log'
    const separator = ','
    const sharedPreloadLibraries = getServerSetting(databaseSettings, settingSharedPreloadLibraries)
    const sharedPreloadLibrariesArray = sharedPreloadLibraries && sharedPreloadLibraries.split(separator)

    const isEnabled = sharedPreloadLibrariesArray &&
        sharedPreloadLibrariesArray.includes(expectedExtension) &&
        !!getServerSetting(databaseSettings, settingPgauditLog)

    const success = isInstalled && isEnabled
    return {
        success,
    }
}

// invoke
validate(databaseSettings);