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?

Plowshare

Can you add the Terraform and provider versions used? – Norse

Added on the question. – Plowshare

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.

Peti

Thanks. It worked but I followed a different approach. I create a db , the user and then after the user is created I run a psql command to connect to the db and create the schema. I will post my answer as well. – Plowshare

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;\""
  }
}

Plowshare

local-exec is great to use but keep in mind that terraform will not know about the postgresql schema you created. It depends on how you use it and what you plan to do with terraform but I just thought to call it out. If for any reason the schema is deleted but the user still exists then terraform will think everything is great and will not recreate it on a "terraform apply" – Peti

Yes I have learned that during the implementation. Thanks for the information. For what I pretend to implement there is no problem with that. But I will keep that in mind for the future – Plowshare