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)

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

Security
Reliability
Performance
efficiency

The PostgreSQL open-source project regularly issues new releases that can be available to Google Cloud Platform (GCP) customers. By upgrading your GCP PostgreSQL database instances with the new version of PostgreSQL, you will introduce new software features, fix reported bugs through security patches, and provide better compatibility with other cloud applications and/or 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.

Note: For PostgreSQL 9.6, the first two numbers in the version number form the major version number. For example, for version 9.6.1, Google Cloud uses 9.6 as the major version, and 1 as the minor version. For PostgreSQL 10 and above, the first number is the major version number. For example, for version 10.1, 10 is the major version number, and 1 is the minor version.


Audit

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

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter tree box, select Type and PostgreSQL then press Enter, to list only the PostgreSQL database instances provisioned for the selected GCP project.

05 Click on the identifier (ID) of the database instance that you want to examine.

06 In the navigation panel, select Overview to access the configuration details available for the selected instance.

07 In the Configuration section, check the PostgreSQL database major version available for the Database version is <postgresql-version> attribute, where <postgresql-version> is the PostgreSQL engine version installed. Compare the installed version with the latest major version of the PostgreSQL database engine supported by Google Cloud SQL service. If the PostgreSQL version installed is lower than the latest major version supported by Google Cloud PostgreSQL instances, the selected instance is not using the newest version of the PostgreSQL database.

08 Repeat step no. 5 – 7 to check the database engine version for other PostgreSQL instances available within the selected project.

09 Repeat steps no. 2 – 8 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run projects list command (Windows/macOS/Linux) using custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your Google Cloud account:

gcloud projects list
	--format="table(projectId)"

02 The command output should return the requested GCP project identifiers:

PROJECT_ID
cc-mobile-project-123123
cc-bigdata-project-112233

03 Run sql instances list command (Windows/macOS/Linux) using custom filtering to describe the name of each PostgreSQL database instance provisioned for the selected Google Cloud project:

gcloud sql instances list
	--project cc-mobile-project-123123
	--filter='DATABASE_VERSION:POSTGRES*'
	--format="(NAME)"

04 The command output should return the requested database instance name(s):

NAME
cc-web-sql-server-instance
cc-app-sql-server-instance

05 Run sql instances describe command (Windows/macOS/Linux) using the name of the PostgreSQL database instance that you want to examine as identifier parameter and custom query filters to describe the PostgreSQL major version installed on the selected database instance:

gcloud sql instances describe cc-app-postgres-instance
	--format=json | jq '.databaseVersion'

06 The command output should return the requested flag configuration value:

"POSTGRES_10"

Compare the version returned by the sql instances describe command output with the latest major version of the PostgreSQL database engine supported by Google Cloud SQL service. If the version installed is lower than the latest major version supported by Google Cloud PostgreSQL instances, the selected instance is not using the newest version of the PostgreSQL database.

07 Repeat step no. 5 and 6 to check the database engine version for other PostgreSQL database instances created for the selected project.

08 Repeat steps no. 3 – 7 for each project available within your Google Cloud account.

Remediation / Resolution

Automatic upgrade to a major PostgreSQL database version is not currently supported within Google Cloud Platform (GCP). To upgrade your Google Cloud PostgreSQL instances to the latest major version of the PostgreSQL database, you have to re-create the existing instance, export data from the existing (source) instance, and importing that data into a new (target) instance running the latest major version of PostgreSQL. To implement the rule remediation process, perform the following actions:

Note: Before moving your data to a database instance running a newer version of PostgreSQL, you should familiarize yourself with the differences between the database engine versions by verifying the PostgreSQL official documentation.

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter tree box, select Type and PostgreSQL then press Enter, to display only the PostgreSQL database instances available for the selected project.

05 Click on the ID of the database instance that you want to re-create and collect all the configuration information available for the selected resource.

06 Navigate back to the Cloud SQL Instances at https://console.cloud.google.com/sql/instances and click on the CREATE INSTANCE button from the dashboard top menu to initiate the PostgreSQL instance setup process.

07 From Choose your database engine panel, select Choose PostgreSQL.

08 On the Instance info panel, perform the following operations:

  1. Provide a unique identifier for the new database instance within the Instance ID box.
  2. Type your password for the "postgres" user in the Default user password text box.
  3. Use the Region and Zone settings to deploy the new database instance in the same region and zone as the source instance.
  4. Select the latest major version of the PostgreSQL database engine from the Database version dropdown list.
  5. Click on Show configuration options to open the Configuration options panel, and configure the rest of the instance settings based on the configuration information taken from the source instance at step no. 5. Ensure that the new instance has sufficient storage to hold the source instance data, the same authorized networks as the source instance and the same user accounts, with the same PostgreSQL privileges and passwords.
  6. Click Create to launch the new PostgreSQL database instance.

09 Once the new Google Cloud PostgreSQL database instance is provisioned, export the source instance data to a PostgreSQL dump file and import that data to the newly created (target) instance.

10 Update your application(s) to connect to the new database instance.

11 Now that your new instance is operating successfully, you can remove the source instance in order to stop adding charges to your Google Cloud monthly bill. Click on the identifier (ID) of the resource that you want to delete (see Audit section part I to identify the source instance).

12 Click on the DELETE button from the dashboard top menu to initiate the removal process.

13 Within Delete instance? confirmation box, confirm resource deletion by typing the instance identifier, then click DELETE.

14 Repeat step no. 5 – 13 to manually upgrade other PostgreSQL instances available in the selected project to the latest major version of PostgreSQL database.

15 Repeat steps no. 2 – 14 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run sql instances describe command (Windows/macOS/Linux) using the name of the PostgreSQL database instance that you want to re-create as identifier parameter and custom query filters to describe the configuration metadata available for the selected database server:

gcloud sql instances describe cc-app-postgres-instance
	--format=json

02 The command output should return the requested configuration information (metadata):

{
  "backendType": "SECOND_GEN",
  "databaseVersion": "POSTGRES_10",
  "gceZone": "us-central1-a",
  "instanceType": "CLOUD_SQL_INSTANCE",
  "kind": "sql#instance",
  "name": "cc-app-postgres-instance",
  "project": "cc-mobile-project-123123",
  "region": "us-central1",
  "serviceAccountEmailAddress": "pabcd1234abcd-abcabc@gcp-sa-cloud-sql.iam.gserviceaccount.com",
  "settings": {
    "activationPolicy": "ALWAYS",
    "availabilityType": "ZONAL",

    ...

    "dataDiskSizeGb": "150",
    "dataDiskType": "PD_SSD",
    "ipConfiguration": {
      "ipv4Enabled": true
    },
    "locationPreference": {
      "kind": "sql#locationPreference",
      "zone": "us-central1-a"
    },
    "pricingPlan": "PER_USE",
    "replicationType": "SYNCHRONOUS",
    "tier": "db-n1-standard-2"
  },
  "state": "RUNNABLE"
}

03 Run sql instances create command (Windows/macOS/Linux) using the information returned at the previous step as configuration data for the command parameters, to create a new Google Cloud SQL database instance with the PostgreSQL database major version set to PostgreSQL 12:

gcloud sql instances create cc-new-postgres-instance
	--database-version=POSTGRES_12
	--tier=db-n1-standard-2
	--storage-size=150
	--storage-type=SSD
	--zone=us-central1-a
	--availability-type=ZONAL
	--activation-policy=always
	--assign-ip
	--require-ssl
	--root-password=xxxxxxxxxx
	--project=cc-mobile-project-123123

04 The command output should return the metadata for the newly created PostgreSQL 12 database instance:

Creating Cloud SQL instance...done.
Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-mobile-project-123123/instances/cc-new-postgres-instance].
NAME                      DATABASE_VERSION  LOCATION       TIER              PRIMARY_ADDRESS  STATUS
cc-new-postgres-instance  POSTGRES_12       us-central1-a  db-n1-standard-2  xxx.xxx.xxx.xxx  RUNNABLE

05 Once the new Google Cloud PostgreSQL database instance is provisioned, export the source instance data to a PostgreSQL dump file and import that data to the newly created (target) instance.

06 Update your application(s) to connect to the new database instance.

07 Now that your new instance is operating successfully, you can remove the source instance in order to stop adding charges to your Google Cloud bill. Run sql instances delete command (Windows/macOS/Linux) using the ID of the PostgreSQL database instance that you want to remove as identifier parameters (see Audit section part II to identify the right resource), to delete the specified database instance:

gcloud sql instances delete cc-app-postgres-instance

08 Type Y to confirm the resource removal. All data, including backups, is permanently lost when that instance is deleted, therefore make sure that your data has been successfully exported to the new PostgreSQL instance before removal:

All of the instance data will be lost when the instance is deleted.
Do you want to continue (Y/n)? Y

09 The output should return the sql instances delete command request status:

Deleting Cloud SQL instance...done.
Deleted [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-mobile-project-123123/instances/cc-app-postgres-instance].

10 Repeat step no. 1 – 9 to upgrade other PostgreSQL instances available in the selected project to the latest major version of PostgreSQL database.

11 Repeat steps no. 1 – 10 for each project created within your Google Cloud account.

References

Publication date Apr 21, 2021

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