[ILUG] MySQL V4 with SSL has problems with iptables

[Bailey, Darragh]

> -----Original Message-----
> From: Andrew McGill [mailto:glug at lunch.za.net]
> Sent: 15 October 2008 13:26
> To: ilug at linux.ie
> Cc: Bailey, Darragh
> Subject: Re: [ILUG] MySQL V4 with SSL has problems with iptables
>

<snipped>

> It sounds like restarting the firewall is resetting your connection
> tracking
> state.  That's probably not ideal in your case.

I will admit iptables is a little bit of a mystery to me, so I'm not 100% sure about what's going on in the firewall.


> Have another look at your firewall configuration:
>
>     iptables-save
>
> It sounds as if you have rules like this:
>
>     iptables -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
>     iptables -A INPUT -p tcp --dport 3306 -m state --state NEW -j ACCEPT
>     iptables -A INPUT -j DROP
>
> This would mean that traffic which is not known by the state table will
> get
> ignored.  This means that the only way the client will know that its
> connection to the server has gone bad is that it will time out.  (15
> minutes
> is not such a bad timeout if you are going between continents.)

Maybe, but it's not good when the two machines are less that 6" away from each other ;).

By default all traffic on the network that MySQL is communicating over should be getting accepted. Also this problem only occurs if the connection is using SSL, if it's not, then our firewall rules appear to work perfectly fine.

I say appear to, since there could still be something going wrong, but not appearing when using non SSL connections.


> You can change the ACCEPT rule to accept *any* traffic to port 3306,
> regardless of the state the firewall thinks the connection is in.  That
> will
> give the kernel a chance to say "your connection has gone away" (or not):
>
>     iptables -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
>     iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
>     iptables -A INPUT -j DROP
>
> Alternatively, you can REJECT the port 3306 traffic the firewall doesn't
> like:
>     iptables -A INPUT -p tcp --dport 3306 -j REJECT

So are you suggesting the following for input rules?
     iptables -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
     iptables -A INPUT -p tcp --dport 3306 -m state --state NEW -j ACCEPT
     iptables -A INPUT -p tcp --dport 3306 -j REJECT


> That should fix it up -- provided you don't have similarly misbehaving
> OUTPUT
> rules.
>
> &:-)

Since the problem only occurs if the firewall on the client system connecting to the server is restarted, I'm not convinced that it's the input rules that are losing the information, unless of course you are suggesting that its iptables rules on the server machine that are not rejecting correctly.


The other thing about rejecting on port 3306, I believe the way MySQL server and clients work is that after the initial handshake on port 3306, the client is told to reconnect via another port for its queries, where a server thread will handle its requests.

In this case, if the firewall is restarted, the connection that is the problem is not over 3306, but a different dynamically assigned port, which means the REJECT should have no effect.


*******
Looks like it's a pilot caused issue :(

Just after removing the custom iptables rules and trying using the redhat setup tool to permit all traffic over eth0 (the network that mysql is talking over), and permit ssh and www access. No hangs, no timeouts.

So the custom iptables config is obviously the problem. Note to self, never trust the test results of someone else, always verify that all test results can be reproduced by yourself.


Oh well, back to the drawing board, I guess this is now an opportunity for me to learn how to write iptable rules :).


Thanks for the suggestions.

--
Regards,
Darragh Bailey

Systems Software Engineer
Hewlett Packard Galway Ltd.

Postal Address:    Hewlett Packard Galway Limited, Ballybrit Business Park, Galway
Registered Office: Hewlett Packard Galway Limited, 63-74 Sir John Rogerson's Quay Dublin 2
Registered Number: 361933

_______________________________________________
The contents of this message and any attachments to it are confidential and may be legally privileged. If you have received this message in error you should delete it from your system immediately and advise the sender.
To any recipient of this message within HP, unless otherwise stated you should consider this message and attachments as "HP CONFIDENTIAL".