Deploying Cloud SQL for PostgreSQL with Terraform

Cloud SQL for PostgreSQL

https://cloud.google.com/sql/postgresql?hl=ja

Summary:
A fully managed PostgreSQL service provided by GCP that simplifies database setup, maintenance, and management.
Features:

• High Availability: Provides automatic failover functionality using primary and standby instances.
• Scalability: Allows creation of read-only replicas to distribute read loads.
• Security: Features security such as data encryption, IAM integration, and SSL/TLS connections.
• Version Support: Supports PostgreSQL from 9.6 to the latest version.

Prerequisites

1. Terraform is installed.
2. A GCP project is created, and appropriate APIs are enabled.
3. Service account credentials are configured.

Operating Environment

terraform {
  required_version = "1.9.8"
  required_providers {
    google = {
      source = "hashicorp/google"
      version = "6.11.0"
    }
  }
  backend "gcs" {}
}

Simple Terraform Template

👇 I'll have ChatGPT generate a minimal template and delve into the parts that appear to be key points.

provider "google" {
  project = "<YOUR_PROJECT_ID>"  # Enter your GCP project ID here
  region  = "us-central1"         # Region to create the instance
}

resource "google_sql_database_instance" "postgres_instance" {
  name             = "my-postgres-instance"  # Cloud SQL instance name
  database_version = "POSTGRES_14"           # PostgreSQL version to use
  region           = "us-central1"           # Region

  settings {
    tier = "db-f1-micro"  # Machine type (using minimum configuration within free tier)
  }
}

resource "google_sql_database" "default_db" {
  name     = "defaultdb"                               # Database name
  instance = google_sql_database_instance.postgres_instance.name
}

resource "google_sql_user" "default_user" {
  name     = "myuser"                                  # User name
  password = "mypassword"                              # Password
  instance = google_sql_database_instance.postgres_instance.name
}

Supported PostgreSQL Versions

https://cloud.google.com/sql/docs/postgres/db-versions?hl=ja#database-version-support

In this guide, we will ultimately configure it to use PostgreSQL 16.

First, let's look at the database_version section. The versions available at this time are as follows 👇.

• As of December 2024

The Terraform documentation is as follows:

https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#database_version-1

Also, it seems that specifying POSTGRES_16 defaults the Cloud SQL edition to the Enterprise Plus edition...

Note: If the database version of the instance is PostgreSQL 16 or later, the default Cloud SQL edition is Enterprise Plus. If the database version is earlier than PostgreSQL 16, the default edition is Enterprise.

https://cloud.google.com/sql/docs/postgres/create-instance?hl=ja

You need to explicitly specify the Enterprise edition.

  settings {
    tier      = "${var.tier}"
    edition   = "ENTERPRISE"
    ...
  }

https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#edition-1

Refer here for the differences between editions 👇

https://cloud.google.com/sql/docs/postgres/editions-intro?hl=ja#edition-features

Machine Types

https://cloud.google.com/sql/pricing?hl=ja

• Shared-core machine types
  • db-f1-micro: For lightweight workloads requiring minimal resources.
  • db-g1-small: Provides more resources than db-f1-micro and is suitable for light workloads.
• Custom machine types
  • Specified in the format db-custom-<vCPU count>-<Memory amount (MB)>
  • Example:
    • To create an instance with 2 vCPUs and 7.5 GB (7,680 MB) of memory, specify tier = "db-custom-2-7680".

The Terraform documentation is as follows:

https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#tier-1

In Case of Private IP

Next, let's look at the settings for building Cloud SQL for PostgreSQL within a Private IP network and assuming an SSL connection.

  settings {
    tier      = "${var.tier}"
    edition   = "ENTERPRISE"
    ...
    ip_configuration {
      private_network = "..."
      allocated_ip_range = "..."
    }
  }

First, configure the private_network and allocated_ip_range to be used.

Regarding private_network, refer to 👇

https://cloud.google.com/sql/docs/postgres/connect-overview?hl=ja#private_ip

And for allocated_ip_range, refer to 👇

https://cloud.google.com/sql/docs/postgres/private-ip?hl=ja#allocated_ip_address_ranges

Next, let's examine settings > ip_configuration > ssl_mode.

ssl_mode

Specifies how SSL connections are enforced for database connections. Supported values are ALLOW_UNENCRYPTED_AND_ENCRYPTED, ENCRYPTED_ONLY, and TRUSTED_CLIENT_CERTIFICATE_REQUIRED (not supported for SQL Server). For details, refer to the API reference documentation.

https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#ssl_mode-1

This corresponds to Manage SSL mode under Cloud SQL > Target Instance > Connections > Security in the GCP Console.

Example)

  settings {
    tier      = "${var.tier}"
    edition   = "ENTERPRISE"
    ...
    ip_configuration {
      ssl_mode        = "TRUSTED_CLIENT_CERTIFICATE_REQUIRED"
    }
  }

Access from a Bastion Server after Environment Construction

Finally, let's look at how to access Cloud SQL for PostgreSQL built with a Private IP from a bastion server. We will proceed with a method using the Cloud SQL Auth Proxy for the connection.

https://cloud.google.com/sql/docs/mysql/connect-auth-proxy?hl=ja

About Cloud SQL Auth Proxy

https://cloud.google.com/sql/docs/mysql/sql-proxy?hl=ja

Cloud SQL Auth Proxy is a tool that provides secure and easy access to your Google Cloud SQL instances. By leveraging IAM authentication, it allows for secure connections without needing passwords or IP restrictions. Furthermore, it eliminates the need to manage connection settings or SSL certificates, enabling consistent connectivity even from local development environments. The proxy manages communication between your application and Cloud SQL via the Google Cloud API, reducing complexity. After installation, you can start the proxy with a simple command and connect your application via localhost.

• Installation on the bastion server

$ curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.13.0/cloud-sql-proxy.linux.amd64
$ chmod +x cloud-sql-proxy

• Reference the targeted *INSTANCE_CONNECTION_NAME*

  • You can check this by going to the Cloud SQL page > Overview > Target Instance > Connection to this instance > Connection name
    

• Connection command

  ./cloud-sql-proxy --port 5432 --private-ip ***INSTANCE_CONNECTION_NAME***
  

  👇 By running this command, the proxy becomes active, allowing you to connect via localhost!

Reference URLs

https://zenn.dev/monicle/articles/e03a329c021873

https://zenn.dev/monicle/articles/d98a8c269c9839