Check for PostgreSQL Major Version

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

Risk level: Low (generally tolerable level of risk)
Rule ID: PostgreSQL-009

Ensure that your Microsoft Azure PostgreSQL database servers are using the latest major version of PostgreSQL database in order to receive new or enhanced features and the most recent security fixes.

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

Security
Reliability
Performance
efficiency

The PostgreSQL open-source project regularly issues new releases that can be available to Microsoft Azure cloud customers. By upgrading your Azure PostgreSQL database servers with the new version of PostgreSQL, you will introduce new software features, fix reported bugs through security patches, and provide better compatibility with other applications and services. For example, PostgreSQL 11 represents a major version that includes a variety of improvements associated with partitioning, parallelism, stored procedures, covering indexes and other important performance enhancements.


Audit

To determine if your Azure PostgreSQL servers are using the latest major version of PostgreSQL database, perform the following actions:

Using Azure Portal

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 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, choose Properties to access the selected PostgreSQL server properties.

06 On the Properties page, check the PostgreSQL version attribute value. Compare the configured version with each major version supported by the PostgreSQL engine for the Azure Database for PostgreSQL service. If the PostgreSQL version value is lower than the latest major version supported by the PostgreSQL engine within Microsoft Azure cloud, the selected database server is not using the latest major version of the PostgreSQL database.

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

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

Using Azure CLI

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-project5-db-server   cloud-shell-storage-westeurope
cc-internal-db-server   cloud-shell-storage-westeurope

03 Run postgres server show command (Windows/macOS/Linux) using the name of the Azure PostgreSQL server that you want to examine as identifier parameter and custom query filters to describe the PostgreSQL engine major version configured for the selected database server:

az postgres server show
	--name cc-project5-db-server
	--resource-group cloud-shell-storage-westeurope
	--query 'version'

04 The command output should return the requested PostgreSQL engine version:

"9.6"

Compare the version returned by the postgres server show command output with each major version supported by the PostgreSQL engine for the Azure Database for PostgreSQL service. If the verified version is lower than the latest major version supported by the PostgreSQL engine within Microsoft Azure cloud, the selected database server is not using the latest major version of the PostgreSQL database.

05 Repeat step no. 3 and 4 for each 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

Automated upgrade to a major PostgreSQL engine version is not currently supported. For example, there is not an automatic upgrade from PostgreSQL 9.5 to PostgreSQL version 9.6. To upgrade your Azure PostgreSQL server to the latest major version of the PostgreSQL database, you have to re-create the existing server, export a database dump, and restore it to the PostgreSQL server with the new engine version. To implement the rule remediation process, perform the following actions:

Using Azure Portal

01 Sign in to Azure Management Console.

02 Navigate to Azure Monitor blade at https://portal.azure.com/#blade/Microsoft_Azure_Monitoring/AzureMonitoringBrowseBlade/overview.

03 Identify the PostgreSQL server that you want to re-create (see Audit section part I – Azure Console) and collect the configuration information available for the selected resource.

04 Click on the Add + button from the dashboard top menu to initiate the PostgreSQL server setup process:

  1. On the Select Azure Database for PostgreSQL deployment option page, choose Single server plan and click Create to launch a new single PostgreSQL database server.
  2. On the Basics panel, perform the following actions:
    • Provide a unique name for the database server within Server name box.
    • Select the latest major version of the PostgreSQL engine from the Version dropdown list.
    • Configure the rest of the settings available on the page based on the information taken from the PostgreSQL server (i.e. source server) identified at step no. 3.
    • Click Next : Tags > to continue the setup process.
  3. On the Tags panel, set any required tags, then click Next : Review + create > to continue.
  4. On the Review + create panel, review the server configuration, then click Create to launch the new PostgreSQL database server.

05 Once the new Azure PostgreSQL database server is provisioned, migrate your PostgreSQL database from the source server to the destination (new) server using the PostgreSQL dump and restore operations.

06 Now you can remove the source server in order to stop adding charges to your Azure bill. Click on the database server that you want to delete (see Audit section part I – Azure Console) and select the Delete button from the Overview blade top menu to start the removal. On the confirmation panel, type the name of the database server, then click Delete to remove the selected PostgreSQL resource from your Azure account.

07 Repeat steps no. 3 – 6 for each PostgreSQL database server available in the selected subscription.

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

Using Azure CLI

01 Run postgres server show command (Windows/macOS/Linux) using the name of the Azure PostgreSQL server that you want to re-create as identifier parameter (see Audit section part II to identify the right Azure resource) to describe the configuration information available for the selected database server:

az postgres server show
	--name cc-project5-db-server
	--resource-group cloud-shell-storage-westeurope

02 The command output should return the requested metadata. This information will be required for configuring the new PostgreSQL database server:

{
  "earliestRestoreDate": "2020-03-28T11:00:00.597000+00:00",
  "fullyQualifiedDomainName": "cc-project5-db-server.postgres.database.azure.com",,
  "name": "cc-project5-db-server",
  "replicaCapacity": 5,
  "resourceGroup": "cloud-shell-storage-westeurope",
  "sku": {
    "capacity": 2,
    "family": "Gen5",
    "name": "GP_Gen5_2",
    "size": null,
    "tier": "GeneralPurpose"
  },

  ...

  "sslEnforcement": "Enabled",
  "storageProfile": {
    "backupRetentionDays": 7,
    "geoRedundantBackup": "Disabled",
    "storageAutogrow": "Enabled",
    "storageMb": 20480
  },
  "type": "Microsoft.DBforPostgreSQL/servers",
  "userVisibleState": "Ready",
  "version": "9.6"
}

03 Run postgres server create command (Windows/macOS/Linux) using the source server configuration information returned at the previous step as command parameter data, to create a new Azure PostgreSQL database server, configured with the latest major version of the PostgreSQL engine. To identify the latest major version supported by the Azure Database for PostgreSQL service, check this Azure documentation page:

az postgres server create
	--name cc-project5-pg11-server
	--resource-group cloud-shell-storage-westeurope
	--sku-name GP_Gen5_2
	--storage-size 20480
	--location eastus
	--version 11
	--admin-user <cc-pg-admin-user>
	--admin-password <cc-pg-admin-password>

04 The command output should return the metadata for the new Azure PostgreSQL database server:

{
  "administratorLogin": "",
  "earliestRestoreDate": "2020-03-29T11:49:11.340000+00:00",
  "fullyQualifiedDomainName": "cc-project5-pg11-server.postgres.database.azure.com",
  "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcd1234abcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.DBforPostgreSQL/servers/cc-project5-pg11-server",
  "location": "eastus",
  "masterServerId": "",
  "name": "cc-project5-pg11-server",
  "replicaCapacity": 5,
  "replicationRole": "None",
  "resourceGroup": "cloud-shell-storage-westeurope",
  "sku": {
    "capacity": 2,
    "family": "Gen5",
    "name": "GP_Gen5_2",
    "size": null,
    "tier": "GeneralPurpose"
  },
  "sslEnforcement": "Enabled",
  "storageProfile": {
    "backupRetentionDays": 7,
    "geoRedundantBackup": "Enabled",
    "storageAutogrow": "Enabled",
    "storageMb": 20480
  },
  "tags": null,
  "type": "Microsoft.DBforPostgreSQL/servers",
  "userVisibleState": "Ready",
  "version": "11"
}

05 Once the new Azure PostgreSQL database server is successfully deployed, migrate your PostgreSQL database from the source server to the destination (new) server using the PostgreSQL dump and restore operations.

06 Now you can remove the source server in order to stop adding charges to your Microsoft Azure cloud bill. Run postgres server delete command (Windows/macOS/Linux) using the name of the source server that you want to delete (see Audit section part II to identify the right Azure resource), to remove the selected PostgreSQL resource from your Azure account:

az postgres server delete
	--name cc-project5-db-server
	--resource-group cloud-shell-storage-westeurope

07 The postgres server delete command request should ask for your confirmation. Press y to confirm:

Are you sure you want to perform this operation? (y/n):

08 Repeat steps no. 1 – 7 for each Azure PostgreSQL database server available in the current subscription.

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

References

Publication date Apr 6, 2020

Unlock the Remediation Steps


Gain free unlimited access
to our full Knowledge Base


Over 750 rules & best practices
for AWS and Azure

You are auditing:

Check for PostgreSQL Major Version

Risk level: Low