Connecting Cloud SQL in a Private VPC

Cloud SQL (private) ──▶ Cloud Function (VPC connector) ──▶ GCS bucket ──▶ BonData
                                   ▲
                            Cloud Scheduler (cron)

# ──────────────────────────────────────────────
# Variables - fill these in
# ──────────────────────────────────────────────
variable "project_id"           { description = "GCP project ID" }
variable "region"               { default = "us-central1" }
variable "vpc_network"          { description = "VPC network name (e.g. default)" }
variable "vpc_connector_cidr"   { default = "10.8.0.0/28" description = "Unused /28 CIDR for the VPC connector" }
variable "db_host"              { description = "Cloud SQL private IP" }
variable "db_port"              { default = "5432" }
variable "db_name"              { description = "Database name" }
variable "db_user"              { description = "Database user" }
variable "db_password"          { sensitive = true }
variable "tables"               { default = "public.users,public.orders" description = "Comma-separated tables" }
variable "schedule"             { default = "0 * * * *" description = "Cron schedule (default: every hour)" }
variable "bucket_name"          { default = "bondata-cloudsql-exports" }

provider "google" {
  project = var.project_id
  region  = var.region
}

# ──────────────────────────────────────────────
# Enable required APIs
# ──────────────────────────────────────────────
resource "google_project_service" "apis" {
  for_each = toset([
    "cloudfunctions.googleapis.com",
    "cloudbuild.googleapis.com",
    "cloudscheduler.googleapis.com",
    "vpcaccess.googleapis.com",
    "run.googleapis.com",
  ])
  service            = each.value
  disable_on_destroy = false
}

# ──────────────────────────────────────────────
# GCS bucket
# ──────────────────────────────────────────────
resource "google_storage_bucket" "export" {
  name                        = var.bucket_name
  location                    = var.region
  uniform_bucket_level_access = true
  public_access_prevention    = "enforced"
}

# ──────────────────────────────────────────────
# Serverless VPC Access connector
# ──────────────────────────────────────────────
resource "google_vpc_access_connector" "connector" {
  name          = "bondata-export"
  region        = var.region
  network       = var.vpc_network
  ip_cidr_range = var.vpc_connector_cidr

  depends_on = [google_project_service.apis]
}

# ──────────────────────────────────────────────
# Service account
# ──────────────────────────────────────────────
resource "google_service_account" "function" {
  account_id   = "bondata-export-fn"
  display_name = "BonData Cloud SQL Export"
}

resource "google_storage_bucket_iam_member" "writer" {
  bucket = google_storage_bucket.export.name
  role   = "roles/storage.objectCreator"
  member = "serviceAccount:${google_service_account.function.email}"
}

resource "google_project_iam_member" "log_writer" {
  project = var.project_id
  role    = "roles/logging.logWriter"
  member  = "serviceAccount:${google_service_account.function.email}"
}

# ──────────────────────────────────────────────
# Cloud Function source code
# ──────────────────────────────────────────────
data "archive_file" "function" {
  type        = "zip"
  output_path = "${path.module}/function.zip"

  source {
    content  = <<-PYTHON
import os, io, json, logging
from datetime import datetime, timezone
from google.cloud import storage
import psycopg2, pyarrow as pa, pyarrow.parquet as pq

logger = logging.getLogger(__name__)

DB = dict(host=os.environ["DB_HOST"], port=int(os.environ.get("DB_PORT","5432")),
          dbname=os.environ["DB_NAME"], user=os.environ["DB_USER"], password=os.environ["DB_PASSWORD"])
BUCKET  = os.environ["GCS_BUCKET"]
PREFIX  = os.environ.get("GCS_PREFIX", "cloudsql-exports")
TABLES  = [t.strip() for t in os.environ["TABLES"].split(",")]
CHUNK   = int(os.environ.get("CHUNK_SIZE", "50000"))
gcs = storage.Client()

def export_table(cur, table, ts):
    safe = table.replace('"','').replace('.','__')
    cur.execute(f"SELECT * FROM {table} LIMIT 0")
    cols = [d[0] for d in cur.description]
    cur.execute(f"DECLARE _c CURSOR FOR SELECT * FROM {table}")
    part, total = 0, 0
    bucket = gcs.bucket(BUCKET)
    while True:
        cur.execute(f"FETCH {CHUNK} FROM _c")
        rows = cur.fetchall()
        if not rows: break
        tbl = pa.table({c: [r[i] for r in rows] for i,c in enumerate(cols)})
        buf = io.BytesIO()
        pq.write_table(tbl, buf); buf.seek(0)
        blob = bucket.blob(f"{PREFIX}/{safe}/dt={ts}/part-{part:05d}.parquet")
        blob.upload_from_file(buf, content_type="application/octet-stream")
        total += len(rows); part += 1
    cur.execute("CLOSE _c")
    return total

def handler(request):
    ts = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H%M%SZ")
    conn = psycopg2.connect(**DB)
    try:
        conn.autocommit = False; cur = conn.cursor()
        res = {}
        for t in TABLES:
            try: res[t] = export_table(cur, t, ts)
            except Exception as e: logger.error(f"{t}: {e}"); res[t] = str(e); conn.rollback()
        conn.commit()
    finally: conn.close()
    logger.info(json.dumps(res))
    return json.dumps({"results": res}), 200
    PYTHON
    filename = "main.py"
  }

  source {
    content  = <<-REQS
functions-framework==3.*
psycopg2-binary==2.9.9
pyarrow==15.0.0
google-cloud-storage==2.*
    REQS
    filename = "requirements.txt"
  }
}

resource "google_storage_bucket" "source" {
  name                        = "${var.bucket_name}-fn-source"
  location                    = var.region
  uniform_bucket_level_access = true
}

resource "google_storage_bucket_object" "source" {
  name   = "function-${data.archive_file.function.output_md5}.zip"
  bucket = google_storage_bucket.source.name
  source = data.archive_file.function.output_path
}

# ──────────────────────────────────────────────
# Cloud Function (2nd gen)
# ──────────────────────────────────────────────
resource "google_cloudfunctions2_function" "export" {
  name     = "bondata-cloudsql-export"
  location = var.region

  build_config {
    runtime     = "python312"
    entry_point = "handler"
    source {
      storage_source {
        bucket = google_storage_bucket.source.name
        object = google_storage_bucket_object.source.name
      }
    }
  }

  service_config {
    max_instance_count    = 1
    available_memory      = "512Mi"
    timeout_seconds       = 300
    service_account_email = google_service_account.function.email

    vpc_connector                 = google_vpc_access_connector.connector.id
    vpc_connector_egress_settings = "PRIVATE_RANGES_ONLY"

    environment_variables = {
      DB_HOST    = var.db_host
      DB_PORT    = var.db_port
      DB_NAME    = var.db_name
      DB_USER    = var.db_user
      DB_PASSWORD = var.db_password
      GCS_BUCKET = google_storage_bucket.export.name
      TABLES     = var.tables
    }
  }

  depends_on = [google_project_service.apis]
}

# ──────────────────────────────────────────────
# Cloud Scheduler (cron trigger)
# ──────────────────────────────────────────────
resource "google_service_account" "scheduler" {
  account_id   = "bondata-export-scheduler"
  display_name = "BonData Export Scheduler"
}

resource "google_cloudfunctions2_function_iam_member" "invoker" {
  project        = var.project_id
  location       = var.region
  cloud_function = google_cloudfunctions2_function.export.name
  role           = "roles/cloudfunctions.invoker"
  member         = "serviceAccount:${google_service_account.scheduler.email}"
}

resource "google_cloud_run_service_iam_member" "invoker" {
  project  = var.project_id
  location = var.region
  service  = google_cloudfunctions2_function.export.name
  role     = "roles/run.invoker"
  member   = "serviceAccount:${google_service_account.scheduler.email}"
}

resource "google_cloud_scheduler_job" "trigger" {
  name     = "bondata-cloudsql-export"
  region   = var.region
  schedule = var.schedule

  http_target {
    uri         = google_cloudfunctions2_function.export.url
    http_method = "POST"
    oidc_token {
      service_account_email = google_service_account.scheduler.email
    }
  }

  depends_on = [google_project_service.apis]
}

# ──────────────────────────────────────────────
# Outputs
# ──────────────────────────────────────────────
output "bucket"        { value = google_storage_bucket.export.name }
output "function_url"  { value = google_cloudfunctions2_function.export.url }

terraform init
terraform apply \
  -var="project_id=my-gcp-project" \
  -var="vpc_network=default" \
  -var="db_host=10.0.0.3" \
  -var="db_name=production" \
  -var="db_user=bondata_user" \
  -var="db_password=CHANGEME" \
  -var="tables=public.users,public.orders"

┌──────────────────────────────────────────────┐
│               Your GCP VPC                   │
│                                              │
│  ┌───────────┐       ┌────────────────┐     │
│  │ Cloud SQL │◀──────│ BonData Tunnel │─────┼──▶ BonData Cloud (port 443 outbound)
│  │ (private) │       │    Agent       │     │
│  └───────────┘       └────────────────┘     │
│                                              │
└──────────────────────────────────────────────┘