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.