Securing sensitive data in Cloud SQL

In the previous part we created our GKE Autopilot cluster. In this part we will configure the Cloud SQL Instance.

The following resources will be created:

• A highly available private Cloud SQL MySQL Instance
• A database and a user
• An automatic internal IP range for private connection
• A private connection to GCP services

Cloud SQL

• The Cloud SQL Instance used is a MySQL database server
• The Multiples zones option is enabled to ensure high-availability
• The Instance is not publicly accessible and it's reachable only using its private IP
• The authentication is done via IAM
• Automated backup is enabled
• We create a database and a user for later

Create a terraform file infra/plan/cloud-sql.tf

resource "random_string" "db_name_suffix" {
  length  = 4
  special = false
  upper   = false
}

resource "google_sql_database_instance" "mysql" {

  # Instance info
  name             = "mysql-private-${random_string.db_name_suffix.result}"
  region           = var.region
  database_version = var.mysql_database_version

  settings {

    # Region and zonal availability
    availability_type = var.mysql_availability_type
    location_preference {
      zone = var.mysql_location_preference
    }

    # Machine Type
    tier              = var.mysql_machine_type

    # Storage
    disk_size         = var.mysql_default_disk_size

    # Connections
    ip_configuration {
      ipv4_enabled        = false
      private_network     = google_compute_network.custom.id
    }

    # Backups
    backup_configuration {
      binary_log_enabled = true
      enabled = true
      start_time = "06:00"
    }
  }
  depends_on = [
    google_service_networking_connection.private-vpc-connection
  ]
}

data "google_secret_manager_secret_version" "wordpress-admin-user-password" {
  secret = "wordpress-admin-user-password"
}

resource "google_sql_database" "wordpress" {
  name     = "wordpress"
  instance = google_sql_database_instance.mysql.name
}

resource "google_sql_user" "wordpress" {
  name = "wordpress"
  instance = google_sql_database_instance.mysql.name
  password = data.google_secret_manager_secret_version.wordpress-admin-user-password.secret_data
}

Add the following outputs

output "cloud-sql-connection-name" {
  value = google_sql_database_instance.mysql.connection_name
}

output "cloud-sql-instance-name" {
  value = "mysql-private-${random_string.db_name_suffix.result}"
}

Private connection

We need to configure private services access to allocate an IP address range and create a private service connection. This will allow resources in the Web subnet to connect to the Cloud SQL instance.

Complete the file infra/plan/vpc.tf with the following resources:

resource "google_compute_global_address" "private-ip-peering" {
  name          = "google-managed-services-custom"
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"
  prefix_length = 24
  network       = google_compute_network.custom.id
}

resource "google_service_networking_connection" "private-vpc-connection" {
  network = google_compute_network.custom.id
  service = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [
    google_compute_global_address.private-ip-peering.name
  ]
}

Complete the file infra/plan/variable.tf:

variable "region" {
  type = string
  default = "europe-west1"
}

variable "mysql_location_preference" {
  type = string
  default = "europe-west1-b"
}

variable "mysql_machine_type" {
  type = string
  default = "db-n1-standard-2"
}

variable "mysql_database_version" {
  type = string
  default = "MYSQL_8_0"
}

variable "mysql_default_disk_size" {
  type = string
  default = "100"
}

variable "mysql_availability_type" {
  type = string
  default = "REGIONAL"
}

Before applying the changes, we need to create the secret of the user password:

gcloud services enable secretmanager.googleapis.com --project $PROJECT_ID

gcloud beta secrets create wordpress-admin-user-password --locations $REGION --replication-policy user-managed
echo -n "changeme" | gcloud beta secrets versions add wordpress-admin-user-password --data-file=-

Let's deploy our Cloud SQL instance

cd infra/plan

gcloud services enable sqladmin.googleapis.com --project $PROJECT_ID

terraform apply

Let's check if all the resources have been created and are working correctly:

Cloud SQL instance

Peering connection

Private connection

Conclusion

Our Cloud SQL instance is now available. In the last part, we'll establish a connection between a container deployed in GKE cluster and a database created in an Cloud SQL instance.