// Richard Hart / Hates_

SSH Tunnels with Postgres & pgAdmin

computing
Share

One of my previous posts showed how to use an SSH Tunnel to connect to a remote MySQL server. Since moving to Postgres I’ve been wanting to do the same thing with pgAdmin, which sadly isn’t as simple. There’s no way of creating the tunnel automatically in the app so we need to resort to the terminal.

The first step is as before, we create the SSH tunnel to our server:


  ssh -fNg -L 5555:localhost:5432 {your_username}@{yourdomain.com}

Here I’m mapping the remote Postgres post 5432 to the local post 5555. Now this is where things start to differ. I had been trying to connect as the local ‘postgres’ user but continually received authentication errors. You need to create a user on the server that relates to your machine’s local user. So if your local username is “foo” you need to create a user in Postgres with the same name. First log into the psql console:


  psql template1

Then issue the commands:


  CREATE USER foo;
  ALTER USER foo WITH PASSWORD 'password';
  ALTER USER foo WITH CREATEUSER CREATEDB;

Then in pgAdmin, simply connect with user foo and you’re good to go.

UPDATE: I’ve since discovered this is related to IDENT authentication. Switching to PASSWORD authentication allows you to use any username/password combination.


  • Pingback: Tweets that mention SSH Tunnels with Postgres & pgAdmin -- Topsy.com

  • http://about.me/ddavis/ Dustin Davis

    Wow, all I needed was that ssh tunnel command to connect. Very simple. Thanks!

    • Hates

      Glad it helped. I’ve since moved to using Navicat Essentials ($5 or so) as that has SSH tunnels all built in. Makes life a bit more easier if you connect to a remote database a lot.

  • Stefan Haberl

    Great! Works like a charm!