Last Updated: May 19, 2016
· sergiotapia
Avatar mario

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


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

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.

Say Thanks

4 Responses
Add your response


¡Hola Sergio! Tried this using a VPS I have running on DigitalOcean and it worked like a charm from RubyMine. Saludos desde Puerto Rico :)

over 1 year ago ·
Avatar mario

Awesome, glad it worked fine. I also use RubyMine to check production records on my Postgresql instance. Saludos desde Bolivia!

over 1 year ago ·

It's so nice to meet other hispanic RoR developers. You can follow me on Twitter (JonahBinario) and Github (jonahoffline), keep in touch!

over 1 year ago ·

Although it might be a bit unsafe, because your postgresql instance will be accessible for any IP over the internet, it works yes.

over 1 year ago ·