postgresql port closed for remote access

Question

I'm trying to connect to a PostgreSQL database in an Ubuntu Server, which I have root access, but somehow I can't open port 5432 for remote access, just local. This is what happens when I use "nmap" command on the server (XXX.XXX.X.XX represents server's IP):

nmap -p 5432 localhost
PORT      STATE  SERVICE 
5432/tcp  open   postgresql


nmap -p 5432 XXX.XXX.X.XX
PORT      STATE  SERVICE 
5432/tcp  closed postgresql

I have already edited the files 'pg_hba.conf' and 'postgresql.conf' but it didn't work.

The changes:

pg_hba.conf file:

# IPv4 local connections:
host    all             all             all            md5
# IPv6 local connections:
host    all             all             all            md5

postgresql.conf file:

listen_addresses = '*'

And when i try to connect with pgAdmin III, this is what it shows me:

Server doesn't listen

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "XXX.XXX.X.XX" and accepting TCP/IP connections on port 5432?

I have tried to open the port using "ufw" command but it didin't work as well.

Can anyone help me?

Solution

Make sure your firewall is not blocking traffic.

Append the following rules to your iptables (change X.X.X.X to your server IP address):

# iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d X.X.X.X  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
# iptables -A OUTPUT -p tcp -s X.X.X.X --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

If it works, save the iptables and restart it:

# apt-get install iptables-persistent
# /etc/init.d/iptables restart

If you have an external firewall on your network, you should allow the connection there as well.

Security note: opening your PostgreSQL port to public might be a security concern. You should consider limiting the incoming traffic to specific IP address/range by changing the -s 0/0 parameter in the first iptables command to -s X.X.X.X/X

Comments

[natxo asenjo]

did you restart the postgresql service after modifying those settings?