How to work with postgresql resource through bastion host?

[ghost]

This issue was originally opened by @andreichernov as hashicorp/terraform#18591. It was migrated here as a result of the provider split. The original body of the issue is below.

---

How I can make some actions with Postgresql db on AWS RDS through bastion host in the same VPC

provider "postgresql" {
  host     = "${var.db_host}"
  port     = "${var.db_port}"
  username = "${var.db_master_user}"
  password = "${var.db_master_password}"
  database = "${var.db_name}"
}

resource "postgresql_role" "api_role" {
  name            = "api"
  superuser       = false
  create_database = true
  login           = true
  password        = "${var.db_role_api_password}"
  create_role     = false
  connection {
    type                = "ssh"
    host                = "${var.db_host}"
    port                = 5432
    private_key         = "${var.private_key}"
    bastion_host        = "${var.bastion_public_ip}"
    bastion_port        = 22
    bastion_host_key    = "${var.public_key}"
    bastion_private_key = "${var.private_key}"
    bastion_user        = "ec2-user"
  }

but after terraform plan terraform gives an error Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp 10.0.31.22:5432: getsockopt: connection timed out

Of course, I know that I can create following ssh config on my local PC:

Host bastion-multiplex
 HostName _bastion-public-ip-here_
 Port 22
 User ec2-user
 IdentityFile  _path-to-ssh-key-here_
 ControlMaster auto
 ControlPersist 20m
 LocalForward 54320 some-name.rds.amazonaws.com:5432

or I can add null resource:

resource "null_resource" "ssh-tunnel" {
  depends_on = ["aws_instance.bastion"]

  provisioner "local-exec" {
    command = "ssh -i ${path.root}/${var.environment_tier}.pem -L 54320:${var.db_host}:${var.db_port} ec2-user@${local.bastion_public_ip} -N"
  }
}

but I am looking for right terraform way to solve this.

[max-rocket-internet]

We are also looking for a way to do this. It would be great if the provider could support this!

For example, other PostgreSQL tools support this, like Postico and pgAdmin etc.

I think it could work like this on the provider:

provider "postgresql" {
  host     = "${var.db_host}"
  port     = "${var.db_port}"
  username = "${var.db_master_user}"
  password = "${var.db_master_password}"
  database = "${var.db_name}"
  tunnel = {
    type                = "ssh"
    private_key         = "~/.ssh/some-key_id"
    bastion_host        = "my-ssh-bastion.domain.com"
    bastion_port        = 22
  }
}

resource "postgresql_role" "api_role" {
  name           = "api"
  password    = "${var.db_role_api_password}"
  }

[andreichernov]

If anyone know any workaround to solve this problem please let know. I cannot to solve it even with using manual ssh forwarding because terraform not support #2430 dependency for providers and postgresql provider produce an error because started without dependency from ssh forwarding resource

resource "null_resource" "run_ssh_forwarding" {
  depends_on = [
    "aws_instance.bastion"
  ]

  provisioner "local-exec" {
    command = "ssh -i ${var.private_key} -4 -fN -o ControlMaster=auto -o ControlPersist=5m -o ControlPath=${local.ssh_fwd_socketfile} -L ${var.local_port}:${var.remote_host}:${var.remote_port} ${local.bastion_name} >./stdout.log 2>./stderr.log"
    interpreter = ["/bin/sh", "-c"]
  }

  provisioner "local-exec" {
    when    = "destroy"
    command = "( [ -e \"${local.ssh_fwd_socketfile}\" ] &&  ssh -S ${local.ssh_fwd_socketfile} -O exit ${local.bastion_name} )"
    interpreter = ["/bin/sh", "-c"]
  }
}

[holms]

Any progress on this? How can one even not have this feature, I mean everything in AWS should be in VPC right?

[renatoargh]

Unfortunately, not being able to pass ssh tunnel configs to the provider is a big show stopper for us. Would be more than awesome having a configs like this.

I managed to work around by using @andreichernov answer AND version v0.1.0 (which waits before trying to connect, so there is time for the null_resource to initiate the connection). The problem is that it works only for the first execution, not when trying to update the stack, when the provisioner is not re-executed, for example. Other drawback is to manually have to close the connection to the bastion after terraform runs.

Anyways, thanks a lot for the work so far and a big +1 to this feature request!

[zytek]

Any progress on this? How can one even not have this feature, I mean everything in AWS should be in VPC right?

I successfully use VPN that gives me access to VPC.

[raskam]

Simple workaround for those who do not want to install VPN. It proxies all traffic to specific IP address through bastion host using SSH.

sshuttle -r <user>@<bastion_host> <rds_instance_ip>/32

Then run terraform as you would normally.

[hatch-carl]

An unmentioned but also valid way to accomplish this is by setting up a tunnel using SSH, then running terraform with localhost as host.

$ ssh -L 5432:remote-database-host.cloud.example.com:5432 -N USER@bastion-host.example.com

[j94305]

@hatch-carl : The challenge here is that the remote endpoint of PostgreSQL (Amazon RDS) will only be created within the course of executing Terraform. At the time of starting "terraform plan", there is nothing. No VPC to tunnel into, no bastion host, no database. At the time of starting "terraform apply", there may also be nothing. The entire environment will only be created by Terraform.

So, in essence, two features are needed:

1. Providers need to be able to depend on resources being available (resources that may be not be present at the time of "terraform plan"). As a workaround, I am currently exploring the possibility of using a local backend to share the output of one Terraform apply with another. Terraform should support inter-module dependencies that lead to dependent modules possibly being ignored because dependent provider pre-conditions are not (yet) met. If you want to have the plan checking capability retained in a consistent way, Terraform may start with a message "These modules cannot be planned/applied because required provider resources are missing: postgres_db_setup". It may finish a first "apply" run saying something along the lines of "Terraform has now created new resources that were pre-conditions for modules that could not be run. The next 'plan' and 'apply' will also consider these modules: postgres_db_setup". The next "terraform apply" would leave the results of the previous run intact and continue with the dependent, previously ignored module. Incidentially, as this chain of dependencies may be longer, one does not know in advance how many cycles will be necessary in order to complete the entire setup. This topic seems to be a rather old issue: https://github.com/hashicorp/terraform/issues/2430

2. The PostgreSQL provider (and other providers as well, e.g., Kubernetes, Rundeck) needs to support ssh tunnels to better accomodate the network topology of secured VPCs. This should be in alignment and consistent with resources being created and becoming available only within the course of Terraform (module) execution. I look forward to this being completed and hope it will really fill the gap and not call for more workarounds: https://github.com/terraform-providers/terraform-provider-postgresql/pull/80

Cheers, --j.

[J0zi]

Any update here? Thank you.

[j94305]

Not as far as I know... It's been quiet and there were no announcements into this direction. I'm still fiddling with workarounds. And I am exploring Pulumi as a replacement for Terraform.

[voltechs]

There is a preliminary draft that has been opened by @dominik-lekse.

It looks like some progress has stalled. He does state that he's willing to open up write access for those interested in contributing, however, he doesn't state what the limitations he's experiencing are. I'm not particularly knowledgeable of Go, but would be willing to pitch in if I had an idea of how to contribute. Or if someone who knows Go/Terraform could push this across the finish line, there is a cold beer waiting for them from me :D

[minhajuddin]

I blogged about the workaround mentioned here in detail: https://minhajuddin.com/2020/05/09/how-to-use-a-single-aurora-cluster-for-multiple-databases-each-with-its-own-restricted-user/#Connecting-to-an-RDS-database-via-an-SSH-tunnel . This requires you to have an SSH tunnel open while terraform runs a plan or apply.

[voltechs]

This requires you to have an SSH tunnel open while terraform runs a plan or apply.

Thanks for the blog post. This of course requires the existence of the Bastion host, making dependencies tricky. One cannot stand up a given infrastructure in one go, let alone without prior knowledge or additional tooling to complete the infrastructure application. :(