Enabling remote access to a PostgreSQL database hosted on AWS

The goal: To enable access via pgAdmin and psql from our local machine to a PostgreSQL database server hosted on an Amazon Web Services EC2 instance.

The environment:
Server:

• AWS EC2 Instance running Ubuntu Server 12.04.1 LTS, 64-bit
• PostgreSQL 9.1
• Default, out-of-the-box PostgreSQL configuration. That includes listening on port 5432, a database named postgres, and a role named postgres.

Client:

• Windows 7
• pgAdmin III version 1.16.1
• psql version 9.2.3

Until the proper security settings are applied, the client can't connect to PostgreSQL on the server. If we try to connect using pgAdmin, a Server not listening error occurs:

If we try to connect using psql, a Connection timed out error occurs:

To enable connections, we must edit the AWS Security Group and two PostgreSQL configuration files.

Editing the AWS Security Group:

• Log in to AWS.
• On the EC2 Dashboard, select your Instance and note which Security Group it's using:

• Select that Security Group, click the Inbound tab, and add a rule. The port should be 5432, and the source should be the IP address or our local machine (not the server), followed by /32. Don't forget to click the Apply Rule Changes button.

 

Editing the PostgreSQL pg_hba.conf file:

• Use an SSH client to connect to the EC2 Instance.
• cd to the directory that contains the PostgreSQL configuration files. This may vary, but mine are in /etc/postgresql/9.1/main.
• Use a text editor to modify pg_hba.conf.
• Locate the line host    all             all             127.0.0.1/0               md5.
• Immediately below it, add this new line: host    all             all             0.0.0.0/0               md5
• Save the file.

Editing the PostgreSQL postgresql.conf file:

• Use a text editor to modify postgresql.conf.
• Locate the line that starts with #listen_addresses = 'localhost'.
• Uncomment the line by deleting the #, and change localhost to *.
• The line should now look like this: listen_addresses = '*'                  # what IP address(es) to listen on;.
• Save the file.
• Restart PostgreSQL. The command may vary. In my case, it was: sudo /etc/init.d/postgresql restart.

Now you should be able to connect to the PostgreSQL server from your local machine, using your choice of pgAdmin3 or psql!

 

You'll be prompted for the postgres role's password when you connect, unless you've stored the password in %APPDATA%\postgresql\pgpass.conf on your local machine. (If your local machine is running Linux, the analogous file is .pgpass, usually found in your home directory.)

 

Caveats: The above instructions aren't optimized for security. In a production environment, you'll likely want tighter control over access to PostgreSQL. For example, in pg_hba.conf, we granted access to all IP addresses, which might be overkill. And logging in as the default role, postgres, might not be the best idea.