MySQL Bugs: #100688: Client cannot connect to remote mysql-server when the latter is configured with

Bug #100688	Client cannot connect to remote mysql-server when the latter is configured with
Submitted:	29 Aug 2020 8:55	Modified:	7 Nov 2020 10:09
Reporter:	Jean-christophe Manciot	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.21	OS:	Linux (Debian & Ubuntu)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86
Tags:	lets_encrypt, public_ca, SSL, tls

Description:
Ubuntu focal
mysql-server-8.0: 8.0.21-0ubuntu0.20.04.4

Ubuntu groovy & Debian bullseye
mysql-server-8.0: 8.0.21-1

in /etc/mysql/mysql.cnf (target of /etc/alternatives/my.cnf symlink):
ssl-capath=/etc/ssl/lets_encrypt
ssl-cert=/etc/ssl/domain/domain.crt
ssl-key=/etc/ssl/domain/domain_rsakey.pem.decrypted
require_secure_transport=ON

the server certificate is a wildcard certificate delivered by let's encrypt and successfully used on other services.

Those settings are read by the server:
# mysqld --verbose --help|grep -P ^"(ssl|tls)"
ssl                     TRUE
ssl-ca                  (No default value)
ssl-capath              /etc/ssl/lets_encrypt
ssl-cert                /etc/ssl/domain/domain.crt
ssl-cipher              (No default value)
ssl-crl                 (No default value)
ssl-crlpath             (No default value)
ssl-fips-mode           OFF
ssl-key                 /etc/ssl/domain/domain_rsakey.pem.decrypted
tls-ciphersuites        (No default value)
tls-version             TLSv1,TLSv1.1,TLSv1.2,TLSv1.3

Yet, they seem to have no effect:
# echo 'q' | sudo systemctl --no-pager --full status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2020-08-29 10:27:16 CEST; 4s ago
    Process: 3230835 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 3230844 (mysqld)
     Status: "Server is operational"
      Tasks: 41 (limit: 9271)
     Memory: 409.5M
     CGroup: /system.slice/mysql.service
             └─3230844 /usr/sbin/mysqld
...
Aug 29 10:27:15 xxxxxxxxxx mysqld[3230844]: 2020-08-29T08:27:15.324990Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

I am aware that some default certificates/keys are always created at each server start:
# find /var/lib/mysql/ -name "*.pem"
/var/lib/mysql/8.0/public_key.pem
/var/lib/mysql/8.0/ca-key.pem
/var/lib/mysql/8.0/server-key.pem
/var/lib/mysql/8.0/ca.pem
/var/lib/mysql/8.0/server-cert.pem
/var/lib/mysql/8.0/client-cert.pem
/var/lib/mysql/8.0/private_key.pem
/var/lib/mysql/8.0/client-key.pem

However, warning that "ca.pem is self signed" when another CA is configured seems to indicate that the latter is not used.

Also, testing the TLS connection with mysql server across a network leads to an error (no such issue with other services running on the same host and using the same CA/wildcard certificate):

# openssl s_client -connect mysql.domain:3306 -msg -name $(hostname).$(dnsdomainname) -servername mysql.domain -showcerts -state -status

CONNECTED(00000003)
SSL_connect:before SSL initialization
>>> ??? [length 0005]
    16 03 01 01 39
>>> TLS 1.3, Handshake [length 0139], ClientHello
    01 00 01 35 03 03 0e f1 c2 48 b8 f4 11 0a b5 41
    3a 8c 9f 93 48 2f 5c 0e 3f 18 b4 59 b0 2e 72 f3
    97 aa 45 59 bf 46 20 e6 75 34 d8 03 39 df d2 ee
    98 3c b4 ab 3b 0c 37 3b 0f f6 b8 cf 5e b2 94 b8
    f9 76 b1 f9 3d 85 62 00 3e 13 02 13 03 13 01 c0
    2c c0 30 00 9f cc a9 cc a8 cc aa c0 2b c0 2f 00
    9e c0 24 c0 28 00 6b c0 23 c0 27 00 67 c0 0a c0
    14 00 39 c0 09 c0 13 00 33 00 9d 00 9c 00 3d 00
    3c 00 35 00 2f 00 ff 01 00 00 ae 00 00 00 16 00
    14 00 00 11 6d 79 73 71 6c 2e 73 64 78 6c 69 76
    65 2e 63 6f 6d 00 0b 00 04 03 00 01 02 00 0a 00
    0c 00 0a 00 1d 00 17 00 1e 00 19 00 18 00 23 00
    00 00 05 00 05 01 00 00 00 00 00 16 00 00 00 17
    00 00 00 0d 00 2a 00 28 04 03 05 03 06 03 08 07
    08 08 08 09 08 0a 08 0b 08 04 08 05 08 06 04 01
    05 01 06 01 03 03 03 01 03 02 04 02 05 02 06 02
    00 2b 00 05 04 03 04 03 03 00 2d 00 02 01 01 00
    33 00 26 00 24 00 1d 00 20 e7 a8 da ca 4a 5e c3
    b1 e9 1b 89 ef ef c7 f9 73 ae 15 64 a5 e9 79 00
    ac ef f7 3d a7 0a e1 ff 65
SSL_connect:SSLv3/TLS write client hello
<<< ??? [length 0005]
    4c 00 00 00 0a
SSL_connect:error in error
140665896469824:error:1408F10B:SSL routines:ssl3_get_record:wrong version number:../ssl/record/ssl3_record.c:331:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 5 bytes and written 318 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---

Same issue if I specify the CA (ssl-ca instead of ssl-capath).

How to repeat:
On one of the following Linux platforms:
Ubuntu focal
mysql-server-8.0: 8.0.21-0ubuntu0.20.04.4

Ubuntu groovy & Debian bullseye
mysql-server-8.0: 8.0.21-1

Replace 'domain' with your domain (such as example.com):
in /etc/mysql/mysql.cnf (target of /etc/alternatives/my.cnf symlink):
ssl-capath=/etc/ssl/lets_encrypt
ssl-cert=/etc/ssl/domain/domain.crt
ssl-key=/etc/ssl/domain/domain_rsakey.pem.decrypted
require_secure_transport=ON

domain.crt is a wildcard certificate

on the remote server:
# systemctl restart mysql

on the local client:
# openssl s_client -connect mysql.domain:3306 -msg -name $(hostname).$(dnsdomainname) -servername mysql.domain -showcerts -state -status

The title has been trimmed: the original one was:

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

Hi,

Sorry for the delay, I tested this on centos/oracle linux and on fedora and I was not able to reproduce the problem. I'll retest this on debian when I set one up but I have to check few things first
 - where is this binary of mysql coming from, are you sure you are installing deb files from the oracle? or you maybe used tar.gz distribution?

Thanks
Bogdan

Sorry for the delay.

I have downloaded from [official mysql site](https://dev.mysql.com/downloads/mysql/) & installed all relevant binary packages from mysql-server_8.0.21-1ubuntu20.04_amd64.deb-bundle.tar.

On the remote server:
# systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2020-10-02 11:31:56 CEST; 7min ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 502646 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 502706 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 9271)
     Memory: 343.6M
     CGroup: /system.slice/mysql.service
             └─502706 /usr/sbin/mysqld

Oct 02 11:31:54 hostname systemd[1]: Starting MySQL Community Server...
Oct 02 11:31:54 hostname mysqld[502706]: 2020-10-02T09:31:54.920409Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as pro>
Oct 02 11:31:54 hostname mysqld[502706]: 2020-10-02T09:31:54.931791Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
Oct 02 11:31:56 hostname mysqld[502706]: 2020-10-02T09:31:56.010481Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
Oct 02 11:31:56 hostname mysqld[502706]: 2020-10-02T09:31:56.113417Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 1, name 'sys/sys_config', file './sy>
Oct 02 11:31:56 hostname mysqld[502706]: 2020-10-02T09:31:56.197258Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ':>
Oct 02 11:31:56 hostname mysqld[502706]: 2020-10-02T09:31:56.508558Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
Oct 02 11:31:56 hostname mysqld[502706]: 2020-10-02T09:31:56.508779Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. En>
Oct 02 11:31:56 hostname mysqld[502706]: 2020-10-02T09:31:56.536119Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version>
Oct 02 11:31:56 hostname systemd[1]: Started MySQL Community Server.

On the local client:
# openssl s_client -connect mysql.domain:3306 -name $(hostname).$(dnsdomainname) -servername mysql.domain -showcerts -state -status
CONNECTED(00000003)
SSL_connect:before SSL initialization
SSL_connect:SSLv3/TLS write client hello
SSL_connect:error in error
140189896140096:error:1408F10B:SSL routines:ssl3_get_record:wrong version number:../ssl/record/ssl3_record.c:331:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 5 bytes and written 318 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)

Hi,

I'm still not reproducing this. The difference is that I do use self signed self made certs and not lets_encrypt ones but it should be same. Have you tried removing the keys from the var/lib/ and copying your keys to the same dir with those names? Might be one of the debian "security" features (like selinux on redhat)

all best
Bogdan

The whole point is about using a public certificate.
You are unable to reproduce this issue because you're not using one such certificate.

The files you're referring to are automatically created each time the systemd service is restarted, so your proposed trick cannot be implemented.

All public certificate related files are readable by mysql user.
Finally, selinux is not used. Instead, apparmor is and I have added the necessary configuration to allow mysql server to read the right folders and files.

Hi,

> The whole point is about using a public certificate.

The public certifice should work exactly the same without any difference.

> You are unable to reproduce this issue because you're not using one such certificate.

I'm using cert generated by me (not one auto-generated) so it is "generated" certificate. The difference is I know this one is generated properly. I have no clue how lets_encrypt make those

> The files you're referring to are automatically created each time the systemd
> service is restarted, so your proposed trick cannot be implemented.

Nope, the certs I tested are created as described in the manual
https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html

> All public certificate related files are readable by mysql user.
> Finally, selinux is not used. Instead, apparmor is and I have added the necessary configuration to allow mysql server to read the right folders and files.

Have you tried with self-signed certs made by you? So not the ones made by the lets_encrypt? as described https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html

How exactly are you creating the lets_encrypt ones? You just certbot -domains x.y or ?

Thanks
Bogdan

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".