Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Check for PostgreSQL Log Retention Period

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Medium (should be achieved)
Rule ID: PostgreSQL-007

Ensure that all your Microsoft Azure PostgreSQL servers have a sufficient log retention period, i.e. greater than 3 days, configured for reliability and compliance purposes. The retention period, represented by the "log_retention_days" parameter, is the number of days to retain log data for the databases hosted on Azure PostgreSQL servers. The compliant value for the retention period is 4 to 7 days (inclusive).

This rule resolution is part of the Conformity Security & Compliance tool for Azure.

Security
Reliability

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 security and performance issues. The logging data can contain query logs – useful to check for sub-optimal performance, and error logs – used to identify anomalies and potential security breaches, misuse of information and unauthorized access to PostgreSQL databases.

Audit

To determine if the log retention period ("log_retention_days" parameter) configured for your PostgreSQL database servers is greater than 3 days, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

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

03 From the Type filter box, select Azure Database for PostgreSQL server to list only the PostgreSQL servers provisioned in your Azure account.

04 Click on the name of the PostgreSQL database server that you want to examine.

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

06 On Server parameters page, find the log_retention_days parameter using the Search to filter items search box. Once the parameter is found, check its configuration value available within the VALUE column. If the parameter value is set to 3 (days) or less, the "log_retention_days" parameter value is not compliant, therefore the selected Azure PostgreSQL database server does not have a sufficient log retention period configured.

07 Repeat steps no. 4 – 6 for each PostgreSQL database server available within the current Azure subscription.

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

Using Azure PowerShell

01 Run postgres server list command (Windows/macOS/Linux) using custom query filters to list the names of all PostgreSQL database servers (and the name of their associated resource groups) available in the current Azure subscription: 

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

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

Name                ResourceGroup
------------------  ------------------------------
cc-staging-server   cloud-shell-storage-westeurope
cc-postgres-server  cloud-shell-storage-westeurope

03 Run postgres server configuration show command (Windows/macOS/Linux) using the name of the Azure PostgreSQL server that you want to examine and its associated resource group as identifier parameters, with custom query filters, to get the "log_retention_days" parameter value for the selected database server: 

az postgres server configuration show
	--server-name "cc-staging-server"
	--resource-group "cloud-shell-storage-westeurope"
	--name log_retention_days
	--query 'value'

04 The command output should return the requested configuration value (i.e. number of days): 

"3"

If postgres server configuration show command output returns 3 (days) or less, as shown in the output example above, the "log_retention_days" parameter value is not compliant, thus the selected Azure PostgreSQL database server does not have a sufficient log retention period currently configured.

05 Repeat step no. 3 and 4 for each Microsoft Azure PostgreSQL server available in the selected subscription.

06 Repeat steps no. 1 – 5 for each subscription available within your Microsoft Azure cloud account.

Remediation / Resolution

To extend the log retention period for your Microsoft Azure PostgreSQL database servers, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at to access all your Microsoft Azure resources.

03 From the Type filter box, select Azure Database for PostgreSQL server to list only the PostgreSQL servers available in your Azure account.

04 Click on the name of the PostgreSQL server that you want to reconfigure (see Audit section part I to identify the right Azure PostgreSQL resource).

05 In the navigation panel, under Settings, select Server parameters to access the configuration parameters for the selected database server.

06 On Server parameters page, find the log_retention_days parameter using the Search to filter items search box.

07 Once the log_retention_days server parameter is found, enter a value between 4 and 7 (inclusive) within the VALUE box. This value represents the number of days to retain log data for the databases available on the selected Azure PostgreSQL server.

08 Click Save to apply the configuration changes.

09 Repeat steps no. 4 – 8 for each PostgreSQL database server provisioned in the selected subscription.

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

Using Azure CLI and PowerShell

01 Run postgres server configuration set command (Windows/macOS/Linux) using the name of the PostgreSQL server that you want to reconfigure as identifier parameter (see Audit section part II to identify the right Azure resource) to reconfigure the "log_retention_days" parameter in order extend the log retention period set for the selected Azure PostgreSQL database server to 7 days: 

az postgres server configuration set
	--server-name "cc-staging-server"
	--resource-group "cloud-shell-storage-westeurope"
	--name log_retention_days
	--value 7

02 The command output should return the metadata for the reconfigured server parameter: 

{
  "allowedValues": "1-7",
  "dataType": "Integer",
  "defaultValue": "3",
  "description": "Sets how many days a log file is saved for.",
  "id": "/subscriptions/abcdabcd-abcd-abcd-abcd-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.DBforPostgreSQL/servers/cc-staging-server/configurations/log_retention_days",
  "name": "log_retention_days",
  "resourceGroup": "cloud-shell-storage-westeurope",
  "source": "user-override",
  "type": "Microsoft.DBforPostgreSQL/servers/configurations",
  "value": "7"
}

03 Repeat step no. 1 and 2 for each PostgreSQL database server available in the selected subscription.

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

References

Publication date Jul 29, 2019

Related PostgreSQL rules