Exposing Postgres port externally

Latest response

Can anyone assist with the exposure of the postgress database port to external connection. I have the following configured but it is not working.

--------------pg_hba.conf----------------------------

local all all md5
host all all {MYMACHINESIPADDRES}/32 trust
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host all all 0.0.0.0/0 password

------IPTABLES------
-A INPUT -p tcp -m tcp --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -m tcp --sport 5432 -m state --state ESTABLISHED -j ACCEPT

DG
Log in to join the conversation

Responses

jg Active Contributor 200 points
4 April 2016 3:59 PM

Did you look at the manual for pg_hba.conf?

The important bit;

Note: Remote TCP/IP connections will not be possible unless the server is started with an appropriate value for the listen_addresses configuration parameter, since the default behavior is to listen for TCP/IP connections only on the local loopback address localhost.

You can see if it is bound to 0.0.0.0/24 vs. 127.0.0.1 with netstat

%> netstat -anp | grep 5432
DG Community Member 50 points
4 April 2016 4:26 PM

The postgres config is set to listen on * and this is what I get with a netstat netstat -anp |grep 5432 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 30418/postgres

DG Community Member 50 points
4 April 2016 4:27 PM

with better formatting netstat -anp |grep 5432

 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 30418/postgres
jg Active Contributor 200 points
4 April 2016 5:57 PM

Everything looks like it should be working. Can you disable your iptables rules and verify? Process of eliminiation.

PS Guru 6494 points
6 April 2016 3:27 AM

I suspect it is because you are using '-A' to add your iptables rules which will add the rules to the end of the chain, which is likely to be after a drop rule that matches the same traffic (eg. a drop all / log rule).

Try adding your iptables rules with '-I' as this will insert them at the top of the chain.

If that still fails, can you provide the output of

iptables -L -v -n
DG Community Member 50 points
6 April 2016 2:15 PM

]# iptables -L -v -n

Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination                                                     
1290K 1724M ACCEPT     all  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               state RELATED,ESTABLISHED
   82  6772 ACCEPT     icmp --  *      *       0.0.0.0/0            0.0.0.0/0                                                       
  216 12918 ACCEPT     all  --  lo     *       0.0.0.0/0            0.0.0.0/0                                                       
   40  2076 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               state NEW tcp dpt:22
  106  5512 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               tcp dpt:80 state NEW,ESTABLISHED
    0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               tcp dpt:443 state NEW,ESTABLISHED
   86  4458 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               tcp dpt:3306 state NEW,ESTABLISHED
51720 5489K REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               reject-with icmp-host-prohibited
    0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               tcp dpt:5432 state NEW,ESTABLISHED
    0     0 ACCEPT     tcp  --  *      *       143.91.1.0/24        0.0.0.0/0                                                               state NEW tcp dpt:137
    0     0 ACCEPT     tcp  --  *      *       143.91.1.0/24        0.0.0.0/0                                                               state NEW tcp dpt:138
    0     0 ACCEPT     tcp  --  *      *       143.91.1.0/24        0.0.0.0/0                                                               state NEW tcp dpt:139
    0     0 ACCEPT     tcp  --  *      *       143.91.1.0/24        0.0.0.0/0                                                               state NEW tcp dpt:445

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination                                                     
    0     0 REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT 745K packets, 74M bytes)
 pkts bytes target     prot opt in     out     source               destination                                                     
  667  563K ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               tcp spt:80 state ESTABLISHED
    0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               tcp spt:443 state ESTABLISHED
16255   24M ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               tcp spt:3306 state ESTABLISHED
    0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               tcp spt:5432 state ESTABLISHED
jg Active Contributor 200 points
6 April 2016 6:15 PM

PixelDrift.NET Support is correct;

    0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               tcp dpt:5432 state NEW,ESTABLISHED

Is directly following

51720 5489K REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0                                                               reject-with icmp-host-prohibited

Everything is getting dropped before it gets to your incoming rule for postgres.

DG Community Member 50 points
6 April 2016 6:23 PM

Interesting, It sounds like I can use an I instead of a A to insert my new rules before anything else but where is the reject coming from? I don't see a global reject in my file. Should I do something else other than use an I such as move the statements up the list?

*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [18:3284]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -m state --state NEW,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 443 -m state --state NEW,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
-A OUTPUT -p tcp -m tcp --sport 80 -m state --state ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -m tcp --sport 443 -m state --state ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -m tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -m tcp --sport 5432 -m state --state ESTABLISHED -j ACCEPT

-A INPUT -s 143.91.1.0/24 -m state --state NEW -p tcp --dport 137 -j ACCEPT
-A INPUT -s 143.91.1.0/24 -m state --state NEW -p tcp --dport 138 -j ACCEPT
-A INPUT -s 143.91.1.0/24 -m state --state NEW -p tcp --dport 139 -j ACCEPT
-A INPUT -s 143.91.1.0/24 -m state --state NEW -p tcp --dport 445 -j ACCEPT



COMMIT
PS Guru 6494 points
6 April 2016 11:06 PM

Douglas,

Your global reject on INPUT is this

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

Which is 5 lines above where you have added your INPUT rule for postgres.

You need to move your postgres input rule immediately above this line.

Close

Welcome! Check out the Getting Started with Red Hat page for quick tours and guides for common tasks.