Backtrack

Risk Level: Low (generally tolerable level of risk)

Rule ID: RDS-034

Ensure that the Backtrack feature is enabled for your Amazon Aurora (with MySQL compatibility) database clusters in order to backtrack your clusters to a specific time, without using backups. Backtrack is an Amazon RDS feature that allows you to specify the amount of time that an Aurora MySQL database cluster needs to retain change records, in order to have a fast way to recover from user errors, such as dropping the wrong table or deleting the wrong row by moving your MySQL database to a prior point in time without the need to restore from a recent backup.

This rule resolution is part of the Conformity Security & Compliance tool for AWS.

Reliability

Once the Backtrack feature is enabled, Amazon RDS can quickly "rewind" your Aurora MySQL database cluster to a point in time that you specify. In contrast to the backup and restore method, with Backtrack you can easily undo a destructive action, such as a DELETE query without a WHERE clause, with minimal downtime, you can rewind your Aurora cluster in just few minutes, and you can repeatedly backtrack a database cluster back and forth in time to help determine when a particular data change occurred.

Audit

To determine if your Amazon Aurora MySQL-compatible database clusters are using the Backtrack feature, perform the following actions:

Using AWS Console

01 Sign in to the AWS Management Console.

02 Navigate to Amazon RDS console at https://console.aws.amazon.com/rds/.

03 In the navigation panel, under Amazon RDS, choose Databases.

04 Click on the name (link) of the Aurora database cluster that you want to examine. To identify Aurora MySQL-compatible database clusters, check the database engine type available in the Engine column (i.e. Aurora MySQL).

05 Select the Maintenance & backups tab and check the Backtrack window configuration attribute value. If the Backtrack window value is set to Disabled, the Backtrack feature is not enabled for the selected Amazon Aurora database cluster.

06 Repeat steps no. 4 and 5 for each Amazon Aurora MySQL database cluster available within the current AWS region.

07 Change the AWS cloud region from the navigation bar and repeat the Audit process for other regions.

Using AWS CLI

01 Run describe-db-clusters command (OSX/Linux/UNIX) using custom query filters to list the names of all the Aurora MySQL-compatible database clusters available in the selected AWS region:

aws rds describe-db-clusters
  --region us-east-1
  --output table
  --query 'DBClusters[?Engine==`aurora-mysql`].DBClusterIdentifier | []'

02 The command output should return a table with the requested Aurora MySQL clusters:

-------------------------------
|     DescribeDBClusters      |
+-----------------------------+
|  cc-aurora-mysql-cluster    |
|  cc-aurora-wp-web-cluster   |
+-----------------------------+

03 Execute describe-db-clusters command (OSX/Linux/UNIX) using the name of the Aurora database cluster that you want to examine as the identifier parameter and custom query filters to describe the backtrack window, in seconds, configured for the selected cluster:

aws rds describe-db-clusters
  --region us-east-1
  --db-cluster-identifier cc-aurora-mysql-cluster
  --query 'DBClusters[*].BacktrackWindow'

04 The command output should return an array that contains the target backtrack window time frame (in seconds), or an empty array if backtracking is currently disabled:

[]

If the describe-db-clusters command output returns an empty array (i.e. []), as shown in the output example above, the Backtrack feature is not enabled for the selected Amazon Aurora database cluster.

05 Repeat steps no. 3 and 4 for each Amazon Aurora database cluster available in the selected AWS region.

06 Change the AWS cloud region by updating the --region command parameter value and repeat the Audit process for other regions.

Remediation / Resolution

To enable the Backtrack feature for an Amazon Aurora MySQL-compatible database cluster, you must re-create the database cluster and configure the feature during the setup process. To implement backtracking for your Aurora MySQL database clusters, perform the following actions:

Using AWS CloudFormation

01 CloudFormation template (JSON):

{
	"AWSTemplateFormatVersion": "2010-09-09",
	"Description": "Enable and Configure Backtrack for Aurora Cluster",
	"Parameters": {
		"Username": {
			"Type": "String"
		},
		"Password": {
			"Type": "String",
			"NoEcho" : "true"
		},
	},
	"Resources": {
		"RDSCluster": {
			"Type": "AWS::RDS::DBCluster",
			"Properties": {
				"DBClusterIdentifier": "cc-new-aurora-mysql-cluster",
				"DatabaseName" : "auroradb",
				"MasterUsername": {
					"Ref": "Username"
				},
				"MasterUserPassword": {
					"Ref": "Password"
				},
				"Engine": "aurora",
				"DBSubnetGroupName": "default",
				"BacktrackWindow": 86400
			}
		},
		"ClusterDBInstance1": {
			"Type": "AWS::RDS::DBInstance",
			"Properties": {
				"Engine": "aurora",
				"DBSubnetGroupName": "default",
				"DBClusterIdentifier": {
					"Ref": "RDSCluster"
				},
				"PubliclyAccessible": "true",
				"DBInstanceClass": "db.t2.small"
			}
		},
		"ClusterDBInstance2": {
			"Type": "AWS::RDS::DBInstance",
			"Properties": {
				"Engine": "aurora",
				"DBSubnetGroupName": "default",
				"DBClusterIdentifier": {
					"Ref": "RDSCluster"
				},
				"PubliclyAccessible": "true",
				"DBInstanceClass": "db.t2.small"
			}
		}
	}
}

02 CloudFormation template (YAML):

AWSTemplateFormatVersion: '2010-09-09'
	Description: Enable and Configure Backtrack for Aurora Cluster
	Parameters:
		Username:
		Type: String
		Password:
		Type: String
		NoEcho: 'true'
	Resources:
		RDSCluster:
		Type: AWS::RDS::DBCluster
		Properties:
			DBClusterIdentifier: cc-new-aurora-mysql-cluster
			DatabaseName: auroradb
			MasterUsername: !Ref 'Username'
			MasterUserPassword: !Ref 'Password'
			Engine: aurora
			DBSubnetGroupName: default
			BacktrackWindow: 86400
		ClusterDBInstance1:
		Type: AWS::RDS::DBInstance
		Properties:
			Engine: aurora
			DBSubnetGroupName: default
			DBClusterIdentifier: !Ref 'RDSCluster'
			PubliclyAccessible: 'true'
			DBInstanceClass: db.t2.small
		ClusterDBInstance2:
		Type: AWS::RDS::DBInstance
		Properties:
			Engine: aurora
			DBSubnetGroupName: default
			DBClusterIdentifier: !Ref 'RDSCluster'
			PubliclyAccessible: 'true'
			DBInstanceClass: db.t2.small

Using Terraform

01 Terraform configuration file (.tf):

terraform {
	required_providers {
		aws = {
			source  = "hashicorp/aws"
			version = "~> 3.27"
		}
	}

	required_version = ">= 0.14.9"
}

provider "aws" {
	profile = "default"
	region  = "us-east-1"
}

resource "aws_rds_cluster_instance" "rds-cluster-instances" {
	count              = 2
	identifier         = "cc-aurora-mysql-cluster-${count.index}"
	cluster_identifier = aws_rds_cluster.rds-cluster.id
	instance_class     = "db.t2.small"
	engine             = aws_rds_cluster.rds-cluster.engine
	engine_version     = aws_rds_cluster.rds-cluster.engine_version
}

resource "aws_rds_cluster" "rds-cluster" {
	cluster_identifier      = "cc-aurora-mysql-cluster"
	engine                  = "aurora-mysql"
	engine_version          = "5.7.mysql_aurora.2.10.2"
	availability_zones      = ["us-east-1a", "us-east-1b"]
	database_name           = "auroradb"
	master_username         = "aurorausr"
	master_password         = "aurorapasswd"

	# Enable and Configure Backtrack For Aurora Cluster
	backtrack_window = 86400
}

Using AWS Console

01 Sign in to the AWS Management Console.

02 Navigate to Amazon RDS console at https://console.aws.amazon.com/rds/.

03 In the navigation panel, under Amazon RDS, choose Databases.

04 Select the Amazon Aurora database cluster that you want to re-create, choose Actions, and select Create clone.

05 On the Create Clone setup page, perform the following operations:

Select Aurora MySQL from the DB engine dropdown list.
Provide a unique name for the primary instance of the clone cluster in the DB instance identifier box.
In the Additional configuration section, under Backtrack, select Enable Backtrack to enable the feature, then specify the amount of time (in hours, up to 72) that you want to be able to backtrack, within the Target Backtrack window configuration box. This setting must be configured in order to remember how far back in time you could go with backtracking.
Choose Create Clone to launch the new Aurora MySQL database cluster.

06 Once the new MySQL database cluster is created, replace the required endpoints within your application code to switch the source cluster with the new cluster.

07 (Optional) You can remove the source Aurora database cluster from your AWS cloud account in order to avoid unnecessary charges on your AWS bill. To delete the source Aurora cluster, perform the following actions:

Select the primary database instance provisioned for the Aurora cluster that you want to terminate.
Choose Actions from the console top menu and select Delete.
Within Delete <instance-name> instance? confirmation box, choose whether or not to create a final snapshot for the selected database instance, type delete me into the required field, then choose Delete to confirm the action. This should also remove the source database cluster.

08 Repeat steps no. 4 – 7 for each Aurora database cluster available within the current AWS region.

09 Change the AWS cloud region from the navigation bar and repeat the Remediation process for other regions.

Using AWS CLI

01 Run restore-db-cluster-to-point-in-time command (OSX/Linux/UNIX) to re-create your Aurora MySQL database cluster and enable backtracking for the new database cluster. The following command request example creates a clone named cc-new-aurora-mysql-cluster from a source Aurora database cluster called cc-aurora-mysql-cluster that has the Backtrack window set to 24 hours (86400 seconds). When configured, the target Backtrack window must be set to a number from 0 to 259,200 (72 hours):

aws rds restore-db-cluster-to-point-in-time
  --region us-east-1
  --source-db-cluster-identifier cc-aurora-mysql-cluster
  --db-cluster-identifier cc-new-aurora-mysql-cluster
  --restore-type copy-on-write
  --use-latest-restorable-time
  --backtrack-window 86400

02 The command output should return the configuration metadata for the clone database cluster:

{
	"DBCluster": {
		"MasterUsername": "ccadmin",
		"ReaderEndpoint": "cc-new-aurora-mysql-cluster.cluster-ro-abcdabcdabcd.us-east-1.rds.amazonaws.com",
		"HttpEndpointEnabled": false,
		"ReadReplicaIdentifiers": [],
		"VpcSecurityGroups": [
			{
				"Status": "active",
				"VpcSecurityGroupId": "sg-0abcd1234abcd1234"
			},
			{
				"Status": "active",
				"VpcSecurityGroupId": "sg-abcd1234"
			}
		],
		"CopyTagsToSnapshot": true,
		"HostedZoneId": "ABCDABCDABCDAB",
		"EngineMode": "provisioned",
		"Status": "available",
		"MultiAZ": false,
		"LatestRestorableTime": "2021-05-12T09:00:00.162Z",
		"DomainMemberships": [],
		"PreferredBackupWindow": "04:06-04:36",
		"DBSubnetGroup": "default-vpc-abcdabcd",
		"AllocatedStorage": 50,
		"BackupRetentionPeriod": 7,
		"PreferredMaintenanceWindow": "tue:05:48-tue:06:18",
		"Engine": "aurora-mysql",
		"Endpoint": "cc-new-aurora-mysql-cluster.cluster-abcdabcdabcd.us-east-1.rds.amazonaws.com",
		"AssociatedRoles": [],
		"EarliestRestorableTime": "2021-05-12T09:03:00.657Z",
		"CrossAccountClone": false,
		"IAMDatabaseAuthenticationEnabled": true,
		"ClusterCreateTime": "2021-05-12T09:00:00.853Z",
		"EngineVersion": "5.7.mysql_aurora.2.07.2",
		"DeletionProtection": true,
		"DBClusterIdentifier": "cc-new-aurora-mysql-cluster",
		"DbClusterResourceId": "cluster-ABCDABCDABCDABCDABCDABCDAB",
		"DBClusterMembers": [
			{
				"IsClusterWriter": true,
				"DBClusterParameterGroupStatus": "in-sync",
				"PromotionTier": 1,
				"DBInstanceIdentifier": "cc-new-aurora-mysql-cluster-instance-1"
			}
		],
		"DBClusterArn": "arn:aws:rds:us-east-1:123456789012:cluster:cc-new-aurora-mysql-cluster",
		"StorageEncrypted": false,
		"DatabaseName": "",
		"DBClusterParameterGroup": "default.aurora-mysql5.7",
		"AvailabilityZones": [
			"us-east-1c",
			"us-east-1d",
			"us-east-1a"
		],
		"Port": 3306
	}
}

03 Once the new database cluster is created, replace the required endpoints within your application code to switch the source cluster with the new cluster.

04 (Optional) You can remove the source Aurora database cluster in order to avoid further charges:

Run delete-db-instance command (OSX/Linux/UNIX) to remove the primary database instance from the Aurora cluster that you want to terminate:

aws rds delete-db-instance
  --region us-east-1
  --db-instance-identifier cc-aurora-mysql-cluster-instance-1
  --skip-final-snapshot
  --query 'DBInstance.DBInstanceStatus'

The command output should return the current status for the selected database instance:
```
"deleting"
```

Execute delete-db-cluster command (OSX/Linux/UNIX) to delete the source Aurora MySQL database cluster:

aws rds delete-db-cluster
  --region us-east-1
  --db-cluster-identifier cc-aurora-mysql-cluster
  --skip-final-snapshot

The command output should return the metadata available for the terminated cluster:

{
	"DBCluster": {
		"MasterUsername": "ccadmin",
		"ReaderEndpoint": "cc-aurora-mysql-cluster.cluster-ro-abcdabcdabcd.us-east-1.rds.amazonaws.com",
		"HttpEndpointEnabled": false,
		"ReadReplicaIdentifiers": [],
		"VpcSecurityGroups": [
			{
				"Status": "active",
				"VpcSecurityGroupId": "sg-0abcd1234abcd1234"
			},
			{
				"Status": "active",
				"VpcSecurityGroupId": "sg-abcd1234"
			}
		],
		"CopyTagsToSnapshot": true,
		"HostedZoneId": "ABCDABCDABCDAB",
		"EngineMode": "provisioned",
		"Status": "available",
		"MultiAZ": false,
		"LatestRestorableTime": "2021-05-12T09:00:00.162Z",
		"DomainMemberships": [],
		"PreferredBackupWindow": "04:06-04:36",
		"DBSubnetGroup": "default-vpc-abcdabcd",
		"AllocatedStorage": 50,
		"BackupRetentionPeriod": 7,
		"PreferredMaintenanceWindow": "tue:05:48-tue:06:18",
		"Engine": "aurora-mysql",
		"Endpoint": "cc-aurora-mysql-cluster.cluster-abcdabcdabcd.us-east-1.rds.amazonaws.com",
		"AssociatedRoles": [],
		"EarliestRestorableTime": "2021-05-12T09:03:00.657Z",
		"CrossAccountClone": false,
		"IAMDatabaseAuthenticationEnabled": true,
		"ClusterCreateTime": "2021-05-12T09:00:00.853Z",
		"EngineVersion": "5.7.mysql_aurora.2.07.2",
		"DeletionProtection": true,
		"DBClusterIdentifier": "cc-aurora-mysql-cluster",
		"DbClusterResourceId": "cluster-ABCDABCDABCDABCDABCDABCDAB",
		"DBClusterMembers": [
			{
				"IsClusterWriter": true,
				"DBClusterParameterGroupStatus": "in-sync",
				"PromotionTier": 1,
				"DBInstanceIdentifier": "cc-aurora-mysql-cluster-instance-1"
			}
		],
		"DBClusterArn": "arn:aws:rds:us-east-1:123456789012:cluster:cc-aurora-mysql-cluster",
		"StorageEncrypted": false,
		"DatabaseName": "",
		"DBClusterParameterGroup": "default.aurora-mysql5.7",
		"AvailabilityZones": [
			"us-east-1c",
			"us-east-1d",
			"us-east-1a"
		],
		"Port": 3306
	}
}

05 Repeat steps no. 1 – 4 for each Aurora database cluster available in the selected AWS region.

06 Change the AWS cloud region by updating the --region command parameter value and repeat the Remediation process for other regions.

References

AWS Documentation
Amazon RDS FAQs
Backtracking an Aurora DB Cluster
Managing Amazon Aurora MySQL
Cloning Databases in an Aurora DB Cluster

AWS Command Line Interface (CLI) Documentation
rds
describe-db-clusters
restore-db-cluster-to-point-in-time
delete-db-instance
delete-db-cluster

AWS Blog(s)
Amazon Aurora Backtrack – Turn Back Time

CloudFormation Documentation
Amazon Relational Database Service resource type reference

Terraform Documentation
AWS Provider

Publication date Oct 18, 2018

Audit

Using AWS Console

Using AWS CLI

Remediation / Resolution

Using AWS CloudFormation

Using Terraform

Using AWS Console

Using AWS CLI

References

Related RDS rules