Configure Postgres to accept remote connections

The default postgres configuration will only accept local connections to the database. If you are, for example, using psql and trying to connect to a remote server called remote-server you may receive a response like:

psql -h remote-server -U username -W $%54myPassword123
psql: error: could not connect to server: Connection refused
	Is the server running on host "remote-server" (10.0.0.245) and accepting
	TCP/IP connections on port 5432?

Running netstat on the server sheds light on the problem: the postgresql server is bound to address 127.0.0.1

 netstat -tulpen|grep postgres
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      128        168608     16184/postgres

This behavior is defined in the file postgresql.conf. Insert (or change) the following line to allow connections from all hosts

listen_addresses = '*'             #listening on all addressess

After you restart the server and run netstat again you should see the server listening to all addresses.

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      128        175484     16509/postgres      
tcp6       0      0 :::5432                 :::*                    LISTEN      128        175485     16509/postgres 

However if you try again to connect using psql you may receive a different message.

psql -h remote-server -U username -W 123password!==
psql: error: FATAL:  no pg_hba.conf entry for host "your.client.ip.address", user "username", database "username", SSL on
FATAL:  no pg_hba.conf entry for host "your.client.ip.address", user "username", database "username", SSL off

This indicates that even though the server listens on all interfaces, it is unable to find a suitable configuration entry in the file pg_hba.conf. In order to correct this, you will have to edit pg_hba.conf and add a new “host” line in the form:

host       database  user  address  auth-method  [auth-options]

To allow all users to log with password identification use:

host    all          all             0.0.0.0/0               md5

The user will now need to provide a valid postgresql password in order to connect. Be careful as postgresql users and passwords are not related to the system users and passwords. See “creating user” on this document. Failure to provide the correct user name and password will result in the following message. Here must be noted that if a user is required to provide a password in the pg_hba.conf file and the user does not have a password then the authentication will always fail.

psql -h remote-server -U username -W 123swordfish!==
psql: error: FATAL:  password authentication failed for user "username"
FATAL:  password authentication failed for user "username"