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?

Answer 1

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