main.tf:
provider "google" {
project = "PROJECT_ID"
}
data "google_project" "project" {}
resource "google_container_cluster" "test-sql" {
name = "test-sql"
location = "europe-central2-a"
remove_default_node_pool = true
initial_node_count = 1
network = google_compute_network.test-sql.self_link
subnetwork = google_compute_subnetwork.test-sql.self_link
ip_allocation_policy {}
private_cluster_config {
enable_private_nodes = true
master_ipv4_cidr_block = "10.255.255.240/28"
enable_private_endpoint = true
}
master_authorized_networks_config {}
workload_identity_config {
workload_pool = "${data.google_project.project.project_id}.svc.id.goog"
}
}
resource "google_container_node_pool" "test-sql" {
name = "test-sql"
cluster = google_container_cluster.test-sql.id
location = google_container_cluster.test-sql.location
initial_node_count = 1
node_config {
service_account = google_service_account.test-sql-node.email
workload_metadata_config {
mode = "GKE_METADATA"
}
}
}
resource "google_compute_instance" "test-sql" {
name = "test-sql"
machine_type = "e2-micro"
zone = "europe-central2-a"
boot_disk {
initialize_params {
image = "debian-12"
}
}
network_interface {
subnetwork = google_compute_subnetwork.test-sql.self_link
access_config {}
}
metadata = {
enable-oslogin = true
startup-script = file("openvpn.sh")
}
}
resource "google_artifact_registry_repository" "test-sql" {
repository_id = "test-sql"
format = "DOCKER"
location = "europe-central2"
}
resource "google_compute_router" "test-sql" {
name = "test-sql"
network = google_compute_network.test-sql.name
region = google_compute_subnetwork.test-sql.region
}
resource "google_compute_router_nat" "test-sql" {
name = "test-sql"
router = google_compute_router.test-sql.name
source_subnetwork_ip_ranges_to_nat = "ALL_SUBNETWORKS_ALL_IP_RANGES"
nat_ip_allocate_option = "AUTO_ONLY"
region = google_compute_router.test-sql.region
}
resource "google_compute_network" "test-sql" {
name = "test-sql"
auto_create_subnetworks = false
}
resource "google_compute_subnetwork" "test-sql" {
name = "test-sql"
ip_cidr_range = "10.0.0.0/20"
region = "europe-central2"
network = google_compute_network.test-sql.self_link
}
resource "google_compute_firewall" "test-sql-ssh" {
name = "test-sql-ssh"
network = google_compute_network.test-sql.name
source_ranges = ["35.235.240.0/20"]
allow {
protocol = "tcp"
ports = [22]
}
}
resource "google_compute_firewall" "test-sql-vpn" {
name = "test-sql-vpn"
network = google_compute_network.test-sql.name
source_ranges = ["SOURCE_IP"]
allow {
protocol = "udp"
ports = [1194]
}
}
resource "google_service_account" "test-sql-node" {
account_id = "test-sql-node"
}
resource "google_project_iam_member" "test-sql-log-writer" {
project = data.google_project.project.project_id
role = "roles/logging.logWriter"
member = "serviceAccount:${google_service_account.test-sql-node.email}"
}
resource "google_project_iam_member" "test-sql-monitoring-metric-writer" {
project = data.google_project.project.project_id
role = "roles/monitoring.metricWriter"
member = "serviceAccount:${google_service_account.test-sql-node.email}"
}
resource "google_project_iam_member" "test-sql-monitoring-viewer" {
project = data.google_project.project.project_id
role = "roles/monitoring.viewer"
member = "serviceAccount:${google_service_account.test-sql-node.email}"
}
resource "google_project_iam_member" "test-sql-metadata-writer" {
project = data.google_project.project.project_id
role = "roles/stackdriver.resourceMetadata.writer"
member = "serviceAccount:${google_service_account.test-sql-node.email}"
}
resource "google_project_iam_member" "test-sql-autoscaling-metrics-writer" {
project = data.google_project.project.project_id
role = "roles/autoscaling.metricsWriter"
member = "serviceAccount:${google_service_account.test-sql-node.email}"
}
resource "google_artifact_registry_repository_iam_member" "test-sql" {
location = google_artifact_registry_repository.test-sql.location
repository = google_artifact_registry_repository.test-sql.name
role = "roles/artifactregistry.reader"
member = "serviceAccount:${google_service_account.test-sql-node.email}"
}
resource "google_sql_database_instance" "test-sql" {
name = "test-sql"
database_version = "POSTGRES_15"
region = "europe-central2"
settings {
tier = "db-f1-micro"
database_flags {
name = "cloudsql.iam_authentication"
value = "on"
}
# cloud sql: private ip
ip_configuration {
ipv4_enabled = false
private_network = google_compute_network.test-sql.id
}
}
depends_on = [google_service_networking_connection.test-sql]
}
resource "google_sql_database" "test-sql" {
name = "test-sql"
instance = google_sql_database_instance.test-sql.name
}
resource "google_sql_user" "test-sql" {
name = regex("(.*)\\.gserviceaccount\\.com$", google_service_account.test-sql.email)[0]
instance = google_sql_database_instance.test-sql.name
type = "CLOUD_IAM_SERVICE_ACCOUNT"
}
resource "google_service_account" "test-sql" {
account_id = "test-sql"
}
resource "google_project_iam_member" "test-sql-client" {
project = data.google_project.project.project_id
role = "roles/cloudsql.client"
member = "serviceAccount:${google_service_account.test-sql.email}"
condition {
title = google_sql_database_instance.test-sql.id
expression = "resource.name == 'projects/${data.google_project.project.project_id}/instances/${google_sql_database_instance.test-sql.id}' && resource.service == 'sqladmin.googleapis.com'"
}
}
resource "google_project_iam_member" "test-sql-instance-user" {
project = data.google_project.project.project_id
role = "roles/cloudsql.instanceUser"
member = "serviceAccount:${google_service_account.test-sql.email}"
condition {
title = google_sql_database_instance.test-sql.id
expression = "resource.name == 'projects/${data.google_project.project.project_id}/instances/${google_sql_database_instance.test-sql.id}' && resource.service == 'sqladmin.googleapis.com'"
}
}
# cloud sql: private ip
resource "google_project_service" "project" {
service = "servicenetworking.googleapis.com"
}
# cloud sql: private ip
resource "google_service_networking_connection" "test-sql" {
network = google_compute_network.test-sql.id
service = "servicenetworking.googleapis.com"
reserved_peering_ranges = [google_compute_global_address.test-sql-peering.name]
}
# cloud sql: private ip
resource "google_compute_global_address" "test-sql-peering" {
name = "test-sql-peering"
purpose = "VPC_PEERING"
address_type = "INTERNAL"
prefix_length = 16
network = google_compute_network.test-sql.id
}
data "google_client_config" "test-sql" {}
provider "kubernetes" {
host = "https://${google_container_cluster.test-sql.endpoint}"
token = data.google_client_config.test-sql.access_token
cluster_ca_certificate = base64decode(google_container_cluster.test-sql.master_auth[0].cluster_ca_certificate)
}openvpn.sh (see this gist)
k8s.tf:
resource "kubernetes_deployment" "test-sql" {
metadata {
name = "test-sql"
}
spec {
selector {
match_labels = {
app = "test-sql"
}
}
template {
metadata {
labels = {
app = "test-sql"
}
}
spec {
container {
name = "test-sql"
image = "europe-central2-docker.pkg.dev/PROJECT_ID/test-sql/test-sql"
}
container {
name = "cloud-sql-auth-proxy"
image = "gcr.io/cloud-sql-connectors/cloud-sql-proxy:2-alpine"
args = [
"--private-ip", # cloud sql: private ip
"--auto-iam-authn",
google_sql_database_instance.test-sql.connection_name]
security_context {
run_as_non_root = true
}
}
service_account_name = kubernetes_service_account.test-sql.metadata[0].name
}
}
}
}
resource "kubernetes_service_account" "test-sql" {
metadata {
name = "test-sql"
annotations = {
"iam.gke.io/gcp-service-account" = "${google_service_account.test-sql.email}"
}
}
}
resource "google_service_account_iam_member" "test-sql" {
service_account_id = google_service_account.test-sql.name
role = "roles/iam.workloadIdentityUser"
member = "serviceAccount:${data.google_project.project.project_id}.svc.id.goog[default/${kubernetes_service_account.test-sql.metadata[0].name}]"
}Dockerfile:
FROM alpine:3.19
RUN apk add build-base ruby-dev postgresql-dev wait4ports \
&& gem install pg -v 1.5.6
COPY a.sh a.sh
CMD ["sh", "a.sh"]a.sh:
set -eux
wait4ports tcp://localhost:5432
ruby -rpg -e "p PG.connect(host: 'localhost', user: 'test-sql@PROJECT_ID.iam', dbname: 'postgres')"
sleep infinity// replace PROJECT_ID, SOURCE_IP
$ docker run --rm -it \
-v "$PWD:/app" -w /app \
-v /var/run/docker.sock:/var/run/docker.sock:ro \
google/cloud-sdk:457.0.0-alpine
/app # gcloud auth login --update-adc
/app # apk add terraform
/app # mv k8s.tf k8s.tf.bak
/app # terraform init
/app # terraform apply
/app # gcloud compute ssh test-sql --project PROJECT_ID --zone europe-central2-a \
--tunnel-through-iap --command 'sudo -i cat client.conf' > client.conf
$ sudo openvpn client.conf
/app # gcloud auth configure-docker europe-central2-docker.pkg.dev
/app # docker build -t europe-central2-docker.pkg.dev/PROJECT_ID/test-sql/test-sql \
-f Dockerfile .
/app # docker push europe-central2-docker.pkg.dev/PROJECT_ID/test-sql/test-sql
/app # gcloud components install kubectl
/app # gcloud container clusters get-credentials test-sql --location europe-central2-a \
--project PROJECT_ID
/app # mv k8s.tf.bak k8s.tf
/app # terraform apply
/app # kubectl get pods
NAME READY STATUS RESTARTS AGE
test-sql-6f74c5c85c-qq6mt 2/2 Running 0 5s
/app # gcloud logging read \
'resource.labels.pod_name=test-sql-6f74c5c85c-qq6mt \
AND resource.labels.container_name:*' \
--freshness=5m \
--format 'yaml(timestamp, textPayload, resource.labels.container_name)' \
--project PROJECT_ID
---
resource:
labels:
container_name: test-sql
textPayload: + sleep infinity
timestamp: '2024-04-18T03:26:50.778835781Z'
---
resource:
labels:
container_name: cloud-sql-auth-proxy
textPayload: 2024/04/18 03:26:50 [PROJECT_ID:europe-central2:test-sql] client closed
the connection
timestamp: '2024-04-18T03:26:50.775134226Z'
---
resource:
labels:
container_name: test-sql
textPayload: '#<PG::Connection:0x0000785a05ab4d20 host=localhost port=5432 user=test-sql@PROJECT_ID.iam>'
timestamp: '2024-04-18T03:26:50.770653856Z'
---
resource:
labels:
container_name: cloud-sql-auth-proxy
textPayload: 2024/04/18 03:26:50 [PROJECT_ID:europe-central2:test-sql] Accepted connection
from 127.0.0.1:41616
timestamp: '2024-04-18T03:26:50.658348574Z'
---
resource:
labels:
container_name: cloud-sql-auth-proxy
textPayload: 2024/04/18 03:26:50 [PROJECT_ID:europe-central2:test-sql] client closed
the connection
timestamp: '2024-04-18T03:26:50.633643072Z'
---
resource:
labels:
container_name: cloud-sql-auth-proxy
textPayload: 2024/04/18 03:26:50 [PROJECT_ID:europe-central2:test-sql] Accepted connection
from 127.0.0.1:41606
timestamp: '2024-04-18T03:26:50.486472191Z'
---
resource:
labels:
container_name: test-sql
textPayload: "+ ruby -rpg -e 'p PG.connect(host: '\"'\"'localhost'\"'\"', user: '\"\
'\"'test-sql@PROJECT_ID.iam'\"'\"', dbname: '\"'\"'postgres'\"'\"')'"
timestamp: '2024-04-18T03:26:50.486004050Z'
---
resource:
labels:
container_name: test-sql
textPayload: Trying localhost:5432 (127.0.0.1:5432) ... SUCCESS!
timestamp: '2024-04-18T03:26:50.485093086Z'
---
resource:
labels:
container_name: cloud-sql-auth-proxy
textPayload: 2024/04/18 03:26:50 The proxy has started successfully and is ready for
new connections!
timestamp: '2024-04-18T03:26:50.221205563Z'
---
resource:
labels:
container_name: cloud-sql-auth-proxy
textPayload: 2024/04/18 03:26:50 [PROJECT_ID:europe-central2:test-sql] Listening
on 127.0.0.1:5432
timestamp: '2024-04-18T03:26:50.221141861Z'
---
resource:
labels:
container_name: cloud-sql-auth-proxy
textPayload: 2024/04/18 03:26:49 Authorizing with Application Default Credentials
timestamp: '2024-04-18T03:26:49.711457832Z'
---
resource:
labels:
container_name: test-sql
textPayload: Trying localhost:5432 (127.0.0.1:5432) ... failed.
timestamp: '2024-04-18T03:26:49.485034641Z'
---
resource:
labels:
container_name: test-sql
textPayload: + wait4ports tcp://localhost:5432
timestamp: '2024-04-18T03:26:49.484492428Z'