Skip to main content

Minimum Number of SQL Backups To Be Retained

More Info:​

Ensure that SQL Instances have a reasonable number of backups retained in order to recover from failures.

Risk Level​

Medium

Address​

Reliability, Security

Compliance Standards​

  • APRA CPS 234 (Australia)
  • BSI C5 (Germany)
  • Brazil LGPD
  • CCPA / CPRA (California)
  • CIS Critical Security Controls v8
  • CMMC 2.0
  • CSA Cloud Controls Matrix v4
  • DPDPA
  • Digital Operational Resilience Act (EU)
  • Essential 8
  • GDPR
  • ISO/IEC 27017
  • ISO/IEC 27018
  • ISO/IEC 27701
  • KSA PDPL
  • MAS Technology Risk Management (Singapore)
  • MITRE ATT&CK (Cloud)
  • NIS2 Directive
  • NIST SP 800-171
  • NYDFS 23 NYCRR 500
  • SWIFT Customer Security Controls Framework
  • Sarbanes-Oxley IT General Controls
  • Securities and Exchange Board of India (SEBI) - Cloud Security Adoption Framework
  • UK NCSC Cyber Assessment Framework

Triage and Remediation​

Remediation​

Using Console

The "Minimum Number of SQL Backups To Be Retained" misconfiguration means that the minimum number of backups required for a Cloud SQL instance is not set. This can lead to data loss in case of unexpected failures or errors. To remediate this issue in GCP using GCP console, please follow the steps below:

  1. Open the GCP console and navigate to the Cloud SQL instances page.

  2. Select the instance for which you want to configure the backup retention policy.

  3. Click on the "Edit" button at the top of the page.

  4. In the "Backup" section, scroll down to the "Retention" field.

  5. Set the "Minimum number of backups to be retained" to the desired value. It is recommended to retain at least 7 backups.

  6. Click on the "Save" button to apply the changes.

  7. Verify that the backup retention policy has been updated by checking the "Backups" tab for the instance.

By following these steps, you can remediate the "Minimum Number of SQL Backups To Be Retained" misconfiguration for a Cloud SQL instance in GCP using GCP console.

Using CLI

The misconfiguration "Minimum Number of SQL Backups To Be Retained" in GCP can be remediated by following these steps using GCP CLI:

  1. Open the Cloud Shell in GCP Console.

  2. Run the following command to list all the available Cloud SQL instances:

gcloud sql instances list

  1. Choose the instance for which you want to remediate the misconfiguration.

  2. Run the following command to set the minimum number of backups to be retained:

gcloud sql instances patch [INSTANCE_NAME] --backup-retention-window [NUMBER_OF_DAYS]

Replace [INSTANCE_NAME] with the name of your instance and [NUMBER_OF_DAYS] with the minimum number of days for which you want to retain the backups.

For example, if you want to retain backups for a minimum of 7 days, run the following command:

gcloud sql instances patch my-instance --backup-retention-window 7
  1. Verify that the configuration has been updated by running the following command:
gcloud sql instances describe [INSTANCE_NAME]

This will display the details of your instance, including the updated backup retention window.

By following these steps, you can remediate the misconfiguration "Minimum Number of SQL Backups To Be Retained" for GCP using GCP CLI.

Using Python

The misconfiguration "Minimum Number of SQL Backups To Be Retained" means that there is no minimum number of backups set for the SQL database in GCP. This can lead to data loss in case of any disaster or system failure. To remediate this misconfiguration, we need to set the minimum number of backups to be retained.

Here are the step-by-step instructions to remediate this misconfiguration for GCP using Python:

  1. First, we need to install the required libraries. Open the command prompt and run the following command:

    pip install google-cloud-storage google-auth google-auth-oauthlib google-auth-httplib2 google-cloud-sql

  2. Next, we need to authenticate ourselves with the GCP project. For this, we need to create a service account and download the JSON key file. Follow the instructions given in the link to create a service account: https://cloud.google.com/iam/docs/creating-managing-service-accounts

  3. Once the service account is created, download the JSON key file and set the environment variable GOOGLE_APPLICATION_CREDENTIALS to the path of the JSON key file.

    export GOOGLE_APPLICATION_CREDENTIALS="/path/to/key.json"

  4. Now, we can write a Python script to remediate the misconfiguration. Here is a sample script to set the minimum number of backups to be retained to 7:

    from google.oauth2 import service_account
    from google.cloud import sql_v1beta4
    from google.cloud.sql_v1beta4.services.sql_backup_runs_service import SqlBackupRunsServiceClient

    # Authenticate with GCP using the service account key file
    credentials = service_account.Credentials.from_service_account_file('/path/to/key.json')

    # Set the project ID and instance name
    project_id = 'project-id'
    instance_name = 'instance-name'

    # Set the minimum number of backups to be retained
    retention_count = 7

    # Create a client object for the SQL Backup Runs API
    backup_runs_client = SqlBackupRunsServiceClient(credentials=credentials)

    # Get the latest backup run for the instance
    backup_runs = backup_runs_client.list(project=project_id, instance=instance_name).items
    latest_backup_run = sorted(backup_runs, key=lambda x: x.end_time, reverse=True)[0]

    # Set the retention policy for the latest backup run
    retention_policy = sql_v1beta4.types.BackupRetentionSettings(retention_unit='COUNT', retention_count=retention_count)
    backup_runs_client.update(project=project_id, instance=instance_name, id=latest_backup_run.id, body={'settings': {'backupRetentionSettings': retention_policy}})

    Replace project-id, instance-name, and /path/to/key.json with the appropriate values.

  5. Save the script with a .py extension and run it using the following command:

    python script_name.py

  6. After running the script, the minimum number of backups to be retained for the SQL database will be set to the specified value.

Using Terraform
resource "google_sql_database_instance" "PRIMARY_INSTANCE" {
  name             = "PRIMARY_INSTANCE_NAME"      # replace with your instance name
  database_version = "POSTGRES_15"                # replace with your engine/version
  region           = "INSTANCE_REGION"            # e.g., us-central1

  settings {
    tier = "db-custom-2-7680"                     # replace with your tier

    backup_configuration {
      enabled = true

      backup_retention_settings {
        retained_backups = 2                      # minimum backups to retain (threshold)
        retention_unit   = "COUNT"                # keep a fixed number of backups
      }
    }
  }
}

Changing retained_backups is an in‑place update and does not force replacement of the instance.

To verify, terraform plan should show an in-place update to settings.0.backup_configuration.0.backup_retention_settings.0.retained_backups to value 2 (or higher).