Client cannot connect to remote mysql-server when the latter is configured with ssl parameters using a public CA

Hello Jean,

Thank you for taking the time to file a bug report.

So, in a clean Focal installation, if I do:

"""
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysql]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
ssl-capath=/etc/mysql/ssl/

[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
ssl-capath=/etc/mysql/ssl/
"""

in my.cnf... I get:

2020-08-14T18:16:44.983214Z 0 [Warning] [MY-013414] [Server] Server SSL certificate doesn't verify: self signed certificate
2020-08-14T18:16:44.983499Z 0 [Warning] [MY-010068] [Server] CA certificate /etc/mysql/ssl/ca.pem is self signed.
2020-08-14T18:16:44.983822Z 0 [Warning] [MY-010068] [Server] CA certificate /etc/mysql/ssl//ca.pem is self signed.
2020-08-14T18:16:44.984106Z 0 [Warning] [MY-010068] [Server] CA certificate /etc/mysql/ssl//server-cert.pem is self signed.
2020-08-14T18:16:44.984412Z 0 [Warning] [MY-010068] [Server] CA certificate /etc/mysql/ssl//client-cert.pem is self signed.
2020-08-14T18:16:44.984777Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

in /var/log/mysql/error.log.

Meaning that my certificate is being used. When testing:

mysql> status
--------------
mysql Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu))

Connection id: 19
Current database:
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: less -R --chop-long-lines
Using outfile: ''
Using delimiter: ;
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 7 min 57 sec

and all certificates were created using Example 1 of https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html.

Permissions are:

$ ls -lahR ssl
ssl:
total 40K
drwxr-x--- 2 mysql root 4.0K Aug 14 18:31 .
drwxr-xr-x 5 root root 4.0K Aug 14 17:40 ..
-rw------- 1 mysql root 1.7K Aug 14 18:29 ca-key.pem
-rw-r--r-- 1 mysql root 1.4K Aug 14 18:29 ca.pem
-rw-r--r-- 1 mysql root 1.2K Aug 14 18:29 client-cert.pem
-rw-r--r-- 1 mysql root 1.7K Aug 14 18:29 client-key.pem
-rw------- 1 mysql root 1001 Aug 14 18:29 client-req.pem
-rw------- 1 mysql root 1.2K Aug 14 18:29 server-cert.pem
-rw------- 1 mysql root 1.7K Aug 14 18:29 server-key.pem
-rw------- 1 mysql root 1001 Aug 14 18:29 server-req.pem

Note that some files are +r just so my user can read them when executing mysql client.

With all that said, could you point out where you think the bug is and/or a way to reproduce what you are facing ?

Note: your openssl s_client command should change CA and verification paths to the place where you're placing the SSL certificates for mysql server. Nevertheless, its much easier to simply test it using the mysql client using the same configuration changes as [mysqld] in my.cnf.

Since it seems likely to me that t...

I appreciate the time you took to answer.
However, your example is a different use case: you're using a self-signed certificate with a private CA.

My use case use a public CA (namely let's encrypt, but it could be anything else).
In my setup, client and servers are on totally different machines. They should be able to communicate over TLS like you're using your browser right now to communicate over https with the remote server https://bugs.launchpad.net/ which happens to also use let's encrypt as a public certificate.

I am aware that the official mysql doc only displays SSL examples using only a private CA.
I suspect this issue to come from an inability to perform correctly with a public CA/certificate.

I meant let's encrypt as a public certificate ... authority.

Also, if it is of any help:
mysql> status
--------------
mysql Ver 8.0.21-0ubuntu4 for Linux on x86_64 ((Ubuntu))

Connection id: 31
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.21-0ubuntu4 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 15 hours 45 min 24 sec

There is no fundamental difference between a "public" and a "private" CA. The only difference is in what root certificates (if any) are trusted.

Since your report is "mysql-server does not take into account configured ssl parameters" I see no reason why a simple reproduction of your case with self-signed certificates should not be possible.

I suggest you start by trying to provide full steps to reproduce your problem using self-signed certificates. If you cannot, but the same configuration steps do not work when adjusted to use certificates signed by Let's Encrypt, then that would demonstrate that the problem somehow only exhibits itself when using a "public CA". Either way, please provide such reproduction steps and then we can look again.

I changed the title to better reflect the core issue of this thread.

I suggest you try to configure your mysql-server with SSL parameters using a public CA, since this is the use case of this issue.

Hi,

I'd say that the problem is with mysqld using a different CA certificate from the one specified by the ssl-ca option. I doubt it's the letsencrypt certificate the one being used, correct me if I'm wrong (y can check with e.g. `openssl c_client`).

Could you please:

1. share the ssl config snippet from mysql.cnf?

2. confirm that you don't need the client part to reproduce the problem, as the "CA is self-signed" message is a mysqld log message that is printed before any connection attempt? This is mostly to verify that I correctly understood the problem.

3. Set ssl-capath to /etc/ssl/lets_encrypt/ and see if it behaves differently?

Please change the report status back to New after commenting back. Thanks!

1. already shared in my first post
2. You don't seem to understand this issue.
Not a problem, because in the meantime, I have upgraded to groovy with:

mysql-server-8.0:
  Installed: 8.0.21-0ubuntu4

Now I get:
...
SSL_connect:SSLv3/TLS write finished
---
Certificate chain
 0 s:CN = *.domain
   i:C = US, O = Let's Encrypt, CN = Let's Encrypt Authority X3
-----BEGIN CERTIFICATE-----

This issue has been solved on groovy.

FYI, the systemd service continues to show an incorrect warning:
[Warning] [MY-010068] [Server] CA certificate ca.pem is self signed

Sorry: I have inadvertently tested with a port matching another service (853 instead of 3306), so my previous post must be ignored.

This issue is still there, even on groovy. Let me answer your questions again:

0) with a service answering correctly to TLS connections requests, we get with a wildcard server certificate:
...
SSL_connect:SSLv3/TLS write finished
---
Certificate chain
 0 s:CN = *.domain
   i:C = US, O = Let's Encrypt, CN = Let's Encrypt Authority X3
-----BEGIN CERTIFICATE-----

As my first post shows, there is an initial error in the connection preventing openssl to show the CA used or the server certificate:
...
SSL_connect:error in error
139858538362176:error:1408F10B:SSL routines:ssl3_get_record:wrong version number:../ssl/record/ssl3_record.c:331:
---
no peer certificate available
---

1) The full SSL configuration (besides the 3 parameters already described in my first post) also includes the following parameters which are IMHO not relevant because his issue still happens if I remove them:
ssl-cipher=...
tls-version=...
require_secure_transport=ON

2) You don't seem to understand this issue. The client don't need special configuration, other than at least one matching cipher and required TLS version if they are specified on the server side.

3)I have already tried that: same issue.

Hi Jean-Christophe,

Sorry for this back and forth, I tried again to reproduce what you mentioned but with a self-signed CA (like Rafael did in the first comment) and it worked like a charm. Before you saying this is not the same scenario you are facing, as far as I can see you are reporting a problem when setting the ssl parameters (if the logs are saying "CA certificate ca.pem is self signed" is because it is likely not reading the files from your Let's Encrypt directory). In this case it does not matter if it's a private or public CA. If you can confirm it is reading your Let's Encrypt files and it is reporting that the certificate is self signed share the logs and config files here and it will likely be an upstream issue.

You might be tired of us asking for reproduction steps but there is no way around that, we can't reproduce your problem. Try to add as much details as you can, for instance, create a new container/VM of Ubutu Focal/Groovy, install mysql-server, run command X, edit these files adding these content, restart mysql service and so on.

I am marking this bug again as Incomplete, when you have more input for us please change the status back to New.

Same issue with mysql-server 8.0.21-1 on groovy.

[Expired for mysql-8.0 (Ubuntu) because there has been no activity for 60 days.]