How to connect to Postgres Database Remotely

By default, PostgreSQL database server remote access disabled for security reasons. You can by default only access it from the same machine you are on.

Step # 1: Allow remote IP address to access PostgreSQL

You need to open file called /var/lib/pgsql/data/pg_hba.conf. Login as postgres user using su command:
$ su - postgres
$ vi /var/lib/pgsql/data/pg_hba.conf  (directory might be different depending on where you installed postgres).
Now append following line. Let us say you would like to give access to 192.168.0.xxx on your network:

-------------------------------------------------------------------------------------

host    all    all   192.168.0.0  255.255.255.0  trust ----------------------------------------------------

Please replace 192.168.1.0 and 255.255.255.0 to reflect the actual network IP address range of the clients system in your own network.

Save close the file.

Step # 2: Allow communication over TCP/IP

You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf

$ vi /var/lib/pgsql/data/postgresql.conf

Change the line listen_addresses, which by default is usually commented out with value 'localhost' to a value to accept remote machines connecting to it.

-----------------------------------------------------------

listen_addresses = '*' -------------------------------------

Make sure your address is not commented out  (the "#" is a comment from that symbol to end of line).

Save and close the file.

Step # 3: Restart PostgreSQL server

Restart the PostgreSQL server. It's best to shutdown, then restart it and check the 'Logfile' for any errors. If you botched something, it will most likely show up in the logfile right away.This will open default port 5432. 

Step # 4: Test your setup

Use psql command from client system as follows:

psql -h PostgreSQL-IP-ADDRESS -U USERNAME -d DATABASENAME