// Richard Hart / Hates_

SSH Tunnels with Postgres & pgAdmin

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.