Damir's Corner.

Database connections over SSH

In practice, it is not uncommon to have access to only a few ports on a server. If that is the case, the database port is probably not one of them. However, you can still connect to the database as long as you have SSH access to the server.

To do this, you need to set up an SSH tunnel with the following command:

ssh -L 5417:localhost:5407 username@server

The -L 5417:localhost:5407 option is responsible for setting up the SSH tunnel, with the individual parts being as follows:

• 5417 is the local port where the tunnel is exposed
• localhost is the name of the remote host to which the tunnel connects (from the server's point of view, i.e. localhost represents the server)
• 5407 is the server port to be made accessible through the tunnel (in our case, the database port)

After the connection is established, you can connect to the database through the local port as if it were running locally on your machine, e.g. jdbc:postgresql://localhost:5417/database.

This is great for running database migrations or connecting to the database from an application you are developing locally. However, if you want to connect from DataGrip, you can use the built-in support for SSH tunnels. In the DB connection properties dialog, switch to the SSH/SSL tab, tick the Use SSH tunnel check box, and click the ... button to the right of it:

In the next dialog you can enter the details of your SSH connection and test the SSH connection itself:

Once you have that set up, you can close the dialog and return to the General tab of the previous dialog, where you can enter the details of the database connection (as seen from the server, i.e. you can use localhost as the Host and enter the actual Port).

You can use an SSH tunnel even if you cannot connect directly to a server (because of a firewall), as long as you can make an SSH connection to another server on the same network that acts as a proxy. In this case, you must use a so-called proxy jump by adding the -J username1@proxy-server option to the command:

ssh -L 5417:localhost:5407 -J proxyusername@proxy-server username@server

To avoid having to specify the proxy jump every time you connect, you can configure it in your ~/.ssh/config file:

Host proxy-server
    HostName proxy-server
    User proxyusername

Host server
    Hostname server
    User username
    ProxyJump proxy-server

This file allows you to use a simplified ssh command without having to specify the proxy server (or username):

ssh -L 5417:localhost:5407 server

Unfortunately, I could not get the SSH tunnel to work with a proxy jump in DataGrip. There is no way to explicitly configure a proxy jump server in DataGrip. But even with the configuration in the ~/.ssh/config file, DataGrip cannot connect and reports the following error:

Cannot connect to remote host: net.schmizz.sshj.transport.TransportException: Server closed connection during identification exchange

Of course, you can also set up the tunnel yourself and configure DataGrip to connect to the local port of this SSH tunnel (without SSH configuration in DataGrip):

The disadvantage of this approach is that you must remember to always set up the SSH tunnel yourself before connecting to it from DataGrip.

SSH is a very flexible tool. In this post, I have described some ways you can use it to connect to remote databases even if you do not have direct access to the servers and ports they are running on.