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
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
- Aiven CLI
- psql
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.
- Log in to Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.
- On the Overview page of your service, select Service settings from the sidebar.
- On the Service settings page, go to the Advanced configuration section and select Configure.
- In the Advanced configuration window, select Add configuration options, add
the
pgaudit.feature_enabled
parameter, set it totrue
, and select Save configuration.
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
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 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
If you use PostgreSQL 14 or earlier, upgrade to
PostgreSQL 15 or later to enable pgaudit
on your database.
-
Run the following query:
SET pgaudit.log='ddl';
Enable for a user's role
If you use PostgreSQL 14 or earlier, upgrade to
PostgreSQL 15 or later to enable pgaudit
for a user's role.
-
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.
- 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
- Aiven CLI
- psql
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.
- Log in to Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.
- On the Overview page of your service, select Service settings from the sidebar.
- On the Service settings page, go to the Advanced configuration section and select Configure.
- 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.
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 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
If you use PostgreSQL 14 or earlier, upgrade to
PostgreSQL 15 or later to configure pgaudit
on your database.
-
Run the following query:
SET pgaudit.PARAMETER_NAME='all';
Configure for a user's role
If you use PostgreSQL 14 or earlier, upgrade to
PostgreSQL 15 or later to configure pgaudit
for a user's role.
-
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.
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';
ALTER DATABASE defaultdb SET pgaudit.log='read,ddl';
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
- Log in to the Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.
- On the Overview page of your service, select Logs from the sidebar.
Access in integrated service
- Aiven Console
- Aiven CLI
- Aiven API
Access your Aiven for PostgreSQL logs by enabling OpenSearch log integration.
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.
Call the ServiceIntegrationCreate endpoint passing the following parameters in the request body:
integration_type
:logs
source_service
: the name of an Aiven for PostgreSQLdestination_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
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.

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
- Aiven CLI
- psql
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.
- Log in to Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.
- On the Overview page of your service, select Service settings from the sidebar.
- On the Service settings page, go to the Advanced configuration section and select Configure.
- In the Advanced configuration window, select Add configuration options, add the
pgaudit.feature_enabled
parameter, set it tofalse
, and select Save configuration.
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 allows you to disable audit logging on a few levels: database, user's role, or database-role combination.
Disable on a database
If you use PostgreSQL 14 or earlier, upgrade to
PostgreSQL 15 or later to disable pgaudit
on your database.
-
Run the following query:
ALTER DATABASE DATABASE_NAME SET pgaudit.log='none';
Disable for a user's role
If you use PostgreSQL 14 or earlier, upgrade to
PostgreSQL 15 or later to disable pgaudit
for a user's role.
-
Run the following query:
ALTER ROLE_NAME SET pgaudit.log='none';