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
  • Aiven Console
  • Aiven CLI client
  • psql for advanced configuration
• Aiven for OpenSearch® service for accessing and visualizing your logs

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.

Aiven Console

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.

Aiven CLI

Use the Aiven CLI client to run the avn service update command. Update your service by setting the pgaudit.feature_enabled parameter's value to true.

avn service update -c pgaudit.feature_enabled=true SERVICE_NAME

important

By default, audit logging does not emit any audit records. To trigger a logging operation and start receiving audit records, configure audit logging parameters as detailed in Configure audit logging.

psql

note

psql allows for fine-grained enablement of audit logging: on a database, for a user's role, or for a database-role combination.

Enable on a database

important

If you use PostgreSQL 14 or earlier, upgrade to PostgreSQL 15 or later to enable pgaudit on your database.

1. Connect to your Aiven for PostgreSQL service.

2. Run the following query:

   SET pgaudit.log='ddl';

Enable for a user's role

important

If you use PostgreSQL 14 or earlier, upgrade to PostgreSQL 15 or later to enable pgaudit for a user's role.

1. Connect to your Aiven for PostgreSQL service.

2. Run the following query:

   ALTER ROLE ROLE_NAME SET pgaudit.log='ddl';

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.

Aiven Console

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.

Aiven CLI

Use the Aiven CLI client to configure audit logging on your service by running the following command:

avn service update -c pgaudit.PARAMETER_NAME=PARAMETER_VALUE SERVICE_NAME

psql

note

psql allows for fine-grained configuration of audit logging: on a database, for a user's role, or for a database-role combination.

Configure on a database

important

If you use PostgreSQL 14 or earlier, upgrade to PostgreSQL 15 or later to configure pgaudit on your database.

1. Connect to your Aiven for PostgreSQL service.

2. Run the following query:

   SET pgaudit.PARAMETER_NAME='all';

Configure for a user's role

important

If you use PostgreSQL 14 or earlier, upgrade to PostgreSQL 15 or later to configure pgaudit for a user's role.

1. Connect to your Aiven for PostgreSQL service.

2. Run the following query:

   ALTER ROLE_NAME SET pgaudit.PARAMETER_NAME=PARAMETER_VALUE;

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

Choose one of the tools or methods for accessing, monitoring, or analyzing your audit logs.

Example: Aiven for OpenSearch®

Aiven Console

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

Aiven CLI

Use the Aiven CLI to create the service integration.

avn service integration-create --project $PG_PROJECT  \
   -t logs                                            \
   -s $PG_SERVICE_NAME                                \
   -d $OS_SERVICE_NAME

After the service integration is set up and propagated to the service configuration, the logs are available in Aiven for OpenSearch. Each log record emitted by audit logging is stored in Aiven for OpenSearch as a single message, which cannot be guaranteed for external integrations such as Remote Syslog.

Aiven API

Call the ServiceIntegrationCreate endpoint passing the following parameters in the request body:

• integration_type: logs
• source_service: the name of an Aiven for PostgreSQL
• destination_service: the name of an Aiven for OpenSearch service

curl --request POST \
   --url https://api.aiven.io/v1/project/{project_name}/integration \
   --header 'Authorization: Bearer REPLACE_WITH_YOUR_BEARER_TOKEN' \
   --header 'content-type: application/json' \
   --data
      '{
         "integration_type": "logs",
         "source_service": "REPLACE_WITH_POSTGRESQL_SERVICE_NAME",
         "destination_service": "REPLACE_WITH_OPENSEARCH_SERVICE_NAME",
      }'

Visualize your logs

Choose one of the tools or methods for visualizing your collected audit logs.

Example: OpenSearch Dashboards

1. Integrate your Aiven for PostgreSQL with Aiven for OpenSearch by following the steps in Access-your-logs.
2. Go to OpenSearch Dashboards.
3. Set up an Index Pattern in OpenSearch Dashboards to match your audit logs index. For guidance, see Create index patterns in OpenSearch Dashboards. The index name for audit logs typically starts with avnlog-pg-.
4. To filter the audit logs:
   1. Go to Discover.
   2. Select your audit logs index pattern.
   3. In the filter tool, set the value of AIVEN_AUDIT_FROM to pg.
   4. Apply the filter.
5. Preview and analyze the logs.

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.

Aiven Console

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.

Aiven CLI

Use the Aiven CLI client to run the avn service update command. Update your service by setting the pgaudit.feature_enabled parameter's value to false.

avn service update -c pgaudit.feature_enabled=false SERVICE_NAME

psql

note

psql allows you to disable audit logging on a few levels: database, user's role, or database-role combination.

Disable on a database

important

If you use PostgreSQL 14 or earlier, upgrade to PostgreSQL 15 or later to disable pgaudit on your database.

1. Connect to your Aiven for PostgreSQL service.

2. Run the following query:

   ALTER DATABASE DATABASE_NAME SET pgaudit.log='none';

Disable for a user's role

important

If you use PostgreSQL 14 or earlier, upgrade to PostgreSQL 15 or later to disable pgaudit for a user's role.

1. Connect to your Aiven for PostgreSQL service.

2. Run the following query:

   ALTER ROLE_NAME SET pgaudit.log='none';