1 Overview

Overview

SSH tunneling (also known as SSH port forwarding) is a technique used to securely forward network connections between two endpoints with the help of the SSH protocol. In this process, a secure encrypted SSH connection is established between a client and a server, and then network traffic is routed through this connection.

Similar to the MySQL Database Account, the PostgreSQL Database Account and PostgreSQL Dynamic Account support SSH tunneling to the database host. This article describes how the functionality can be tested using Docker.

Setting Up the Bastion Host

Using docker, you can set up a bastion host using the Bastion docker image: Docker.

Step 1: Pull the image.

docker pull binlab/bastion

Step 2: Create an RSA private key and corresponding public key. Place it in postgres-ssh.pem.

ssh-keygen -t rsa -b 4096 -m PEM

Step 3: Create a directory named config in your current working directory and copy the public key into the new file named authorized_keys. You can use this file to store all public keys you want to use to secure an SSH connection to this bastion host.

mkdir config
cp postgres-ssh.pem.pub config/
mv config/postgres-ssh.pem.pub config/authorized_keys

Step 4: Start the bastion host using the following command:

docker run -d \
    --name bastion \
    --hostname bastion \
    --restart unless-stopped \
    -v $PWD/config:/var/lib/bastion \
    -v bastion:/usr/etc/ssh:rw \
    --add-host docker-host:172.17.0.1 \
    -p 22222:22/tcp \
    -e "PUBKEY_AUTHENTICATION=true" \
    -e "GATEWAY_PORTS=true" \
    -e "PERMIT_TUNNEL=true" \
    -e "X11_FORWARDING=false" \
    -e "TCP_FORWARDING=true" \
    -e "AGENT_FORWARDING=true" \
    binlab/bastion

You can attempt a connection to it to verify if the auth setup has been done correctly.

ssh -i postgres-ssh.pem -p 22222 bastion@127.0.0.1

Setting up the Postgres instance

Using the official Postgres Docker image, you can quickly start an instance. Use the POSTGRES_INITDB_ARGS to allow password authentication from localhost and the bastion host. You can use port 54320 to allow connection directly to the Postgres instance.

docker pull postgres
docker run -p 127.0.0.1:54320:5432 --name snap-postgres -e POSTGRES_PASSWORD=mypassword -e POSTGRES_INITDB_ARGS="--auth-host=md5 --auth-local=md5" -d postgres

You can use psql to set up tables, for testing, or to verify the PostgreSQL instance is in order.

Connecting the two Containers

Create a docker network that contains both containers.

Now, you are set to configure a PostgreSQL account in your local SnapLogic application to connect to the Bastion host and PostgreSQL instance. You need to enter the private key (postgres-ssh.pem) either by uploading a file or pasting the string into the account.

A passphrase is required if you use the latter method.

Notice the use of port 5432, since you can access it within the bastion host, which is on the Docker network you created.

SnapLogic Documentation

SSH Tunneling Testing with PostgreSQL

Overview

Setting Up the Bastion Host

Setting up the Postgres instance

Connecting the two Containers