could not start transaction: dial tcp : connect: connection timed out

[Manoj-07]

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version-1.0.11

##provider version

postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.12.0"
 } 
 
provider "postgresql" {
  host             = aws_db_instance.test_db.address
  port             = aws_db_instance.test_db.port
  username         = aws_db_instance.test_db.username
  password         = var.postgres_password
  expected_version = aws_db_instance.test_db.engine_version
  sslmode          = "require"
}

My aws_db_instance is created using a private subnet and it is not publicly_accessible.
while I am trying to create the following by using the above provider version I am getting timeout error
resource "postgresql_database" "dev_db" {
}
could anyone please let me know how to resolve it?

[sachinb4u]

Facing same issue while trying to create multiple databases. I tried with public access and disabling SSL but still it fails with connection timeout

provider "postgresql" {
  scheme   = "awspostgres"
  host     = aws_db_instance.server.address
  port     = aws_db_instance.server.port
  username = aws_db_instance.server.username
  password = aws_db_instance.server.password

  superuser = false
}


resource "postgresql_database" "db" {
  for_each = var.databases

  provider = "postgresql"
  name     = each.key
  owner    = aws_db_instance.server.username
}

Error

Error: error detecting capabilities: error PostgreSQL version: dial tcp 10.0.101.243:5432: connect: operation timed out
│ 
│   with postgresql_database.db["dealsdb"],
│   on database.tf line 13, in resource "postgresql_database" "db":
│   13: resource "postgresql_database" "db" {

[jeffling]

Also facing the same issue. It tries to dial a local IP

[cyrilgdn]

@Manoj-07 From where are you running Terraform?

If you are outside the VPC, it will not be able to reach your instance, you'll need a network load balancer, a VPN or SSH tunnel to execute it.
If you are inside your VPC, maybe you miss a security group rule? Could you share the full error message?

[cyrilgdn]

@sachinb4u Same as for @Manoj-07 . Do you run terraform from outside your VPC?

dial tcp 10.0.101.243:5432: connect: operation timed out

This is a private IP so can be reachable only from within the VPC.

[nkhalilian]

What if I apply the plan from Terraform cloud?
I get this error Error: could not start transaction: dial tcp 8.0.65.148:5432: connect: connection timed out which has the private ip for my RDS instance.

[solarmosaic-kflorence]

@cyrilgdn I am on version 1.14.0 and I see the same issue with AWS RDS PostgreSQL 13.4

Error: error detecting capabilities: error PostgreSQL version: dial tcp 44.193.XXX.XXX:5432: connect: operation timed out

I tried making the instance publicly available but still got the same error. I assumed making it publicly available would rule out VPC issues, is that assumption not correct?

EDIT: apparently not. Even for publicly accessible RDS instances, VPC/security groups still need to be configured.

[nk9]

I'm having this problem as well. My Postgres instance is in a private subnet, and I'm using Terraform Cloud. The IP reported is a private IP.

Unless I'm mistaken, it sounds like you cannot use this provider if your install is in the cloud and you are following security recommendations and disallowing public access to the instance.

I'm using Flask for this project, and I was trying to use this provider to install the PostGIS extension. Instead of doing this in Terraform, I solved the problem by moving the create extension code into the Flask initialization:

__init.py__

db.engine.execute("CREATE EXTENSION IF NOT EXISTS postgis")

[solarmosaic-kflorence]

@nk9 I have successfully used this provider with RDS in a private subnet. It just requires all the proper VPC plumbing to ensure wherever you are running the provider from has access to that subnet.

[probsJustin]

@nk9 I have successfully used this provider with RDS in a private subnet. It just requires all the proper VPC plumbing to ensure wherever you are running the provider from has access to that subnet.

Do you happen to have an example of this success? this would be helpful as this seems to be a pretty widely seen problem

[larskinder]

I am pretty sure that he meant the following.
A.) Either place your RDS in a public subnet and allow access via the SG to your RDS (would not recommend this)
B.) Use a bastion host to execute Terraform code that needs access to your RDS instance (if you use private subnets)
C.) Use a VPC Lambda to modify the RDS instance and use Terraform to create this Lambda (if you use private subnets)

I was confused as well, as the documentation does not make that clear. Maybe that is context to add.

[robacarp]

Hm, I landed here after assuming that the awspostgres / GoCloud integration would take care of this.

The docs:

By default, the provider uses the lib/pq library to directly connect to PostgreSQL host instance. For connections to AWS/GCP hosted instances, the provider can connect through the GoCloud library. GoCloud simplifies connecting to AWS/GCP hosted databases, managing any proxy or custom authentication details.
AWS
To enable GoCloud based connections to AWS RDS instances, set scheme to awspostgres and host to the RDS database's endpoint value. (e.g.: instance.xxxxxx.region.rds.amazonaws.com)

That sure seems like a fancy dannce, but if the postgres instance was publicly accessible, no magic would be needed to connect to it. Since it is in a private subnet, if I want to create roles/databases I need to build a proxy or bastion host that it can bounce through.

Perhaps it's unreasonable given what it would take, but I assumed that GoCloud would "manage a proxy" for me?