cr2a1a
Last Updated: May 19, 2016
·
19.91K
· 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

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

Say Thanks
Respond

4 Responses
Add your response

8429
4693d7cfa88635d430c0de9a92f8dd84

¡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 ·
8431
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 ·
8432
4693d7cfa88635d430c0de9a92f8dd84

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 ·
18779
Bfca328df7d399721004fadacca763a1

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 ·