DEV Community

Cover image for Securing sensitive data in Cloud SQL
Onepoint x Stack Labs profile image Chabane R.
Chabane R. for Onepoint x Stack Labs

Posted on • Edited on

Securing sensitive data in Cloud SQL

#googlecloud #cloudsql #terraform #mysql

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

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 }
Enter fullscreen mode Exit fullscreen mode

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}" }
Enter fullscreen mode Exit fullscreen mode

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 ] }
Enter fullscreen mode Exit fullscreen mode

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" }
Enter fullscreen mode Exit fullscreen mode

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=-
Enter fullscreen mode Exit fullscreen mode

Let's deploy our Cloud SQL instance

cd infra/plan gcloud services enable sqladmin.googleapis.com --project $PROJECT_ID terraform apply
Enter fullscreen mode Exit fullscreen mode

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

Cloud SQL instance

Cloud SQL OverviewCloud SQL ConnectionCloud SQL UserCloud SQL Database

Peering connection

Peering connection

Private connection

Allocated IP ranges for servicesPrivate connection to services

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.

Top comments (0)