Redash and docker, setting up an ssh tunnel for a database connection

~ 3 min read

data data visualization docker redash ssh tunnel

Redash is an excellent free tool to connect and query your data sources, build dashboards to visualize data and share them with your company.

However as they have now moved to push self-hosting, the Docker solution out of the box does not allow you to use an SSH tunnel to connect to a data source such as MySQL. This article shows how to modify the docker-compose.yml file to create such a tunnel.

This article is using an AWS EC2 instance built from an official pre-built AMI, the instructions should apply to other setups with just the install directory changing.

Once you’ve spun up an EC2 image in your region of choice, ssh into it and navigate to the install directory /opt/redash (you’ve probably already been here setting up SSL if you are running in production)

Setting up an SSH key pair for the tunnel

First, we need to create some additional directories to hold an ssh key, plus then generate the key pair using the following shell commands:-

sudo mkdir /opt/redash/ssh
sudo ssh-keygen -t rsa -b 4096 -C "autossh" -f /opt/redash/ssh/id_rsa

The above should have created /opt/redash/ssh/id_rsa and /opt/redash/ssh/id_rsa.pub the contents of the id_rsa.pub file should be copied and appended to the ~/.ssh/authorized_keys on the remote server we’re going to be tunnelling to.

Modifying docker-compose.yml to add a container running autossh with the new key pair

Now edit /opt/redash/docker-compose.yml and append the following to it, replacing the two {token} placeholders with your own system’s values. The following assumes the remote server is running MySQL or MariaDB on the standard port 3306 locally (127.0.0.1) and expose the tunnel at the Redash docker end on all docker container interfaces (0.0.0.0) and host port 33061 and container network port 3306 (33061:3306).

autossh:
  image: jnovack/autossh
  environment:
    - SSH_REMOTE_USER={user-to-login-of-your-remote-server}
    - SSH_REMOTE_HOST={ip-of-your-remote-server}
    - SSH_TUNNEL_PORT=33061
    - SSH_TARGET_HOST=127.0.0.1
    - SSH_TARGET_PORT=3306
    - SSH_MODE=-L
    - SSH_BIND_IP=0.0.0.0
  ports:
    - "33061:3306"
  restart: always
  volumes:
    - /opt/redash/ssh/id_rsa:/id_rsa
  dns:
    - 8.8.8.8
    - 4.2.2.4

Next at the top of /opt/redash/docker-compose.yml you’ll want to change the depends_on section (lines 4-6) to add autossh to the list so it looks like

depends_on:
  - postgres
  - redis
  - autossh

This tells the various Redash components to wait for autossh to start in addition to the already configured postgres and redis containers. Now let’s tell docker to rebuild with the new config, NO existing settings in Redash will be lost by the rebuild.

sudo docker-compose up -d

That’s it, open up the Redash web interface and add a new MySQL data source, with the MySQL username and password (you already have set up?), just use a host of autossh and port 3306, which will then use the tunnel to get to the remote host you defined in the docker-compose.yml

Need a second tunnel?

Need another tunnel to another host? Just duplicate the autossh: section of the docker-compose.yml renaming the duplicate to something else such as autossh-next: (pick a better name!).

Then:-

  • configure the appropriate username and IP of the remote host (and possible port if it’s not MySQL)
  • generate and install new shh keys saved to a new file name other than /opt/redash/ssh/id_rsa
  • modify the volumes statement in the new section to point at the new key file you just generated
  • run sudo docker-composer up -d to pick up the changes you just made
  • When setting up the data source in Redash the host will be whatever you called the new section e.g. autossh-next

I hope the above helped and saved you time.

all posts →