Skip to main content

Collect audit logs in Aiven for PostgreSQL®

Enable and configure the Aiven for PostgreSQL® audit logging feature on your service. Access and visualize your logs to monitor activities on your databases.

Prerequisites

  • PostgreSQL version 11 or higher
  • avnadmin superuser role
  • Dev tool of your choice to interact with the feature

Enable audit logging

Enable audit logging by setting the pgaudit.feature_enabled parameter to true in your service's advanced configuration. Using the Aiven console, CLI, or psql.

important

In Aiven Console, you can enable audit logging at the service level only. To enable it on a database or for a user's role, use psql.

  1. Log in to Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.
  2. On the Overview page of your service, select Service settings from the sidebar.
  3. On the Service settings page, go to the Advanced configuration section and select Configure.
  4. In the Advanced configuration window, select Add configuration options, add the pgaudit.feature_enabled parameter, set it to true, and select Save configuration.

Configure audit logging

Configure audit logging by setting its parameters in the Aiven Console, with the Aiven CLI, or using psql.

important
  • Advanced configuration of the audit logging feature requires using psql.
  • Any configuration changes take effect only on new connections.

For information on all the audit logging configuration parameters, refer to Settings.

important

In the Aiven Console, you can enable audit logging on a service only. To enable it on a database or for a user's role, use psql.

  1. Log in to Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.
  2. On the Overview page of your service, select Service settings from the sidebar.
  3. On the Service settings page, go to the Advanced configuration section and select Configure.
  4. In the Advanced configuration window, select Add configuration options, find a desired parameter (all prefixed with pgaudit.log), set its value as needed, and select Save configuration.

Configure session audit logging

Session audit logging allows recording detailed logs of all SQL statements and commands executed during a database session in the system's backend.

important

If you use PostgreSQL 14 or earlier, upgrade to PostgreSQL 15 or later to use the session audit logging.

To enable the session audit logging, run the following query:

ALTER DATABASE DATABASE_NAME SET pgaudit.log='ddl';
Example
ALTER DATABASE defaultdb SET pgaudit.log='read,ddl';
See also

For more details on how to set up, configure, and use session audit logging, check Session audit logging.

Access your logs

You can access your Aiven for PostgreSQL audit logs either directly in the log output of your service or by integrating with another service that allows monitoring and analyzing logs, such as Aiven for OpenSearch®.

Access in Aiven for PostgreSQL

  1. Log in to the Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.
  2. On the Overview page of your service, select Logs from the sidebar.

Access in integrated service

Access your Aiven for PostgreSQL logs by enabling OpenSearch log integration.

Visualize your logs

If your logs are available in Aiven for OpenSearch, use OpenSearch Dashboards to visualize the logs.

To preview your audit logs in OpenSearch Dashboards, use the filtering tool: select AIVEN_AUDIT_FROM, set its value to pg, and apply the filter.

note

If the index pattern in OpenSearch Dashboards had been configured before you enabled the service integration, the audit-specific AIVEN_AUDIT_FROM field is not available for filtering. Refresh the fields list for the index in OpenSearch Dashboards under Stack Management > Index Patterns > Your index pattern > Refresh field list.

Disable audit logging

Disable audit logging by setting the pgaudit.feature_enabled parameter to false in your service's advanced configuration. Use the Aiven console, CLI, or psql.

important

In the Aiven Console, you can disable audit logging on a service only. To disable it on a database or for a user's role, use psql.

  1. Log in to Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.
  2. On the Overview page of your service, select Service settings from the sidebar.
  3. On the Service settings page, go to the Advanced configuration section and select Configure.
  4. In the Advanced configuration window, select Add configuration options, add the pgaudit.feature_enabled parameter, set it to false, and select Save configuration.