paint-brush
How to Connect Your Bastion Server to a PostgreSQL Server with an SSH Tunnelby@theBenForce
10,361 reads
10,361 reads

How to Connect Your Bastion Server to a PostgreSQL Server with an SSH Tunnel

by Ben ForceMarch 30th, 2021
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

Too Long; Didn't Read

An SSH tunnel is a way to redirect requests to a port on localhost to a remote server. Some DB clients handle this automatically for you, but if you want to interact with the database through the shell you'll need to create it yourself. To create an SSH config with all of the tunnel settings, edit your.config to create the tunnel and add your. SSH key to the bastion server so that you don't have to type in your password every time you connect to the database.

Company Mentioned

Mention Thumbnail
featured image - How to Connect Your Bastion Server to a PostgreSQL Server with an SSH Tunnel
Ben Force HackerNoon profile picture

If you use a bastion server to access PostgreSQL or any other SQL database for that matter, you'll need to create an SSH tunnel to access it. Some DB clients handle this automatically for you, but if you want to interact with the database through the shell you'll need to create it yourself.

What is an SSH Tunnel?

Put simply, an SSH tunnel is a way to redirect requests to a port on localhost to a remote server.

Creating the Tunnel

Before creating the tunnel, you will need to know three things: the bastion server's hostname, your username on the bastion server, and the SQL server's hostname. You should also verify that you can ssh into the bastion server. To do this, run the following command and enter your password when prompted.

ssh <username>@<bastion_server>

Adding your Profile (Optional)

You can add your SSH key to the bastion server so that you don't have to type in your password every time you connect. To do so, use the following command:

ssh-copy-id <username>@<bastion_server>

Open the Tunnel

Now that you know you can connect to the bastion server, open the tunnel like this:

ssh -L localhost:5433:<sql_server>:5432 <username>@<bastion_server>

You should see the standard bash prompt that came up when you directly logged into the bastion server. Leave this window open to keep the tunnel open. Now when you connect to the port 

5433
 on 
localhost
, you'll actually be talking to the SQL server on port 
5432
!

To verify that your connection works, open a new terminal and execute:

psql --port=5433 --host=localhost -c "SELECT * FROM pg_catalog.pg_tables"

You should see a list of tables returned.

Create SSH Config (Optional)

Okay, I forget the command to open a tunnel all the time. Plus it's a pain to have to look up the hostname of two servers every time you want to connect to the database. To simplify things, you can create an SSH config with all of the tunnel settings. Edit your 

~/.ssh/config
 file and add the following entry:

Host bastion-production
  HostName <bastion_server>
  User <username>
  LocalForward localhost:5433 <sql_server>:5432

Now to create the tunnel, just run SSH bastion-production and it will behave the same as the previous command.

Previously published at https://justwriteapps.com/connect-to-postgresql-through-ssh-tunnel/