Terraform Google SQL DB Modules

The safer_mysql module implements a security-first architecture for MySQL on Google Cloud by centralizing identity management through Cloud IAM.

Key security principles include:

• IAM-based Authentication: Uses Cloud IAM for primary authentication, reducing reliance on long-lived passwords.
• Proxy-only Access: The module restricts network connections so they must be mediated via the Cloud SQL Proxy. Even if assign_public_ip is set to true, the module ensures no 'authorized networks' are configured, forcing all traffic through the proxy and IAM authentication.
• Network Isolation: For maximum security, set assign_public_ip = false. This restricts access to the VPC only, requiring bastion hosts or custom tooling for debugging.
• Secondary Access Controls: MySQL users and passwords are treated as secondary controls to further restrict permissions (e.g., preventing an app user from performing structural schema changes).

To promote a secondary instance (Instance 2) to primary and reconfigure the original primary (Instance 1) as a failover replica, follow these three steps:

1. Promote Instance 2

Remove the master_instance_name attribute from the pg2 module configuration and run terraform apply. This breaks the replication link and allows Instance 2 to act as a standalone primary.

2. Remove Instance 1

Remove all code blocks and outputs related to pg1 from your Terraform configuration and run terraform apply. This deletes the original primary instance.

3. Reconfigure Instance 1 as a Replica

Add the pg1 module code back into your configuration, but set its master_instance_name to point to the new primary (module.pg2.instance_name). Run terraform apply to provision Instance 1 as a replica of Instance 2.

# Step 1: Promote Instance 2
module "pg2" {
  source  = "terraform-google-modules/sql-db/google//modules/postgresql"
  version = "~> 28.3"
-  master_instance_name = module.pg1.instance_name
...
}

# Step 2: Remove Instance 1 (Delete the module and outputs)
- module "pg1" {
-   ...
- }
- output "instance1_name" {
-   ...
- }

# Step 3: Re-add Instance 1 as a replica
module "pg1" {
+ master_instance_name = module.pg2.instance_name
  ...
}

To access the database from a workstation or application, you must use the Cloud SQL Proxy.

Human Access (Workstation)

1. Install the Proxy:

   wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
   chmod +x cloud_sql_proxy

2. Connect (Requires assign_public_ip = true or a bastion host):

   mkdir $HOME/mysql_sockets
   ./cloud_sql_proxy --dir=$HOME/mysql_sockets --instances=myproject:region:instance
   
   # In a new terminal, connect via the socket
   mysql -S $HOME/mysql_sockets/myproject:region:instance -u user -p

Production Access (VM/GKE)

1. Assign a service account with roles/cloudsql.client to the VM or GKE node pool.
2. Run the Cloud SQL Proxy on the instance/node.
3. Configure the application to connect via the proxy using the generated credentials.

# Install
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
chmod +x cloud_sql_proxy

# Run Proxy
mkdir $HOME/mysql_sockets
./cloud_sql_proxy --dir=$HOME/mysql_sockets --instances=myproject:region:instance

# Connect
mysql -S $HOME/mysql_sockets/myproject:region:instance -u user -p

The migrate4.py script automates the Terraform state movement required when upgrading to v4.0.0 (specifically for additional_databases, additional_users, and read_replicas).

Migration Workflow:

1. Download and prepare the script:

   curl -O https://raw.githubusercontent.com/terraform-google-modules/terraform-google-sql-db/master/helpers/migrate4.py
   chmod +x migrate4.py

2. Backup your state: Always pull a backup of your current state before running migration commands.

   terraform state pull >> state.bak

3. Dry run: Run the script with the --dryrun flag to see the terraform state mv commands that will be generated without actually executing them.

   ./migrate4.py --dryrun

4. Execute migration: Run the script to apply the state moves.

   ./migrate4.py

5. Verify: Run terraform plan to ensure that the state migration was successful and that no unexpected resource changes are pending.

# 1. Download
curl -O https://raw.githubusercontent.com/terraform-google-modules/terraform-google-sql-db/master/helpers/migrate4.py
chmod +x migrate4.py

# 2. Backup
terraform state pull >> state.bak

# 3. Dry run
./migrate4.py --dryrun

# 4. Execute
./migrate4.py

# 5. Verify
terraform plan

Because the resource addresses for IAM accounts changed in version 15.0.0 (from using the email string to using the new id string), you must migrate your existing Terraform state to avoid resource destruction and recreation.

Option 1: Using moved blocks (Recommended)

Add moved blocks to your Terraform configuration to explicitly migrate the state. This is the safest and most declarative method.

Option 2: Using terraform state mv

If you prefer manual migration, use the terraform state mv command. First, identify the current state addresses using:

terraform state list | grep google_sql_user.iam_account

Then, move the resources manually. For example:

terraform state mv \
  'module.pg.google_sql_user.iam_account["test-sa@$my-project-id.iam.gserviceaccount.com true"]' \
  'module.pg.google_sql_user.iam_account["test-sa"]'

# Example moved blocks for SQL DB 15.0.0 migration

moved {
  from = module.pg.google_sql_user.iam_account["test-sa@${var.project_id}.iam.gserviceaccount.com true"]
  to   = module.pg.google_sql_user.iam_account["test-sa"]
}

moved {
  from = module.pg.google_sql_user.iam_account["john.doe@gmail.com false"]
  to   = module.pg.google_sql_user.iam_account["john.doe"]
}