MariaDB-client 10.2.24 and applications using MariaDB-shared-10.2.24 are unable to connect to any server running mysql-community-server with secure-transport = ON

Server fedora30 running MySQL Community Server 8.0.16 with require-secure-transport = ON

Certificates are self-signed created using openssl (script attached)

NOTE:
mysql-community-client and application using mysql-community-libs have no issues connecting to either mysql-community-server or mariadb-server

Hi,

"TLS alert unknown ca" error message indicates, that the server doesn't accept the client certificate. It could be the wrong certificate or wrong certiticate chain.

Questiions:

• According to the examples and the server configuration it's not mandatory that the client has to send a certiticate. Did you try to connect without client cert/key?

• can you provide a tcpdump/pcap (wireshark) for getting more information?

Thx for your help!

> "TLS alert unknown ca" error message indicates, that the server doesn't accept the client certificate. It could be the wrong certificate or wrong certiticate chain.

This is what I suspected, but I am unable to figure out the issue. The moment I change the libs/client to mysql-community-libs/mysql-community-client, the problem dissapears. Also with the same certificates I'm able to connect to any MariaDB
server. The issue happens only when the destination serer is a mysql-community 8.x server. I will also attempt by downgrading the
mysql-community-server and mariadb-client / mariabb-shared packages and see if I get the same problem. e.g. this is what i get if I use a MariaDB server as the backend

$ mysql -u indimail -p indimail -h 172.17.0.2 --ssl --silent
Enter password:
MariaDB [indimail]> status
--------------
mysql Ver 15.1 Distrib 10.2.24-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 11
Current database: indimail
Current user: indimail@172.17.0.2
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
...
TCP port: 3306
Uptime: 47 min 29 sec

Threads: 7 Questions: 36 Slow queries: 2 Opens: 26 Flush tables: 1 Open tables: 20 Queries per second avg: 0.012

> According to the examples and the server configuration it's not mandatory that the client has to send a certiticate. Did you try to connect without client cert/key?

Yes. But since the server has require-secure-transport = ON, I have to specify --ssl on the command line. But I get this strange error
"Access denied", even though the password is correct. The connection happens successfuly if I turn off require-secure-transport.

$ mysql -u indimail -p indimail -h 172.17.0.1 --ssl
Enter password:
ERROR 1045 (28000): Access denied for user 'indimail'@'172.17.0.2'
(using password: YES)

> can you provide a tcpdump/pcap (wireshark) for getting more information?

Here are two sets of tcpdump files for both client & server. I used -vv -A arguments to tcpdump. If I need to specify something else, let me know. This is what I used

1) on the server
$ sudo tcpdump -vv -A -i docker0 ip host 172.17.0.1 and 172.17.0.2

2) on the client
$ sudo tcpdump -vv -A -i eth0 ip host 172.17.0.1 and 172.17.0.2

The first set is where the client key is specified in the ini files.The second set is without any keys and just with the -ssl argument to mysql comm and line client. I am also including the mysql-communityserver and ini file in mysql-communit-server_details.txt. Something I forgot to mention is that I am using a centos docker image for the mariadb-client testing.

Appreciate the help.

The dumpfiles aren't very useful in text format, would it be possible to provide them in binary format (You can capture them either by wireshark or by tcpdump with -w option (for binary format).

I created certificate and keys with your script, and didn't get any error:

~/work/mariadb/server/10.4/bld/client/mysql -uroot -h127.0.0.1 -P13000 --ssl-cert=client-cert.pem --ssl_key=client-key.pem --ssl_ca=ca.pem -e"show variables like 'require_secure_transport'";

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

| Variable_name            | Value |

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

| require_secure_transport | ON    |

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

Client is from MariaDB Server 10.4, using OpenSSL 1.1.1

I am goint to attach the files as tcpdump_server.out and tcpdump_client.out. Meanwhile, I have done some more troubleshooting. Here are my results

From Centos7 (docker machine), I am able to connect to a mysqld server from oracle using mysql_real_connect() API from both libmysqlclient.so.18.0.0 and libmariadb.so.3

$ rpm -qf /usr/lib64/libmariadb.so.3 /usr/lib64/libmysqlclient.so.18.0.0 

MariaDB-shared-10.2.24-1.el7.centos.x86_64

MariaDB-compat-10.2.24-1.el7.centos.x86_64

however mysql client isn't able to connect. /usr/bin/mysql is linked with openssl 1.0.2

$ ldd /usr/bin/mysql

        linux-vdso.so.1 =>  (0x00007fffedfe9000)

        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fd98ec4d000)

        libz.so.1 => /lib64/libz.so.1 (0x00007fd98ea37000)

        libdl.so.2 => /lib64/libdl.so.2 (0x00007fd98e833000)

        libssl.so.10 => /lib64/libssl.so.10 (0x00007fd98e5c1000)

        libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007fd98e15f000)

        libncurses.so.5 => /lib64/libncurses.so.5 (0x00007fd98df38000)

        libtinfo.so.5 => /lib64/libtinfo.so.5 (0x00007fd98dd0e000)

        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fd98da07000)

        libm.so.6 => /lib64/libm.so.6 (0x00007fd98d705000)

        libc.so.6 => /lib64/libc.so.6 (0x00007fd98d338000)

        /lib64/ld-linux-x86-64.so.2 (0x00007fd98f4a4000)

        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007fd98d0eb000)

        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007fd98ce02000)

        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007fd98cbfe000)

        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007fd98c9cb000)

        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fd98c7b5000)

        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007fd98c5a5000)

        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007fd98c3a1000)

        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fd98c188000)

        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007fd98bf61000)

        libpcre.so.1 => /lib64/libpcre.so.1 (0x00007fd98bcff000)

$ rpm -qf /lib64/libssl.so.10

openssl-libs-1.0.2k-16.el7_6.1.x86_64

I have tried the same test by installing mariadb client on Debian 9 Stretch and Fedora 30 install and I don't find the issue. I have also installed centos7 from DVD into a virtual box image and tthat too has the mariadb mysql client connecting to mysqld oracle server without any issue.

So it looks like a localized issue with the docker centos7:latest image or mariadb client Ver 15.1 Distrib 10.2.24-MariaDB My opesource project which can load either libmariadb or libmysqlclient is able to connect with ssl without any issue. It's just the mysql command line client which fails to connect to mysqld server with require_secure_transport = ON.

I was doing something with the C APIs and I stumbled upon something that suggests that this could be a docker issue

1. I am running the mariadb mysql client on a docker machine. It has the docker network interface eth0 with IP 172.17.0.2
2. I am running the oracle mysqld service on a laptop with FC30 installed. It has the wifi network interface with IP 192.168.0.8 and the docker interface docker0 with the IP 172.17.0.1

If I use mysql_real_connect() from /usr/lib64/libmariadb.so.3, with the host as 172.17.0.1 I get the error

SSL connection error: tlsv1 alert unknown ca

The above error is the same error that the mariadb mysql client is printing on the screen

If I use mysql_real_connect() with the host as 192.168.0.8 (which is on the same host that has 172.17.0.1), I am able to connect without any error

Now it gets even wierd if I use mysql_real_connect() by loading /usr/lib64/libmysqlclient.so.18.0.0.0 and host as 172.17.0.1

SSL connection error: error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed

The above error is actually an error returned by the openssl library.

$ rpm -qf /usr/lib64/libmariadb.so.3 /usr/lib64/libmysqlclient.so.18.0.0

MariaDB-shared-10.2.25-1.el7.centos.x86_64

MariaDB-compat-10.2.25-1.el7.centos.x86_64

I am now totally lost. This error looks like some combination of centos7 docker + openssl + mariadb client libs

Hi,

as you can see in attached screenshot the server refuses to accept the client certificate (alert 21).
Can you try to connect using the certificates which are included in the server package?

PEM files on GitHub

Hi.
I just downloaded the certificates from github and renamed it to match my.cnf. I get the same error. I tried both the network interfaces (the docker IP and the wifi card IP).

1. However I'm able to connect using mysql_real_connect() api on the wifi card IP (192.168.0.8), but the API fails to connect using the docker interface IP.
2. If I comment out the certificates in the .cnf file, the connection goes through even using the mysql client

$ mysql -u indimail -p -h 192.168.0.8

Enter password: 

ERROR 2026 (HY000): SSL connection error: tlsv1 alert unknown ca

$ mysql -u indimail -p -h 172.17.0.1

Enter password: 

ERROR 2026 (HY000): SSL connection error: tlsv1 alert unknown ca

$ ls -l /var/indimail/mysqldb/ssl

total 28

-rw-r--r--. 1 mysql mysql 1708 Jun 27 12:25 ca-key.pem

-rw-r--r--. 1 mysql mysql 4321 Jun 27 12:21 ca.pem

-rw-r--r--. 1 mysql mysql 4420 Jun 27 12:25 client-cert.pem

-rw-r--r--. 1 mysql mysql 6099 Jun 27 12:26 client-key.pem

$ egrep "ca|client| /etc/indimail/lindimail.cnf

[client]

ssl-ca=/var/indimail/mysqldb/ssl/ca.pem

ssl-cert=/var/indimail/mysqldb/ssl/client-cert.pem

ssl-key=/var/indimail/mysqldb/ssl/client-key.pem

# Securing the Database with ssl option and certificates

ssl-ca=/var/indimail/mysqldb/ssl/ca.pem

If I comment out the above ssl- lines, I am able to connect

$ mysql --silent -u indimail -p -h 172.17.0.1 --ssl

Enter password: 

MySQL [(none)]> status

--------------

mysql  Ver 15.1 Distrib 10.2.25-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          31

Current database:

Current user:           indimail@172.17.0.2

SSL:                    Cipher in use is ECDHE-RSA-AES256-GCM-SHA384

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server:                 MySQL

Server version:         8.0.16 MySQL Community Server - GPL

Protocol version:       10

Connection:             172.17.0.1 via TCP/IP

Server characterset:    utf8mb4

Db     characterset:    utf8mb4

Client characterset:    utf8mb4

Conn.  characterset:    utf8mb4

TCP port:               3306

Uptime:                 17 min 18 sec

Threads: 2  Questions: 109  Slow queries: 1  Opens: 219  Flush tables: 3  Open tables: 123  Queries per second avg: 0.105

--------------

MySQL [(none)]>

EDIT to the above comment:

1. The issue has nothing to do with docker. I am able to connect to mysql community server using any network interface if I use the APIs. It was a missing call to mysql_ssl_set() that I had missed out when connecting to mysqld using the docker interface.
2. The mariadb mysql client is never able to connect to mysqld community server if the certificates are provided in the .cnf file.
3. Without the certs in the .cnf file, the mariadb client is able to connect if I pass the --ssl command line arg
4. This issue is happening only on the latest CentOS7 Docker image. Details below

   $ docker pull centos

   In the docker I did the following

   # yum update

   # cat > /etc/yum.repos.d/mariadb.repo

   [mariadb]

   name = MariaDB

   baseurl = http://yum.mariadb.org/10.2/centos7-amd64

   gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

   gpgcheck=1

   # yum install MariaDB-client MariaDB-devel MariaDB-common MariaDB-shared Mariadb-compat Mariadb-sever

As of now, my application, which can load the MariaDB or MySQL shared libs using dlopen is working fine under all possible permutation combination if I call mysql_ssl_set(). So this issue is solved as far as I am concerned. But I am curious to find out why the mariadb mysql client is unable to connect.

I will try with a lower version of mariadb client and see if this issue persists.

can you attach also your my.cnf file?

I have attached the cnf file with the name indimail.cnf

Hmm... this could be related to CONC-396.
Can you try to specify the location of configuration file with mysql --defaults-extra-file=/path/indimail.cnf ?

No luck even by specifying --defaults-extra-file. I can confim that the indimail.cnf is being read. The strace output shows this

2032  munmap(0x7f91c009b000, 4096)      = 0

2032  stat("/etc/my.cnf.d/indimail.cnf", {st_mode=S_IFREG|0644, st_size=2263, ...}) = 0

2032  open("/etc/my.cnf.d/indimail.cnf", O_RDONLY|O_CLOEXEC) = 4

2032  fstat(4, {st_mode=S_IFREG|0644, st_size=2263, ...}) = 0

2032  mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f91c009b000

2032  read(4, "[client]\nport      = 3306\nsocket"..., 4096) = 2263

2032  read(4, "", 4096)                 = 0

2032  close(4)                          = 0

2032  munmap(0x7f91c009b000, 4096)      = 0

2032  stat("/etc/my.cnf.d/mysql-clients.cnf", {st_mode=S_IFREG|0644, st_size=232, ...}) = 0

2032  open("/etc/my.cnf.d/mysql-clients.cnf", O_RDONLY|O_CLOEXEC) = 4

2032  fstat(4, {st_mode=S_IFREG|0644, st_size=232, ...}) = 0

2032  mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f91c009b000

2032  read(4, "#\n# These groups are read by Mar"..., 4096) = 232

2032  read(4, "", 4096)                 = 0

2032  close(4)                          = 0

2032  munmap(0x7f91c009b000, 4096)      = 0

Are there any ssl/tls options in mysql-clients.cnf ? If so, previous settings will be overwritten.

No ssl/tls options. The file is installation default

#

# These groups are read by MariaDB command-line tools

# Use it for options that affect only one utility

#

[mysql]

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]

[mysqlimport]

[mysqlshow]

[mysqlslap]

One more discovery. All this time I have been saying that the APIs are working. Just found out that they are working because they are not reading the indimail.cnf file. The moment I include a call to mysql_options(), my applicaitons fail with the same error that is being reported by the mysql client.

Finally found something that makes it work. I used mysql_ssl_rsa_setup binary from mysql-community-server package to generate the certs. If I copy the certs, I am able to connect to any mysql-community-server using the command line mysql client from mariadb.

If I use the command openssl x509 -text -in client-cert.pem to capture the output and do a diff, there is what I observe. It looks like the client certificate generated by mysql_ssl_rsa_setup has x509_v3 extension but the ones generated by hand do not.

3c3

<         Version: 1 (0x0)

---

>         Version: 3 (0x2)

33a34,36

>         X509v3 extensions:

>             X509v3 Basic Constraints: critical

>                 CA:FALSE

Finally some good news.

The certificates also work if I do the opposite. i.e. Copy the certicate generated using openssl command to the datadir of the mysql-community-server

I have managed to fix the issue and also find the situation in which this bug happens.

First, the situation in which this bug happens.

• The first time startup of mysqld (mysql-community-server) always creates the certificate automatically. When this happens, mariadb client setup which has certs listed in my.cnf are not able to connect. They fail with "tlsv1 alert unknown ca"
• If you generate the certificate using mysql_ssl_rsa_setup, the same problem as above happens. All connections from mariadb clients fail with "tlsv1 alert unknown ca"

I have found three ways to make mariadb clients talk to the mysql-community-server, which has secure transport turned on

1. Copy the certificates generated in the datadir of the mysql-community-server to your mariadb client setups. These files are ca.pem, ca-key.pem, client-cert.pem, client-key.pem
2. Generate certs on your mariadb client host and copy those files to the datadir of mysql-community-server. These files are ca.pem, ca-key.pem, client-cert.pem, client-key.pem, server-cert.pem, server-key.pem. I have tested this with the script that I am attaching - mariadb_ssl_rsa_setup. This scripts creates the certs in $HOME/ssl mariadb_ssl_rsa_setup
3. By not specifying ssl-ca, ssl-cert, ssl-key in my.cnf

This seems to be more a problem with oracle's mysql_ssl_rsa_setup. You can close the ticket. I will take this up with Oracle.

FWIW, I did some reverse engineering of mysql_ssl_rsa_setup utility using strace. I am putting down exactly what it does in the form of a shell script.

If I use the same ca.pem an ca-key.pem to generate certicates, I am able to successfully use the client-cert certs/keys with MariaDB client and applications using MariaDB connectors

#!/bin/sh

# create certificates with x509 v3 extension

echo "basicConstraints=CA:TRUE"  > cav3.ext

echo "basicConstraints=CA:FALSE" > certv3.ext

# Create CA

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout ca-key.pem \

    -subj /CN=MySQL_8.0.16_Auto_Generated_CA_Certificate -out ca-req.pem

openssl rsa -in ca-key.pem -out ca-key.pem 

openssl x509 -sha256 -days 3650 -extfile cav3.ext -set_serial 1 \ 

    -req -in ca-req.pem -signkey ca-key.pem -out ca.pem

# Create Server Cert

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem \

    -subj /CN=MySQL_8.0.16_Auto_Generated_Server_Certificate -out server-req.pem

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 2 -req \

    -in server-req.pem -CA ca.pem -CAkey ca-key.pem -out server-cert.pem

# Create Client Cert

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem \

    -subj /CN=MySQL_8.0.16_Auto_Generated_Client_Certificate -out client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 3 -req \

    -in client-req.pem -CA ca.pem -CAkey ca-key.pem -out client-cert.pem

# Verifiy the certificates

openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

# Generate public & private key

openssl genrsa  -out private_key.pem 2048

openssl rsa -in private_key.pem -pubout -out public_key.pem

chmod 600 ca-key.pem

chmod 644 ca.pem

chmod 600 server-key.pem

chmod 644 server-cert.pem

chmod 600 client-key.pem

chmod 644 client-cert.pem

chmod 600 private_key.pem

chmod 644 public_key.pem

/bin/rm -f ca-req.pem server-req.pem client-req.pem cav3.ext certv3.ext