Point In Time Restore Should Be Enabled

More Info:

Ensures SQL instances can be restored to a recent point. GCP will maintain a point to which the database can be restored. This point should not drift too far into the past, or else the risk of irrecoverable data loss may occur.

Risk Level

High

Address

Reliability, Security

Compliance Standards

• Cloudanix Best Practice

Triage and Remediation

Remediation

Remediation

Using Console

To remediate the "Point In Time Restore Should Be Enabled" misconfiguration in GCP using GCP console, please follow these steps:

1. Login to the GCP console (console.cloud.google.com).
2. Navigate to the Cloud SQL instances page.
3. Select the instance that you want to remediate.
4. Click on the Edit button at the top of the page.
5. Scroll down to the Backup section.
6. Under the Backup Configuration section, select the checkbox for Enable automatic backups.
7. Under the Backup Configuration section, select the checkbox for Enable point-in-time recovery.
8. Set the Backup retention period to the desired value.
9. Click on the Save button at the bottom of the page.

Once you have completed these steps, automatic backups and point-in-time recovery will be enabled for your Cloud SQL instance, and you will have remediated the "Point In Time Restore Should Be Enabled" misconfiguration.

Using CLI

To remediate the misconfiguration "Point In Time Restore Should Be Enabled" for GCP using GCP CLI, please follow the below steps:

1. Open the Cloud Shell in the GCP Console.

2. Run the following command to enable point-in-time recovery for all the Cloud SQL instances in the current project:

gcloud sql instances patch INSTANCE_NAME --backup-start-time 23:00 --backup-location REGION --enable-point-in-time-recovery

Replace INSTANCE_NAME with the name of your Cloud SQL instance and REGION with the region where your instance is located. The --backup-start-time flag specifies the time of day when automated backups should start, and the --enable-point-in-time-recovery flag enables point-in-time recovery.

3. Verify that point-in-time recovery is enabled for the instance by running the following command:

gcloud sql instances describe INSTANCE_NAME

The output should include the following line:

backupConfiguration:
  enabled: true
  pointInTimeRecoveryEnabled: true

This indicates that point-in-time recovery is enabled for the instance.

4. Repeat the above steps for all the Cloud SQL instances in your project.

By following the above steps, you should be able to remediate the misconfiguration "Point In Time Restore Should Be Enabled" for GCP using GCP CLI.

Using Python

To remediate the "Point In Time Restore Should Be Enabled" misconfiguration in GCP using Python, follow these steps:

1. Import the necessary libraries:

from google.cloud import bigquery
from google.cloud.bigquery import enums
from google.cloud.bigquery import table

2. Create a BigQuery client object:

client = bigquery.Client()

3. Get the dataset and table objects:

dataset_id = 'your_dataset_id'
table_id = 'your_table_id'
dataset_ref = client.dataset(dataset_id)
table_ref = dataset_ref.table(table_id)

4. Get the table metadata:

table = client.get_table(table_ref)

5. Check if the table has point-in-time recovery enabled:

if table.time_partitioning and table.time_partitioning.type == enums.TimePartitioningType.HOUR:
    print('Point-in-time recovery is enabled for the table.')
else:
    print('Point-in-time recovery is not enabled for the table.')

6. If point-in-time recovery is not enabled, enable it:

if not table.time_partitioning:
    table.time_partitioning = table.TimePartitioning(type_=enums.TimePartitioningType.HOUR)
else:
    table.time_partitioning.type_ = enums.TimePartitioningType.HOUR
table = client.update_table(table, ['time_partitioning'])
print('Point-in-time recovery has been enabled for the table.')

7. Verify that point-in-time recovery has been enabled:

table = client.get_table(table_ref)
if table.time_partitioning and table.time_partitioning.type == enums.TimePartitioningType.HOUR:
    print('Point-in-time recovery is now enabled for the table.')
else:
    print('Failed to enable point-in-time recovery for the table.')

This Python code will enable point-in-time recovery for a BigQuery table in GCP. You can modify it as per your specific requirements.

Using Terraform

resource "google_sql_database_instance" "PRIMARY_INSTANCE" {
  name             = "PRIMARY_INSTANCE_NAME"      # replace with your instance name
  database_version = "MYSQL_8_0"                  # or your current MySQL version
  region           = "PRIMARY_INSTANCE_REGION"    # replace with your region

  settings {
    tier = "db-custom-1-3840"                     # replace with your machine tier

    backup_configuration {
      enabled            = true                   # REQUIRED: automatic backups on
      binary_log_enabled = true                   # REQUIRED: enables point-in-time recovery (MySQL)
      start_time         = "02:00"                # optional: set to your preferred backup window (UTC)
    }

    # ...other settings you already use...
  }

  # ...other arguments you already use (root_password, ip_configuration, etc.)...
}

This enables automatic backups and binary logging on the Cloud SQL instance so it can be restored to a recent point in time; these changes are in-place and do not force replacement of the instance.

To verify, terraform plan should show the existing google_sql_database_instance updating with backup_configuration.enabled changing to true and backup_configuration.binary_log_enabled changing to true (and start_time if you set it).

Additional Reading:

• https://cloud.google.com/sql/docs/mysql/instance-settings