Postgres Setup

setup.txt

Solution From https://bosnadev.com/2015/12/15/allow-remote-connections-postgresql-database-server/
CONNECT TO THE REMOTE SERVER
First things first, you need to login to the remote server:

mirzap@bosnadev:~$ ssh root@remote-host
1
mirzap@bosnadev:~$ ssh root@remote-host

CHANGE THE LISTEN ADDRESS
By default, PostgreSQL DB server listen address is set to the 'localhost' , and we need to change it so it accepts connection from any IP address; or you can use comma separated list of addresses. Here is how it looks by default:

root@fe35577e9f8b:/# grep listen /etc/postgresql/9.4/main/postgresql.conf  
listen_addresses = 'localhost'		# what IP address(es) to listen on;
1
2
root@fe35577e9f8b:/# grep listen /etc/postgresql/9.4/main/postgresql.conf  
listen_addresses = 'localhost'		# what IP address(es) to listen on;
Open your postgresql.conf  file in your editor:

root@fe35577e9f8b:/# vim /etc/postgresql/9.4/main/postgresql.conf
1
root@fe35577e9f8b:/# vim /etc/postgresql/9.4/main/postgresql.conf
search for listen_addresses , and set it to '*' :

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'         # what IP address(es) to listen on;
1
2
3
4
5
6
7
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
 
# - Connection Settings -
 
listen_addresses = '*'         # what IP address(es) to listen on;
or if you want to set connection restrictions to a few IP’s, then you should set listen_addresses  to something like this:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '192.168.1.100,192.168.1.101,192.168.1.110'         # what IP address(es) to listen on;
1
2
3
4
5
6
7
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
 
# - Connection Settings -
 
listen_addresses = '192.168.1.100,192.168.1.101,192.168.1.110'         # what IP address(es) to listen on;
To find out more about connections and authentication and available parameters, check the official documentation page.

OPEN POSTGRESQL TO THE WORLD
In this step, you need to allow remote connections to actually reach your PostgreSQL server. Open pg_hba.conf :

root@fe35577e9f8b:/# vim /etc/postgresql/9.4/main/pg_hba.conf
1
root@fe35577e9f8b:/# vim /etc/postgresql/9.4/main/pg_hba.conf
To allow connections from absolutely any address with password authentication add this line at the end of pg_hba.conf  file:

host all all 0.0.0.0/0 md5
1
host all all 0.0.0.0/0 md5
You can also use your network/mask instead just 0.0.0.0/0 .

RESTART
You have made it! Just make sure to restart your PostgreSQL instance before leaving remote SSH session:

root@fe35577e9f8b:/# /etc/init.d/postgresql restart
1
root@fe35577e9f8b:/# /etc/init.d/postgresql restart
Now you should be able to connect to the PostgreSQL instance with any of DB tools.