Debian squeeze 64 bit, packages from official MariaDB repositories

Hi,

Could you please provide the output of
ldd /usr/sbin/mysqld
ldd /usr/bin/mysql
sudo lsof | grep ssl | grep mysqld

and from MySQL client
SHOW STATUS LIKE '%ssl%';
SHOW VARIABLES LIKE '%ssl%';

Also, do you remember how long ago / on what system / with what openssl version your certs were generated?

And which MySQL 5.1 minor version did you use last before upgrading to MariaDB 5.5?

Thanks.

Hi,

Thank you for your reply.
Here is the info you requested:

ldd /usr/sbin/mysqld
linux-vdso.so.1 => (0x00007fff445ba000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00007fb12d2d9000)
libz.so.1 => /usr/lib/libz.so.1 (0x00007fb12d0c2000)
librt.so.1 => /lib/librt.so.1 (0x00007fb12ceb9000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x00007fb12cc82000)
libdl.so.2 => /lib/libdl.so.2 (0x00007fb12ca7e000)
libssl.so.0.9.8 => /usr/lib/libssl.so.0.9.8 (0x00007fb12c826000)
libcrypto.so.0.9.8 => /usr/lib/libcrypto.so.0.9.8 (0x00007fb12c485000)
libstdc+.so.6 => /usr/lib/libstdc+.so.6 (0x00007fb12c171000)
libm.so.6 => /lib/libm.so.6 (0x00007fb12beee000)
libc.so.6 => /lib/libc.so.6 (0x00007fb12bb8c000)
/lib64/ld-linux-x86-64.so.2 (0x00007fb12ecea000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00007fb12b976000)

ldd /usr/bin/mysql
linux-vdso.so.1 => (0x00007fffdcdff000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00007f810ea83000)
libncurses.so.5 => /lib/libncurses.so.5 (0x00007f810e83d000)
libz.so.1 => /usr/lib/libz.so.1 (0x00007f810e625000)
librt.so.1 => /lib/librt.so.1 (0x00007f810e41d000)
libssl.so.0.9.8 => /usr/lib/libssl.so.0.9.8 (0x00007f810e1c6000)
libcrypto.so.0.9.8 => /usr/lib/libcrypto.so.0.9.8 (0x00007f810de24000)
libdl.so.2 => /lib/libdl.so.2 (0x00007f810dc20000)
libstdc+.so.6 => /usr/lib/libstdc+.so.6 (0x00007f810d90c000)
libm.so.6 => /lib/libm.so.6 (0x00007f810d689000)
libc.so.6 => /lib/libc.so.6 (0x00007f810d327000)
/lib64/ld-linux-x86-64.so.2 (0x00007f810f208000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00007f810d111000)

lsof | grep ssl | grep mysqld
mysqld 12689 mysql mem REG 254,0 356608 56198 /usr/lib/libssl.so.0.9.8

MariaDB [(none)]> SHOW STATUS LIKE '%ssl%';
----------------------------------------------------+

----------------------------------------------------+

----------------------------------------------------+
24 rows in set (0.00 sec)

SHOW VARIABLES LIKE '%ssl%';
--------------------------------------------------+

--------------------------------------------------+

--------------------------------------------------+
7 rows in set (0.00 sec)

The original certificates were generated in January.
Thinking there might be a problem with these certs I regenerated them on this very machine and the problem still persists.

The MySQL version was 5.1.66 from Debian

Thanks.

Hi Catalin,
thank you. Just to summarize, do I understand correctly that you

• generated new certificates as described in http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html (btw, which example, 1 or 2 did you use?),
• put the certificates in /etc/mysql/newcerts,
• restarted the server,
• and you can't connect to it with the client command provided in the description?

Could you please also run
openssl verify -CAfile /etc/mysql/newcerts/ca-cert.pem /etc/mysql/newcerts/server-cert.pem /etc/mysql/newcerts/client-cert.pem
and
ls -l /etc/mysql/newcerts/*.pem

Thanks.

Hi,

I used example 1.
I tried both certificate sets in connecting to the database:

mysql -v -u user -p database --ssl-ca /etc/mysql/newcerts/ca-cert.pem --ssl-cert /etc/mysql/newcerts/client-cert.pem --ssl-key /etc/mysql/newcerts/client-key.pem
mysql -v -u user -p database --ssl-ca /etc/mysql/newcerts/ca-cert.pem --ssl-cert /etc/mysql/newcerts/server-cert.pem --ssl-key /etc/mysql/newcerts/server-key.pem

openssl verify -CAfile /etc/mysql/newcerts/ca-cert.pem /etc/mysql/newcerts/server-cert.pem /etc/mysql/newcerts/client-cert.pem
/etc/mysql/newcerts/server-cert.pem: /C=RO/ST=Sibiu/L=Sibiu/O=Organisation/OU=ODOPS/CN=ODOPS/emailAddress=mail@mail.com
error 18 at 0 depth lookup:self signed certificate
OK
/etc/mysql/newcerts/client-cert.pem: /C=RO/ST=Sibiu/L=Sibiu/O=Organisation/OU=ODOPS/CN=ODOPS/emailAddress=mail@mail.com
error 18 at 0 depth lookup:self signed certificate
OK

ls -l /etc/mysql/newcerts/*.pem

rw-rr- 1 root root 1667 Jun 25 12:04 /etc/mysql/newcerts/ca-cert.pem
rw-rr- 1 root root 1679 Jun 25 12:03 /etc/mysql/newcerts/ca-key.pem
rw-rr- 1 root root 1306 Jun 25 12:08 /etc/mysql/newcerts/client-cert.pem
rw-rr- 1 root root 1675 Jun 25 12:08 /etc/mysql/newcerts/client-key.pem
rw-rr- 1 root root 1062 Jun 25 12:08 /etc/mysql/newcerts/client-req.pem
rw-rr- 1 root root 1306 Jun 25 12:07 /etc/mysql/newcerts/server-cert.pem
rw-rr- 1 root root 1675 Jun 25 12:07 /etc/mysql/newcerts/server-key.pem
rw-rr- 1 root root 1062 Jun 25 12:07 /etc/mysql/newcerts/server-req.pem

Thanks

Interesting, I don't think it's supposed to give "error 18 at ...".
I generated the keys (also on Debian Squeeze 64-bit) trying to reproduce the problem, and I was not getting this error. But I was using Example 2, the script.

I'll re-do it through Example 1 and will get back to you. Maybe there is something with the keys that MySQL tolerates but MariaDB doesn't...

Thank you, I was able to reproduce the problem with keys created using the information you provided – the keys work with MySQL 5.1.66, but do not work 5.5.31.

Hi Catalin,

I ran some tests and got something that you might try as (at least) a workaround.

The errors ("error 18 at 0 depth...") make me suppose that when you were creating the certs, you used the exact same info for each of CA certificate, server certificate and client certificate. That's when the error pops up; and that's what causes MariaDB to reject the connection later when these certificates are used.

It seems important that at least one field in CA certificate differs from the field in the server and client certificates (information in the latter two can be identical). I was experimenting with Locality Name field, I suppose others can be used as well, but I didn't check.

Could you please try the following (I will use the information you pasted above, if you obfuscated it, please feel free to modify accordingly, but preserve the difference that I highlight):

Execute the cert creation procedure again;

First time when you are asked for Country / State / Locality /Organisation etc., enter
Country Name: RO
State or Province Name: Sibiu
Locality Name: Sibiu
Organization Name: Organisation
etc, whatever you usually enter

Second and third time when you are asked for Country / State / Locality / Organisation etc., enter
Country Name: RO
State or Province Name: Sibiu
Locality Name: <Enter> <= do not enter any name here, just press <Enter>, so that the default name is used
Organization Name: Organisation
etc. whatever you usually enter

Make sure that on the last step, during verification, you got "OK" for both certs, without "error 18 at 0...".

After that, restart MariaDB server using the new certificates and try to connect to it, also using the new certificates.

My guess (not confirmed yet) with yaSSL, which MySQL 5.1 on Debian is using, this error is ignored, while with OpenSSL which is used by MariaDB 5.5, it's checked and thus causes the connection reject. I don't know yet if the difference is inside the libraries themselves or is on the mysql server level, it is something to figure out, we'll keep investigating it. Chances are that the new behavior is actually correct, since openssl itself says that the certificates are erroneous and cannot be trusted.

Hi Elena,

Thank you for your help.
Using your instructions, I was able to generate some certificates and be able to use SSL for connecting to MariaDB

openssl verify -CAfile /etc/mysql/newcerts/ca-cert.pem /etc/mysql/newcerts/server-cert.pem /etc/mysql/newcerts/client-cert.pem
/etc/mysql/newcerts/server-cert.pem: OK
/etc/mysql/newcerts/client-cert.pem: OK

Thanks again for your support.

Hi Sergei,

Passing it to you for a verdict.
Long story short, there are certain combinations of certificates created through openssl which make openssl suspicious. In our example it happens because all information in CA certificate and server/client certificate is identical. On certificate verification, openssl itself throws an error.

MariaDB using openssl library refuses to connect with such certificates. MySQL (and I suppose MariaDB) using yaSSL allows to connect all right.

On one hand, it looks like openssl-based builds behave in a more correct way since the library itself thinks that the certificates are not trustworthy.
One the other hand, at this point it's a regression for many people who have been using such certificates forever and suddenly started getting an error.

I'd say it's not a bug. One can use SSL certificates. The command-line openssl complains that they are invalid. It's logical to expect that they won't work. In this case it's not our random decision, but a standard SSL verification tool that rejects them. One could even argue that it was a bug that invalid certificated worked at all.

An additional note for those who might have found this issue while looking up the problem.

There is also Percona bug report https://bugs.launchpad.net/percona-server/+bug/1169505, currently there is a user who says they are not getting an error on openssl verify, but still can't connect using the certificates after upgrade from one Percona version to another. It's hard to say whether it's a user error or a different problem.

If you observe similar behavior on a MariaDB version (no errors on openssl verify, but can't connect), please leave a comment with any information you can provide (see the previous discussion here for the hints what might be useful).

I was having the same problem and this workaround fixed it for me.

One added difficulty in my case was that I had an invalid path specified in my.cnf for the `ssl-ca` setting and it was returning the same exact error code/message. Once I fixed that and then applied this workaround, everything worked just fine.

I'm on CentOS 5.8, OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008, and mysql 5.5.27

Please don't mind the older versions of everything, I'm in the process of migrating off of this server by end of month.

Thank you very much for the fix/workaround.