weird error detecting capabilities: error PostgreSQL version: dial tcp 127.0.0.1:5432: connect: connection refused error

[tristandibbens]

Hi,

Please can you help me.
I have a Fargate/RDS terraform build.
I'm constantly struggling trying to get Terraform to work with the postgreSQL connector. (I'm still here at 4pm on a Saturday)

I ran a tf apply to apply a database encryption from not, which destroyed the database as expected with the view to rebuild it encrypted.
Then it errored on the postgresql connection from the postgresql provider
Since then I cant, destroy, state rm, apply or anything
I have manually edited the state file but tf didnt like that so I have restored that
I cant seem to get past the point of this weird error detecting capabilities: error PostgreSQL version: dial tcp 127.0.0.1:5432: connect: connection refused error

All I wanted to do was encrypt the database at rest. Its taken probably two days of time so far and still no further with it.

Terraform Version

1.0.1 and 1.0.5

Affected Resource(s)

postgresql _role and postgresql_database

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.
Its just the dependency between the db and this connector that is flakey

Terraform Configuration Files

Expected Behavior

Terraform should have ignored the provider side of things - since the database was destroyed and does not exist in the state file now - why is it trying to connect at all at this point!

Actual Behavior

Error: error detecting capabilities: error PostgreSQL version: dial tcp 127.0.0.1:5432: connect: connection refused
Error: error detecting capabilities: error PostgreSQL version: dial tcp 127.0.0.1:5432: connect: connection refused
Makefile:60: recipe for target 'tfapply' failed

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

Important Factoids

Trying to make an encrypted rds psql instance, this cyrilgdn connector doesnt seem to like that at all
Just added vpc peering to allow this box to communicate better with the db, not tested since not past this issue

References

#120

[alert101-gof]

We have a similar problem with an Azure PostgreSQL instance. We are trying to upgrade the server and get the same error. I'm thinking the expected_version parameter of the PostgreSQL provider isn't working properly.

Here's a simple project I used to reproduce the error, versions used:

• terraform 1.0.0
• azurerm 2.64.0
• postgresql 1.13.0

All that needs to be changed are the locals. After running apply with the values below I end up with a 9.6 server with the role myRole. After that if I've tried changing just psql_version to 10 or both psql_version and expected_version, either way I get the same error as OP already during the plan stage on the postgresql_role.user resource:
Error: error detecting capabilities: error PostgreSQL version: dial tcp 127.0.0.1:5432: connect: connection refused

locals {
    rg               = "myRg"    # resource group name
    ip               = "1.2.3.4" # your ip for the firewall rule
    psql_version     = "9.6"     # postgresql server version
    expected_version = "9.6"     # postgresql provider expected version
}

terraform {
  backend "local" {
  }

  required_version = "1.0.0"

  required_providers {
    azurerm = {
      version = "= 2.64.0"
      source  = "hashicorp/azurerm"
    }
    postgresql = {
      version = "= 1.13.0"
      source  = "cyrilgdn/postgresql"
    }
  }
}

provider "azurerm" {
  skip_provider_registration = true

  features {
  }
}

resource "azurerm_postgresql_server" "psql_server_test" {
  name                              = "psql-test-server-123456"
  location                          = "westeurope"
  resource_group_name               = local.rg
  administrator_login               = "adminuser"
  administrator_login_password      = "myAdminPassword123"
  version                           = local.psql_version
  ssl_enforcement_enabled           = true
  ssl_minimal_tls_version_enforced  = "TLS1_2"
  sku_name                          = "B_Gen5_1"
  storage_mb                        = 5120
  auto_grow_enabled                 = false
  backup_retention_days             = 7
  geo_redundant_backup_enabled      = false
  public_network_access_enabled     = true
}

resource "azurerm_postgresql_firewall_rule" "psql_fw" {
  name                = "my-ip"
  resource_group_name = local.rg
  server_name         = azurerm_postgresql_server.psql_server_test.name
  start_ip_address    = local.ip
  end_ip_address      = local.ip
}

provider "postgresql" {
  host              = azurerm_postgresql_server.psql_server_test.fqdn
  port              = 5432
  username          = "${azurerm_postgresql_server.psql_server_test.administrator_login}@${azurerm_postgresql_server.psql_server_test.name}"
  password          = azurerm_postgresql_server.psql_server_test.administrator_login_password
  database_username = azurerm_postgresql_server.psql_server_test.administrator_login
  sslmode           = "require"
  connect_timeout   = 15
  superuser         = false
  expected_version  = local.expected_version
}

resource "postgresql_role" "user" {
  depends_on = [
    azurerm_postgresql_server.psql_server_test,
    azurerm_postgresql_firewall_rule.psql_fw
  ]
  name        = "myRole"
  password    = "myRolePassword123"
  login       = true
  roles       = []
  search_path = []
}

Update: I'm able to work around this issue by updating the server version and expected version to the new version number and then using -target azurerm_postgresql_server.psql_server_test to just upgrade the server. After that the postgresql provider works just fine. This is however NOT an option with our actual automated deploy pipelines.

[jamesdowning-chip]

Seeing the same issue as above, in this scenario deploying an aws_rds_cluster and postgresql_role/postgresql_database resources in the same directory.

provider "postgresql" {
  alias            = "rds_master_user"
  host             = module.database.rds_endpoint
  port             = module.database.rds_port
  username         = module.database.rds_master_username
  password         = random_string.admin_rds_master_password.result
  expected_version = module.database.rds_engine_version
  sslmode          = "require"
  superuser        = false
}

resource "postgresql_database" "database" {
  provider   = postgresql.rds_master_user
  owner      = module.database.rds_master_username
  lc_collate = "en_US.UTF-8"
  lc_ctype   = "en_US.UTF-8"
  encoding   = "UTF8"
  depends_on = [module.database]
}

ERROR

postgresql_database.database: Refreshing state... [id=********-development]
2021-11-03T12:49:15.044Z [INFO]  backend/local: plan operation completed
╷
│ Error: dial tcp 127.0.0.1:5432: connect: connection refused
│ 
│   with postgresql_role.rw_app_user_1,
│   on database-users.tf line 2, in resource "postgresql_role" "rw_app_user_1":
│    2: resource "postgresql_role" "rw_app_user_1" {
│ 
╵
╷
│ Error: could not start transaction: dial tcp 127.0.0.1:5432: connect: connection refused
│ 
│   with postgresql_database.database,
│   on database.tf line 2, in resource "postgresql_database" "database":
│    2: resource "postgresql_database" "database" {
│ 
╵

I presume the code worked initially but every subsequent run produces the above error. Only current workaround is to hardcode the database endpoint under the host variable in provider "postgresql"

Update: Seemed to have worked around this issue via running the usual Terraform plan but with -refresh-only=true. It appears the data sources refuse to refresh and so just pull default values? In my particular case the Terraform workflow would usually be run within a CI pipeline targeting an otherwise unreachable DB. Even though running locally timed out due to the obvious network limitation the refresh seemed to do the trick.

[fraozy]

I also had a similar problem. It is not a solution, but I could fix it removing the resources with problem using terraform state rm.

After remove the resources, all back to work fine.

[ak55toy]

Having the same issue as above. Worked around it by hardcoding the hostname and running a plan and apply. After this, changed it back and it works....weird!

[nitrocode]

Hard coding worked for me. I used terraform show to find the endpoint and then hard coded it in the postgresql provider and it worked...

provider "postgresql" {
  # host    = module.aurora_postgres_cluster.master_host
  host      = "aurora-example-shared-writer.snip.snip.org"
  username  = module.aurora_postgres_cluster.master_username
  password  = local.admin_password
  superuser = false
}

Somehow the endpoint is returning localhost or 127.0.0.1 instead of the correct record.

[Draiget]

Have the same weird issue with below config:

provider "postgresql" {
  alias           = "dev-pg"
  host            = "<ip_or_dns_name>"
  port            = 5432
  database        = "<redacted>"
  username        = "<redacted>"
  password        = "<redacted>"
  superuser       = false
}

And I'm getting Error: error detecting capabilities: error PostgreSQL version: dial tcp 127.0.0.1:5432: connect: connection refused when trying to use postgresql_extension. Both with IP and DNS name in host.
Using provider version 1.16.0 and Terraform version 1.0.5.

UDP:
Resolved it, I had an alias in provider block but in the actual resource I've not using it and those settings was taken as default ones (some of them was empty), weird, but ok.

[pie-r]

Hello,
I think I've found the solution to this issue.

Pre-requisite:

• no networking issues/permission

The problem is that the meaning of endpoint between rds aurora and rds is different so the fix is to use:
for RDS instances:

• db_instance_address

For Aurora:

• cluster_endpoint

---

During my test I was using RDS postgres:

First of all, I was using db_instance_endpoint, getting the error:

YYYYYY.XXXXX.REGION.rds.amazonaws.com:5432 unknown host

Remember that thedb_instance_endpoint output is something like:

YYYYYY.XXXXX.REGION.rds.amazonaws.com:5432

So I tried to hardcode:

• YYYYYY.XXXXX.REGION.rds.amazonaws.com -- All good
• YYYYYY.XXXXX.REGION.rds.amazonaws.com:5432 -- Fail with error YYYYYY.XXXXX.REGION.rds.amazonaws.com:5432:5432 unknown host (make sense)

Then I tried with db_instance_address and all was working fine.

To me is not clear why, if using db_instance_endpoint the error message is not as expected.
It should give YYYYYY.XXXXX.REGION.rds.amazonaws.com:5432:5432 unknown host with two times the port value

[roberthluo]

Did anyone find a permanent solution to this issue? I also had to hard code it to get it to work, but it's extremely fragile and forces creation + deletion of the instance.

Using provider registry.terraform.io/cyrilgdn/postgresql v1.18.0 and Terraform version Terraform v1.3.7 on darwin_arm64.

provider "postgresql" {
  # host             = module.YYY.host
  host             = "dev-XXX.us-east-1.rds.amazonaws.com"
  port             = module.YYY.port
  username         = module.YYY.user
  password         = module.YYY.password
  superuser        = false
  sslmode          = "require"
  connect_timeout  = 5
  expected_version = 13.7
}

[roberto-caylent]

I'm also having the same issue

[tomfankhaenel]

I also have the same issue and it could be worked around with the ".refresh=false" parameter: "terraform plan -refresh=false"
This however does not resolve the issue itself and can cause other unwanted behavior. As mentioned in #268 there could be an additional attribute or something like that.

[torreytsui]

It seems that it always try to connect to the DB to refresh the state as @tomfankhaenel and #268 detailed

Workaround: Use -target

Example below (copied from @alert101-gof in previous thread)

terraform plan -target=azurerm_postgresql_server.psql_server_test
terraform apply
terraform plan
terraform apply

Add the changing resources into the plan target, apply it, and then do the normal plan and apply

The target will skip the postgres provider refresh state (and avoided the DB connection error) and only focus on the resource changes which were blocked by the connection refused error

The workaround also worked along with -replace. The same issue triggers when we recreate the RDS.

terraform plan -target=azurerm_postgresql_server.psql_server_test \
    -replace=azurerm_postgresql_server.psql_server_test

It is not ideal as you need to manually add and remove the targets but at least this keeps TF happy and don't need to hack the states.

[rdbisme]

On a brand new Terraform project:

data "aws_secretsmanager_secret_version" "root" {
  secret_id = data.aws_secretsmanager_secret.root.id
}

provider "postgresql" {
  alias = "xxx"
  host     = jsondecode(data.aws_secretsmanager_secret_version.root.secret_string)["host"]
  username = jsondecode(data.aws_secretsmanager_secret_version.root.secret_string)["username"]
  password = jsondecode(data.aws_secretsmanager_secret_version.root.secret_string)["password"]
  port     = jsondecode(data.aws_secretsmanager_secret_version.root.secret_string)["port"]
}

resource "postgresql_role" "my_admin" {
  name     = local.my_admin_role_name
  login    = true
  password = random_password.my_admin.result
}

Errors with error detecting capabilities: error PostgreSQL version: dial tcp 127.0.0.1:5432: connectex: No connection could be made because the target machine actively refused it.. I tried to hardcode the host, but it doesn't work either.

[Sweenz14]

Having the same issue. When I had no alias on the provider it worked fine. Now I am needing to use alias to work with different clusters and multiple providers defined. Has anyone found a way to still use an alias and not get the connection refused error?

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

[vlalwani-tm]

Running into this issue on Amazon Aurora as well as regular Postgres RDS