> ## Documentation Index > Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # Sql replicas encrypted remediation ### Triage and Remediation ### Remediation Sure, I can help you with that. Here are the step-by-step instructions to remediate the issue of SQL Read Replica Instances not being encrypted using Customer Managed Keys (CMKs) in GCP Console: 1. Open the GCP Console and navigate to the Cloud SQL instances page. 2. Select the read replica instance that you want to encrypt. 3. Click on the "Edit" button at the top of the page. 4. Scroll down to the "Encryption" section and select "Customer-managed key" from the dropdown menu. 5. Click on the "Select a key" button and choose the desired key from the list of available keys. 6. If you don't have a key yet, click on the "Create a key" button and follow the instructions to create a new key. 7. Once you have selected or created the key, click on the "Save" button to apply the changes. 8. Wait for the encryption to complete. This may take some time depending on the size of your database. 9. Once the encryption is complete, verify that the replica is now using customer-managed encryption keys by checking the "Encryption" section of the instance details page. Congratulations! You have successfully remediated the issue of SQL Read Replica Instances not being encrypted using Customer Managed Keys (CMKs) in GCP Console. # To remediate this misconfiguration in GCP using GCP CLI, please follow the below steps: 1. Firstly, identify the SQL Read Replica instance that needs to be encrypted using CMKs. You can use the following command to list all the SQL instances in your GCP project: ```bash theme={null} gcloud sql instances list ``` 2. Once you have identified the SQL Read Replica instance, you can enable encryption using the following command: ```bash theme={null} gcloud sql instances patch [INSTANCE_NAME] --backup-encryption-key [KEY_NAME] --backup-encryption-key-path [KEY_PATH] ``` Replace `[INSTANCE_NAME]` with the name of your SQL Read Replica instance, `[KEY_NAME]` with the name of the CMK that you want to use for encryption, and `[KEY_PATH]` with the path to the CMK. For example: ```bash theme={null} gcloud sql instances patch my-sql-replica --backup-encryption-key my-cmk --backup-encryption-key-path projects/my-project/locations/global/keyRings/my-key-ring/cryptoKeys/my-cmk ``` 3. Verify that the encryption has been enabled for the SQL Read Replica instance using the following command: ```bash theme={null} gcloud sql instances describe [INSTANCE_NAME] --format="get(settings.backupConfiguration.enabled)" ``` Replace `[INSTANCE_NAME]` with the name of your SQL Read Replica instance. The output of the above command should be `True`, indicating that encryption has been enabled for the SQL Read Replica instance using CMKs. By following the above steps, you can remediate the misconfiguration of SQL Read Replica Instances not being encrypted using Customer Managed Keys (CMKs) in GCP. To remediate this misconfiguration for GCP using Python, you can follow the below steps: 1. First, you need to ensure that you have enabled the Cloud KMS API for your GCP project. 2. Next, you need to create a new customer-managed key (CMK) in the Cloud KMS service. You can use the following Python code to do this: ```python theme={null} from google.cloud import kms_v1 from google.cloud.kms_v1 import enums def create_key(project_id, location_id, key_ring_id, key_id): client = kms_v1.KeyManagementServiceClient() parent = client.key_ring_path(project_id, location_id, key_ring_id) purpose = enums.CryptoKey.CryptoKeyPurpose.ENCRYPT_DECRYPT response = client.create_crypto_key(parent, key_id, {'purpose': purpose}) print('Created key: {}'.format(response.name)) create_key('project-id', 'global', 'my-key-ring', 'my-key-id') ``` 3. Once you have created the CMK, you can use it to encrypt the read replica instance. To do this, you can use the `google-cloud-sql` Python library. First, install the library using pip: ``` pip install google-cloud-sql ``` 4. Then, you can use the following Python code to encrypt the read replica instance: ```python theme={null} from google.cloud.sql_v1beta4 import SqlInstancesServiceClient from google.cloud.sql_v1beta4.types import SqlInstancesSetRootPasswordRequest def encrypt_instance(project_id, instance_name, cmk_path): client = SqlInstancesServiceClient() instance_path = client.instance_path(project_id, 'us-central1', instance_name) request = SqlInstancesSetRootPasswordRequest() request.instance = instance_path request.root_password = 'new-root-password' request.encryption_config = {'kind': 'sql#encryptionConfig', 'kms_key_name': cmk_path} response = client.instances_service.set_root_password(request) print('Encrypted instance: {}'.format(response.name)) encrypt_instance('project-id', 'my-instance', 'projects/project-id/locations/global/keyRings/my-key-ring/cryptoKeys/my-key-id') ``` In the above code, replace `'project-id'` with your GCP project ID, `'my-instance'` with the name of your read replica instance, and `'projects/project-id/locations/global/keyRings/my-key-ring/cryptoKeys/my-key-id'` with the path to your CMK. ```hcl theme={null} # Customer-managed key to use for the read replica disks resource "google_kms_crypto_key" "SQL_READ_REPLICA_CMEK" { name = "SQL_READ_REPLICA_CMEK_NAME" # replace with desired key name key_ring = "KMS_KEY_RING_ID" # replace with full key ring ID: projects/PROJECT_ID/locations/LOCATION/keyRings/KEY_RING_NAME purpose = "ENCRYPT_DECRYPT" rotation_period = "2592000s" # 30 days; adjust as needed } # Allow the Cloud SQL service account to use the key resource "google_kms_crypto_key_iam_binding" "sql_cmek_binding" { crypto_key_id = google_kms_crypto_key.SQL_READ_REPLICA_CMEK.id role = "roles/cloudkms.cryptoKeyEncrypterDecrypter" members = [ "serviceAccount:SQL_SERVICE_ACCOUNT_EMAIL", # replace with the Cloud SQL service account for the project ] } # Read replica instance encrypted with the CMEK resource "google_sql_database_instance" "READ_REPLICA_INSTANCE" { name = "READ_REPLICA_INSTANCE_NAME" # replace with your replica name database_version = "MYSQL_8_0" # or POSTGRES_15, SQLSERVER_2019_STANDARD, etc. region = "SQL_REGION" # e.g., us-central1 project = "PROJECT_ID" # replace with your GCP project ID master_instance_name = "PRIMARY_SQL_INSTANCE_NAME" # replace with the name of the primary instance settings { tier = "db-custom-2-8192" # replace with your desired machine type } # Enforce CMEK for the read replica disks disk_encryption_configuration { kms_key_name = google_kms_crypto_key.SQL_READ_REPLICA_CMEK.id } } ``` Note: Applying CMEK to an existing Cloud SQL instance (including read replicas) requires recreating the instance; Terraform will force replacement when you add or change `disk_encryption_configuration.kms_key_name`, so plan for potential downtime and data migration. Verification: `terraform plan` should show creation (or replacement) of the `google_kms_crypto_key` (if new), the IAM binding, and the read replica `google_sql_database_instance` with `disk_encryption_configuration.kms_key_name` set to the CMEK, and no other unrelated changes.