When using a database like PostgreSQL, we usually use a managed database like Amazon RDS nowadays.

Then we would configure to allow only some servers to connect to the database (e.g. only EC2 servers can connect to a database on RDS).

But sometimes, we want to connect to such a database and check something from our local. In that case, we can use an SSH tunnel way.

This post describes how to do that.

Docker image

To connect to your database, you have to execute a command like psql, mysql, or something like that.
So let's prepare a Docker image which is compatible with your actual database.

In this post, I plan to use PostgreSQL as an example, but there is no PostgreSQL specific information. Please select your favorite one.

SSH tunnel

Let's try to create an SSH tunnel next!

The actual command would be like this:

$ ssh -L 15432:db.some-service.jp:5432 web.some-service.jp -N

To do that, you have to be able to ssh web.some-service.jp, if your SSH tunneling failed, please try ssh -i {your_ssh_key} {your_ssh_user}@web.some-service.jp or make sure your ssh config is correct.

The above command means forwarding your local's 15432 port to db.some-service.jp's 5432 port.

By specifying -N option, the command does not execute a remote command. This is useful for just forwarding ports.

Don't close this command and let's try to actually connect to your database in the next step!

Connect to your database

Please open a new tab and let's try to connect to your database.

The actual command would be like this:

$ docker run -it --rm postgres:11.9 psql -h host.docker.internal -p 15432 -U db-user -W db_name

# Enter your db password
Password:
db_name=> SELECT * from users;
...

You can connect to the special DNS name host.docker.internal which resolves to the internal IP address used by the host.

And then by specifying 15432 which is forwarded in the previous step, you can connect to your database and execute commands there!

At last, don't forget to close your forwarding command!

Summary

• Using an SSH tunnel and Docker, we can connect to our remote database easily.

References

• https://docs.docker.com/docker-for-mac/networking/#use-cases-and-workarounds