Thread: Port forwarding via iptables to postgres listening locally

Hi,

I have a posgres instance configured to listen at the localhost default. I'm trying to enable port forwarding via iptables. I set up the following rule:

iptables -t nat -I PREROUTING --source 0/0 --destination 0/0 -p tcp --dport 5432 -j REDIRECT

and can see that it is redirecting packets received at port 5432. But I am unable to establish a remote connection to the postgres instance.

Actually my situation seems to be the same as in this [1] question ( Message-id: <AANLkTim7uAXOvwp=pjR6uEUcoN-8ttmgKw7mU0x4ofWY@mail.gmail.com> ).

Thanks in advance,
David


[1] http://archives.postgresql.org/pgsql-general/2010-11/msg00783.php

On 06/19/2011 12:07 PM, David Resnick wrote:
> Hi,
>
> I have a posgres instance configured to listen at the localhost default.
> I'm trying to enable port forwarding via iptables. I set up the
> following rule:
>
> iptables -t nat -I PREROUTING --source 0/0 --destination 0/0 -p tcp
> --dport 5432 -j REDIRECT
>
> and can see that it is redirecting packets received at port 5432. But I
> am unable to establish a remote connection to the postgres instance.

The arguments "0/0" to source and destination make absolutely no sense.
I assume you're trying to hide the real values? If not, there's your
problem.

If your real rule has real IPs and ports, then you have other rules
getting in the way. If iptables is correctly configured you should be
getting a working connection. Most likely you have a port-based or
IP-based OUTPUT or FORWARD rule that's dropping reply packets.

Use wireshark on the postgresql server to see whether it receives the
transformed packets and if so whether it tries to reply to them. See
what address and port is in the reply. Enable connection logging in
postgresql and see if it logs any connection attempts.

--
Craig Ringer

Thanks a lot for your reply!

OK, the source and destination parameters were not intended to hide anything. Setting them like that got the same results in the iptables status output as when not specifying --source and --destination at all. Does --source need to be set to the address the machine is listening at? As you can guess, this is basically my first stab at configuring iptables :).

I now have the following rule in iptables:

Chain PREROUTING (policy ACCEPT 242 packets, 27431 bytes)
 pkts bytes target     prot opt in     out     source               destination
    0     0 REDIRECT   tcp  --  *      *       192.23.25.177         127.0.0.1           tcp dpt:5432

Running tcpdump with "tcpdump -i any tcp port 5432" while attempting to connect remotely has this output:
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on any, link-type LINUX_SLL (Linux cooked), capture size 96 bytes
07:00:53.673349 IP 192.23.197.91.36349 > 192.23.25.177.postgresql: S 2589386703:2589386703(0) win 2360 <mss 1360,nop,nop,sackOK,nop,wscale 6,opt-112:040203030001d50ad1fe110a66fe110a,eol>
07:00:56.673042 IP 192.23.197.91.36349 > 192.23.25.177.postgresql: S 2589386703:2589386703(0) win 2360 <mss 1360,nop,nop,sackOK,nop,wscale 6,opt-112:040203030001d50ad1fe110a66fe110a,eol>
07:01:02.672689 IP 192.23.197.91.36349 > 192.23.25.177.postgresql: S 2589386703:2589386703(0) win 5840 <mss 1400,sackOK,timestamp 1081653372 0,nop,wscale 6>
07:01:14.672182 IP 192.23.197.91.36349 > 192.23.25.177.postgresql: S 2589386703:2589386703(0) win 5840 <mss 1400,sackOK,timestamp 1081665372 0,nop,wscale 6>
07:01:38.671236 IP 192.23.197.91.36349 > 192.23.25.177.postgresql: S 2589386703:2589386703(0) win 5840 <mss 1400,sackOK,timestamp 1081689372 0,nop,wscale 6>

I've turned on connection logging in postgres; there is no indication of any connection attempt. There don't seem to be any additional rules configured in iptables that would drop the packets.

Is there anything I'm missing here? Do you have any additional suggestions for something I could check?

- David

On 06/19/2011 08:35 PM, David Resnick wrote:
> Thanks a lot for your reply!
>
> OK, the source and destination parameters were not intended to hide
> anything. Setting them like that got the same results in the iptables
> status output as when not specifying --source and --destination at all.

Whoops, I missed that in the docs. Thanks for clarifying.

> Does --source need to be set to the address the machine is listening at?

No, it's the real source of the traffic.

> Chain PREROUTING (policy ACCEPT 242 packets, 27431 bytes)
>   pkts bytes target     prot opt in     out     source
> destination
>      0     0 REDIRECT   tcp  --  *      *       192.23.25.177
> 127.0.0.1           tcp dpt:5432

Do you have any INPUT rule that explicitly ALLOWs traffic from 0.0.0.0/0
to 192.23.25.177/32 on tcp port 5432 ? Or a more general rule that
allows that traffic too?

Remember that INPUT and FORWARD rules act on traffic *after* the
PREROUTING rules have been applied, so you need to specify the
destination host and port as 192.168.25.177 port 5432, not whatever host
and port your gateway has.

It's often helpful to insert a -j LOG rule just before the end of each
table, with a log prefix like 'INPUT_dfl_drop',  'OUTPUT_dfl_drop', etc
just before the end of the INPUT, OUTPUT and FORWARD tables. That way
you can see what traffic your firewall is dropping in the logs. You'll
find that you need to add explicit DROP rules to silently drop certain
high-noise traffic without logging it first. Logging dropped traffic not
explicitly matched by any rule is something I find extremely handy as a
diagnostic aid.

This is really a bit off-topic for the PostgreSQL mailing list, though.


> 07:01:38.671236 IP 192.23.197.91.36349 > 192.23.25.177.postgresql: S
> 2589386703:2589386703(0) win 5840 <mss 1400,sackOK,timestamp 1081689372
> 0,nop,wscale 6>

OK, so a series of connection requests and no replies, but after the
PREROUTING rule has been applied so you're seeing the transformed addresses.

I'd say you need a rule in the FORWARD table to allow that traffic to be
forwarded.

> I've turned on connection logging in postgres; there is no indication of
> any connection attempt. There don't seem to be any additional rules
> configured in iptables that would drop the packets.

Are the tables set to drop by default, or allow by default?

--
Craig Ringer

I've turned on connection logging in postgres; there is no indication of