DEV Community

Maxiviper117
Maxiviper117

Posted on

Securely Connect to a Remote PostgreSQL Database with SSH Tunneling

#ssh #tunneling #postgressql #security

Securely Connect to a Remote PostgreSQL Database with SSH Tunneling

Exposing your database port directly to the internet is like leaving your house unlocked with a “welcome” sign on the door. A safer approach is to use an SSH tunnel (a secure pipe between your local machine and the remote server). This way PostgreSQL can stay bound to localhost on the server, while you still connect from your laptop.

1. Create an SSH key pair

If you don’t already have an SSH key, generate one locally:

ssh-keygen -t ed25519 -C "your_email@example.com"
Enter fullscreen mode Exit fullscreen mode
  • Private key: ~/.ssh/id_ed25519
  • Public key: ~/.ssh/id_ed25519.pub

Keep the private key safe and never share it. The public key is what you’ll add to the server.

2. Copy your public key to the server

Replace user@remote-server.com with your real username and server address:

ssh-copy-id -i ~/.ssh/id_ed25519.pub user@remote-server.com
Enter fullscreen mode Exit fullscreen mode

If ssh-copy-id isn’t available, you can manually paste the contents of the .pub file into the server’s ~/.ssh/authorized_keys.

3. Add a convenient SSH config entry

Save yourself some typing by creating an entry in ~/.ssh/config:

Host my-server
    HostName remote-server.com
    User user
    IdentityFile ~/.ssh/id_ed25519
    IdentitiesOnly yes
Enter fullscreen mode Exit fullscreen mode

Now you can connect with:

ssh my-server
Enter fullscreen mode Exit fullscreen mode

4. Open the SSH tunnel

Assume PostgreSQL is running on the server at localhost:5432. We’ll forward that to port 5555 on your machine:

ssh -L 5555:localhost:5432 my-server
Enter fullscreen mode Exit fullscreen mode
  • 5555: local port on your computer
  • localhost:5432: PostgreSQL port on the server
  • my-server: alias from your SSH config

Keep this terminal open while the tunnel is active.

Run in the background:

ssh -fN -L 5555:localhost:5432 my-server
Enter fullscreen mode Exit fullscreen mode
  • -f = fork to background
  • -N = don’t execute a remote command, just tunnel

5. Connect with pgAdmin (or any client)

In pgAdmin, DBeaver, or psql, use:

  • Host: localhost
  • Port: 5555
  • User: your database user (e.g. postgres)
  • Password: your database password
  • Database name: the actual DB name

The client talks to localhost:5555, and the SSH tunnel securely forwards everything to PostgreSQL on the server.

6. Closing the tunnel

  • Foreground tunnel: press Ctrl+C.
  • Background tunnel: find and kill it: 
  ps aux | grep 'ssh -L'
  kill <pid>
Enter fullscreen mode Exit fullscreen mode

To Sum Up

With this setup:

  • Your database port remains hidden from the outside world.
  • All traffic is encrypted via SSH.
  • You can connect safely using your favorite GUI tool or CLI.

This pattern works for PostgreSQL, MySQL, Redis, or any other service that you’d rather not expose directly.

Top comments (0)