≡ Menu

How to Allow Remote Connection to PostgreSQL Database using psql

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=#
Add your comment

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

Comments on this entry are closed.

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

    Whart about firewall setup listening ports?

  • Bruno February 6, 2014, 11:55 am

    Pretty easy this one! Really appreciate it.

  • Henry February 6, 2014, 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

  • yOz February 16, 2014, 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.

  • Anonymous January 22, 2016, 5:10 am

    Thank you!!! Helped me tons!!!!

  • Ramya March 6, 2017, 2:43 am

    How should I perform reload and restart in windeows after changing the pg_hba.conf file and after changing the listen address

  • mateusz June 20, 2017, 9:47 am

    @Ramya

    on windows, in the PostgreSQL folder (C:\Program Files\postgresql\9.6\) or something like that, you should’ve bin folder and the pg_ctl binary (exe) file in it.

    Run it with restart argument.

    pg_ctl.exe restart