Allowing remote connections to your Postgresql VPS installation.

Here's another short little tip I thought I'd write down and share.

Painting the scene:

Note: Actual file locations may change depending on what package and operating system you use. Just google where to find your postgres conf file and you'll be fine.

You have a shiny new VPS configured with a fresh install of PostgreSQL. In my case, I'm using Ubuntu 13.04 x64 and PostgreSQL 9.1

How can I connect to it from my local machine?, you ask. Here's how!

Change the address PostgreSQL listens to.

Using Nano (or vim/emacs/whatever), edit your postgresql.conf file. We're specifically looking for a line that says listen_addresses. We're going to modify where PostgreSQL is listening on.

# /etc/postgresql/9.1/main/postgresql.conf

#---------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'  # Postgres, pretty please keep your ears open on everything. 

Allow remote connections to actually reach your PostgreSQL instance.

Next we're going to edit our pg_hba.conf file and configure it to allow remote connections. At the very end of the file, add the following line:

# /etc/postgresql/9.1/main/pg_hba.conf

host all all  0.0.0.0/0 md5

This is a rule that basically tells PostgreSQL to allow anyone to access the instance as long as they have proper credentials.

If you want to whitelist your IP only you can use your network/mask instead of 0.0.0.0/0.

Restart your PostgreSQL instance.

That's it, you're done. Restart it.

sudo service postgresql restart

Now you can use a tool like PgAdmin or RubyMine to access your remote database.