Thursday, March 27, 2014

Allow Remote Connection to PostgreSQL Database using psql

If the remote connection is not enabled you will be getting the following error.

“psql: could not connect to server: Connection refused” error message

To enable remote connection make the following changes in pg_hba.conf .

Modify pg_hba.conf to add Client Authentication Record

TYPE   DATABASE USER       ADDRESS           METHOD
host      all                   all            127.0.0.1/32    trust

TYPE  DATABASE  USER  IP-ADDRESS   IP-MASK                  METHOD
host     all                   all         127.0.0.1     255.255.255.255    trust

You can add 0.0.0.0/0 for allowing universal access to database.

Change the Listen Address in postgresql.conf
# grep listen /var/lib/pgsql/data/postgresql.conf
listen_addresses = 'localhost'
# grep listen /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'

Test the Remote Connection
psql -U postgres -h xxx.xxx.xxx.xxx
Welcome to psql 8.1.11 (server 8.4.18), the PostgreSQL interactive terminal.
postgres=

 

No comments:

Post a Comment