Allow Remote Connections

Problem

Remote connections are needed for troubleshooting and also creating reports or you would probably just like to connection your postgres client to the database.

Solution

  • Check the ports that postgres is listening on:

vagrant@ubuntu-xenial:~$ sudo netstat -tupln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1104/sshd
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1336/postgres

  • Then edit the PostgreSQL configuration file and put the IP that you would like to allow. If you want to allow all IPs, then use 0.0.0.0

$ sudo vim /etc/postgresql/9.5/main/pg_hba.conf

  • Then add the following line to end of the file and then save and quit.

host   all     all     0.0.0.0/0       md5

  • Next Allow TCP/IP socket. Open the config file below and change listen directive from localhost to * for allow all.

$ sudo vim /etc/postgresql/9.5/main/postgresql.conf

  • Search for the line, listen_addresses. Un-comment the line and then change localhost to * (you can use comma-separated list of addresses; * is all ip address)

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

listen_addresses = '*'          # what IP address(es) to listen on;

  • Restart postgres.

vagrant@ubuntu-xenial:~$ ps aux | grep postgres
postgres 1336 0.0 0.7 293436 24224 ? S Apr24 0:03 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
postgres 1359 0.0 1.1 293568 35892 ? Ss Apr24 0:01 postgres: checkpointer process
postgres 1360 0.0 0.1 293436 5596 ? Ss Apr24 0:01 postgres: writer process
postgres 1361 0.0 0.2 293436 8756 ? Ss Apr24 0:01 postgres: wal writer process
postgres 1362 0.0 0.2 293872 6532 ? Ss Apr24 0:01 postgres: autovacuum launcher process
postgres 1363 0.0 0.1 148948 4132 ? Ss Apr24 0:02 postgres: stats collector process
vagrant@ubuntu-xenial:~$ sudo service postgresql stop
vagrant@ubuntu-xenial:~$ ps aux | grep postgres
vagrant 18972 0.0 0.0 12944 936 pts/0 S+ 11:30 0:00 grep --color=auto postgres
vagrant@ubuntu-xenial:~$ sudo service postgresql start
vagrant@ubuntu-xenial:~$ ps aux | grep postgres
postgres 19010 0.2 0.7 293436 24344 ? S 11:30 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
postgres 19012 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: checkpointer process
postgres 19013 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: writer process
postgres 19014 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: wal writer process
postgres 19015 0.0 0.2 293876 6740 ? Ss 11:30 0:00 postgres: autovacuum launcher process
postgres 19016 0.0 0.1 148948 4100 ? Ss 11:30 0:00 postgres: stats collector process
vagrant 19032 0.0 0.0 12944 1020 pts/0 S+ 11:30 0:00 grep --color=auto postgres

 

  • Then check if postgres is listening to all All connections

vagrant@ubuntu-xenial:~$ sudo netstat -tupln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1104/sshd
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 19010/postgres
tcp6 0 0 :::443 :::* LISTEN 1350/apache2
tcp6 0 0 :::80 :::* LISTEN 1350/apache2
tcp6 0 0 :::8083 :::* LISTEN 1350/apache2
tcp6 0 0 :::22 :::* LISTEN 1104/sshd
tcp6 0 0 :::5432 :::* LISTEN 19010/postgres
udp 0 0 0.0.0.0:68 0.0.0.0:* 856/dhclient

References

How Do I Enable remote access to PostgreSQL database server?