Often it's good to access databases directly from desktop. Or maybe you want your database server to be different from your application server. Whatever your reason is, the requirement is enabling remote access to your PostgreSQL database server.

It's very simple procedure. First locate your DB configuration files. Commonly they lives under [inline-code]/etc/postgresql/9.3/main/[/inline-code] directory. Be sure to replace '9.3' with your own database version.

Now open up the file [inline-code]pg_hba.conf[/inline-code] with your favorite text editor and paste the bellow code at the end of the file:

host all all md5
Okay, Attention first: this will open up your server to the whole world. You can alter the above code to meet your specific need. Like replace the first 'all' with your single database name or replace second 'all' with your database user name. Also you can limit the network at the 4th column. Just tweak it as you need. It'll be more secure.

Next open up [inline-code]postgresql.conf[/inline-code] and change the value of [inline-code]listen_addresses = 'localhost'[/inline-code] to something like this:

listen_addresses = '*'
Now PostgreSQL will listen to the whole network.

Simply restart your server:

sudo service postgresql restart

You should be good to go by now!

Test your setup:

psql -h server.ip -U postgres

What's on your mind?


먹튀텔레캅 commented almost 2 years ago

Informative article, totally what I was looking for.

Jimmy commented over 7 years ago

I could not refrain from commenting. Very well written!