How to Allow Remote Connection to PostgreSQL Database using psql

by Ramesh Natarajan on February 5, 2014

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.

In the following example, from a client machine, we are trying to connect to a PostgreSQL database that is running on 192.168.102.1 server. As you see from the output, it clearly says that the remote PostgreSQL database is not accepting connection.

# psql -U postgres -h 192.168.102.1
psql: could not connect to server: Connection refused
        Is the server running on host "192.168.102.1" 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. As indicated below, it accepts connections only from the localhost.

# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               ident

Add the following line to the pg_hba.conf server. This will allow connection from “192.168.101.20″ ip-address (This is the client in our example). 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/data/pg_hba.conf
host    all         all         192.168.101.20/24    trust

The following are various client authentication record format supported in the pg_hba.conf file. We are using the #2 format from this list.

  • local database user authentication-method [authentication-option]
  • host database user CIDR-address authentication-method [authentication-option]
  • hostssl database user CIDR-address authentication-method [authentication-option]
  • hostnossl database user CIDR-address authentication-method [authentication-option]

Instead of “CIDR-address” format, you can also specify the ip-address and the network mask in separate fields using the following record format.

  • host database user IP-address IP-mask authentication-method [authentication-option]
  • hostssl database user IP-address IP-mask authentication-method [authentication-option]
  • hostnossl database user IP-address IP-mask authentication-method [authentication-option]

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/data/postgresql.conf
listen_addresses = 'localhost'

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/data/postgresql.conf
listen_addresses = '*'

3. Test the Remote Connection

Now, login to the client machine 192.168.101.20, and perform the psql remote connection to the PostgreSQL database server (192.168.102.1) as shown below. This time, it should work.

# psql -U postgres -h 192.168.102.1
Welcome to psql 8.1.11 (server 8.4.18), the PostgreSQL interactive terminal.
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=192.168.102.1
# psql -U postgres
Welcome to psql 8.1.11 (server 8.4.18), the PostgreSQL interactive terminal.
postgres=#

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 4 comments… read them below or add one }

1 Kyle Amadio February 6, 2014 at 1:15 am

Whart about firewall setup listening ports?

2 Bruno February 6, 2014 at 11:55 am

Pretty easy this one! Really appreciate it.

3 Henry February 6, 2014 at 1:07 pm

You should command the server to reload the configuration. Or is that not necessary anymore?

$ pg_ctl -D /path/to/dataDirectory reload

4 yOz February 16, 2014 at 2:56 pm

postgresql listens by default on port tcp/5432

for changing pg_hba.conf reload is ok, but for changing listen_address restart is needed.

Leave a Comment

Previous post:

Next post: