How to enable remote access to PostgreSQL database server

Jaspalsinh Chauhan -

Once you have your PostgreSQL database server installed, up and running, due to security reasons by default remote access to connect with your database instance is disabled.

If you try connecting with your PostgreSQL database server from client machine, you will encounter error.

could not connect to server: Connection refused (0x0000274D/10061)

Following screenshot displays error I received when trying to connect from a windows client machine using pgAdmin III

Following below steps you will be able to enabled remote access to PostgreSQL database server.

Before even we start it is important to know that there are two configuration files (and others) which control how your database server works. This configuration files contains settings that needs to be tweaked or changed to make your database instance run as per your needs.

  • postgresql.conf (Used to set various parameters that affect the database)
  • pg_hba.conf (Used to control client authentication, HBA means "Host based authentication")

Above mentioned files can be found at /etc/postgresql/9.3/main/ folder (here 9.3 is the version of PostgreSQL I have installed on my Ubuntu machine).

You can also query the location of these files from the psql CLI:

First login to your psql CLI

sudo -u postgres psql

To query location of postgresql.conf file

show config_file;

To query location of pg_hba.conf file

show hba_file;

You can get values of all parameters set for your database instance using following command

show all;

To enable remote access first we need to change the listen_addresses setting which is set to localhost by default

show listen_addresses;

Open postgresql.conf file in edit mode

sudo nano postgresql.conf

Find the line which starts with #listen_addresses = 'localhost' and change it to listen_addresses = '*'.

This enables the PostgreSQL database server to listen for incoming connection request and query request from any / all available IP address on the host machine. Save the changes by CTRL+X and YES.

Above steps only enables networking related settings for remote access, next steps enables client authentication by making changes to the pg_hba.conf file.

Before we start making changes to pg_hba.conf file, I recommend you understand and learn how CIDR - Classless Inter-Domain Routing. You can use UltraTool's CIDR program to compose IP Address CIDR notation that you need to use to enable remote acccess to PostgreSQL database server for user you wish to connect with. You can also add client authentication that can be restricted to a single IP Address / host name.

For the sake of this example I will use CIDR notation 192.168.1.0 / 24 where I wish to connect to my PostgreSQL database which is hosted on 192.168.1.106 from client machine with IP Address 192.168.1.249

Open pg_hba.conf file in edit mode

sudo nano pg_hba.conf

Client authentication allow / restrict entry follows below format (there are more formats you can use too):

[TYPE]    [DATABASE]    [USER]    [ADDRESS]    [METHOD]

Find line that resembles

host	all		all		127.0.0.1/32	md5

and add following line after this entry

host	all		all		192.168.1.0/24	md5

Above added line denotes that client authentication is allowed from host which has IP address between range 192.168.1.1 and 192.168.1.254 to any / all database on the PostgreSQL database server and can be any database exists user using MD5 authentication mode.

Note: you need to restart PostgreSQL database server to allow this changes get effective.

sudo service postgresql stop
sudo service postgresql start

After restart your database will allow connection from remote client machines. Make sure your host machine's firewall rules are not blocking any request.