GCP CloudSQL Setup

[zking2000]

Preparation

PROJECT_ID="causal-hour-418204"
PRIVATE_NETWORK_NAME="private-network"

Assign IAM policy for cloud-sql-access service account
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:cloud-sql-access@$PROJECT_ID.iam.gserviceaccount.com" \
    --role="roles/cloudsql.client"

gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:cloud-sql-access@$PROJECT_ID.iam.gserviceaccount.com" \
    --role="roles/cloudsql.instanceUser"

# setup VPC Peering
PRIVATE_NETWORK_ID=$(gcloud compute networks describe $PRIVATE_NETWORK_NAME --format="value(name)")
PRIVATE_IP_ADDRESS_NAME=$(gcloud compute addresses list --format="value(name)")

gcloud services vpc-peerings connect \
  --network=$PRIVATE_NETWORK_ID \
  --service=servicenetworking.googleapis.com \
  --ranges=$PRIVATE_IP_ADDRESS_NAME \
  --project=$PROJECT_ID

Create CloudSQL Instance and Proxy

provider "google" {
  credentials = file("./sa.json")
  project     = "${var.project}"
  region      = "${var.region}"
}

resource "google_compute_network" "private_network" {
  name                    = "private-network"
  auto_create_subnetworks = false
}

resource "google_compute_subnetwork" "private_subnet" {
  name          = "private-subnet"
  ip_cidr_range = "10.0.0.0/16"
  network       = google_compute_network.private_network.id
  region        = var.region
}

resource "google_project_service" "service_networking" {
  service = "servicenetworking.googleapis.com"
}

resource "google_compute_global_address" "private_ip_address" {
  name          = "private-ip-address"
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"
  prefix_length = 16
  network       = google_compute_network.private_network.name
}

# resource "google_service_networking_connection" "private_vpc_connection" {
#   network                 = google_compute_network.private_network.name
#   service                 = "servicenetworking.googleapis.com"
#   reserved_peering_ranges = [google_compute_global_address.private_ip_address.name]

#   depends_on = [google_project_service.service_networking]
# }

resource "google_sql_database_instance" "private_instance" {
  name             = "private-postgres-instance"
  database_version = "POSTGRES_16"
  region           = "${var.region}"

  # depends_on = [google_service_networking_connection.private_vpc_connection]

  settings {
    tier = "db-custom-1-3840"
    activation_policy = "ALWAYS"

    ip_configuration {
      ipv4_enabled = false
      private_network = google_compute_network.private_network.self_link
    }

    disk_autoresize = true

    backup_configuration {
      enabled = true
    }

    availability_type = "REGIONAL"

    location_preference {
      zone = "${var.region}-a"
    }
    database_flags {
      name  = "pgaudit.log"
      value = "all"
    }

    database_flags {
      name  = "cloudsql.iam_authentication"
      value = "on"
    }

    database_flags {
      name  = "max_connections"
      value = "1000"
    }
  }

  deletion_protection = false
}

resource "google_sql_database" "database" {
  name     = "my-database"
  instance = google_sql_database_instance.private_instance.name
  charset = "UTF8"
  collation = "en_US.UTF8"
}

resource "google_service_account" "sql_service_account" {
  account_id   = "cloud-sql-access"
  display_name = "Cloud SQL Access Service Account"
}

# resource "google_project_iam_member" "sql_access_iam" {
#   project = "${var.project}"
#   role    = "roles/cloudsql.client"
#   member  = "serviceAccount:${google_service_account.sql_service_account.email}"

#   depends_on = [ google_service_account.sql_service_account ]
# }

resource "google_compute_instance" "proxy_instance" {
  name         = "cloud-sql-proxy"
  machine_type = "n1-standard-1"
  zone         = "${var.region}-a"

  boot_disk {
    initialize_params {
      image = "ubuntu-os-cloud/ubuntu-2004-lts"
    }
  }

  network_interface {
    network = google_compute_network.private_network.self_link
    subnetwork = google_compute_subnetwork.private_subnet.self_link
    access_config {}
  }

  metadata = {
    enable-oslogin = "TRUE"
  }

  metadata_startup_script = <<-EOF
    #!/bin/bash
    wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
    chmod +x cloud_sql_proxy
    ./cloud_sql_proxy -enable_iam_login -instances=${google_sql_database_instance.private_instance.connection_name}=tcp:5432
  EOF

  service_account {
    email  = google_service_account.sql_service_account.email
    scopes = ["https://www.googleapis.com/auth/cloud-platform"]
  }

  allow_stopping_for_update = true

  tags = ["cloud-sql-proxy"]
}

resource "google_compute_firewall" "allow_proxy_and_ssh_access" {
  name    = "allow-proxy-and-ssh-access"
  network = google_compute_network.private_network.self_link

  allow {
    protocol = "tcp"
    ports    = ["5432", "22"]
  }

  source_ranges = ["0.0.0.0/0"]
  target_tags   = ["cloud-sql-proxy"]
}

variable "project" {
  default = "causal-hour-418204"
  type = string
}

variable "region" {
  type = string
  description = "(optional) describe your variable"
  default = "asia-east1"
}

Constraint: Some commands unable to complete through terraform. So it also need gcloud as compensation

[zking2000]

Assign user with full control to a DB

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO nextclouduser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO nextclouduser;

GRANT ALL PRIVILEGES ON DATABASE "nextclouddb-stable" TO nextclouduser;
ALTER DEFAULT PRIVILEGES FOR USER nextclouduser IN SCHEMA public GRANT ALL ON TABLES TO nextclouduser;
ALTER DEFAULT PRIVILEGES FOR USER nextclouduser IN SCHEMA public GRANT ALL ON FUNCTIONS TO nextclouduser;