Check Log Files Retention Period for PostgreSQL Flexible Servers

Risk Level: Medium (should be achieved)

For compliance and auditing purposes, ensure that your Azure PostgreSQL flexible database servers have a sufficient retention period configured for log files, i.e. greater than 3 days. The retention period, represented by the "logfiles.retention_days" parameter, is the number of days to retain log files for the databases hosted on Azure PostgreSQL flexible servers. The compliant value for the retention period is 4 to 7 days (inclusive).

Security

A retention period of 4 days or more should allow you to collect the necessary amount of logging data required to identify and mitigate any PostgreSQL database security and performance issues. The logging data can contain query logs - useful for checking sub-optimal performance, and error logs - used to identify anomalies, potential security breaches, and unauthorized access to PostgreSQL flexible servers.

Audit

To determine the log files retention period configured for your Azure PostgreSQL flexible database servers, perform the following operations:

Using Azure Console

01 Sign in to the Microsoft Azure Portal.

02 Navigate to All resources blade available at https://portal.azure.com/#browse/all to access all your Azure cloud resources.

03 Choose the Azure subscription that you want to access from the Subscription equals all filter box and choose Apply.

04 From the Type equals all filter box, choose Equals, select Azure Database for PostgreSQL - Flexible Server, and choose Apply to list only the Azure PostgreSQL flexible servers available in the selected subscription.

05 Click on the name (link) of the PostgreSQL flexible server that you want to examine.

06 In the resource navigation panel, under Settings, select Server parameters to access the configuration parameters available for the selected PostgreSQL server.

07 On the Server parameters page, select the All tab, and find the logfiles.retention_days configuration parameter using the Search to filter items search box. Check the logfiles.retention_days parameter value, listed in the Value column to determine the number of days to retain log files for the selected server. If the parameter value is set to 3 (days) or less, the logfiles.retention_days parameter value is not compliant. Therefore, the selected Azure PostgreSQL flexible database server does not have a sufficient retention period configured for generated log files.

08 Repeat steps no. 5 - 7 for each PostgreSQL flexible server deployed within the selected Azure subscription.

09 Repeat steps no. 3 – 8 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI

01 Run account list command (Windows/macOS/Linux) with custom output filters to list the IDs of the cloud subscriptions available in your Azure cloud account:

az account list
	--query '[*].id'

02 The command output should return the requested subscription identifiers (IDs):

[
	"abcdabcd-1234-abcd-1234-abcdabcdabcd",
	"abcd1234-abcd-1234-abcd-abcd1234abcd"
]

03 Run account set command (Windows/macOS/Linux) with the ID of the Azure cloud subscription that you want to examine as the identifier parameter to set the selected subscription to be the current active subscription (the command does not produce an output):

az account set
	--subscription abcdabcd-1234-abcd-1234-abcdabcdabcd

04 Run postgres flexible-server list command (Windows/macOS/Linux) with custom output filters to list the names of all PostgreSQL flexible servers (and the name of their associated resource groups) available in the selected Azure subscription:

az postgres flexible-server list
	--output table
	--query '[*].{name:name, resourceGroup:resourceGroup}'

05 The command output should return a table with requested PostgreSQL server information:

Name                     ResourceGroup
-----------------------  ------------------------------
cc-postgres-flexible-db  cloud-shell-storage-westeurope
cc-project5-flexible-db  cloud-shell-storage-westeurope

06 Run postgres flexible-server parameter show command (Windows/macOS/Linux) with the name of the Azure PostgreSQL flexible server that you want to examine and its associated resource group as the identifier parameters, to describe the logfiles.retention_days parameter value in order to determine the number of days to retain log files for the selected PostgreSQL database server:

az postgres flexible-server parameter show
	--server-name "cc-postgres-flexible-db"
	--resource-group "cloud-shell-storage-westeurope"
	--name logfiles.retention_days
	--query 'value'

07 The command output should return the requested parameter value (i.e., the number of days to retain log files):

"1"

If the postgres flexible-server parameter show command output returns 3 (days) or less, as shown in the example above, the logfiles.retention_days parameter value is not compliant. As a result, the selected Azure PostgreSQL flexible database server does not have a sufficient retention period configured for generated log files.

08 Repeat steps no. 6 and 7 for each PostgreSQL flexible server provisioned in the selected Azure subscription.

09 Repeat steps no. 3 – 8 for each subscription available within your Microsoft Azure cloud account.

Remediation / Resolution

To extend the log files retention period for your Azure PostgreSQL flexible database servers, perform the following operations:

Using Azure Console

01 Sign in to the Microsoft Azure Portal.

02 Navigate to All resources blade available at https://portal.azure.com/#browse/all to access all your Microsoft Azure cloud resources.

03 Choose the Azure subscription that you want to access from the Subscription equals all filter box and choose Apply.

05 Click on the name (link) of the PostgreSQL flexible server that you want to configure.

06 In the resource navigation panel, under Settings, select Server parameters to access the configuration parameters available for the selected PostgreSQL server.

07 On the Server parameters page, perform the following actions:

Select the All tab and find the logfiles.retention_days configuration parameter using the Search to filter items search box.
Once the logfiles.retention_days server parameter is found, enter a value between 4 and 7 (inclusive) in the Value box. This value represents the number of days to retain log files data for all the databases available on the selected Azure PostgreSQL flexible database server.
Choose Save to apply the configuration changes.

08 Repeat steps no. 5 - 7 for each PostgreSQL flexible server deployed in the selected Azure subscription.

09 Repeat steps no. 3 – 8 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI

01 Run postgres flexible-server parameter set command (Windows/macOS/Linux) with the name of the PostgreSQL flexible server that you want to configure as the identifier parameter, to modify the logfiles.retention_days parameter value in order extend the log files retention period for the selected PostgreSQL database server to 7 days:

az postgres flexible-server parameter set
	--server-name "cc-postgres-flexible-db"
	--resource-group "cloud-shell-storage-westeurope"
	--name logfiles.retention_days
	--value 7

02 The command output should return the information available for the configured server parameter:

{
	"allowedValues": "1-7",
	"dataType": "Integer",
	"defaultValue": "3",
	"description": "Sets the retention period window in days for server logs - after this time data will be deleted.",
	"id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.DBforPostgreSQL/flexibleServers/cc-postgres-flexible-db/configurations/logfiles.retention_days",
	"isConfigPendingRestart": false,
	"isDynamicConfig": true,
	"isReadOnly": false,
	"name": "logfiles.retention_days",
	"resourceGroup": "cloud-shell-storage-westeurope",
	"source": "user-override",
	"systemData": null,
	"type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
	"unit": null,
	"value": "7"
}

03 Repeat steps no. 1 and 2 for each PostgreSQL flexible server provisioned in the selected Azure subscription.

04 Repeat steps no. 1 – 3 for each subscription created within your Microsoft Azure cloud account.

References

Azure Official Documentation
What is Azure Database for PostgreSQL - Flexible Server?
List all server parameters
Security Control: Logging and threat detection

Azure Command Line Interface (CLI) Documentation
az postgres flexible-server list
az postgres flexible-server parameter show
az postgres flexible-server parameter set

Publication date Feb 28, 2025

Audit

Using Azure Console

Using Azure CLI

Remediation / Resolution

Using Azure Console

Using Azure CLI

References

Related Sql rules