Unable to enable a private IP for my Postgres Cloud SQL instance

Question

When I try to enable a private IP on my Cloud SQL instance (Postgresql 9.6) I get the follwoing error message:

Network association failed due to the following error: set Service Networking service account as servicenetworking.serviceAgent role on consumer project

I have a VPC which I select in the "Associated Network" dropdown and I chose a managed services network too which I have already setup so in theory it should all work.

I cannot find anything under IAM that relates to the error message, either a service account or even the servicenetworking.serviceAgent permission.

Update Including the relevant terraform snippets

## VPC Setup
resource "google_compute_network" "my_network" {
  project                 = "${var.project_id}"
  name                    = "vpc-play"
  auto_create_subnetworks = "false"
  routing_mode            = "REGIONAL"
}
# There is a bunch of subnets linked to this network which are not included here

## Managed services network

resource "google_compute_global_address" "default" {
  name = "google-managed-services-vpc-${var.project_id}"
  project = "${var.project_id}"
  provider = "google-beta"
  ip_version = "IPV4"
  prefix_length = 16
  address_type = "INTERNAL"
  purpose = "VPC_PEERING"
  network = "${google_compute_network.my_network.self_link}"
}


## Error occurs on this step
## Error is : google_service_networking_connection.private_vpc_connection: set Service Networking service account as servicenetworking.serviceAgent role on consumer project

resource "google_service_networking_connection" "private_vpc_connection" {
    provider = "google-beta"
    network       = "${google_compute_network.my_network.self_link}"
    service       = "servicenetworking.googleapis.com"
    reserved_peering_ranges = ["${google_compute_global_address.default.name}"]
}

## Database configuration <-- omitted private ip stuff for now as doesn't even get to creation of this, error in previous step

resource "google_sql_database_instance" "my_db" {
  depends_on = ["google_service_networking_connection.private_vpc_connection"]
  name             = "my_db"
  project          = "${var.project_id}"
  database_version = "POSTGRES_9_6"
  region           = "${var.region}"
  lifecycle {
    prevent_destroy = true
  }

  settings {
    tier = "db-f1-micro"

    backup_configuration {
      enabled     = true
      start_time  = "02:00"
    }

    maintenance_window {
      day = 1
      hour = 3
      update_track = "stable"
    }

    ip_configuration {
      authorized_networks = [
        {
          name  = "office"
          value = "${var.my_ip}"
        },
      ]
    }

    disk_size         = 10
    availability_type = "ZONAL"

    location_preference {
      zone = "${var.zone}"
    }
  }
}

Switchman

There are some APIS to enable for this to work, is the network service api enabled? – Sixtynine

You mean this one - console.cloud.google.com/apis/library/… service Yes it is enabled – Switchman

That's stange it shouldn't have any need for a service account, did you retry to enable it? – Sixtynine

How are you enabling the private IP? via Cloud Console? via API call? or via gcloud command? Can you try to run the command gcloud beta sql instances patch INSTANCE --network=NETWORK? You can find more info here [1]. [1]: cloud.google.com/sdk/gcloud/reference/beta/sql/instances/patch – Cordelia

@mcgin, Also, another question, the network where you are trying to enable the private IP is a shared network? if so, you'll be unable to do it if the PostgreSQL instance is already created, due to this should be done on creation [2] (This document is for MySQL, but that restriction applies for PostgreSQL also). If you're using a VPC inside of your project, you don't have to worry about this. [2]: cloud.google.com/sql/docs/mysql/private-ip#network_requirements – Cordelia

@AlexRiquelme I created the database with terraform as a public databse. Once private IP's went GA I tried to convert it to a private IP first using the UI and then also with terraform. I can pinpoint the specific resource that is failing now with the terrform config, it's not the DB it is the google_service_networking_connection creationg that is failing. – Switchman

@AlexRiquelme I tried executing the patch command and got an internal error, the logs simply state "An Unknown Error occurred" – Switchman

@Switchman 1st:You need a Service account to run this Terraform code, please, add at the top of the file this code: provider "google-beta" { credentials = "${file("CREDENTIALS.json")}" project = "PROJECT" region = "us-central1" } – Cordelia

2nd: I saw the same error in the past using the Terraform code, the unknown error is because the VPC peering was not made correctly. I'll post an answer in some minutes with the correct code. – Cordelia

These instructions worked for me, but I had to disable and re-enable both the container.googleapis.com and servicenetworking.googleapis.com APIs. My GKE cluster was failing to build with the error: "reason": "backendError" – Lenis

Answer 1

It seems terraform messed up the permissions on the account at some point and removed the servicenetworking.serviceAgent role from all users.

Disabling and then reenabling the service networking API resolves the problem by resetting the permissions on all users of the system.

Switchman

Thanks, I also had to disable and reenable servicenetworking.googleapis.com . The answer is always to reboot, eh? ;) – Seely

Answer 2

The Terraform code to create a Cloud SQL instance with Private IP has some errors. The first one is that the ${google_compute_network.private_network.self_link} variable get the entire name of the network, that means that will be something like www.googleapis.com/compute/v1/projects/PROJECT-ID/global/networks/testnw2. This value is not allowed in the field google_compute_global_address.private_ip_address.network, so, you need to change ${google_compute_network.private_network.self_link} to ${google_compute_network.private_network.name}.

Another error is that the format in google_sql_database_instance.instance.settings.ip_configuration.private_network should be projects/PROJECT_ID/global/networks/NW_ID. so you need to change the field to projects/[PROJECT_ID]/global/networks/${google_compute_network.private_network.name} in order to work.

The third error, and also, the one that you shared in your initial message, you need to set a service account in the Terraform code to have the proper privileges to avoid this error. Please, check the first lines of the shared code.

The fourth error is that you need to do this using the google-beta provider, not the google default one

As discussed in the comment that I posted, I saw the "An Unknown Error occurred" error before using that Terraform code, this error refers to an error when doing the VPC peering. I understand that is frustrating to troubleshoot this, because it doesn't show any useful information, but if you open a ticket in Google Cloud Platform Support we will be able to check the real error using our internal tools.

As promised, this is the code that I'm using to create a private network and attach it to a Google Cloud SQL instance on creation.

provider "google-beta" {
 credentials = "${file("CREDENTIALS.json")}"
 project     = "PROJECT-ID"
 region      = "us-central1"
}
resource "google_compute_network" "private_network" {
    name       = "testnw"
}

resource "google_compute_global_address" "private_ip_address" {
    provider="google-beta"
    name          = "${google_compute_network.private_network.name}"
    purpose       = "VPC_PEERING"
    address_type = "INTERNAL"
    prefix_length = 16
    network       = "${google_compute_network.private_network.name}"
}

resource "google_service_networking_connection" "private_vpc_connection" {
    provider="google-beta"
    network       = "${google_compute_network.private_network.self_link}"
    service       = "servicenetworking.googleapis.com"
    reserved_peering_ranges = ["${google_compute_global_address.private_ip_address.name}"]
}

resource "google_sql_database_instance" "instance" {
    provider="google-beta"
    depends_on = ["google_service_networking_connection.private_vpc_connection"]
    name = "privateinstance"
    region = "us-central1"
    settings {
        tier = "db-f1-micro"
        ip_configuration {
            ipv4_enabled = "false"
            private_network = "projects/PROJECT-ID/global/networks/${google_compute_network.private_network.name}"
        }
    }
}

Cordelia

Thanks but this still doesn't solve it, I still receive the error when terraform is creating the service network connection google_service_networking_connection.private_vpc_connection: set Service Networking service account as servicenetworking.serviceAgent role on consumer project. The credentials I've supplier to terraform are for an owner of the project so would expect that to be fine? – Switchman

@Switchman are you using a credentials.json with enough privileges to perform this action? can you try to do it with a project Editor service account? – Cordelia

It's with the owner account. It seems like terraform was screwing with the permissions in some way. If I disable the service networking api and reenable it I could then run this as normal. So the solution seems to be disable + reenable the API which causes the permssions to reset. – Switchman

@AlexRiquelme - I am trying to create multiple DB instance with private IPs using Terraform and experience intermittent "An Unknown Error occurred" errors. Some of the DB instances are create successfully, while others fail with this error message. How can I debug it? – Crinum

@AlexRiquelme - I did not manage to login in the link you provided. I found an ugly yet working workaround. Please see the question and answer I posted. – Crinum

@AlexRiquelme - If you can file the bug I found in your internal systems and Cc me on the progress, it would be great. – Crinum

@Switchman Did you find a workaround to not have to manually disable and re-enable the API? Running into the same issue – Nedi

@Nedi no, that was the only way I could do it at the time, however I know there has been changes to how terraform manages the google_project_service resources since I had this problem, the behaviour of the old google_project_services resources which I was using sounds like it was a likely culprit - terraform.io/docs/providers/google/guides/… – Switchman

Answer 3

It seems to be related as well with the error

Error: googleapi: Error 400: Precondition check failed., failedPrecondition

as for both errors I disable and enable networking API and it get to works again...

Accommodative

Answer 4

This saved me hence:

gcloud projects add-iam-policy-binding YOUR_HOST_PROJECT_NAME \
  --member=serviceAccount:[e-mail] \
  --role=roles/servicenetworking.serviceAgent

https://thedataguy.in/cloudsql-shared-vpc-private-ip-and-servicenetworking.serviceagent-role/

Pearsall