DEV Community

Andrew B. Collier
Andrew B. Collier

Posted on • Originally published at datawookie.dev on

SSH Tunnel from Docker

I’m building a crawler which I’m going to wrap up in a Docker image. The crawler writes data to a remote MySQL database. However, there’s a catch: the database connection is via an SSH tunnel. Another wrinkle: the crawler is going to be run on ECS, so the whole thing (including setting up the SSH tunnel) needs to be baked into the Docker image.

ECS

This post illustrates the process of connecting to a remote MySQL data via a SSH tunnel from Docker. I’m not sure how secure this is. And there are probably better ways to do this. But it’s a start and it works!

SSH Keypair

First we’ll need to generate a new SSH keypair.

ssh-keygen -N "" -t rsa -f id_rsa
Enter fullscreen mode Exit fullscreen mode

This will not prompt for a passphrase and will generate two files:

  • id_rsa (private key) and
  • id_rsa.pub (public key).

Copy the contents of the public key, id_rsa.pub in ~/.ssh/authorized_keys on the remote host. 🚨 Make sure that the whole public key is on a single line.

Dockerfile

Next we’ll set up the Dockerfile.

FROM ubuntu:20.04

RUN apt-get update -qq && \
    apt-get install -y -qq openssh-client mysql-client && \
    rm -rf /var/lib/apt/lists/*

ARG PRIVATE_KEY

RUN mkdir /root/.ssh

COPY $PRIVATE_KEY /root/.ssh/id_rsa

RUN echo "Host *" > ~/.ssh/config && \
  echo " StrictHostKeyChecking accept-new" >> ~/.ssh/config && \
  echo " ControlMaster auto" >> ~/.ssh/config && \
  echo " ControlPath ~/.ssh/%r@%h:%p" >> ~/.ssh/config

COPY tunnel-mysql.sh .

CMD ./tunnel-mysql.sh
Enter fullscreen mode Exit fullscreen mode

This does the following:

  1. copies an SSH private key across onto the image;
  2. configures SSH not to prompt for confirmation when connecting to a new host; and
  3. copies a BASH script onto the image.

Script

Now for the BASH script.

#!/bin/bash

echo -n "Creating SSH tunnel to $HOST... "
ssh -4 -q -N -f -T -M -L 3306:127.0.0.1:3306 $HOST
echo "Done!"

export MYSQL_PWD=$PASSWORD
mysql -e 'SHOW DATABASES;' --user $USERNAME -h 127.0.0.1

echo -n "Closing SSH tunnel... "
ssh -q -T -O "exit" $HOST
echo "Done!"
Enter fullscreen mode Exit fullscreen mode

This sets up an SSH tunnel to the remote host, connects to the MySQL database on the remote host and executes a simple SQL query, then closes the SSH tunnel.

The SQL query is just a placeholder. Whatever database interactions you need to do would go here. So, in my case, this is where the crawler would kick in.

Building & Running

Let’s build the image, passing id_rsa as the value for the PRIVATE_KEY specified in the Dockerfile. This private key is going to be baked into the image. 💡 It’s equally possible to provide the private key at run time, however, I ultimately opted to supply it at build time.

docker build --build-arg PRIVATE_KEY=id_rsa -t docker-ssh-tunnel .
Enter fullscreen mode Exit fullscreen mode

Once it’s built, run it.

docker run --rm -t --env-file .env docker-ssh-tunnel
Enter fullscreen mode Exit fullscreen mode

We’re passing through some environment variables from an .env file which looks like this (all values fictitious!):

HOST=wookie@63.129.24.53
USERNAME=wookie
PASSWORD=04cmRXCPJ111coQpuqmHH6Uc
Enter fullscreen mode Exit fullscreen mode

And the fruits of our labour:

Creating SSH tunnel to wookie@63.129.24.53... Done!
+---------------------+
| Database |
+---------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+---------------------+
Closing SSH tunnel... Done!
Enter fullscreen mode Exit fullscreen mode

Nice!

Automation

The final component is wrapping this up so that it will build using GitLab CI. Here’s the content of .gitlab-ci.yml:

stages:
  - build

variables:
  IMAGE_NAME: docker-ssh-tunnel
  TAG_LATEST: $CI_REGISTRY_IMAGE/$IMAGE_NAME:latest
  DOCKER_TLS_CERTDIR: ""

build:
  image: docker:stable
  stage: build
  only:
    - master
  services:
    - docker:dind
  script:
  - cp $PRIVATE_KEY id_rsa
  - docker build --build-arg PRIVATE_KEY=id_rsa -t $TAG_LATEST .
  - docker login -u gitlab-ci-token -p $CI_BUILD_TOKEN $CI_REGISTRY
  - docker push $TAG_LATEST
Enter fullscreen mode Exit fullscreen mode

The content of id_rsa is stored in a CI/CD file variable called PRIVATE_KEY. This file is copied across into the Docker build context before the image is built.

So now the image is stored in a registry accessible from ECS. We can set environment variables on the ECS task to hold the values of HOST, USERNAME and PASSWORD.

🚨 One important caveat to this approach is that anybody who has access to the Docker image also has access to the SSH private key. You can (and should) mitigate the risk by (i) ensuring that the image is stored in a secure, private registry and (ii) using non-trivial database credentials.

Discussion (0)