Configure postgresql to accept remote connections
By default postgresql accept connections from local machine only, you can cross check by running the following command
$ netstat -nlt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN # <----------
tcp6 0 0 :::8080 :::* LISTEN
tcp6 0 0 :::80 :::* LISTEN
tcp6 0 0 :::22 :::* LISTEN
Follow below two steps to change this and to start accepting all remote connections.
1. Update postgresql.conf file
you can fine the postgresql.conf file in the following location
/etc/postgresql/9.5/main/postgresql.conf
Note: 9.5 is the postgresql version, in will be different if you are running a different version of postgresql
open the file in edit mode and goto Connection Settings section under CONNECTIONS AND AUTHENTICATION you will see something like following
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
If you see the value of listen_addresses is localhost , change this to '*' like following
listen_addresses = '*'
2. Update pg_hba.conf file
next step is to update pg_hba.conf file which will be in the same location where the postgresql.conf file present
Open the file and goto the bottom, you will see the following settings by default
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
Change these to the following
# IPv4 local connections:
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::/0 md5
3. Restart Postgresql server
Run the following command to restart postgresql
sudo systemctl restart postgresql
Once the server is restarted, it will start accepting the remote connections.