Production PostgreSQL on GCP, the Right Way

1. Introduction

The default configurations given by most Cloud SQL examples are generally designed to get you up and running, but have a few properties that make them unsuitable for use in production. The database is usually assigned a public IPv4 address and secured by an authorized-networks allowlist; the root password is chosen by hand, or, if the configuration is given in Terraform, generated via random_password and hence stored as plaintext in Terraform state; and applications given in the usage examples generally authenticate via credentials supplied through environment variables, e.g. $POSTGRES_PASSWORD.

This post addresses each property, with the goal of presenting a complete, usable configuration that is sufficient for use in enterprise environments. The CloudSQL instance is provisioned sans public address, reachable only via Private Service Access (PSA), the root password is generated, stored in a secret, easily rotatable, and not present in Terraform state, and applications authenticate through Cloud SQL Auth Proxy using IAM authentication, so no database password is necessary. We will use a standalone keycloak service as an example application.

The complete configuration is available here, along with a lot of other goodies. Reusable modules are organized under modules/, and are composed depending on environment configuration, e.g. dev, stage, prod. Values used in this post assume a development environment, so they are optimized for cost at the expense of durability (e.g. db-f1-micro), but modules are parameterized and so the values can be easily swapped out to fit your needs.

2. Network prerequisites

A Cloud SQL instance with a private address requires a VPC network, a subnet, and a Private Service Access connection. These must exist before the instance is created. The VPC module provisions all three, plus the firewall rules that govern the consumer subnet.

1
resource "google_compute_network" "main" {
2
  name                    = "${var.project_id}-vpc-${var.environment}"
3
  project                 = var.project_id
4
  auto_create_subnetworks = false
5
  routing_mode            = var.routing_mode
6
  mtu                     = var.mtu
7
}
8

9
resource "google_compute_subnetwork" "main" {
10
  for_each                 = { for s in var.subnets : "${s.region}/${s.name}" => s }
11
  name                     = each.value.name
12
  project                  = var.project_id
13
  region                   = each.value.region
14
  network                  = google_compute_network.main.id
15
  ip_cidr_range            = each.value.ip_cidr_range
16
  private_ip_google_access = lookup(each.value, "enable_private_access", true)
17

18
  dynamic "secondary_ip_range" {
19
    for_each = each.value.secondary_ip_ranges != null ? each.value.secondary_ip_ranges : []
20
    content {
21
      range_name    = secondary_ip_range.value.range_name
22
      ip_cidr_range = secondary_ip_range.value.ip_cidr_range
23
    }
24
  }
25

26
  dynamic "log_config" {
27
    for_each = each.value.flow_logs != null ? [""] : []
28
    content {
29
      aggregation_interval = each.value.flow_logs.aggregation_interval
30
      flow_sampling        = each.value.flow_logs.sampling
31
      metadata             = each.value.flow_logs.metadata
32
    }
33
  }
34
}

auto_create_subnetworks = false suppresses creation of a default subnet in every region, making address planning explicit.

1
resource "google_compute_global_address" "psa" {
2
  for_each      = { for r in var.psa_ranges : r.name => r }
3
  name          = each.value.name
4
  project       = var.project_id
5
  purpose       = "VPC_PEERING"
6
  address_type  = "INTERNAL"
7
  address       = each.value.address
8
  prefix_length = each.value.prefix_length
9
  network       = google_compute_network.main.id
10
}
11

12
resource "google_service_networking_connection" "psa" {
13
  count                   = length(var.psa_ranges) > 0 ? 1 : 0
14
  network                 = google_compute_network.main.id
15
  service                 = "servicenetworking.googleapis.com"
16
  reserved_peering_ranges = [for r in google_compute_global_address.psa : r.name]
17
  depends_on              = [google_compute_global_address.psa]
18
  deletion_policy         = "ABANDON"
19
}
20

21
resource "google_compute_network_peering_routes_config" "psa" {
22
  count   = length(var.psa_ranges) > 0 ? 1 : 0
23
  project = var.project_id
24
  peering = google_service_networking_connection.psa[0].peering
25
  network = google_compute_network.main.name
26

27
  export_custom_routes = false
28
  import_custom_routes = false
29
}

1
resource "google_compute_firewall" "rules" {
2
  for_each = { for r in var.firewall_rules : r.name => r }
3
  name     = "${var.project_id}-fw-${var.environment}-${each.value.name}"
4
  project  = var.project_id
5
  network  = google_compute_network.main.id
6
  priority = each.value.priority
7

8
  dynamic "allow" {
9
    for_each = each.value.allow != null ? each.value.allow : []
10
    content {
11
      protocol = allow.value.protocol
12
      ports    = lookup(allow.value, "ports", null)
13
    }
14
  }
15

16
  dynamic "deny" {
17
    for_each = each.value.deny != null ? each.value.deny : []
18
    content {
19
      protocol = deny.value.protocol
20
      ports    = lookup(deny.value, "ports", null)
21
    }
22
  }
23

24
  source_ranges = each.value.source_ranges
25
  target_tags   = each.value.target_tags
26
}


17 collapsed lines
1
variable "project_id" {
2
  type = string
3
}
4

5
variable "environment" {
6
  type = string
7
}
8

9
variable "routing_mode" {
10
  type    = string
11
  default = "GLOBAL"
12
}
13

14
variable "mtu" {
15
  type    = number
16
  default = null
17
}
18

19
variable "subnets" {
20
  type = list(object({
21
    name                  = string
22
    region                = string
23
    ip_cidr_range         = string
24
    enable_private_access = optional(bool, true)
25
    flow_logs = optional(object({
26
      aggregation_interval = optional(string, "INTERVAL_5_SEC")
27
      sampling             = optional(number, 0.5)
28
      metadata             = optional(string, "INCLUDE_ALL_METADATA")
29
    }))
30
    secondary_ip_ranges = optional(list(object({
31
      range_name    = string
32
      ip_cidr_range = string
33
    })))
34
  }))
35
  default = []
36
}
37

38
variable "psa_ranges" {
39
  type = list(object({
40
    name          = string
41
    address       = string
42
    prefix_length = number
43
  }))
44
  default = []
45
}
46

47
variable "firewall_rules" {
48
  type = list(object({
49
    name     = string
50
    priority = optional(number, 1000)
51
    allow = optional(list(object({
52
      protocol = string
53
      ports    = optional(list(string))
54
    })))
55
    deny = optional(list(object({
56
      protocol = string
57
      ports    = optional(list(string))
58
    })))
59
    source_ranges = optional(list(string))
60
    target_tags   = optional(list(string))
61
  }))
62
  default = []
63
}

1
output "network_id" {
2
  value = google_compute_network.main.id
3
}
4

5
output "network_name" {
6
  value = google_compute_network.main.name
7
}
8

9
output "network_self_link" {
10
  value = google_compute_network.main.self_link
11
}
12

13
output "subnets" {
14
  value = { for k, v in google_compute_subnetwork.main : k => v }
15
}
16

17
output "subnet_ids" {
18
  value = { for k, v in google_compute_subnetwork.main : k => v.id }
19
}
20

21
output "subnet_self_links" {
22
  value = { for k, v in google_compute_subnetwork.main : k => v.self_link }
23
}
24

25
output "psa_connection" {
26
  value = try(google_service_networking_connection.psa[0].id, null)
27
}
28

29
output "subnet_secondary_ranges" {
30
  value = {
31
    for k, v in google_compute_subnetwork.main : k => v.secondary_ip_range
32
  }
33
}

1
terraform {
2
  required_version = "~> 1.14.0"
3
  required_providers {
4
    google = {
5
      source  = "hashicorp/google"
6
      version = "~> 7.14.0"
7
    }
8
  }
9
}

Subnets are declared explicitly. The parameter private_ip_google_access is enabled. This allows resources without external addresses to reach Google API endpoints, which is necessary for them to reach things like Artifact Registry, Secret Manager, and the Cloud SQL Admin API.

The development environment instantiates the module as follows.

1
module "vpc" {
2
  source      = "../../../modules/vpc"
3
  project_id  = var.project_id
4
  environment = "dev"
5

6
  subnets = [
7
    {
8
      name          = "main"
9
      region        = var.region
10
      ip_cidr_range = "10.0.0.0/20"
11
      secondary_ip_ranges = [
12
        {
13
          range_name    = "gke-pods"
14
          ip_cidr_range = "10.1.0.0/16"
15
        },
16
        {
17
          range_name    = "gke-services"
18
          ip_cidr_range = "10.2.0.0/20"
19
        }
20
      ]
21
    }
22
  ]
23

24
  psa_ranges = [
25
    {
26
      name          = "psa"
27
      address       = "10.0.64.0"
28
      prefix_length = 22
29
    }
30
  ]
31

32
  firewall_rules = [
33
    {
34
      name          = "allow-internal"
35
      allow         = [{ protocol = "all" }]
36
      source_ranges = ["10.0.0.0/20"]
37
    },
38
    {
39
      name          = "allow-health-checks"
40
      allow         = [{ protocol = "tcp" }]
41
      source_ranges = ["35.191.0.0/16", "130.211.0.0/22"]
42
    },
43
    {
44
      name          = "allow-iap-ssh"
45
      allow         = [{ protocol = "tcp", ports = ["22"] }]
46
      source_ranges = ["35.235.240.0/20"]
47
      target_tags   = ["allow-iap-ssh"]
48
    },
49
    {
50
      name          = "deny-all-ingress"
51
      priority      = 65534
52
      deny          = [{ protocol = "all" }]
53
      source_ranges = ["0.0.0.0/0"]
54
    }
55
  ]
56
}

2.1. Private Service Access

It’s important to note that Cloud SQL instances with private IPs reside in a Google managed tenant project and connect to the VPC we created via peering. The VPC reserves an internal address block to allocate to the Google service networking API, and subsequently the Cloud SQL instance receives an address in the allocated range.

The psa.tf file declares all the resources necessary for PSA to work: a global internal address with purpose = "VPC_PEERING", a service networking connection claiming the range, and a configuration declaring peering routes (which in this example neither imports nor exports custom routes).

The reserved range is shared by all downstream PSA consumers in the network, and cannot be resized without disruption, so it’s important to correctly size it at creation time. Setting deletion_policy = "ABANDON" is necessary since dependent instances will block a service networking connection from deletion, which would make terraform destroy fail. Consequently, the peering must be removed separately after terraform destroy. This tradeoff incurs no monetary cost since all VPC resources outlined in this example are free.

The allocated_ip_range argument binds the instance to this allocation.

2.2. Scope of VPC firewall rules

The environment defines four rules:

internal traffic within 10.0.0.0/20
traffic from the Google health-check ranges 35.191.0.0/16 and 130.211.0.0/22
SSH from the Identity-Aware Proxy (IAP) forwarding range 35.235.240.0/20
a low-priority deny for all remaining ingress.

These rules don’t affect traffic to database instances, which go through the VPC peering. They control access to GCE instances. Access to database instances are enforced through the connectors and IAM. The ranges do not overlap (except the low-priority deny which spans the entire address space).

3. Instance configuration

The Cloud SQL module is seven files: the instance itself, the credential machinery described in Section 4, and thin wrappers for databases and IAM users.

1
# [reference](https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#argument-reference)
2
resource "google_sql_database_instance" "main" {
3
  name                = "${var.project_id}-db-${var.environment}"
4
  project             = var.project_id
5
  region              = var.region
6
  database_version    = var.database_version
7
  deletion_protection = var.deletion_protection
8

9
  root_password_wo         = ephemeral.random_password.postgres.result
10
  root_password_wo_version = var.postgres_password_version
11

12
  settings {
13
    tier                        = var.tier
14
    edition                     = var.edition
15
    user_labels                 = var.user_labels
16
    activation_policy           = var.activation_policy
17
    availability_type           = var.availability_type
18
    connector_enforcement       = var.connector_enforcement
19
    disk_autoresize             = var.disk_autoresize
20
    deletion_protection_enabled = var.deletion_protection_enabled
21
    disk_size                   = var.disk_size_gb
22
    disk_type                   = var.disk_type
23

24
    data_cache_config {
25
      data_cache_enabled = var.data_cache_enabled
26
    }
27

15 collapsed lines
28
    backup_configuration {
29
      enabled                        = var.backup_config.enabled
30
      point_in_time_recovery_enabled = var.backup_config.point_in_time_recovery
31
      start_time                     = var.backup_config.start_time
32
      location                       = var.backup_config.location
33
      transaction_log_retention_days = var.backup_config.transaction_log_retention_days
34

35
      dynamic "backup_retention_settings" {
36
        for_each = var.backup_config.retention_count != null ? [""] : []
37
        content {
38
          retained_backups = var.backup_config.retention_count
39
          retention_unit   = "COUNT"
40
        }
41
      }
42
    }
43

44
    ip_configuration {
45
      ipv4_enabled                                  = false
46
      private_network                               = var.network_id
47
      enable_private_path_for_google_cloud_services = true
48
      ssl_mode                                      = var.ssl_mode
49
      allocated_ip_range                            = var.allocated_ip_range
50
    }
51

52
    dynamic "database_flags" {
53
      for_each = var.flags
54
      content {
55
        name  = database_flags.key
56
        value = database_flags.value
57
      }
58
    }
59

60

13 collapsed lines
61
    maintenance_window {
62
      day          = var.maintenance_window.day
63
      hour         = var.maintenance_window.hour
64
      update_track = var.maintenance_window.update_track
65
    }
66

67
    insights_config {
68
      query_insights_enabled  = var.insights_config.enabled
69
      query_plans_per_minute  = var.insights_config.query_plans_per_minute
70
      query_string_length     = var.insights_config.query_string_length
71
      record_application_tags = var.insights_config.record_application_tags
72
      record_client_address   = var.insights_config.record_client_address
73
    }
74
  }
75
}

1
ephemeral "random_password" "postgres" {
2
  length  = 24
3
  special = false
4
}
5

6
resource "google_secret_manager_secret" "postgres_password" {
7
  secret_id = "${var.project_id}-postgres-password-${var.environment}"
8
  project   = var.project_id
9

10
  replication {
11
    auto {}
12
  }
13
}
14

15
resource "google_secret_manager_secret_version" "postgres_password" {
16
  secret                 = google_secret_manager_secret.postgres_password.id
17
  secret_data_wo         = ephemeral.random_password.postgres.result
18
  secret_data_wo_version = var.postgres_password_version
19
}
20

21
resource "google_secret_manager_secret_iam_member" "postgres_password_access" {
22
  secret_id = google_secret_manager_secret.postgres_password.id
23
  role      = "roles/secretmanager.secretAccessor"
24
  member    = "serviceAccount:${var.terraform_sa_email}"
25
}

1
resource "google_sql_user" "iam" {
2
  for_each = toset(var.iam_users)
3

4
  name     = each.key
5
  instance = google_sql_database_instance.main.name
6
  project  = var.project_id
7
  type     = "CLOUD_IAM_SERVICE_ACCOUNT"
8
}

1
resource "google_sql_database" "databases" {
2
  for_each = toset(var.databases)
3

4
  name     = each.value
5
  instance = google_sql_database_instance.main.name
6
  project  = var.project_id
7
}


40 collapsed lines
1
variable "project_id" {
2
  type = string
3
}
4

5
variable "environment" {
6
  type = string
7
}
8

9
variable "region" {
10
  type = string
11
}
12

13
variable "network_id" {
14
  type        = string
15
  description = "VPC network ID for private IP"
16
}
17

18
variable "database_version" {
19
  type = string
20
}
21

22
variable "tier" {
23
  type = string
24
}
25

26
variable "edition" {
27
  type = string
28
  validation {
29
    condition     = contains(["ENTERPRISE", "ENTERPRISE_PLUS"], var.edition)
30
    error_message = "Edition must be ENTERPRISE or ENTERPRISE_PLUS."
31
  }
32
}
33

34
variable "availability_type" {
35
  type = string
36
  validation {
37
    condition     = contains(["ZONAL", "REGIONAL"], var.availability_type)
38
    error_message = "Availability type must be ZONAL or REGIONAL."
39
  }
40
}
41

42
variable "disk_size_gb" {
43
  type = number
44
}
45

46
variable "disk_type" {
47
  type = string
48
}
49

50
variable "disk_autoresize" {
51
  type = bool
52
}
53

54
variable "ssl_mode" {
55
  type = string
56
  validation {
57
    condition     = contains(["ALLOW_UNENCRYPTED_AND_ENCRYPTED", "ENCRYPTED_ONLY", "TRUSTED_CLIENT_CERTIFICATE_REQUIRED"], var.ssl_mode)
58
    error_message = "Invalid SSL mode."
59
  }
60
}
61

62
variable "deletion_protection" {
63
  type = bool
64
}
65

66
variable "flags" {
67
  type        = map(string)
68
  description = "Database flags as key-value pairs"
69
}
70

71
variable "databases" {
72
  type = list(string)
73
}
74

75
variable "iam_users" {
76
  type        = list(string)
77
  description = "Service account emails for IAM database users"
78
}
79

80
variable "backup_config" {
81
  type = object({
82
    enabled                        = optional(bool, true)
83
    point_in_time_recovery         = optional(bool, true)
84
    start_time                     = optional(string, "03:00")
85
    location                       = optional(string)
86
    transaction_log_retention_days = optional(number, 7)
87
    retention_count                = optional(number, 7)
88
  })
89
}
90

91
variable "maintenance_window" {
92
  type = object({
93
    day          = optional(number, 1) # Monday
94
    hour         = optional(number, 4) # 4 AM
95
    update_track = optional(string, "stable")
96
  })
97
}
98

99
variable "insights_config" {
100
  type = object({
101
    enabled                 = optional(bool, true)
102
    query_plans_per_minute  = optional(number, 5)
103
    query_string_length     = optional(number, 1024)
104
    record_application_tags = optional(bool, true)
105
    record_client_address   = optional(bool, false)
106
  })
107
}
108

109
variable "connector_enforcement" {
110
  type = string
111
  validation {
112
    condition     = contains(["NOT_REQUIRED", "REQUIRED"], var.connector_enforcement)
113
    error_message = "Connector enforcement must be NOT_REQUIRED or REQUIRED."
114
  }
115
}
116

117
variable "deletion_protection_enabled" {
118
  type        = bool
119
  description = "GCP-level deletion protection (separate from Terraform's)"
120
}
121

122
variable "allocated_ip_range" {
123
  type        = string
124
  description = "Name of the allocated IP range for private IP (from PSA)"
125
}
126

127
variable "activation_policy" {
128
  type = string
129
  validation {
130
    condition     = contains(["ALWAYS", "NEVER", "ON_DEMAND"], var.activation_policy)
131
    error_message = "Activation policy must be ALWAYS, NEVER, or ON_DEMAND."
132
  }
133
}
134

135
variable "data_cache_enabled" {
136
  type        = bool
137
  description = "Enable data cache (Enterprise Plus only)"
138
}
139

140
variable "user_labels" {
141
  type        = map(string)
142
  description = "User labels for the instance"
143
}
144

145
variable "postgres_password_version" {
146
  type        = number
147
  description = "Increment to rotate postgres password"
148
}
149

150
variable "terraform_sa_email" {
151
  type        = string
152
  description = "Terraform service account email for secret access"
153
}

1
output "instance_name" {
2
  value = google_sql_database_instance.main.name
3
}
4

5
output "connection_name" {
6
  value = google_sql_database_instance.main.connection_name
7
}
8

9
output "private_ip_address" {
10
  value = google_sql_database_instance.main.private_ip_address
11
}
12

13
output "self_link" {
14
  value = google_sql_database_instance.main.self_link
15
}
16

17
output "databases" {
18
  value = { for k, v in google_sql_database.databases : k => v.name }
19
}
20

21
output "users" {
22
  value = { for k, v in google_sql_user.iam : k => v.name }
23
}
24

25
output "postgres_password_secret" {
26
  value = google_secret_manager_secret.postgres_password.id
27
}
28

29
output "postgres_password_secret_version" {
30
  value = google_secret_manager_secret_version.postgres_password.id
31
}

1
terraform {
2
  required_version = "~> 1.14.0"
3
  required_providers {
4
    google = {
5
      source  = "hashicorp/google"
6
      version = "~> 7.14.0"
7
    }
8
  }
9
}

Network access is declared in the ip_configuration block. Setting ipv4_enabled = false ensures the instance has no public address, and so no authorized networks configuration exists. Access to the instance from the public internet is impossible. Consequently, administrator access must go through a bastion host or a proxy.

A couple other settings are worth noting: setting ssl_mode = "ENCRYPTED_ONLY" forces encrypted connections and connector_enforcement = "REQUIRED" requires connections to come through Cloud SQL Auth Proxy or another supported connector. This setting is important to strengthen the authentication requirements by forcing connections to happen through a connector.

Durability and maintenance settings are parameterized. Documentation on each setting can be found on the official documentation page.

The following configuration instantiates CloudSQL with the desired parameters.

1
module "cloudsql" {
2
  source = "../../../modules/cloudsql"
3

4
  terraform_sa_email = "terraform@${var.project_id}.iam.gserviceaccount.com"
5

6
  project_id          = var.project_id
7
  environment         = "dev"
8
  tier                = "db-f1-micro"
9
  region              = var.region
10
  edition             = "ENTERPRISE"
11
  availability_type   = "ZONAL"
12
  database_version    = "POSTGRES_18"
13
  disk_size_gb        = 10
14
  disk_type           = "PD_SSD"
15
  deletion_protection = false
16

17
  # increment to rotate root pw
18
  postgres_password_version = 1
19

20

21
  network_id                  = module.vpc.network_id
22
  allocated_ip_range          = "psa"
23
  deletion_protection_enabled = false
24
  data_cache_enabled          = false
25

26
  user_labels = {
27
    environment = "dev"
28
  }
29

30
  activation_policy = "ALWAYS"
31

32
  insights_config = {
33
    enabled                 = true
34
    query_plans_per_minute  = 5
35
    query_string_length     = 1024
36
    record_application_tags = true
37
    record_client_address   = false
38
  }
39

40
  maintenance_window = {
41
    day          = 1
42
    hour         = 4
43
    update_track = "stable"
44
  }
45

46
  disk_autoresize = true
47

48
  ssl_mode              = "ENCRYPTED_ONLY"
49
  connector_enforcement = "REQUIRED"
50

51
  flags = {
52
    "cloudsql.iam_authentication" = "on"
53
    "log_lock_waits"              = "on"
54
    "log_disconnections"          = "on"
55
    "log_connections"             = "on"
56
    "log_checkpoints"             = "on"
57
    "log_temp_files"              = "0"
58
  }
59

60
  databases = ["example", "keycloak"]
61

62
  iam_users = [
63
    trimsuffix(google_service_account.accounts["app"].email, ".gserviceaccount.com"),
64
    trimsuffix(google_service_account.accounts["keycloak"].email, ".gserviceaccount.com"),
65
  ]
66

67
  backup_config = {
68
    enabled                = false # no need on a dev instance
69
    point_in_time_recovery = false
70
  }
71

72
  depends_on = [module.vpc]
73
}

Deletion protection is configured in two independent places. The deletion_protection argument on the resource prevents terraform destroy from deleting it; settings.deletion_protection_enabled argument that deletion via the Cloud SQL API or GCP console cannot occur unless this setting is disabled. These are distinct, and both should be enabled, at least in production.

4. Eliminating credentials from Terraform state

4.1. The persistence problem

The easiest path to generating a root password is to use random_password and assign the return value to the instance’s password parameter. Unfortunately, this results in both the generated and assigned value to be recorded in Terraform state. State is unencrypted by Terraform and has no field-level protections which means that the database root password is stored in plaintext in any saved plan file (*.tfplan), in CI artifacts that capture plan output, in the result of terraform show, and in the remote state backend (usually a GCS bucket). Anyone person, agent, or other principal with access to the state backend then has access to the database root password.

4.2. Ephemeral values and write-only attributes

Luckily, Terraform has the concept of ephemeral resources, which are values that exist during an operation but are not stored in either state or *.tfplan files. Write only attributes, denoted by _wo suffix, are passed through to the provider API without being recorded.

The secrets.tf tab above applies both. The same ephemeral value is supplied to the instance in main.tf:

1
  root_password_wo         = ephemeral.random_password.postgres.result
2
  root_password_wo_version = var.postgres_password_version

The password never exists as a resource attribute, so there is nothing for terraform show to print and nothing for a plan artifact to leak.

Each _wo attribute must be paired with a version so that Terraform can compute a diff (since write only attributes are missing in the state). A change to the version is what triggers updates. In this case, bumping postgres_password_version results in the ephemeral resource to be regenerated and consumers to receive the new value when apply is run. Because of this, credential rotation is as easy as incrementing the version.

The generated password is stored in Secret Manager. Access is granted narrowly through google_secret_manager_secret_iam_member, which assigns roles/secretmanager.secretAccessor to the Terraform service account.

These features require recent versions of both Terraform and the provider. Write-only arguments were introduced in Terraform 1.11. The configuration provided in this example pins required_version = "~> 1.14.0" and the Google provider at ~> 7.14.0.

The administrative password exists, but is not used by applications.

5. Eliminating credentials from application configuration

Cloud SQL IAM authentication allows principals to authenticate to a Cloud SQL instance with tokens in lieu of passwords. Configuring it requires changes at three layers.

5.1. Instance configuration

IAM authentication is enabled by a database flag, supplied by the environment along with the logging flags discussed in Section 6:

1
  flags = {
2
    "cloudsql.iam_authentication" = "on"
3
    "log_lock_waits"              = "on"
4
    "log_disconnections"          = "on"
5
    "log_connections"             = "on",
6
    "log_checkpoints"             = "on",
7
    "log_temp_files"              = "0"
8
  }

Database users are then declared with type = "CLOUD_IAM_SERVICE_ACCOUNT", as in the users.tf tab above. Users of this type have no password.

The user name is not the full service account email. The environment configuration removes the domain suffix:

1
  iam_users = [
2
    trimsuffix(google_service_account.accounts["app"].email, ".gserviceaccount.com"),
3
    trimsuffix(google_service_account.accounts["keycloak"].email, ".gserviceaccount.com"),
4
  ]

An incorrect name does not fail at apply time. It fails at connection time, which is less convenient to diagnose.

This suffix trimming is required.

5.2. IAM bindings

Each service account requires two project-level roles, granted in the environment configuration:

1
resource "google_project_iam_member" "cloudsql_client" {
2
  for_each = local.app_service_accounts
3
  project  = var.project_id
4
  role     = "roles/cloudsql.client"
5
  member   = "serviceAccount:${google_service_account.accounts[each.key].email}"
6
}
7

8
resource "google_project_iam_member" "cloudsql_instance_user" {
9
  for_each = local.app_service_accounts
10
  project  = var.project_id
11
  role     = "roles/cloudsql.instanceUser"
12
  member   = "serviceAccount:${google_service_account.accounts[each.key].email}"
13
}

The roles/cloudsql.client role authorizes connection through Cloud SQL Auth Proxy. The roles/cloudsql.instanceUser role authorizes login as an IAM database user. Both are necessary. Without both, you will observe a connection failure.

5.3. Client configuration

We use the example of running a standard, single VPS Keycloak server. A production Keycloak deployment on Kubernetes will be its own post in the future! The VPS is a Container-Optimized OS GCE instance running Cloud SQL Auth Proxy as a sidecar. The sidecar and the application are colocated in the same Docker network. Keycloak addresses the proxy by name, and doesn’t concern itself with the database’s actual address.

1
#!/bin/bash
2
set -euo pipefail
3

4
export HOME=/home/chronos
5
docker-credential-gcr configure-docker --registries ${region}-docker.pkg.dev
6

7
ADMIN_PASSWORD=$(docker run --rm \
8
  gcr.io/google.com/cloudsdktool/google-cloud-cli:slim \
9
  gcloud secrets versions access latest \
10
  --secret="${project_id}-keycloak-admin-${environment}" \
11
  --project="${project_id}")
12

13
docker network create keycloak-net
14

15
docker run -d \
16
  --name cloud-sql-proxy \
17
  --network keycloak-net \
18
  --restart unless-stopped \
19
  gcr.io/cloud-sql-connectors/cloud-sql-proxy:${cloud_sql_proxy_version} \
20
  --auto-iam-authn \
21
  --private-ip \
22
  --address 0.0.0.0 \
23
  --port 5432 \
24
  ${cloud_sql_connection_name}
25

26
sleep 5
27

28
docker run -d \
29
  --name keycloak \
30
  --network keycloak-net \
31
  --restart unless-stopped \
32
  -e 'KC_DB=postgres' \
33
  -e 'KC_DB_URL=jdbc:postgresql://cloud-sql-proxy:5432/${db_name}' \
34
  -e 'KC_DB_USERNAME=${db_user}' \
35
  -e 'KC_HOSTNAME=${hostname}' \
36
  -e 'KC_HTTP_ENABLED=true' \
37
  -e 'KC_PROXY_HEADERS=xforwarded' \
38
  -e 'KC_BOOTSTRAP_ADMIN_USERNAME=admin' \
39
  -e "KC_BOOTSTRAP_ADMIN_PASSWORD=$ADMIN_PASSWORD" \
40
  ${keycloak_image} \
41
  start --optimized
42

16 collapsed lines
43
mkdir -p /home/clouduser/caddy/data
44
cat <<'CADDYEOF' > /home/clouduser/caddy/Caddyfile
45
${hostname} {
46
    reverse_proxy keycloak:8080
47
}
48
CADDYEOF
49

50
docker run -d \
51
  --name caddy \
52
  --network keycloak-net \
53
  --restart unless-stopped \
54
  -p 80:80 \
55
  -p 443:443 \
56
  -v /home/clouduser/caddy/Caddyfile:/etc/caddy/Caddyfile \
57
  -v /home/clouduser/caddy/data:/data \
58
  caddy:${caddy_version}

There is no KC_DB_PASSWORD. The proxy supplies a short-lived token per connection, so the application has no database credential to configure, store, or rotate.

1
locals {
2
  db_user = trimsuffix(var.service_account_email, ".gserviceaccount.com")
3
}
4

5
resource "google_compute_address" "keycloak" {
6
  name    = "${var.project_id}-keycloak-ip-${var.environment}"
7
  project = var.project_id
8
  region  = var.region
9
}
10

41 collapsed lines
11
resource "google_compute_firewall" "keycloak_http" {
12
  name    = "${var.project_id}-fw-${var.environment}-keycloak-http"
13
  project = var.project_id
14
  network = var.network_id
15

16
  allow {
17
    protocol = "tcp"
18
    ports    = ["80"]
19
  }
20

21
  source_ranges = ["0.0.0.0/0"]
22
  target_tags   = ["keycloak"]
23
}
24

25
resource "google_compute_firewall" "keycloak_https" {
26
  name    = "${var.project_id}-fw-${var.environment}-keycloak-https"
27
  project = var.project_id
28
  network = var.network_id
29

30
  allow {
31
    protocol = "tcp"
32
    ports    = ["443"]
33
  }
34

35
  source_ranges = ["0.0.0.0/0"]
36
  target_tags   = ["keycloak"]
37
}
38

39
resource "google_compute_firewall" "keycloak_iap_ssh" {
40
  name    = "${var.project_id}-fw-${var.environment}-keycloak-iap"
41
  project = var.project_id
42
  network = var.network_id
43

44
  allow {
45
    protocol = "tcp"
46
    ports    = ["22"]
47
  }
48

49
  source_ranges = ["35.235.240.0/20"]
50
  target_tags   = ["keycloak"]
51
}
52

53
resource "google_compute_instance" "keycloak" {
54
  name         = "${var.project_id}-keycloak-${var.environment}"
55
  project      = var.project_id
56
  zone         = var.zone
57
  machine_type = var.machine_type
58

59
  deletion_protection = var.deletion_protection
60

61
  boot_disk {
62
    initialize_params {
63
      image = "cos-cloud/cos-stable"
64
      size  = 20
65
      type  = "pd-standard"
66
    }
67
  }
68

69
  network_interface {
70
    subnetwork = var.subnet_id
71
    access_config {
72
      nat_ip = google_compute_address.keycloak.address
73
    }
74
  }
75

76
  service_account {
77
    email  = var.service_account_email
78
    scopes = ["https://www.googleapis.com/auth/cloud-platform"]
79
  }
80

81
  metadata = {
82
    enable-oslogin = "TRUE"
83
    startup-script = templatefile("${path.module}/startup.sh.tpl", {
84
      keycloak_image            = var.keycloak_image
85
      hostname                  = var.hostname
86
      cloud_sql_connection_name = var.cloud_sql_connection_name
87
      db_name                   = var.db_name
88
      db_user                   = local.db_user
89
      project_id                = var.project_id
90
      environment               = var.environment
91
      region                    = var.region
92
      cloud_sql_proxy_version   = var.cloud_sql_proxy_version
93
      caddy_version             = var.caddy_version
94
    })
95
  }
96

97
  tags = ["keycloak"]
98

99
  shielded_instance_config {
100
    enable_secure_boot          = true
101
    enable_integrity_monitoring = true
102
  }
103
}

1
ephemeral "random_password" "keycloak_admin" {
2
  length  = 24
3
  special = false
4
}
5

6
resource "google_secret_manager_secret" "keycloak_admin" {
7
  secret_id = "${var.project_id}-keycloak-admin-${var.environment}"
8
  project   = var.project_id
9

10
  replication {
11
    auto {}
12
  }
13
}
14

15
resource "google_secret_manager_secret_iam_member" "keycloak_admin_access" {
16
  secret_id = google_secret_manager_secret.keycloak_admin.id
17
  role      = "roles/secretmanager.secretAccessor"
18
  member    = "serviceAccount:${var.service_account_email}"
19
}
20

21
resource "google_secret_manager_secret_version" "keycloak_admin" {
22
  secret                 = google_secret_manager_secret.keycloak_admin.id
23
  secret_data_wo         = ephemeral.random_password.keycloak_admin.result
24
  secret_data_wo_version = var.keycloak_admin_password_version
25
}


28 collapsed lines
1
variable "project_id" {
2
  type = string
3
}
4

5
variable "environment" {
6
  type = string
7
}
8

9
variable "region" {
10
  type = string
11
}
12

13
variable "zone" {
14
  type = string
15
}
16

17
variable "network_id" {
18
  type = string
19
}
20

21
variable "subnet_id" {
22
  type = string
23
}
24

25
variable "machine_type" {
26
  type    = string
27
  default = "e2-small"
28
}
29

30
variable "keycloak_image" {
31
  type        = string
32
  description = "Full Artifact Registry image URL including tag"
33
}
34

35
variable "hostname" {
36
  type        = string
37
  description = "Public hostname for Keycloak e.g. idp.example.com"
38
}
39

40
variable "cloud_sql_connection_name" {
41
  type        = string
42
  description = "Cloud SQL connection name (project:region:instance)"
43
}
44

45
variable "db_name" {
46
  type    = string
47
  default = "keycloak"
48
}
49

50
variable "deletion_protection" {
51
  type    = bool
52
  default = false
53
}
54

55
variable "keycloak_admin_password_version" {
56
  type        = number
57
  description = "Increment to rotate keycloak admin password"
58
}
59

60
variable "cloud_sql_proxy_version" {
61
  type    = string
62
  default = "2.21.2"
63
}
64

65
variable "caddy_version" {
66
  type    = string
67
  default = "2.11.2"
68
}
69

70
variable "service_account_email" {
71
  type        = string
72
  description = "Email of the service account to run the instance as"
73
}

1
output "static_ip" {
2
  value = google_compute_address.keycloak.address
3
}
4

5
output "instance_name" {
6
  value = google_compute_instance.keycloak.name
7
}

1
terraform {
2
  required_version = "~> 1.14.0"
3
  required_providers {
4
    google = {
5
      source  = "hashicorp/google"
6
      version = "~> 7.14.0"
7
    }
8
  }
9
}

The following block instantiates the standalone Keycloak application instance.

1
module "keycloak-gce" {
2
  source = "../../../modules/keycloak-gce"
3

4
  project_id   = var.project_id
5
  environment  = "dev"
6
  region       = var.region
7
  zone         = "${var.region}-a"
8
  network_id   = module.vpc.network_id
9
  subnet_id    = module.vpc.subnet_ids["${var.region}/main"]
10
  machine_type = "e2-small"
11

12
  keycloak_image            = "us-central1-docker.pkg.dev/${var.project_id}/example-dev-keycloak/keycloak:latest"
13
  hostname                  = "idp.example.com"
14
  cloud_sql_connection_name = module.cloudsql.connection_name
15
  db_name                   = "keycloak"
16
  deletion_protection       = false
17

18
  keycloak_admin_password_version = 1
19
  cloud_sql_proxy_version         = "2.21.2"
20
  caddy_version                   = "2.11.2"
21
  service_account_email           = google_service_account.accounts["keycloak"].email
22

23
  depends_on = [module.vpc, module.cloudsql]
24
}

Since we provide the --auto-iam-authn to the sidecar, the proxy requests a token for the GCE instance’s service account and asserts it rather than a password, automatically refreshing it upon expiry since the tokens are short-lived. Providing the --private-ip flag configures the proxy to use the private IP address rather than try a public endpoint. Since the database username is derived from the service account name, the two cannot diverge.

Keycloak’s own root password follows the same pattern as the root database password discussed earlier. It’s generated ephemerally, written only to Secret Manager, and not persisted anywhere else by Terraform. It is read by the service account at the start, and is not embedded in the container image or instance metadata.

5.4. Operational properties

The final configuration satisfies four important operational properties. Since tokens are short lived, there is no possibility of durable database credential leakage because there are none on the VPS to exfiltrate. Credential rotation is unnecessary because tokens generated by the proxy are short-lived by construction. Revoking access is accomplished by an IAM change and can be done with zero downtime as it does not require redeployment. Connector enforcement discussed earlier ensures no path exists that bypasses this mechanism.

6. Observability configuration

Query Insights is enabled through the insights_config flag, which records the number of sampled query plans per minute, the maximum retained query string length, and whether application tags and client addresses are recorded. The development environment records application tags but does not record client addresses.

The logging flags specified earlier cover connection lifecycle (log_connections, log_disconnections), lock contention (log_lock_waits), checkpoint activity (log_checkpoints), and temporary file creation (log_temp_files). Setting log_temp_files = 0 logs every temporary file, and temporary file creation indicates that a sort or hash operation exceeded work_mem and spilled onto the disk, which is a signal that usually precedes a latency complaint.

7. Limitations

The configuration has the following known limitations.

GRANT for IAM users must be done manually, once, by an administrator, before IAM users will be able to perform operations on the database. If you have a hosted CI runner inside your VPC, this isn’t an issue, because you can use the postgresql Terraform provider to manage the access of database users. For simplicity, and because I didn’t want to provision a runner inside the VPC for this example, this configuration skips this. If you’re using Kubernetes, you can pretty easily make an ephemeral runner that spins up on demand in your cluster, runs CI, and exits cleanly, which is what I would recommend, at least if you’re using GitHub Actions.
Setting deletion_policy = "ABANDON" on the service networking connection causes the VPC peering to persist after terraform destroy, requiring manual removal. As stated earlier, the resource is free, so it’s an inconvenience but not a costly one, especially if you’re not frequently running terraform destroy.
The Private Service Access range cannot be resized after allocation without disrupting existing consumers, so the initial /22 allocation constrains future growth. However, scaling past that point means the problems you have to solve are good problems. The allocated range will get you quite far, and has the ability to allocate thousands of addresses.
No Cloud NAT gateway is provisioned. Workloads requiring general outbound internet access, as opposed to access to Google API endpoints, are not supported without further configuration, but that is outside the scope of this article. Terraforming Cloud NAT is relatively straightforward and would probably be necessary in a production setup.
The module does not provision read replicas or cross-region disaster recovery.
Backup restoration is not exercised automatically. The backup configuration is unverified without a periodic restore procedure.

Thank you for reading.