Configure "max_connections" Flag for PostgreSQL Database Instances

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: Medium (should be achieved)

Ensure that the "max_connections" database flag configured for your Google Cloud PostgreSQL database instances has the optimal value, in accordance with your organization or project guidelines. Prior to running this conformity rule by the Trend Micro Cloud One™ – Conformity engine, you need to define the "max_connections" flag minimum value used within your organization in the rule settings, on your Trend Micro Cloud One™ – Conformity account console.

Performance
efficiency

Generally, depending on the plan used, the maximum number of client connections for Google Cloud PostgreSQL databases instances is relatively low. If the "max_connections" configuration flag is set to a low value, is not going to allow a high-traffic application to handle all the work that the users give to it. You can increase the connection limit by configuring "max_connections" configuration flag to meet the database workload requirements chosen by your organization. The new limit cannot exceed 262143 connections.

Note: Some database flag settings can affect instance availability and/or stability, and eventually remove the PostgreSQL instance from the Google Cloud SQL Service Level Agreement (SLA).


Audit

To determine if the "max_connections" flag set for your Cloud PostgreSQL database instances has the appropriate configuration, perform the following operations:

Using GCP Console

01 Sign in to your Trend Micro Cloud One™ – Conformity account, access the Configure "max_connections" Flag for PostgreSQL Database Instances rule, and note the minimum value configured for the "max_connections" database flag.

02 Sign in to Google Cloud Management Console.

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

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

05 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.

06 Click on the name (ID) of the database instance that you want to examine.

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

08 In the Configuration section, under Database flags, check the value set for the max_connections database flag. If max_connections is not available in the Database flags list, or the flag value is lower than the one identified at step no. 1, the "max_connections" flag configuration for the selected Google Cloud PostgreSQL database instance is not compliant.

09 Repeat step no. 6 – 8 to check the "max_connections" flag configuration for other PostgreSQL database instances available within the selected project.

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

Using GCP CLI

01 Sign in to your Trend Micro Cloud One™ – Conformity account, access the Configure "max_connections" Flag for PostgreSQL Database Instances rule, and note the minimum value configured for the "max_connections" database flag.

02 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)"

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

PROJECT_ID
cc-web-project-112233
cc-gov-project-123123

04 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-web-project-112233
	--filter='DATABASE_VERSION:POSTGRES*'
	--format="(NAME)"

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

NAME
cc-app-postgres-instance
cc-web-postgres-instance

06 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 "max_connections" flag configuration value set for the selected database instance:

gcloud sql instances describe cc-app-postgres-instance
	--format=json | jq '.settings.databaseFlags[] | select(.name=="max_connections")|.value'

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

"100"

If the sql instances describe command output returns null or the flag value (i.e. the maximum number of client connections) is lower than the one defined by your organization and identified at step no. 1, the "max_connections" flag configuration for the selected Google Cloud PostgreSQL database instance is not compliant.

08 Repeat step no. 5 and 6 to verify the "max_connections" flag configuration value for other PostgreSQL database instances created for the selected project.

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

Remediation / Resolution

To configure the "max_connections" database flag value in accordance with your organization and project guidelines, 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 display only the PostgreSQL database instances available for the selected project.

05 Click on the name/ID of the database instance that you want to reconfigure.

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

07 Click on the Edit button from the dashboard top menu to enter the instance edit mode.

08 In the Configuration options section, click on Flags to expand the panel with the database flags configured for the selected PostgreSQL instance.

09 Find the max_connections flag and type the appropriate value (integer) in the flag configuration box, in accordance with your organization and project guidelines. If the flag has not been set on the selected instance before, click Add item, choose the max_connections flag from the Choose one dropdown menu, and set its value (limit) accordingly. Click Close to close the panel.

10 Click Save to apply the configuration changes. IMPORTANT: Configuring the "max_connections" flag requires restarting the selected database instance.

11 For the Changes require restart confirmation box, select SAVE AND RESTART to restart the reconfigured PostgreSQL database instance.

12 Repeat step no. 5 – 11 to configure the specified flag for other PostgreSQL database instances available within the selected project.

13 Repeat steps no. 2 – 12 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run sql instances patch command (Windows/macOS/Linux) using the name of the PostgreSQL database instance that you want to reconfigure as identifier parameters (see Audit section part II to identify the right resource), to set the appropriate value (limit) for the "max_connections" database flag configured for the selected instance, in accordance with your organization and project guidelines. The new limit cannot exceed 262143 connections:

gcloud sql instances patch cc-app-postgres-instance
	--database-flags max_connections=1500

IMPORTANT: Configuring the "max_connections" flag restarts automatically the selected database instance.

02 Type Y to confirm the database configuration change:

The following message will be used for the patch API method.
{"name": "cc-app-postgres-instance", "project": "cc-web-project-112233", "settings": {"databaseFlags": [{"name": "max_connections", "value": "1500"}]}}
WARNING: This patch modifies database flag values, which may require your instance to be restarted. Check the list of supported flags - https://cloud.google.com/sql/docs/postgres/flags - to see if your instance will be restarted when this patch is submitted.
Do you want to continue (Y/n)? Y

03 The output should return the sql instances patch command request status:

Patching Cloud SQL instance...done.
Updated [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-web-project-112233/instances/cc-app-postgres-instance].

04 Repeat step no. 1 – 3 to configure the required flag for other PostgreSQL database instances provisioned for the selected project.

05 Repeat steps no. 1 – 4 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:

Configure "max_connections" Flag for PostgreSQL Database Instances

Risk level: Medium