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

Enable "LOG_DURATION" Parameter for PostgreSQL Servers

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-005

Ensure that "log_duration" server parameter is enabled for all PostgreSQL database servers created in your Microsoft Azure cloud account. Once enabled, the "log_duration" parameter allows recording the duration of each completed PostgreSQL statement. Only users with administrative privileges can change this setting within Azure PostgreSQL server configuration. For database clients using extended query protocol, the duration of the "Parse", "Bind", and "Execute" steps is logged independently.

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

Security

Once "log_duration" parameter is enabled, your PostgreSQL servers can log the duration of each completed SQL statement, which in turn generates query and error logs if any issues are detected. Database query and error logs can be used to identify, troubleshoot, and repair configuration errors and sub-optimal performance for your Azure PostgreSQL database servers.

Audit

To determine if "log_duration" parameter is enabled for your Azure PostgreSQL servers, 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 available 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_duration parameter using the Search to filter items search box. Once this parameter is found, check its configuration value, available in the VALUE column. If the parameter value is set to OFF, the "log_duration" server parameter is not enabled for the selected Azure PostgreSQL database server.

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 account 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-prod-db-server  cloud-shell-storage-westeurope
cc-psql-db-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 expose the "log_duration" parameter value for the selected database server: 

az postgres server configuration show
	--server-name "cc-prod-db-server"
	--resource-group "cloud-shell-storage-westeurope"
	--name log_duration
	--query 'value'

04 The command output should return the requested configuration value ("ON" for enabled, "OFF" for disabled): 

"OFF"

If postgres server configuration show command output returns "OFF", as shown in the output example above, the "log_duration" server parameter is not enabled for the selected Azure PostgreSQL database server.

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

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

Remediation / Resolution

To enable "log_duration" server parameter for all PostgreSQL database servers available within your Microsoft Azure cloud account, 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 the PostgreSQL servers currently 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 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_duration parameter using the Search to filter items search box.

07 Once log_duration server parameter is found, enable this parameter by selecting ON from the toggle button available in the VALUE column.

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 enable "log_duration" parameter for the selected Microsoft Azure PostgreSQL database server: 

az postgres server configuration set
	--server-name "cc-prod-db-server"
	--resource-group "cloud-shell-storage-westeurope"
	--name log_duration
	--value on

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

{
  "allowedValues": "on,off",
  "dataType": "Boolean",
  "defaultValue": "off",
  "description": "Logs the duration of each completed SQL statement.",
  "id": "/subscriptions/abcdabcd-abcd-abcd-abcd-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.DBforPostgreSQL/servers/cc-prod-db-server/configurations/log_duration",
  "name": "log_duration",
  "resourceGroup": "cloud-shell-storage-westeurope",
  "source": "user-override",
  "type": "Microsoft.DBforPostgreSQL/servers/configurations",
  "value": "on"
}

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