Back to Blog

Configure PostgreSQL to allow remote connection

on January 23, 2016

By default PostgreSQL is configured to be bound to "localhost".

1
2$ netstat -nlt
3Proto Recv-Q Send-Q Local Address           Foreign Address         State
4tcp        0      0 0.0.0.0:443             0.0.0.0:*               LISTEN
5tcp        0      0 127.0.0.1:11211         0.0.0.0:*               LISTEN
6tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN
7tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
8tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN
9tcp        0      0 127.0.0.1:3737          0.0.0.0:*               LISTEN
10tcp6       0      0 :::22                   :::*                    LISTEN
11

As we can see above port 5432 is bound to 127.0.0.1. It means any attempt to connect to the postgresql server from outside the machine will be refused. We can try hitting the port 5432 by using telnet.

1
2$ telnet 107.170.11.79 5432
3Trying 107.170.11.79...
4telnet: connect to address 107.170.11.79: Connection refused
5telnet: Unable to connect to remote host
6

Configuring postgresql.conf

In order to fix this issue we need to find postgresql.conf. In different systems it is located at different place. I usually search for it.

1
2$ find / -name "postgresql.conf"
3/var/lib/pgsql/9.4/data/postgresql.conf
4

Open postgresql.conf file and replace line

1
2listen_addresses = 'localhost'
3

with

1
2listen_addresses = '*'
3

Now restart postgresql server.

1
2$ netstat -nlt
3Proto Recv-Q Send-Q Local Address           Foreign Address         State
4tcp        0      0 127.0.0.1:11211         0.0.0.0:*               LISTEN
5tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN
6tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
7tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
8tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN
9tcp        0      0 127.0.0.1:2812          0.0.0.0:*               LISTEN
10tcp6       0      0 ::1:11211               :::*                    LISTEN
11tcp6       0      0 :::22                   :::*                    LISTEN
12tcp6       0      0 :::5432                 :::*                    LISTEN
13tcp6       0      0 ::1:25                  :::*                    LISTEN
14

Here we can see that "Local Address" for port 5432 has changed to 0.0.0.0.

Configuring pg_hba.conf

Let's try to connect to remote postgresql server using "psql".

1
2$ psql -h 107.170.158.89 -U postgres
3psql: could not connect to server: Connection refused
4	Is the server running on host "107.170.158.89" and accepting
5	TCP/IP connections on port 5432?
6

In order to fix it, open pg_hba.conf and add following entry at the very end.

1
2host    all             all              0.0.0.0/0                       md5
3host    all             all              ::/0                            md5
4

The second entry is for IPv6 network.

Do not get confused by "md5" option mentioned above. All it means is that a password needs to be provided. If you want client to allow collection without providing any password then change "md5" to "trust" and that will allow connection unconditionally.

Restart postgresql server.

1
2$ psql -h 107.170.158.89 -U postgres
3Password for user postgres:
4psql (9.4.1, server 9.4.5)
5Type "help" for help.
6
7postgres=# \l
8

You should be able to see list of databases.

Now we are able to connect to postgresql server remotely.

Please note that in the real world you should be using extra layer of security by using "iptables".


You might also like

If you liked this blog post, check out similar ones from BigBinary