Connect to Postgres on EC2 using pgAdmin

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     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     md5

7. Re-load the pg_hba.conf file:

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

6 thoughts on “Connect to Postgres on EC2 using pgAdmin

  1. Steve

    worked, thanks. Very helpful. You may want to make it clear WHY you shouldn’t leave pg_hba.conf open to all IP’s?

  2. Mark Berry Post author

    @Steve, six years later, it’s hard to remember! but it looks like that leaving that open to all IPs means that anyone with the password can get in. And even if they don’t get in, they could burn a lot of resources attempting to brute force their way in. Restricting the IPs, or not allowing external access at all, reduces your attack surface and vulnerability.

  3. Steve

    Thanks, that’s logical.
    Six years ago…the Internet is becoming timeless, ha ha.

  4. Sweta Praharaj

    After running this pg_ctl reload –D /path/to/data/directory
    it says pg_ctl: could not open PID file “/var/lib/pgsql/data/”: Permission denied
    After running sudo pg_ctl reload –D /path/to/data/directory
    it says pg_ctl: cannot be run as root
    Please log in (using, e.g., “su”) as the (unprivileged) user that will
    own the server process.

  5. Sweta Praharaj

    I did restart and reload it using systmcl. But I can’t login still

  6. Mark Berry Post author

    Sweta, I’ve been away from pgAdmin and EC2 for many years so I have no recent experience. As the message suggests, you may need to run the command in the context of the user that owns the file. Googling “pg_ctl: could not open PID file “/var/lib/pgsql/data/”: Permission denied” may give you some more ideas.

Leave a Reply

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.

This site uses Akismet to reduce spam. Learn how your comment data is processed.