GoCLoud for GCP doesn't connect to existing database

[arunskumar21]

Hi there,

@cyrilgdn Thank you for creating and maintaining this terraform provider. It definitely helps us a lot in automating the maintenance of the Postgres database.

I have a Postgres instance in GCP and I'm trying to create custom roles and GRANT these roles for an existing database with the below configuration:

Terraform Version

Terraform v0.13.5

Affected Resource(s)

Google CloudSQL

Terraform Configuration Files

module "sql-db_postgresql" {
    source  = "GoogleCloudPlatform/sql-db/google//modules/postgresql"
    version = "4.3.0"
    project_id     = var.project_id
    region            = var.region
    zone               = var.zone
    database_version  = "POSTGRES_13"
    name              = "${var.db_name}-v13-${random_id.suffix.hex}"
    availability_type = "ZONAL"
    disk_size         = "25"
    tier          = "db-g1-small"
    user_name     = "postgres"
    user_password = var.db_password
}

terraform {
    required_providers {
      postgresql = {
        source = "cyrilgdn/postgresql"
        version = "1.13.0"
      }
    }
  }
  
  provider "postgresql" {
      scheme   = "gcppostgres"
      host     = "${var.project_id}/${var.region}/${module.sql-db_postgresql.instance_name}"
      username = "postgres"
      password = var.db_password
      port     = 5432
      superuser = false
  }
  
  
  resource "google_sql_database" "test-database" {
      name     = "test"
      instance = module.sql-db_postgresql.instance_name
      depends_on = [
        module.sql-db_postgresql,
    ]
  }
  
  resource "postgresql_role" "test-read-role" {
      name     = "test-read-role"
      login    = true
      password = var.ia-test-read-password
  }
  
  resource "postgresql_role" "test-write-role" {
      name     = "test-write-role"
      login    = true
      password = var.ia-test-write-password
  }
  
  resource "postgresql_grant" "test-read-grant" {
      database    = "test"
      role        = "ia-test-read-role"
      object_type = "database"
      privileges  = ["SELECT"]
  }
  
  resource "postgresql_grant" "test-write-grant" {
      database    = "test"
      role        = "ia-test-write-role"
      object_type = "database"
      privileges  = ["SELECT","INSERT","UPDATE","DELETE","TRUNCATE"]
  }

I am getting this error when I run 'terraform plan' and I'm clueless what is causing this.

=====
Error: error detecting capabilities: error PostgreSQL version: dial tcp XX.XX:XX:XX:3307: connect: connection timed out
=====

Steps to Reproduce

1. terraform plan

I'm not sure why is this trying to connect to the port 3307. Is this also using some kind of proxy within the GoCloud library? Am I missing something here? Really appreciate any help on this.

[cyrilgdn]

Hi @arunskumar21

It's a bit hard to debug without more information but just to be sure, did you set the GOOGLE_APPLICATION_CREDENTIALS environment variable before running terraform plan ? cf https://registry.terraform.io/providers/cyrilgdn/postgresql/latest/docs#gcp

If yes, are you able to connect manually with gcloud sql connect ?

If yes for both, could you try to activate debug log for Terraform with:

TF_LOG=debug terraform plan

and link the plan output in the issue? (double check that there's no sensitive data inside before).

Thanks.

[arunskumar21]

Hi @cyrilgdn Thanks for your response. This was a network related error and the server in which I was running the terraform runner didn't have access to GCP VPC. Now, this is fixed.

However, I'm facing another issue at the moment.

Error: error detecting capabilities: error PostgreSQL version: x509: certificate signed by unknown authority

I saw similar threads about this error: https://githubmemory.com/repo/cyrilgdn/terraform-provider-postgresql/issues/65 , but since our infrastructure is on GCP CloudSQL, I'm not sure changing the 'scheme' works for me. Could you please share your thoughts on this?

[rajshrimohanks]

@arunskumar21 Did you solve this problem? If yes, what was the root cause? I'm getting the same error.

However, I'm facing another issue at the moment.

Error: error detecting capabilities: error PostgreSQL version: x509: certificate signed by unknown authority

I saw similar threads about this error: https://githubmemory.com/repo/cyrilgdn/terraform-provider-postgresql/issues/65 , but since our infrastructure is on GCP CloudSQL, I'm not sure changing the 'scheme' works for me. Could you please share your thoughts on this?