Audit logging in Azure Database for PostgreSQL - Flexible Server

  • Article

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

Audit logging of database activities in Azure Database for PostgreSQL flexible server is available through the PostgreSQL Audit extension: pgAudit. pgAudit provides detailed session and/or object audit logging.

If you want Azure resource-level logs for operations like compute and storage scaling, see the Azure Activity Log.

Usage considerations

By default, pgAudit log statements are emitted along with your regular log statements by using Postgres's standard logging facility. In Azure Database for PostgreSQL flexible server, you can configure all logs to be sent to Azure Monitor Log store for later analytics in Log Analytics. If you enable Azure Monitor resource logging, your logs will be automatically sent (in JSON format) to Azure Storage, Event Hubs, and/or Azure Monitor logs, depending on your choice.

To learn how to set up logging to Azure Storage, Event Hubs, or Azure Monitor logs, visit the resource logs section of the server logs article.

Installing pgAudit

Before you can install pgAudit extension in Azure Database for PostgreSQL flexible server, you need to allow-list pgAudit extension for use.

Using the Azure portal:

  1. Select your Azure Database for PostgreSQL flexible server instance.
  2. On the sidebar, select Server Parameters.
  3. Search for the azure.extensions parameter.
  4. Select pgAudit as extension you wish to allow-list. Screenshot showing Azure Database for PostgreSQL - allow-listing extensions for installation.

Using Azure CLI:

You can allow-list extensions via CLI parameter set command.

az postgres flexible-server parameter set --resource-group <your resource group>  --server-name <your server name> --subscription <your subscription id> --name azure.extensions --value pgAudit

To install pgAudit, you need to include it in the server's shared preload libraries. A change to Postgres's shared_preload_libraries parameter requires a server restart to take effect. You can change parameters using the Azure portal, Azure CLI, or REST API.

Using the Azure portal:

  1. Select your Azure Database for PostgreSQL flexible server instance.

  2. On the sidebar, select Server Parameters.

  3. Search for the shared_preload_libraries parameter.

  4. Select pgaudit. Screenshot showing Azure Database for PostgreSQL flexible server enabling shared_preload_libraries for pgaudit.

  5. You can check that pgaudit is loaded in shared_preload_libraries by executing following query in psql:

    show shared_preload_libraries;

    You should see pgaudit in the query result that will return shared_preload_libraries.

  6. Connect to your server using a client (like psql) and enable the pgAudit extension.

    CREATE EXTENSION pgaudit;

Tip

If you see an error, confirm that you restarted your server after saving shared_preload_libraries.

pgAudit settings

pgAudit allows you to configure session or object audit logging. Session audit logging emits detailed logs of executed statements. Object audit logging is audit scoped to specific relations. You can choose to set up one or both types of logging.

Once you have enabled pgAudit, you can configure its parameters to start logging. To configure pgAudit you can follow below instructions. Using the Azure portal:

  1. Select your Azure Database for PostgreSQL server.
  2. On the sidebar, select Server Parameters.
  3. Search for the pgaudit parameters.
  4. Pick appropriate settings parameter to edit. For example to start logging set pgaudit.log to WRITE Screenshot showing Azure Database for PostgreSQL - configuring logging with pgaudit
  5. Click Save button to save changes

The pgAudit documentation provides the definition of each parameter. Test the parameters first and confirm that you are getting the expected behavior.

Note

Setting pgaudit.log_client to ON will redirect logs to a client process (like psql) instead of being written to file. This setting should generally be left disabled.

pgaudit.log_level is only enabled when pgaudit.log_client is on.

Note

In Azure Database for PostgreSQL flexible server pgaudit.log can't be set using a - (minus) sign shortcut as described in the pgAudit documentation. All required statement classes (READ, WRITE, etc.) should be individually specified.

Note

If you set the log_statement parameter to DDL or ALL, and run a CREATE ROLE/USER ... WITH PASSWORD ... ; or ALTER ROLE/USER ... WITH PASSWORD ... ;, command, then PostgreSQL creates an entry in the PostgreSQL logs, where password is logged in clear text, which may cause a potential security risk. This is expected behavior as per PostgreSQL engine design. You can, however, use PGAudit extension and set pgaudit.log='DDL' parameter in server parameters page, which doesn't record any CREATE/ALTER ROLE statement in Postgres Log, unlike Postgres log_statement='DDL' setting. If you do need to log these statements you can add pgaudit.log ='ROLE' in addition, which, while logging 'CREATE/ALTER ROLE' will redact the password from logs.

Audit log format

Each audit entry is indicated by AUDIT: near the beginning of the log line. The format of the rest of the entry is detailed in the pgAudit documentation.

Getting started

To quickly get started, set pgaudit.log to WRITE, and open your server logs to review the output.

Viewing audit logs

The way you access the logs depends on which endpoint you choose. For Azure Storage, see the logs storage account article. For Event Hubs, see the stream Azure logs article.

For Azure Monitor Logs, logs are sent to the workspace you selected. The Postgres logs use the AzureDiagnostics collection mode, so they can be queried from the AzureDiagnostics table. The fields in the table are described below. Learn more about querying and alerting in the Azure Monitor Logs query overview.

You can use this query to get started. You can configure alerts based on queries.

Search for all pgAudit entries in Postgres logs for a particular server in the last day

AzureDiagnostics
| where Resource =~ "myservername"
| where Category == "PostgreSQLLogs"
| where TimeGenerated > ago(1d)
| where Message contains "AUDIT:"

Next steps