Enable Automated Backups for Cloud SQL 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 automated (scheduled) backups are created for all Cloud SQL database instances available within your Google Cloud Platform (GCP) account, in order to protect against data deletion and/or data corruption.

Security

By default, automated backups are not configured for Cloud SQL instances. Automated backups provide a quick and efficient way to restore an SQL database instance to recover lost data or recover from a problem with that instance.


Audit

To determine if your Google Cloud SQL database instances are configured with automated backups, perform the following operations:

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 on the name (ID) of the database instance that you want to examine.

05 In the navigation panel, select Backups to access the database backup configuration settings available for the selected instance.

06 In the Settings section, check the Automated backups feature configuration status. If the feature status is set to Disabled, database automated backups are not enabled for the selected Google Cloud SQL instance.

07 Repeat step no. 4 – 6 to check the Automated backups feature status for other Cloud SQL instances available within the selected project.

08 Repeat steps no. 2 – 7 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-ml-app-project-123123

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

gcloud sql instances list
	--project cc-mobile-project-123123
	--format="(NAME)"

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

NAME
cc-mobile-db-instance
cc-web-int-db-instance

05 Run sql instances describe command (Windows/macOS/Linux) using the name of the Cloud SQL database instance that you want to examine as identifier parameter and custom query filters to describe the Automated backups feature configuration status set for the selected database instance:

gcloud sql instances describe cc-mobile-db-instance
	--format="value(settings.backupConfiguration.enabled)"

06 The command output should return the requested configuration status (True for enabled, False for disabled):

False

If the sql instances describe command output returns False, database automated backups are not enabled for the selected Google Cloud SQL instance.

07 Repeat step no. 5 and 6 to verify the Automated backups feature status for other Cloud SQL instances created for the selected project.

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

Remediation / Resolution

To enable and configure automated backups for your Google Cloud SQL database instances, perform the following operations:

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 on the name/ID of the database instance that you want to reconfigure.

05 In the navigation panel, select Backups to access the database backup configuration settings available for the selected instance.

06 In the Settings section, click on Manage automated backups to initiate the feature setup process.

07 Inside Manage backups settings configuration box, select the Automate backups checkbox and choose the best 4-hour backup window for your database from the configuration dropdown list. Click SAVE to apply the changes and enable automated backups for the selected Google Cloud SQL database instance.

08 Repeat step no. 4 – 7 to enable automated backups for other Cloud SQL 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 sql instances patch command (Windows/macOS/Linux) using the name of the Cloud SQL database instance that you want to reconfigure as identifier parameter, to enable automated backups within the daily backup window set as value for the --backup-start-time parameter. The --backup-start-time parameter is specified in 24-hour time, in the UTC±00 time zone, and specifies the start of a 4-hour backup window. Database backups can start any time during the backup window specified:

gcloud sql instances patch cc-mobile-db-instance
	--backup-start-time 02:00

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

The following message will be used for the patch API method.

{"name": "cc-mobile-db-instance", "project": "cc-mobile-project-123123", "settings": {"backupConfiguration": {"binaryLogEnabled": false, "enabled": true, "location": "us", "startTime": "02:00"}}}

Patching Cloud SQL instance...done.
Updated [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-mobile-project-123123/instances/cc-mobile-db-instance].

03 Repeat step no. 1 and 2 to enable database automated backups for other Cloud SQL instances provisioned for the selected project.

04 Repeat steps no. 1 – 3 for each project created within your Google Cloud account.

References

Publication date Apr 12, 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:

Enable Automated Backups for Cloud SQL Database Instances

Risk level: Medium