Cloud SQL with Private IP & Cloud SQL Auth proxy using Terraform IAAC in Google Cloud

Introduction

While relational databases still offer basic authentication, many of our clients are realizing that this isn’t sufficient or secure enough in today’s complex cloud environments. Some of the commonly stated complaints are lack of two-factor authentication, easier susceptibility to leaked passwords, as well as missing credential rotation capabilities. In order to simplify and secure authorization and authentication into database instances, Google Cloud has developed IAM Database Authentication that supports basic and IAM authentication. Along with this capability, Google released Cloud SQL Auth proxy to provide a secure path for clients to log into their database while eliminating other hurdles.

What is Cloud SQL Auth proxy?

Cloud SQL Auth proxy is used to create secure connections to Cloud SQL database instances. With Cloud SQL Auth proxy, you can specify a database instance connection string and it will handle secure connectivity to the instance for you. The proxy will use automatically-managed short-lived SSL certificates that will ensure that your connectivity is authorized and encrypted. The proxy will then allow you to use Identity and Access Management (IAM) authentication to log into the database by generating an OAuth 2.0 token for your Cloud Identity.

With Cloud SQL Auth proxy, you do not have to worry much about setting up authorized networks, managing certificates, and in some cases, worrying about rotating user credentials. Think of it as a secure way to access your Cloud SQL database instances.

How the proxy works

Cloud SQL Auth proxy is a binary that you run on your local client machine. The proxy initiates a connection using a secure tunnel (TLS with 128-bit cipher) to the proxy service running in Cloud SQL. This server-side proxy service then connects to your SQL instance on the outgoing port tcp/3307. If your resource is behind an outgoing firewall rule, ensure that this rule allows tcp/3307 to your Cloud SQL instance.

Cloud SQL service , the managed database service on Google Cloud, allows you to:

• Set a?private IP on your instance ?and to connect it directly to the VPC of your choice
• Remove the?public IP from your instance

Thanks to these features,?you can enforce the security team requirements.

But, is it a problem when we work day-to-day to not have public IP on Cloud SQL instances?

Let’s check this over 3 use cases:

1. Compute Engine?connectivity
2. Serverless services?connectivity
3. Local environment?connectivity

Cloud SQL proxy binary

Before going deeper into the use cases, I would like to perform a quick focus on the main feature of?Cloud SQL proxy

• This binary opens a?secure and end-to-end encrypted tunnel. In summary, even if your database don’t have SSL certificate, the data are encrypted in transit.
• Before opening the tunnel, the binary?checks against the IAM service API if the current credential is authorized to access the Cloud SQL instance. This is an additional layer of security, in addition to the standard database authentication by user/password.
• The tunnel can be open on a local port (TCP connection mode) or to a Unix socket (not possible on Windows environment)

Preparation:

Step 1 — Install Terraform

wget -O- https://apt.releases.hashicorp.com/gpg | sudo gpg --dearmor -o /usr/share/keyrings/hashicorp-archive-keyring.gpg

$ echo "deb [signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com $(lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/hashicorp.list

$ sudo apt update && sudo apt install terraform        

Link:

https://developer.hashicorp.com/terraform/downloads?ajs_aid=1938538f-202c-4b2c-9d6c-40ba776ebaab&product_intent=terraform

Step 2 — Create terraform .tf Files for Project

c1-versions.tf

terraform {
? required_providers {
? ? google = {
? ? ? source = "hashicorp/google"
? ? ? version = "4.68.0"
? ? }
? }
}


provider "google" {
? # Configuration options
? project = "terraform-project"
? region ?= "us-central1"
? zone ? ?= "us-central1-b" 
}        

c2-serviceaccount.tf

resource "google_service_account" "cloudsql-sa"{ 
? account_id ? = "cloudsql-sa"
? display_name = "Service Account for Cloud SQL"
}


resource "google_project_iam_member" "member-role" {
? for_each = toset([
? ? ?"roles/cloudsql.client",
? ? ?"roles/cloudsql.editor",
? ? ?"roles/cloudsql.admin",
? ? ?"roles/resourcemanager.projectIamAdmin"
? ]) 
? role = each.key
? project = "terraform-project-2277166"
? member = "serviceAccount:${google_service_account.cloudsql-sa.email}"
}


resource "google_service_account_key" "mykey" {
? service_account_id = google_service_account.cloudsql-sa.name
? public_key_type ? ?= "TYPE_X509_PEM_FILE"
}


resource "local_file" "sa_json_file" {
? content ?= base64decode(google_service_account_key.mykey.private_key)
? filename = "${path.module}/cloudsql-sa-key.json"


}        

c3-network.tf

resource "google_compute_network" "nw1-vpc"{ 
? name ? ? ? ? ? ? ? ? ? ?= "nw1-vpc"
? auto_create_subnetworks = false
? mtu ? ? ? ? ? ? ? ? ? ? = 1460
}


resource "google_compute_subnetwork" "nw1-subnet1" {
? name = "nw1-vpc-sub1-us-central1"
? network = google_compute_network.nw1-vpc.id
? ip_cidr_range = "10.10.1.0/24"
? region = "us-central1"
? private_ip_google_access = true


}


resource "google_compute_subnetwork" "nw1-subnet2" {
? name = "nw2-vpc-sub3-euro-west2"
? network = google_compute_network.nw1-vpc.id
? ip_cidr_range = "10.10.2.0/24"
? region = "europe-west2"
? private_ip_google_access = true


}


resource "google_compute_firewall" "nw1-ssh-icmp-allow" {
? name = "nw1-vpc-ssh-allow"
? network = google_compute_network.nw1-vpc.id
? allow {
? ? protocol = "icmp"
? }
? allow {
? ? protocol = "tcp"
? ? ports ? ?= ["22"]
? }
? source_ranges = ["39.33.11.48/32"]
? target_tags = ["nw1-vpc-ssh-allow"]
? priority = 1000
}



resource "google_compute_firewall" "nw1-internal-allow" {
? name = "nw1-vpc-internal-allow"
? network = google_compute_network.nw1-vpc.id
? 
? allow {
? ? protocol = "icmp"
? }


? allow {
? ? protocol = "udp"
? ? ports ? ?= ["0-65535"]


? }
? allow {
? ? protocol = "tcp"
? ? ports ? ?= ["0-65535"]
? }
? source_ranges = ["10.10.0.0/16"]
? priority = 1100
}


resource "google_compute_firewall" "nw1-iap-allow" {
? name = "nw1-vpc-iap-allow"
? network = google_compute_network.nw1-vpc.id
? 
? allow {
? ? protocol = "icmp"
? }


? allow {
? ? protocol = "tcp"
? ? ports ? ?= ["0-65535"]
? }
? source_ranges = ["35.235.240.0/20"]
? priority = 1200
}



resource "google_compute_address" "natpip" {
? name = "ipv4-address"
? region ?= "europe-west2"
}


resource "google_compute_router" "router1" {
? name ? ?= "nat-router1"
? region ?= "europe-west2"
? network = google_compute_network.nw1-vpc.id


? bgp {
? ? asn = 64514
? }
}


resource "google_compute_router_nat" "nat1" {
? name ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? = "natgw1"
? router ? ? ? ? ? ? ? ? ? ? ? ? ? ? = google_compute_router.router1.name
? region ? ? ? ? ? ? ? ? ? ? ? ? ? ? = "europe-west2"
? nat_ip_allocate_option ? ? ? ? ? ? = "MANUAL_ONLY"
? nat_ips ? ? ? ? ? ? ? ? ? ? ? = [google_compute_address.natpip.self_link]
? source_subnetwork_ip_ranges_to_nat = "ALL_SUBNETWORKS_ALL_IP_RANGES"
? min_ports_per_vm ? ? ? ? ? ? ? ? ? = 256
? max_ports_per_vm ? ? ? ? ? ? ? ? ? = 512


? log_config {
? ? enable = true
? ? filter = "ERRORS_ONLY"
? }
}



resource "google_compute_global_address" "private_ip_address" {
? name ? ? ? ? ?= google_compute_network.nw1-vpc.name
? purpose ? ? ? = "VPC_PEERING"
? address_type ?= "INTERNAL"
? prefix_length = 16
? network ? ? ? = google_compute_network.nw1-vpc.name
? 
}


resource "google_service_networking_connection" "private_vpc_connection" {


? network ? ? ? ? ? ? ? ? = google_compute_network.nw1-vpc.id
? service ? ? ? ? ? ? ? ? = "servicenetworking.googleapis.com"
? reserved_peering_ranges = [google_compute_global_address.private_ip_address.name]


}        

c4-compute.tf

resource "google_compute_instance" "private-vm" {
? name = "private-vm"
? zone = "europe-west2-b"
? machine_type = "e2-medium"


? allow_stopping_for_update = true


? network_interface {
? ? #network = "custom_vpc_network"
? ? subnetwork = google_compute_subnetwork.nw1-subnet2.id
? ? #access_config {}
? }


? ? boot_disk {
? ? initialize_params {
? ? ? image = "ubuntu-2204-jammy-v20230606"
? ? ? size = 20
? ? ? 
? ? }


? ? }


? ? service_account {
? ? # Google recommends custom service accounts that have cloud-platform scope and permissions granted via IAM Roles.
? ? ? email ?= "[email protected]"
? ? ? scopes = ["cloud-platform"]
? }


? ? } 


? resource "google_compute_project_metadata" "my_ssh_key" {
? metadata = {
? ? ssh-keys = <<EOF
? ? ? gcp-user:ssh-rsa AAAAB3NzaC1yc2ggjgjjssiureekFuKAwRR8shshssaB9ehT27MQiTRwK5uBuX4oAT6mggDfFwxC86AmLoS20vuUpFtacw0rc2U8bRLxKlxzIZIo9+8MI5D7RW35vXdKM3AvUBVdFrbnLYmQwi9wiY5fpTj6QPh2YUp1FycbOpAkG4C6OhATRM0pIbD2zE/qNRwR1SkL9a6UCr1ihuZZgf03RV5GP7/dXf4J1yevd6JlMC3jIYs529wyS+7FOecSteCEulzf8JB8AyiqsFo4hJIfpsnhK3Ruf3xTBcaPfnQDJtCUEryXQyorW9HTq2Y6LrCRC7u708er94wgvmgCXcOsVhy2/fhZsqcWxy2DPETjlHV+ZY5P5C9o/Fbu5cvmbd44Q/3nzYjkqMFBTrouDA5Tb72kx6CTLkl/qf7SzR+WcVGjikVtVxUFnn5dJHmcn785W2Af/LacWxtZ4veWWe00ccfv/FC0HiD0xUeHGxGGQJOeaC+oJaOj/h6EsznASx5cn7VI90rtEdUSkvMTQLSkiRN06Y/fg5HyeHIjNTojbRgwuOYLxkZUGVDzkuwlaxCkporVoLuiR4XupBdBcKwyiIkM4UhTwcgMhm8trmAT6A9hMhEn4N7bz68ShOUVjwnAXCE6TWOVN7rjrInUPxyS1HTSTF33ZxLL9MSPPs1D291pxvJ6QvPJQ== gcp-user
? ? EOF
? }
} ? 


?
        

c5-cloudsql.tf

resource "google_sql_database_instance" "mysql-from-tf"{
? name = "cloud-mysql"
? region = "us-central1"
? deletion_protection = false
? database_version = "MYSQL_8_0"
? depends_on ? ? ? = [google_service_networking_connection.private_vpc_connection]
? 
? settings {
? ? tier = "db-n1-standard-1"
? ? availability_type = "REGIONAL"
? ? #tier = "db-custom-2-6144"
? ? disk_size = 20
? ? disk_type = "PD_SSD"


? ? ?backup_configuration {


? ? ? binary_log_enabled = true
? ? ? ? ? ? ? ? ?enabled = true
? ? ? ? 
? ? } 



? ? ip_configuration {


? ? ? ipv4_enabled ? ?= false
? ? ? private_network = google_compute_network.nw1-vpc.self_link
? ? ? ?
? ? } 
? ?
? }


}


resource "google_sql_database" "database" {
name = "quickstart_db"
instance = "${google_sql_database_instance.mysql-from-tf.name}"
charset = "utf8"
collation = "utf8_general_ci"
}


resource "google_sql_user" "users" {
? name = "root"
? password = "Abcd1234"
? host = "%"
? instance = "${google_sql_database_instance.mysql-from-tf.name}"
} ?        

c6-outputs.tf

output "private_vm_ip"{
? value = google_compute_instance.private-vm.network_interface.0.network_ip
}


output "private_ip_address_cloudsql" {
? ? value ? ? ? = google_sql_database_instance.mysql-from-tf.private_ip_address
? ? description = "The private IP address of the newly created My SQL"
} 


output "natgw_public_ip_address" {
? ? value ? ? ? = google_compute_address.natpip.address
? ? description = "The public IP address of the newly created Nat Gateway"
}        

Step 3 — Run These Commands

terraform init
terraform validate
terraform plan
terraform apply or terraform apply -auto-approve        

output:

Step 4 — See Resources in Google Cloud

Step 5 — Install SQL Auth Proxy on Private VM

curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.3.0/cloud-sql-proxy.linux.amd6
chmod +x cloud-sql-proxy4
        

Step 6 — Execute Script on Private VM

./cloud-sql-proxy --address 0.0.0.0? --port 3306? terraform-project-227766:us-central1:cloud-mysql --private-ip --credentials-file cloudsql-sa-key.json        

Step 7 — Execute IAP on Personal Laptop

You have to configure credentials using gcloud(gcloud init) on personal laptop

gcloud compute start-iap-tunnel private-vm 3306 --zone=europe-west2-b --local-host-port=localhost:3306        

authproxy-client = VM name?(private-vm)

vm location --zone=europe-west2-b

Step 8 — Connect Private Cloud SQL from Local machine

Connect from MySQL Workbench