Securing sensitive data in Cloud SQL
Tags: googlecloud, cloudsql, terraform, mysql
Published: 2021-06-23 12:07:29

In the [previous part][part-3] 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fgba1px5olb9on4tplm6.png)

# 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`

```terraform
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

```terraform
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:

```terraform
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`:

```terraform
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:

```shell
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

```shell
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

![Cloud SQL Overview](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gn657kl2amg849voshft.png)![Cloud SQL Connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nwl71aoyobx06g3s3y7h.png)![Cloud SQL User](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4ieb6o439ijjl7xe5d7q.png)![Cloud SQL Database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2cm8z5d774d8b765tlo9.png)

Peering connection

![Peering connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cmfnl2zcso7w3kyc3h2l.png)

Private connection

![Allocated IP ranges for services](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bzqwrnnel4649dnzfpnr.png)![Private connection to services](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eom3xqwjknmkdfszlq2p.png)

# Conclusion

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

[part-3]: https://dev.to/stack-labs/creating-a-google-kubernetes-engine-autopilot-cluster-using-terraform-1l22
[part-4]: https://dev.to/stack-labs/securing-the-connectivity-between-a-gke-application-and-a-cloud-sql-database-4d6b