LAB05_GCP_Cloud SQL: Provisioning a PostgreSQL Managed Database

Cloud SQL: Provisioning a PostgreSQL Managed Database

☁ GCP 🟢 Beginner Cloud SQL PostgreSQL Random Provider

Scenario

Your application needs a relational database. Instead of managing VMs and database software manually, you will use Cloud SQL, Google's fully managed relational database service. You need to deploy a PostgreSQL instance, create a database within it, and provision a user with a dynamically generated secure password.

Your Objectives
  • Use the random_password provider to generate a secure random password for the database user.
  • Create a google_sql_database_instance running PostgreSQL 15 on a db-f1-micro tier (to save costs for dev scenarios).
  • Create a google_sql_database inside the instance.
  • Create a google_sql_user using the generated password.
  • Output the instance connection name and the generated password (marked as sensitive).

Additional Context

Deletion Protection: Cloud SQL instances have deletion_protection = true by default in Terraform to prevent destructive accidents. To destroy this lab later, you must explicitly set it to false within your terraform configuration.

Cloud SQL identifiers (names) cannot be reused immediately after deletion (they have a 7-day cooldown period unless you recreate them in the exact same format). Appending a random suffix to instance names is a common best practice to avoid conflicts when destroying/recreating.

variables.tf

variable "project_id" { type = string }
variable "gcp_region" { type = string; default = "us-central1" }

main.tf

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

# Cloud SQL instances require globally unique names, random suffix helps.
resource "random_id" "db_name_suffix" {
  byte_length = 4
}

# Generate a strong, unpredictable password for the DB user
resource "random_password" "db_pass" {
  length           = 16
  special          = true
  override_special = "!#$%&*()-_=+[]{}<>:?"
}

# The Cloud SQL Instance
resource "google_sql_database_instance" "postgres" {
  name             = "app-db-instance-${random_id.db_name_suffix.hex}"
  database_version = "POSTGRES_15"
  region           = var.gcp_region

  # Must disable deletion_protection to allow terraform destroy to work
  deletion_protection = false

  settings {
    tier = "db-f1-micro" # Small tier suitable for dev/learning

    backup_configuration {
      enabled = true
    }
  }
}

# Create a Database within the instance
resource "google_sql_database" "app_database" {
  name     = "primarydb"
  instance = google_sql_database_instance.postgres.name
}

# Create a Database User
resource "google_sql_user" "app_user" {
  name     = "appowner"
  instance = google_sql_database_instance.postgres.name
  password = random_password.db_pass.result
}

outputs.tf

output "instance_connection_name" {
  value       = google_sql_database_instance.postgres.connection_name
  description = "Connection string used by Cloud SQL Proxy or App Engine/Cloud Run"
}

output "db_password" {
  value       = random_password.db_pass.result
  description = "The database user password"
  sensitive   = true # Hides the value in CLI output
}

Workflow Commands

terraform init
terraform apply -var="project_id=my-gcp-project-123"

# Note: Cloud SQL provisioning takes about 5-10 minutes in GCP.

# View the generated password securely
terraform output -raw db_password

terraform destroy -var="project_id=my-gcp-project-123"
✓ Expected Output After Apply
Apply complete! Resources: 5 added, 0 changed, 0 destroyed.

Outputs:

db_password = <sensitive>
instance_connection_name = "my-gcp-project-123:us-central1:app-db-instance-e4f5a6b7"

Related Posts