How to set up port forwarding for postgresql listening on localhost?

I have a postgresql instance running on SLES.

I want to set it up to listen on localhost, and enable iptables to perform port forwarding.

My current configuration

postgresql.conf:

listen_addresses = 'localhost'
port = 5432

pg_hba.conf:

local   all         all                               md5
host    all         all         127.0.0.1/32          md5
host    all         all         0.0.0.0/0          md5

iptables (rule added via iptables -t nat -I PREROUTING -p tcp --dport 5432 -j REDIRECT):

Chain PREROUTING (policy ACCEPT 441 packets, 54049 bytes)
 pkts bytes target     prot opt in     out     source               destination
    6   420 REDIRECT   tcp  --  *      *       0.0.0.0/0            0.0.0.0/0           tcp dpt:5432

Configured like this, I am not able to open a psql connection to the DB.

Without the iptables rule, I get a Connection refused error.

With the iptables rule I get a Connection timed out error.

Solution 1:

You may need to check the filter table. For example, what is the default policy for INPUT? Do you have an explicit rule to allow port 5432 if not allowed by CHAIN policy.

Also, it may not be enough to write the rule as shown in your question. Read this from the man iptables:

REDIRECT
    This target is only valid in the nat table, in the PREROUTING and OUTPUT
    chains, and user-defined chains which are only called from those chains. 
    It redirects the packet to the machine itself by changing the destination IP
    to the primary address of the incoming interface (locally-generated packets are mapped to  the  127.0.0.1 address).

So, it will redirect the request to an IP address not necessarily localhost or 127.0.0.1. You may need to add the 127.0.0.1 IP address explicitly in the NAT rule. You can try:

iptables -t nat -I PREROUTING -p tcp --dport 5432 -j DNAT --to-destination 127.0.0.1