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.
On the SSH Tunnel tab, set the Tunnel host, Username, and Identity file just as you would when connecting with an SSH client:
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
worked, thanks. Very helpful. You may want to make it clear WHY you shouldn’t leave pg_hba.conf open to all IP’s?
@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.
Thanks, that’s logical.
Six years ago…the Internet is becoming timeless, ha ha.
After running this pg_ctl reload –D /path/to/data/directory
it says pg_ctl: could not open PID file “/var/lib/pgsql/data/postmaster.pid”: 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.
I did restart and reload it using systmcl. But I can’t login still
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 postmastef.pid file. Googling “pg_ctl: could not open PID file “/var/lib/pgsql/data/postmaster.pid”: Permission denied” may give you some more ideas.