Connect to Postgres on EC2 using pgAdmin

Mark Berry May 27, 2014

A few quick notes on connecting to a Postgres database running on an Amazon EC2 instance using pgAdmin with its built-in SSH tunneling option.

1. If you want to connect as the postgres admin user, add a password to that user:

sudo su postgres
psql template1
alter user postgres with password 'BvcbWZV6cGOh7iUn';

Caution From now on, you will always need a password to start psql (unless some other rule takes precedence).

2. In your pg_hba.conf file, temporarily add a line that allows connecting as the postgres user to any database and from any IP address:

host   all      postgres    0.0.0.0/0     md5

3. Re-load the pg_hba.conf file:

pg_ctl reload –D /path/to/data/directory

4. Add a new server to pgAdmin. On the Properties tab, set Host to localhost.

EC2 pgAdmin 1

On the SSH Tunnel tab, set the Tunnel host, Username, and Identity file just as you would when connecting with an SSH client:

EC2 pgAdmin 2

5. Click OK to connect. Accept the key files if you trust them. Provide the password for the postgres user that you set in step 1 above. Leave the pass phrase for the identify file empty (assuming you are not using a pass phrase on that file). Do whatever work you need to do, then close the connection.

6. In pg_hba.conf, comment out the remote connection line:

#host   all      postgres    0.0.0.0/0     md5

7. Re-load the pg_hba.conf file:

pg_ctl reload –D /path/to/data/directory


Leave a Reply





*