How to Allow Remote Connection to PostgreSQL Database using psql

Posted: December 9, 2014 in Databases

When you install PostgreSQL, by default connection to the database using TCP/IP is not allowed.

When you try to connect from a client to a remote PostgreSQL database using psql command, you might get “psql: could not connect to server: Connection refused” error message

For example:
# psql -U postgres -h 62.181.1.126
psql: could not connect to server: Connection refused
        Is the server running on host "62.181.1.126" and accepting
        TCP/IP connections on port 5432?

To enable TCP/IP connection for PostgreSQL database, you need to follow the two steps mentioned below:

1. Modify pg_hba.conf to add Client Authentication Record:

On the PostgreSQL database server, by default, you’ll notice the following records towards the end of the /var/lib/pgsql/data/pg_hba.conf or /var/lib/pgsql/9.3/data/pg_hba.conf

As indicated below, it accepts connections only from the localhost.

# IPv4 local connections:
host    all         all         127.0.0.1/32          trust

Add the following line to the pg_hba.conf server.If you want to allow connection from multiple client machines on a specific network, specify the network address here in the CIDR-address format.

# vi /var/lib/pgsql/9.3/data/pg_hba.conf
host all all 62.181.1.126/24 trust

2. Change the Listen Address in postgresql.conf

On the PostgreSQL database server, by default, the listen address will be localhost in the postgresql.conf file as shown below.

grep listen /var/lib/pgsql/9.3/data/postgresql.conf
#listen_addresses = ‘localhost’ # what IP address(es) to listen on;

Modify this line and give *. If you have multiple interfaces on the server, you can also specify a specific interface to be listened.

grep listen /var/lib/pgsql/9.3/data/postgresql.conf
listen_addresses = ‘*’ # what IP address(es) to listen on;

3. Test the Remote Connection

$ psql -U postgres -h 62.181.1.126
psql (9.3.5)
Type "help" for help.
postgres=#

Also, if you don’t want to specify the hostname in the command line parameter every time, you can setup the remote PostgreSQL database ip-address in PGHOST environment variable name as shown below.

$ export PGHOST=62.181.1.126
$ psql -U postgres
psql (9.3.5)
Type "help" for help.
postgres=#
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s