[ILUG] MySQL V4 with SSL has problems with iptables

[Andrew McGill]

On Tuesday 14 October 2008 18:42:40 Bailey, Darragh wrote:
> Some background:
>
> Have a problem at work with MySQL V4.1.22 on RHEL4 using SSL connections,
> where an iptables restart/reconfig will result in connections to the DB
> over SSL hanging for 10-15 minutes, until the TCP timeout kicks in.
>
> What we have is a cluster with an instance of MySQL running on one node and
> all other nodes connecting to the DB server over the network. For security
> reasons we decided to make all the connections to DB authenticated using
> SSL cert's (and encrypted at the same time).
>
> We also wanted to utilise iptables to lock down network access. But we
> found that restarting iptables on any of the nodes that were connecting to
> the DB would cause any open connections to hang, despite all outbound
> traffic being permitted by the iptables.
>
> The problem I believe is a bug in MySQL's network socket handling.
> Previously there was a bug found in the standard network code of MySQL
> which could cause problems with iptables, apparently it was doing a read
> which would block, when they should have been doing a select to check to
> see if there was a response first. Eventually the tcp connections would
> time out. This was fixed, but it appears not in the code path used when SSL
> is enabled.
>
> It was eventually fixed in V5, however that's of limited benefit when using
> RedHat EL 4, since many programs were built against the older library and
> that library has not been fixed. V5 is libmysqlclient.so.15.0.0, V4 is
> libmysqlclient.so.14.0.0. Even the shared compat package for MySQL V5 on
> rhel4 is only including the old library which is broken.
>
> For our own code using the C API, we can of course recompile against the
> newer library and all is well (already confirmed that), but we also use
> perl and python scripts which need to connect to the DB, and also any other
> package that is linked against the V4 client libs will have to be
> recompiled before they would work using SSL and not risk a hang if iptables
> is reconfigured.
>
>
> Some solutions:
>
> One solution is to avoid restarting iptables. Well we try to, but there's
> no guarantee that adding rules to or removing them from iptables without a
> restart won't trigger the problem at the most inconvenient time. An
> iptables restart will definitely trigger the problem but I'd like to avoid
> depending on that to keep everything working.
>
>
> Another is to recompile everything that uses the mysql libraries that will
> be connecting to the DB instance over SSL. I'd like to avoid that, since it
> would involve recompiling updates from RedHat as well before deploying
> them.
>
>
> What I'm Looking at:
>
> Upgrading to MySQL V5, using the standard shared libraries and installing a
> custom rpm that will create symlinks for the older libraries to point to
> the newer V5 client libs and hope that they are very compatible. This would
> mean that all programs linked against the mysql client libraries would be
> using the newer V5 client library instead.
>
>
> I've also considered trying to place some other app in between the client
> and server and try to tunnel the connections and see if by handling the
> network communication via that program if it would avoid the problem with
> the mysql client hanging on a iptables restart. I believe though that since
> mysql would open a connection over another port for the client to send
> queries over, it would need to detect the new connection and do some
> mangling to continue to tunnel the actual queries. Otherwise the only thing
> being tunnelled is the initial handshake part.
>
>
> Anyone have any alternative ideas of what else I can do, besides badgering
> MySQL to fix the bug :)
It sounds like restarting the firewall is resetting your connection tracking 
state.  That's probably not ideal in your case.

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.)

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

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

&:-)