GCP: Unable to deploy SQL Instances with Private IP

[Corey Fonseca]

I'm trying to deploy a MySQL instance, and a PostgreSQL instance, both using a Private IP on an internal VPC. In the past, I was able to do this very easily using the following config:  

resource "random_id" "id" {

   byte_length = 4

   prefix      = "${var.database_name}-"

}

resource "google_sql_database_instance" "postgresql" {

   database_version = "POSTGRES_11"

   region = "${var.region}"

   project = "${var.project_id}"

   name = "${random_id.id.hex}"

    settings {

       tier = "db-f1-micro"

       activation_policy = "ALWAYS"

       availability_type = "REGIONAL"

       replication_type = "SYNCHRONOUS"

       maintenance_window {

           day = 7

           hour = 3

           update_track = "stable"

       }

       ip_configuration {

           ipv4_enabled = "false"

           private_network = "${var.postgresql_private_network}"

       }

       location_preference {

           zone = "${var.zone}"

       }

   }

}

If I run this now, I get the following error: 

google_sql_database_instance.postgresql: Error waiting for Create Instance: Failed to create subnetwork. Please create Service Networking connection with service 'servicenetworking.googleapis.com' from consumer project '`1234567890' network 'vpc' again.

Looking at the latest version of the docs for a database instance, I can see that the docs want you to create a new subnet and an IP address, as the error message suggests. Here's my new config:

resource "random_id" "id" {

    byte_length = 4

    prefix      = "${var.database_name}-"

}

resource "google_compute_global_address" "postgresql" {

    provider = "google-beta"

    name = "postgresql"

    address = "10.50.0.10"

    ip_version = "IPV4"

    purpose = "VPC_PEERING"

    address_type = "INTERNAL"

    project = "${var.project_id}"

    network = "${var.postgresql_private_network}"

}

resource "google_service_networking_connection" "postgresql" {

    provider = "google-beta"

    network = "${var.postgresql_private_network}"

    service = "servicenetworking.googleapis.com"

    reserved_peering_ranges = ["${google_compute_global_address.postgresql.name}"]

}

resource "google_sql_database_instance" "postgresql" {

    database_version = "POSTGRES_11"

    region = "${var.region}"

    project = "${var.project_id}"

    name = "${random_id.id.hex}"

    settings {

        tier = "db-f1-micro"

        activation_policy = "ALWAYS"

        availability_type = "REGIONAL"

        replication_type = "SYNCHRONOUS"

        maintenance_window {

            day = 7

            hour = 3

            update_track = "stable"

        }

        ip_configuration {

            ipv4_enabled = "false"

            private_network = "${var.postgresql_private_network}"

        }

        location_preference {

            zone = "${var.zone}"

        }

    }

}

My project has the servicenetworking API enabled (as well as many other APIs), and my Terraform service account is a Service Networking Admin (also an org admin, project owner, etc.) and should have permissions to do basically everything. I receive the following error:

* google_sql_database_instance.postgresql: Error, failed to create instance bpc-staging-6db99533 with error code 409: googleapi: Error 409: The instance or operation is not in an appropriate state to handle the request., invalidState. This may be due to a name collision - SQL instance names cannot be reused within a week.

* google_compute_global_address.postgresql: Error creating GlobalAddress: googleapi: Error 400: Invalid value for field 'resource.prefixLength': '0'. The field needs to be specified for reserving internal IP range., invalid

I'm not sure why the first error suggests that the name is being reused, because my instance name containers a unique hash. I also don't understand the error about the prefixLength, since the docs indicate that this parameter is optional, and I didn't specify a length. What would be a valid length?

If I try to use a specific address in this same config, I get these errors:

Error: google_sql_database_instance.postgresql: resource depends on non-existent resource '${google_compute_global_address.postgresql}'

Error: google_sql_database_instance.postgresql: resource depends on non-existent resource '${google_service_networking_connection.postgresql}'

I'm quite stuck. Any ideas? I would really appreciate any help you can give me! 

[Kees van Bemmel]

Hate to do this but: same issue here. Any progress?

[Abhijit Chaudhari]

I think  you should add 

depends_on = [google_service_networking_connection.postgresql]

in your google_sql_database_instance and make sure that private_network is set to correct value

private_network = "projects/${var.project_id}/global/networks/${var.network_name}"