PGSQL x iptables

Slansky Lukas wrote:

Hello,

we’re using PG and Application Server (JBoss) on separate CentOS
servers with Cisco PIX in between. On DB side is iptable with
following relevant rules:

1. -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

2. -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s
aaa.bbb.ccc.ddd --dport 5432 -j ACCEPT

3. -A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

I was wondering when these rules are not OK for our environment. It
seems that rules 1 and 2 sometimes pass packets and therefore these
packets are rejected. Such connection is then in some weird state,
doesn’t communicate (obviously – packets are dropped) and psql (or
JBoss) connection is blocking for a long time (at least few hours).

Everything seems to be OK when I have changed rule 2 to “-A
RH-Firewall-1-INPUT -m tcp -p tcp -s aaa.bbb.ccc.ddd --dport 5432 -j
ACCEPT“.

I’m really confused – what other states are possible for iptables
except ESTABLISHED, RELATED or NEW? In iptables manpage is only
INVALID, but why is this state emerging?

this is a linix iptables question, not a postgres question.

Slansky Lukas wrote:

1. -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

2. -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s
aaa.bbb.ccc.ddd --dport 5432 -j ACCEPT

3. -A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

I was wondering when these rules are not OK for our environment. It
seems that rules 1 and 2 sometimes pass packets and therefore these
packets are rejected.

After a long period of inactivity, perhaps?

If you're relying on `-m state' or `-m ctstate' you should be using a
TCP keepalive. Otherwise the connection tracking entry for the
connection will be purged after a while - how long depends on your
firewall configuration - and then packets will no longer be seen as part
of an established connection.

--
Craig Ringer

Slansky Lukas wrote:

1. -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j

ACCEPT

2. -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s
aaa.bbb.ccc.ddd --dport 5432 -j ACCEPT

3. -A RH-Firewall-1-INPUT -j REJECT --reject-with

icmp-host-prohibited

I was wondering when these rules are not OK for our environment. It
seems that rules 1 and 2 sometimes pass packets and therefore these
packets are rejected.

Craig Ringer wrote:

After a long period of inactivity, perhaps?

Is 15 seconds long period? I don't think so.

If you're relying on `-m state' or `-m ctstate' you should be using a
TCP keepalive. Otherwise the connection tracking entry for the

I'll try to lower TCP keepa live times and make some tests.

connection will be purged after a while - how long depends on your
firewall configuration - and then packets will no longer be seen as

part

of an established connection.

Deleting -m state --state NEW seems to be "solution" but I'm trying to
figure out origin of the problem.

To John: I know it's related to iptables but this state seems to be only
on PG connections :-)

L.

On Wed, May 6, 2009 at 3:47 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

Slansky Lukas wrote:

1. -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

2. -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s
aaa.bbb.ccc.ddd --dport 5432 -j ACCEPT

3. -A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

I was wondering when these rules are not OK for our environment. It seems
that rules 1 and 2 sometimes pass packets and therefore these packets are
rejected.

After a long period of inactivity, perhaps?

If you're relying on `-m state' or `-m ctstate' you should be using a TCP
keepalive. Otherwise the connection tracking entry for the connection will
be purged after a while - how long depends on your firewall configuration -
and then packets will no longer be seen as part of an established
connection.

small addendum: i bet this is the problem. You can configure server
keepalives in postgresql.conf. I'd suggest two minutes
(tcp_keepalives_idle=120) .

merlin

Slansky Lukas wrote:

Craig Ringer wrote:

After a long period of inactivity, perhaps?

Is 15 seconds long period? I don't think so.

No. If you see a connection that was working 15 and active seconds ago
suddenly die, it's not due to time-based state table expiry.

Do you see anything in `dmesg'?

Have you used wireshark to trace activity on the interface and analyzed
the dumps? You can often learn a lot about what's actually happening
that way.

To John: I know it's related to iptables but this state seems to be only
on PG connections :-)

Very odd.

--
Craig Ringer

Very odd, indeed.
We have analysed the traffic on both ends and we have found that the
problem is probably in PIX. As I have read in LKML
(http://lkml.org/lkml/2007/7/29/174) CISCO works really badly with SACK
packets and these are then recognized as INVALID and dropped. We still
don't know what is wrong and why SACKs are produced but I suspect PIX
too. The workaround is and some explanation is mentioned in LKML. As we
have found there is not problem only in PG, but in Oracle too (it seems
that DB server or drivers deals with this problem better). So this is
not only PG-related.

Now after turning SACKs off seems connection stabile. But we'll try to
figure out why are such packets produced. But it will probably take
time...

Thanks,
Lukas

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Craig Ringer
Sent: Thursday, May 07, 2009 3:02 AM
To: Slansky Lukas
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PGSQL x iptables

Slansky Lukas wrote:

Craig Ringer wrote:

After a long period of inactivity, perhaps?

Is 15 seconds long period? I don't think so.

No. If you see a connection that was working 15 and active seconds ago
suddenly die, it's not due to time-based state table expiry.

Do you see anything in `dmesg'?

Have you used wireshark to trace activity on the interface and analyzed
the dumps? You can often learn a lot about what's actually happening
that way.

To John: I know it's related to iptables but this state seems to be

only

on PG connections :-)

Very odd.

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general