Using OpenTofu ephemeral resources to create a SSH tunnel to a Postgres DB

README.md

OpenTofu ephemeral ssh tunnel to PostgresDB

Create the docker-compose.yaml and start the containers. To make this work you need a public ssh key to be injected into the "bastion" container.

SSH_PUBLIC_KEY=`cat ~/.ssh/id_rsa.pub` podman compose up

Save the name of the database container:

export DB_CONTAINER_NAME=$(podman ps --format '{{.Names}}'| grep "db-1")

List all databases to see what we have there:

podman exec -it ${DB_CONTAINER_NAME} psql -U postgres -l

Store main.tf into a file and run the following:

tofu init
tofu apply -var=target_db_domain=${DB_CONTAINER_NAME}

docker-compose.yaml

services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example # username is "postgres"

  ubuntu:
    image: ubuntu:latest
    restart: always
    command: sh -ceux "apt update && apt install -y ssh && mkdir /home/ubuntu/.ssh && echo $SSH_PUBLIC_KEY > /home/ubuntu/.ssh/authorized_keys && service ssh start && while true; do echo sleeping && sleep 60; done"
    ports:
      - 32222:22

main.tf

terraform {
  required_providers {
    tunnel = {
      source  = "dfns/tunnel"
      version = "1.5.1"
    }
    postgresql = {
      source  = "a0s/postgresql"
      version = "1.14.0-jumphost-1"
    }
  }
}

variable "bastion_host" {
  type    = string
  default = "127.0.0.1"
}

variable "bastion_port" {
  type    = number
  default = 32222
}

variable "bastion_user" {
  type    = string
  default = "ubuntu"
}

variable "bastion_ssh_private_key" {
  type    = string
  default = "~/.ssh/id_rsa"
}

variable "bastion_private_key_passphrase" {
  type      = string
  nullable  = true
  ephemeral = true
}

variable "target_db_domain" {
  type      = string
}

ephemeral "tunnel_ssh" "postgres" {
  target_host        = var.target_db_domain
  target_port        = "5432"
  ssh_host           = var.bastion_host
  ssh_user           = var.bastion_user
  ssh_port           = var.bastion_port
  ssh_key            = file(var.bastion_ssh_private_key)
  ssh_key_passphrase = var.bastion_private_key_passphrase
}

provider "postgresql" {
  host     = ephemeral.tunnel_ssh.postgres.local_host
  port     = ephemeral.tunnel_ssh.postgres.local_port
  password = "example"
  sslmode  = "disable"
  username = "postgres"
}

resource "postgresql_database" "my_db1" {
  name = "my_db1"
}