Enable Cloud SQL Instance Encryption with Customer-Managed Keys

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 console at https://console.cloud.google.com/sql/instances.

04 Click on the identifier (ID) of the database instance that you want to examine.

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

06 In the Configuration section, check for Encrypted with a customer-managed key <key-resource-id> configuration attribute, where <key-resource-id> represents the full resource ID of the CMK used to encrypt the instance. If the Encrypted with a customer-managed key <key-resource-id> attribute is not listed in the Configuration section, the data on the selected Google Cloud SQL database instance is not encrypted with a Customer-Managed Key (CMK).

07 Repeat step no. 4 – 6 for each Cloud SQL database instance provisioned in the selected project.

08 Repeat steps no. 2 – 7 for each project deployed within your Google Cloud account.

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 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-web-project-112233

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-mysql-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 resource ID of the Customer-Managed Key used to encrypt the instance data:

gcloud sql instances describe cc-mobile-db-instance
	--format=json | jq '.diskEncryptionConfiguration.kmsKeyName'

06 The command output should return the full resource ID of the CMK used to encrypt instance data:

null

07 Repeat step no. 5 and 6 for each Cloud SQL database instance created within the selected project.

08 Repeat steps no. 3 – 7 for each GCP project deployed in your Google Cloud account.

01 Sign in to Google Cloud Management Console.

02 Select the GCP project that you want to access from the console top navigation bar.

03 To create and configure your new Customer-Managed Key (CMK), perform the following:

1. Navigate to Cloud Key Management Service (Cloud KMS) dashboard at https://console.cloud.google.com/security/kms.
2. Before you can set up and manage any Customer-Managed Keys (CMKs), you must create a key ring. A KMS key ring is a grouping of cryptographic keys made available for organizational purposes in a specific location. In the navigation panel, select Cryptographic Keys, and click on the CREATE KEY RING button to set up the required key ring and the new Customer-Managed Key (CMK).
3. A key ring requires a name and location. On the Create key ring page, provide a unique name in the Key ring name box, then choose the appropriate location from the Key ring location dropdown list. The location can be either global or associated with a particular region. If the CMKs created later within the key ring will be used to encrypt/decrypt resources in a given region, select that region as the key ring location. Click CREATE to deploy the new key ring.
4. On the Create key page, select Generated key as the type of the CMK that you want to create. Provide a name for your new key in the Key name box, choose the protection level (software or Hardware Security Module) that you want to use, select Symmetric encrypt/decrypt from the Purpose dropdown list to define the types of operations that your cryptographic key can perform, and configure the key rotation parameters. Click CREATE to deploy your new Cloud KMS Customer-Managed Key (CMK).

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

05 Click on the ID of the database instance that you want to re-create and collect all the configuration information available for the selected resource.

06 Go back to the Cloud SQL Instances console and click on the CREATE INSTANCE button from the dashboard top menu to initiate the database instance setup process.

07 From Choose your database engine panel, select Choose MySQL.

08 On the Instance info panel, perform the following actions:

1. Provide a unique identifier for the new instance within the Instance ID box.
2. Type your password for the "root'@'%" user in the Root password text box.
3. Use the Region and Zone settings to deploy the new database instance in the same region and zone as the source instance.
4. Select the latest major version of the MySQL database engine from the Database version dropdown list.
5. Click on Show configuration options to open the Configuration options panel, select Machine type and storage, and perform the following commands to enable data-at-rest encryption using your own Customer-Managed Key (CMK):
   • In the Encryption section, choose the Customer-managed key option, and select the CMK created at step no. 3 from the Select a customer-managed key dropdown list. If the newly created CMK does not appear in the dropdown list, select Don't see your key? Enter key resource ID and provide the full resource ID of your Customer-Managed Key (CMK).
   • Inside "The service-<project-number>@gcp-sa-cloud-sql.iam.gserviceaccount.com service account does not have permissions to encrypt/decrypt with the selected key." configuration box, click Grant to grant the specified service account the required IAM role on the selected CMK.
   • Click Close to close the Machine type and storage panel.
6. Configure the rest of the instance settings based on the configuration information taken from the source instance at step no. 5. Ensure that the new instance has sufficient storage to hold the source instance data, the same authorized networks as the source instance and the same user accounts, with the same MySQL privileges and passwords.
7. Click Create to launch your new Cloud SQL database instance.

09 Put your source MySQL instance into read-only mode by setting the "read_only" database flag to On. Check this conformity rule to see how MySQL database flags are configured.

10 Export the source instance data to an SQL dump file and import that data to the newly created (target) instance.

11 Update your application(s) to connect to the new database instance.

12 Once the new database is operating successfully, you can remove the source instance in order to stop adding charges to your Google Cloud bill. Click on the identifier (ID) of the resource that you want to delete (see Audit section part I to identify the source instance).

13 Click on the DELETE button from the dashboard top menu to initiate the removal process.

14 Within Delete instance? confirmation box, confirm resource deletion by typing the instance identifier, then click DELETE.

15 Repeat steps no. 5 – 15 to enable encryption at rest with Customer-Managed Keys (CMKs) for other Cloud SQL database instances available in the selected project.

16 Repeat steps no. 2 – 15 for each GCP project available in your Google Cloud account.

01 Before you can set up and manage your Customer-Managed Keys (CMKs), you must create a key ring. A KMS key ring is a grouping of cryptographic keys made available for organizational purposes in a specific Google Cloud location. Run kms keyrings create command (Windows/macOS/Linux) to create a new Cloud KMS key ring in the specified location. If the CMKs created later within this key ring will be used to encrypt/decrypt resources in a given region, select that region as the key ring location:

gcloud kms keyrings create cc-cloud-sql-key-ring
	--location=us
	--project=cc-mobile-project-123123
	--format="table(name)"

02 The command output should return the identifier (name) of the newly created key ring:

NAME
projects/cc-mobile-project-123123/locations/us/keyRings/cc-cloud-sql-key-ring

03 Run kms keys create command (Windows/macOS/Linux) to create a new Cloud KMS Customer-Managed Key (CMK) within the KMS key ring created at the previous steps:

gcloud kms keys create cc-cloud-sql-db-cmk
	--location=us-central1
	--keyring=cc-cloud-sql-key-ring
	--purpose=encryption
	--protection-level=software
	--rotation-period=90d
	--next-rotation-time=2020-7-15T20:00:00.0000Z
	--format="table(name)"

04 The command output should return the name of the new Customer-Managed Key (CMK):

NAME
projects/cc-mobile-project-123123/locations/us-central1/keyRings/cc-cloud-sql-key-ring/cryptoKeys/cc-cloud-sql-db-cmk

05 Run projects add-iam-policy-binding command (Windows/macOS/Linux) to assign the Cloud KMS "CryptoKey Encrypter/Decrypter" role to the necessary service account. Replace <kms-project-id> with the ID of the Google Cloud project where the Customer-Managed Keys are provisioned, and replace <project-number> with the project number (not the project ID) of the Google Cloud project that is running your Cloud SQL database instances:

gcloud projects add-iam-policy-binding <kms-project-id>
	--member serviceAccount:service-<project-number>@gcp-sa-cloud-sql.iam.gserviceaccount.com
	--role roles/cloudkms.cryptoKeyEncrypterDecrypter

06 The command output should return the updated IAM policy (YAML format):

Updated IAM policy for project <kms-project-id>.
bindings:
- members:
  - serviceAccount:service-<project-number>@gcp-sa-cloud-sql.iam.gserviceaccount.com
  role: roles/cloudkms.cryptoKeyEncrypterDecrypter
- members:
  - serviceAccount:service-<project-number>@compute-system.iam.gserviceaccount.com
  - user:admin@cloudconformity.com
  role: roles/owner
etag: abcdabcdabcd
version: 1

07 Run sql instances describe command (Windows/macOS/Linux) using the name of the Cloud SQL database instance that you want to re-create as identifier parameter and custom query filters to describe the configuration metadata available for the selected database instance:

gcloud sql instances describe cc-mobile-db-instance
	--format=json

08 The command output should return the requested configuration metadata:

{
  "backendType": "SECOND_GEN",
  "databaseVersion": "MYSQL_5_7",
  "gceZone": "us-central1-a",
  "instanceType": "CLOUD_SQL_INSTANCE",
  "kind": "sql#instance",
  "name": "cc-mobile-db-instance",
  "project": "cc-mobile-project-123123",
  "region": "us-central1",
  "serviceAccountEmailAddress": "service-<project-number>@gcp-sa-cloud-sql.iam.gserviceaccount.com",
  "settings": {
    "activationPolicy": "ALWAYS",
    "availabilityType": "ZONAL",

    ...

    "dataDiskSizeGb": "100",
    "dataDiskType": "PD_SSD",
    "ipConfiguration": {
      "ipv4Enabled": true
    },
    "locationPreference": {
      "kind": "sql#locationPreference",
      "zone": "us-central1-a"
    },
    "pricingPlan": "PER_USE",
    "replicationType": "SYNCHRONOUS",
    "tier": "db-n1-standard"
  },
  "state": "RUNNABLE"
}

09 Run sql instances create command (Windows/macOS/Linux) using the information returned at the previous step as configuration data for the command parameters, to create a new Google Cloud SQL database instance, encrypted with the Customer-Managed Key (CMK) created at step no. 3:

gcloud sql instances create cc-encrypted-mobile-db-instance
	--project=cc-mobile-project-123123
	--database-version=MYSQL_5_7
	--tier=db-n1-standard-1
	--storage-size=100
	--storage-type=SSD
	--zone=us-central1-a
	--availability-type=ZONAL
	--assign-ip
	--require-ssl
	--root-password=xxxxxxxxxx
	--disk-encryption-key=projects/cc-mobile-project-123123/locations/us-central1/keyRings/cc-cloud-sql-key-ring/cryptoKeys/cc-cloud-sql-db-cmk

10 Press Y at the command prompt to confirm the terms and conditions:

You are creating a Cloud SQL instance encrypted with a customer-managed key. If anyone destroys a customer-managed key, all data encrypted with it will be permanently lost.

Do you want to continue (Y/n)?

11 The command output should return the metadata for the newly created database instance:

Creating Cloud SQL instance...done.

Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-mobile-project-123123/instances/cc-encrypted-mobile-db-instance].

NAME                              LOCATION        TIER             PRIMARY_ADDRESS   STATUS
cc-encrypted-mobile-db-instance   us-central1-a   db-n1-standard   xxx.xxx.xxx.xxx   RUNNABLE

12 Put your source (unencrypted) database instance into read-only mode by setting the "read_only" database flag to On. Check this rule to see how MySQL database flags are configured.

13 Export the source instance data to an SQL dump file and import that data to the newly created (target) instance.

14 Update the associated application(s) to connect to the new database instance.

15 Once the new database is operating successfully, you can remove the source instance in order to stop adding charges to your Google Cloud bill. Run sql instances delete command (Windows/macOS/Linux) using the ID of the MySQL database instance that you want to remove as identifier parameters (see Audit section part II to identify the right resource), to delete the specified database instance:

gcloud sql instances delete cc-mobile-db-instance

16 Type Y to confirm the resource removal. All data, including backups, is permanently lost when that instance is deleted, therefore make sure that your data has been successfully exported to the new database instance before removal:

All of the instance data will be lost when the instance is deleted.
Do you want to continue (Y/n)? Y

17 The output should return the sql instances delete command request status:

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

18 Repeat steps no. 7 – 17 to enable encryption at rest with Customer-Managed Keys (CMKs) for other Cloud SQL database instances provisioned in the selected project.

19 Repeat steps no. 1 – 18 for each GCP project deployed in your Google Cloud account.