issues configuring firewall rules for Postgres on Centos

Question

I am attempting to follow this guide to allow remote connections to postgres http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html
At the moment I am running into issues with the firewall.
I have it setup so I have a /tmp/v4 that I can modify and just restore my firewall rules from.
I have tried 2 settings.
The one the guide recommends:

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

When I use this setting and run nc ipofserver 5432 I get Ncat: connection timed out
If I try settings

-I INPUT -p tcp -s 00.000.000.00  --dport 5432 -j ACCEPT
-I INPUT -p tcp -s 00.000.000.00  --dport 5432 -j ACCEPT

I run the same ncat command, I get Ncat: Connection refused

I use the same base firewalls (not what is listed above) on another server for serving redis and it works correctly.

firewalld has been disabled on this box in favor of iptables, mostly for familiarity purposes.

Both machines are CentOS 7

00.000.000.00 replaces the IP of the computer I am actually using

Solution

centos7 uses firewall-cmd out of the box, unless you have disabled it. I assume you have not because you would have mentioned it otherwise.

With that assumption in place, then you can simply run this on your postgresql host:

firewall-cmd --add-service=postgresql

Once you have verified that it works, then you can run it again with the permanent switch to make it stick after reboots:

firewall-cmd --add-service=postgresql --permanent

If you install the bash-completion package, after re-logging in, firewall-cmd autocompletes, making it really easy to use.

Edit: OP indicates he does not use firewall-cmd. So assuming he uses the old iptables service, then the canonical way to modify is to edit /etc/sysconfig/iptables.

If you want to allow incoming connections to port 5432/tcp in that host, then you need to add this line

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

before

-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

and the reload the iptables service. This will of course allow all connections to the database server from any host.

If you want to limit what hosts may connect (let's limit it to one, 111.222.111.222), then:

-A INPUT -p tcp -s 111.222.111.222 --sport 1024:65535 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

That should do it, remove the other one, of course, and reload the iptables service

Comments

[Jon Heckman]

I forgot about that since it was a while ago and this was a clone from a base image. firewalld was disabled and I am using iptables, unless I am mistaken using this command would also require me to migrate all existing rules to firewalld

[natxo asenjo]

depending on your firewall rules that could be more or less work, indeed. For this case, not so much ;-)

[Jon Heckman]

Thank you for the update, I will accept it once I figure out what is going on and I can verify. I think something else is the problem, I tried disabling the firewall and I am still getting connection refused.

[natxo asenjo]

check the listen_addresses directive in postgresql.conf (possibly in /var/lib/pgsql/data/postgresql.conf). If in there you have 'localhost, change that to '*' and reload postgresql. You probably need to edit pg_hba.conf as well

[Jon Heckman]

I had gone through those steps but I had mistyped the IP address in the pg_hba.conf (Finally got to the point that it would tell me that though). Thank you very much though, everything is working how I want it to work now. I have listen_addresses set to '*' right now, if I limit the IP's in the firewall that should be secure right?

[natxo asenjo]

try it from another host to be sure