Create schema for Google Cloud SQL PostgreSQL database using Terraform

Question

I'm new to Terraform, and I want to create a schema for the postgres database created on a PostgreSQL 9.6 instance on Google cloud SQL.

To create the PostgreSQL instance I have this on main.tf:

resource "google_sql_database_instance" "my-database" {
  name = "my-${var.deployment_name}"
  database_version = "POSTGRES_9_6"
  region = "${var.deployment_region}"

  settings {
      tier = "db-f1-micro"
      ip_configuration {
          ipv4_enabled = true
      }
  }
}

The I was trying to create a PostgreSQL object like this:

provider "postgresql" {
  host            = "${google_sql_database_instance.my-database.ip_address}"
  username        = "postgres"
}

Finally creating the schema:

resource "postgresql_schema" "my_schema" {
  name  = "my_schema"
  owner = "postgres"
}

However, this configurations do not work, we I run terraform plan:

Inappropriate value for attribute "host": string required.

If I remove the Postgres object:

Error: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp :5432: connect: connection refused

Additionally, I would like to add a password for the user postgres which is created by default when the PostgreSQL instance is created.

EDITED: versions used

Terraform v0.12.10
+ provider.google v2.17.0
+ provider.postgresql v1.2.0

Any suggestions?

Answer 1

There are a few issues with the terraform set up that you have above.

1. Your instance does not have any authorized networks defined. You should change your instance resource to look like this: (Note: I used 0.0.0.0/0 just for testing purposes)

    resource "google_sql_database_instance" "my-database" {
      name = "my-${var.deployment_name}"
      database_version = "POSTGRES_9_6"
      region = "${var.deployment_region}"

      settings {
          tier = "db-f1-micro"
          ip_configuration {
            ipv4_enabled = true
            authorized_networks {
              name = "all"
              value = "0.0.0.0/0"
            }
          }
      }
      depends_on = [
        "google_project_services.vpc"
      ]
    }

2. As mentioned here, you need to create a user with a strong password

resource "google_sql_user" "user" {
  name     = "test_user"
  instance = "${google_sql_database_instance.my-database.name}"
  password = "VeryStrongPassword"

  depends_on = [
    "google_sql_database_instance.my-database"
  ]
}

3. You should use the "public_ip_address" or "ip_address.0.ip_address" attribute of your instance to access the ip address. Also, you should update your provider and schema resource to reflect the user created above.

provider "postgresql" {
  host            = "${google_sql_database_instance.my-database.public_ip_address}"
  username        = "${google_sql_user.user.name}"
  password        = "${google_sql_user.user.password}"
}

resource "postgresql_schema" "my_schema" {
  name  = "my_schema"
  owner = "test_user"
}

3. Your postgres provider is dependent on the google_sql_database_instance resource to be done before it is able to set up the provider:

All the providers are initialized at the beginning of plan/apply so if one has an invalid config (in this case an empty host) then Terraform will fail. There is no way to define the dependency between a provider and a resource within another provider. There is however a workaround by using the target parameter

terraform apply -target=google_sql_user.user

This will create the database user (as well as all its dependencies - in this case the database instance) and once that completes follow it with:

terraform apply

This should then succeed as the instance has already been created and the ip_address is available to be used by the postgres provider.

Final Note: Usage of public ip addresses without SSL to connect to Cloud SQL instances is not recommended for production instances.

Answer 2

This was my solution, and this way I just need to run: terraform apply :

// POSTGRESQL INSTANCE
resource "google_sql_database_instance" "my-database" {
  database_version = "POSTGRES_9_6"
  region = var.deployment_region

  settings {
      tier = var.db_machine_type
      ip_configuration {
          ipv4_enabled = true
          authorized_networks {
              name = "my_ip"
              value = var.db_allowed_networks.my_network_ip
          }
      }
  }
}

// DATABASE USER
resource "google_sql_user" "user" {
  name     = var.db_credentials.db_user
  instance = google_sql_database_instance.my-database.name
  password = var.db_credentials.db_password
  depends_on = [
    "google_sql_database_instance.my-database"
  ]

  provisioner "local-exec" {
    command = "psql postgresql://${google_sql_user.user.name}:${google_sql_user.user.password}@${google_sql_database_instance.my-database.public_ip_address}/postgres -c \"CREATE SCHEMA myschema;\""
  }
}