Audit logging in Azure Database for PostgreSQL - Single Server
Audit logging of database activities in Azure Database for PostgreSQL - Single Server is available through the PostgreSQL Audit Extension: pgAudit. pgAudit provides detailed session and/or object audit logging.
pgAudit is in preview on Azure Database for PostgreSQL. The extension can be enabled on General Purpose and Memory Optimized servers only.
If you want Azure resource-level logs for operations like compute and storage scaling, see the Azure Activity Log.
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, these .log files can be downloaded through the Azure portal or the CLI. The maximum storage for the collection of files is 1 GB, and each file is available for a maximum of seven days (the default is three days). This service is a short-term storage option.
Alternatively, 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.
Enabling pgAudit generates a large volume of logging on a server, which has an impact on performance and log storage. We recommend that you use Azure Monitor Logs, which offers longer-term storage options, as well as analysis and alerting features. We recommend that you turn off standard logging to reduce the performance impact of additional logging:
- Set the parameter
- Restart the server to apply this change.
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.
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:
Select your Azure Database for PostgreSQL server.
On the sidebar, select Server Parameters.
Search for the
Restart the server to apply the change.
Connect to your server using a client (like psql) and enable the pgAudit extension
CREATE EXTENSION pgaudit;
If you see an error, confirm that you restarted your server after saving
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.
pgAudit settings are specified globally and cannot be specified at a database or role level.
Once you have installed pgAudit, you can configure its parameters to start logging. The pgAudit documentation provides the definition of each parameter. Test the parameters first and confirm that you are getting the expected behavior.
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.
In Azure Database for PostgreSQL,
pgaudit.log cannot be set using a
- (minus) sign shortcut as described in the pgAudit documentation. All required statement classes (READ, WRITE etc) should be individually specified.
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.
If you need any other fields to satisfy your audit requirements, use the Postgres parameter
log_line_prefix is a string that is output at the beginning of every Postgres log line. For example, the following
log_line_prefix setting provides timestamp, username, database name, and process ID:
t=%m u=%u db=%d pid=[%p]:
To learn more about
log_line_prefix, visit the PostgreSQL documentation.
To quickly get started, set
WRITE, and open your logs to review the output.
Viewing audit logs
If you are using Azure resource logging, 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 Postgres logs for a particular server in the last day
AzureDiagnostics | where LogicalServerName_s == "myservername" | where TimeGenerated > ago(1d) | where Message contains "AUDIT:"
- Learn about logging in Azure Database for PostgreSQL
- Learn how to set parameters using the Azure portal, Azure CLI, or REST API.