Audit Manager v8

Suggest edits

You can use the PEM Audit Manager to simplify audit log configuration for EDB Postgres Advanced Server instances. With Audit Manager, you can configure logging attributes such as:

  • How often PEM collects log files
  • The type of database activities that are included in the log files
  • How often to rotate log files and when

Audit logs can include the following activities:

  • All connections made to the database instance
  • Failed connection attempts
  • Disconnections from the database instance
  • All queries (SELECT statements)
  • All DML statements (INSERT, UPDATE, DELETE)
  • All DDL statements (for example, CREATE, DROP, ALTER)

Once the audit logs are stored on the PEM server, you can use the Audit Log dashboard to review the information in an easy-to-read form. The Audit Log dashboard allows you to filter the log file by:

  • Timestamp range (when an activity occurred)
  • The database on which the activity occurred
  • The user performing the activity
  • The type of command being invoked

Setting the EDB Postgres Advanced Server instance service ID

To configure logging for an EDB Postgres Advanced Server instance, the server must be a PEM-managed server with a bound agent, and the server registration must include the name of a service script. When registering a new server, include the service name in the Service ID field on the Advanced tab of the New Server dialog box.

Before adding a service name to an existing, registered and connected server, you must disconnect the server:

  1. Right-click the server name, and select Disconnect server from the context menu.
  2. Right-click the server name and select Properties from the context menu.
  3. Select the Advanced tab, and add a service name to the Service ID field.

The Service ID field allows the PEM server to stop and start the service.

  • The name of the Advanced Server 12 service script is edb-as-12.
  • The name of the Advanced Server 11 service script is edb-as-11.
  • The name of the Advanced Server 10 service script is edb-as-10.

Setting the EDB Audit Configuration probe

Before configuring audit logging of EDB Postgres Advanced Server servers, you must ensure that the EDB Audit Configuration probe is enabled. To open the Manage Probes tab and check the status of the probe, right-click the name of a registered EDB Postgres Advanced Server server in the tree, and select Management > Manage Probes.

Ensure that the Enabled column in the Probe Configuration dialog box is set to Yes for the EDB Audit Configuration probe. If EDB Audit Configuration isn't enabled, use the Enabled? switch on the Manage Probes tab to enable it.

Configuring audit logging with the Audit Manager

  1. To open the Audit Manager wizard, select Management > Audit Manager. The Audit Manager - Welcome dialog box opens. Select Next to continue.

  2. Use the Select Servers tree to specify the servers to which to apply the auditing configuration. To make a server available in the tree, you must provide the Service ID on the Advanced tab of the Create Server dialog box when registering a server for monitoring by PEM. Only EDB Postgres Advanced Server supports auditing. PostgreSQL servers don't appear in the tree.

    Select Next to continue.

  3. The Auditing Parameters Configuration dialog box lets you enable or disable auditing and choose how often log records are collected into PEM. Use the Auditing Parameters Configuration dialog box to specify auditing preferences:

    • Use the Auditing switch to enable or disable auditing on the specified servers.

    • Use the Audit destination list to select a destination for the audit logs. Select File or Syslog. This feature is supported only on EDB Postgres Advanced Server 10 and later releases.

    • Use the Import logs to PEM switch to periodically import log records from each server to the PEM Server. Set the switch to Yes to import log files. The default is No.

    • Use the Collection frequency list to specify how often PEM collects log records from monitored servers when log collection is enabled.

    • Use the Log format list to select the raw log format to write on each server. If log collection is enabled, the PEM server uses CSV format.

    • Use the File name field to specify the format used when generating log file names. By default, the format is set to audit-%Y-%m-%d_%H%M%S where:

      audit is the file name specified in the Audit Directory Name field
      Y is the year that the log was stored
      m is the month that the log was stored
      d is the day that the log was stored
      H is the hour that the log was stored
      M is the minute that the log was stored
      S is the second that the log was stored

    • Select Change Log Directory for selected servers? and use the Audit Directory Name field to specify a directory name to which to write the audit logs. The directory resides beneath the data directory on the PEM server.

    • Use the Log directory box to specify information about the directory in which the log files are saved:

      • Move the Change log directory for selected servers? switch to Yes to enable the Directory name field.

      • Use the Directory name field to specify the name of the directory on each server to write audit logs to. The directory specified is created as a subdirectory of the data directory on the server.

    Select Next to continue.

  4. The Audit log configuration dialog box is available only if you enabled auditing on the Auditing Parameters Configuration dialog box. Use the Audit Log Configuration dialog box to specify log configuration details to apply to each server:

    • Use the Connection attempts switch to specify whether to log connection attempts. Specify:

      None to disable connection logging

      All to indicate that all connection attempts are logged

      Failed to log any connection attempts that fail

    • Use the Disconnection attempts switch to specify whether to log disconnections. Specify:

      None if you don't want to log disconnections

      All to enable disconnection logging

    • Use the Log statements field to specify the statement types that are logged. Select from:

      Select — Log all statements that include the SELECT keyword.

      Error — Log all statements that result in an error.

      DML — Log all DML (data modification anguage) statements.

      DDL — Log all DDL (data definition language) statements, that is, those that add, delete or alter data.

      Select Select All to select all statement types.

      Select Unselect All to clear all statement types.

    • Use the Audit tag field to specify a tracking tag for the collected logs. Audit tagging functionality is available only for supported versions of EDB Postgres Advanced Server.

    • Use the Log rotation box to specify how to manage the log files on each server:

      Use the Enable? switch to rotate log files. Use a new log file periodically to prevent a single file from becoming too large.

      Use the Day list to select days on which to rotate the log file.

      Use the Size (MB) field to specify a size in megabytes at which to rotate the log file.

      Use the Time (seconds) field to specify the number of seconds between log file rotations.

    Select Next to continue:

  5. Use the Schedule Auditing Changes dialog box to determine when auditing configuration changes take effect.

    • Select Configure logging now? if you want the auditing configuration changes to take place immediately. The affected database servers restart so the auditing changes can take effect.
    • Use the Time? selector to schedule the auditing configuration changes to take place at some point in the future. Select the desired date and time from the lists. The affected database servers restart at the specified date and time to put the auditing changes into effect.

    Select Finish to complete the auditing configuration process.

Audit Manager schedules a job to apply the configuration to each server. The job consists of two tasks: one to update the audit logging configuration on the server and one to restart the server with the new configuration.

You can use the Scheduled Tasks tab to review a list of scheduled jobs. To open the Scheduled Tasks tab, select the name of a server or agent and select Management > Scheduled Tasks.

Viewing the log with the Audit Log dashboard

Use the Audit Log dashboard to view the audit log from EDB Postgres Advanced Server database instances.

To open the Audit Log dashboard, right-click a server or agent node and select Audit Log Analysis from the Dashboards menu. The Audit Log dashboard displays the audit records in reverse chronological order.

The Audit Log dashboard

You can use filtering to limit the number of audit records that are displayed. Select Show Filters to expose the filters panel. Use the filters panel to provide selection criteria for the audit records you want to display.

  • Use the Start field to specify a start date for the report.
  • Use the End field to specify an end date for the report.
  • Use the User field to display only those entries where the activity was started by the given Postgres user.
  • Use the Database field to display only those entries where the activity was issued on the given database.
  • Use the Command type field to display only those entries where the activity was of the given type. Command types you can specify are idle, authentication, and SELECT. (For viewing SQL statements from user applications, specify the idle command type.)

Select Filter to apply the filtering criteria to the log entries.

← Prev

Capacity Manager

↑ Up

Monitoring performance

Next →

Log Manager