Tuesday, June 14, 2016

ubuntu - postgresql port closed for remote access



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



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


No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...