Description:
When trying to configure mysqld to handle a pki:
+---------+
| Root CA |
+---------+
|
/-------+---------\
| |
+----------+ +-----------+
| MySQL CA | | Client CA |
+----------+ +-----------+
| |
+-------------+ +-------------+
| Server | | Client |
| certificate | | certificate |
+-------------+ +-------------+
mysql-client fails to authenticate server certificate.
How to repeat:
Create a sample pki:
```
mkdir mysql
touch mysql/.dir
mkdir ca
touch ca/.cadir
mkdir ca/mysql.certs
touch ca/mysql.certs/.certdir
touch ca/mysql.index.txt
touch ca/mysql.index.txt.attr
echo '00' > ca/mysql.serial
openssl genrsa -out ca/mysql.key 2048
openssl genrsa -out ca/root.key 2048
openssl req -new -x509 '-sha256' -key ca/root.key -out ca/root.crt -days 30 -subj "/O=ACME/OU=DB/CN=Root CA"
mkdir ca/root.certs
touch ca/root.certs/.certdir
touch ca/root.index.txt
touch ca/root.index.txt.attr
echo '00' > ca/root.serial
openssl req -new '-sha256' -key ca/mysql.key -out ca/mysql.csr -subj "/O=ACME/OU=DB/CN=Root CA - Mysql"
echo '[ ca ]' > ca/root.cfg
echo 'default_ca = CA_default' >> ca/root.cfg
echo '[ CA_default ]' >> ca/root.cfg
echo 'dir = /home/baloo/work/dev/distdns/x509/temp/ca' >> ca/root.cfg
echo 'certs = $dir/certs' >> ca/root.cfg
echo 'database = $dir/root.index.txt' >> ca/root.cfg
echo 'serial = $dir/root.serial' >> ca/root.cfg
echo 'policy= policy_match' >> ca/root.cfg
echo '[ policy_match ]' >> ca/root.cfg
echo 'organizationName = match' >> ca/root.cfg
echo 'organizationalUnitName = optional' >> ca/root.cfg
echo 'commonName = supplied' >> ca/root.cfg
echo 'emailAddress = optional' >> ca/root.cfg
echo '[ v3_ca ]' >> ca/root.cfg
echo 'subjectKeyIdentifier=hash' >> ca/root.cfg
echo 'authorityKeyIdentifier=keyid:always,issuer' >> ca/root.cfg
echo 'basicConstraints = CA:true' >> ca/root.cfg
openssl ca -batch -days 30 -notext -md sha256 -in ca/mysql.csr -out ca/mysql.crt -keyfile ca/root.key -cert ca/root.crt -outdir ca/root.certs/ -config ca/root.cfg -extensions v3_ca
openssl genrsa -out mysql/server.key 4096
openssl req -new '-sha256' -key mysql/server.key -out mysql/server.csr -subj "/CN=server"
echo '[ ca ]' > ca/mysql.cfg
echo 'default_ca = CA_default' >> ca/mysql.cfg
echo '[ CA_default ]' >> ca/mysql.cfg
echo 'dir = /home/baloo/work/dev/distdns/x509/temp/ca' >> ca/mysql.cfg
echo 'certs = $dir/mysql.certs' >> ca/mysql.cfg
echo 'database = $dir/mysql.index.txt' >> ca/mysql.cfg
echo 'serial = $dir/mysql.serial' >> ca/mysql.cfg
echo 'policy= policy_match' >> ca/mysql.cfg
echo '[ policy_match ]' >> ca/mysql.cfg
echo 'commonName = supplied' >> ca/mysql.cfg
openssl ca -batch -days 90 -notext -md sha256 -in mysql/server.csr -out mysql/server.crt -keyfile ca/mysql.key -cert ca/mysql.crt -outdir ca/mysql.certs/ -config ca/mysql.cfg
cat mysql/server.crt ca/mysql.crt > mysql/server.cachain
mkdir clients
touch clients/.dir
mkdir ca/client.certs
touch ca/client.certs/.certdir
touch ca/client.index.txt
touch ca/client.index.txt.attr
echo '00' > ca/client.serial
openssl genrsa -out ca/client.key 2048
openssl req -new '-sha256' -key ca/client.key -out ca/client.csr -subj "/O=ACME/OU=DB/CN=Root CA - Client"
openssl ca -batch -days 30 -notext -md sha256 -in ca/client.csr -out ca/client.crt -keyfile ca/root.key -cert ca/root.crt -outdir ca/root.certs/ -config ca/root.cfg -extensions v3_ca
openssl genrsa -out clients/client.key 2048
openssl req -new '-sha256' -key clients/client.key -out clients/client.csr -subj "/CN=client"
echo '[ ca ]' > ca/client.cfg
echo 'default_ca = CA_default' >> ca/client.cfg
echo '[ CA_default ]' >> ca/client.cfg
echo 'dir = /home/baloo/work/dev/distdns/x509/temp/ca' >> ca/client.cfg
echo 'certs = $dir/client.certs' >> ca/client.cfg
echo 'database = $dir/client.index.txt' >> ca/client.cfg
echo 'serial = $dir/client.serial' >> ca/client.cfg
echo 'policy= policy_match' >> ca/client.cfg
echo '[ policy_match ]' >> ca/client.cfg
echo 'commonName = supplied' >> ca/client.cfg
openssl ca -batch -days 30 -notext -md sha256 -in clients/client.csr -out clients/client.crt -keyfile ca/client.key -cert ca/client.crt -outdir ca/client.certs/ -config ca/client.cfg
cat clients/client.crt ca/client.crt > clients/client.cachain
cat ca/client.crt ca/root.crt > ca/client.cachain
```
Resulting files you will need are:
- ca/root.crt
- ca/client.cachain
- clients/client.cachain
- clients/client.key
- mysql/server.cachain
- mysql/server.key
relevant configuration:
```
[mysqld]
ssl
ssl_ca = ca/client.cachain
ssl_cert = mysql/server.cachain
ssl_key = mysql/server.key
[mysql]
ssl
ssl-cert=clients/client.cachain
ssl-key=clients/client.key
ssl-ca=ca/root.crt
ssl-verify-server-cert=1
```
mysql --defaults-extra-file=/tmp/mysql -e 'show status like "Ssl_version"' -vvv
Expected result:
```
--------------
show status like "Ssl_version"
--------------
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Ssl_version | TLSv1.2 |
+---------------+---------+
1 row in set (0.01 sec)
Bye
```
Actual result:
```
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)
```
Suggested fix:
See attached patch
Description: When trying to configure mysqld to handle a pki: +---------+ | Root CA | +---------+ | /-------+---------\ | | +----------+ +-----------+ | MySQL CA | | Client CA | +----------+ +-----------+ | | +-------------+ +-------------+ | Server | | Client | | certificate | | certificate | +-------------+ +-------------+ mysql-client fails to authenticate server certificate. How to repeat: Create a sample pki: ``` mkdir mysql touch mysql/.dir mkdir ca touch ca/.cadir mkdir ca/mysql.certs touch ca/mysql.certs/.certdir touch ca/mysql.index.txt touch ca/mysql.index.txt.attr echo '00' > ca/mysql.serial openssl genrsa -out ca/mysql.key 2048 openssl genrsa -out ca/root.key 2048 openssl req -new -x509 '-sha256' -key ca/root.key -out ca/root.crt -days 30 -subj "/O=ACME/OU=DB/CN=Root CA" mkdir ca/root.certs touch ca/root.certs/.certdir touch ca/root.index.txt touch ca/root.index.txt.attr echo '00' > ca/root.serial openssl req -new '-sha256' -key ca/mysql.key -out ca/mysql.csr -subj "/O=ACME/OU=DB/CN=Root CA - Mysql" echo '[ ca ]' > ca/root.cfg echo 'default_ca = CA_default' >> ca/root.cfg echo '[ CA_default ]' >> ca/root.cfg echo 'dir = /home/baloo/work/dev/distdns/x509/temp/ca' >> ca/root.cfg echo 'certs = $dir/certs' >> ca/root.cfg echo 'database = $dir/root.index.txt' >> ca/root.cfg echo 'serial = $dir/root.serial' >> ca/root.cfg echo 'policy= policy_match' >> ca/root.cfg echo '[ policy_match ]' >> ca/root.cfg echo 'organizationName = match' >> ca/root.cfg echo 'organizationalUnitName = optional' >> ca/root.cfg echo 'commonName = supplied' >> ca/root.cfg echo 'emailAddress = optional' >> ca/root.cfg echo '[ v3_ca ]' >> ca/root.cfg echo 'subjectKeyIdentifier=hash' >> ca/root.cfg echo 'authorityKeyIdentifier=keyid:always,issuer' >> ca/root.cfg echo 'basicConstraints = CA:true' >> ca/root.cfg openssl ca -batch -days 30 -notext -md sha256 -in ca/mysql.csr -out ca/mysql.crt -keyfile ca/root.key -cert ca/root.crt -outdir ca/root.certs/ -config ca/root.cfg -extensions v3_ca openssl genrsa -out mysql/server.key 4096 openssl req -new '-sha256' -key mysql/server.key -out mysql/server.csr -subj "/CN=server" echo '[ ca ]' > ca/mysql.cfg echo 'default_ca = CA_default' >> ca/mysql.cfg echo '[ CA_default ]' >> ca/mysql.cfg echo 'dir = /home/baloo/work/dev/distdns/x509/temp/ca' >> ca/mysql.cfg echo 'certs = $dir/mysql.certs' >> ca/mysql.cfg echo 'database = $dir/mysql.index.txt' >> ca/mysql.cfg echo 'serial = $dir/mysql.serial' >> ca/mysql.cfg echo 'policy= policy_match' >> ca/mysql.cfg echo '[ policy_match ]' >> ca/mysql.cfg echo 'commonName = supplied' >> ca/mysql.cfg openssl ca -batch -days 90 -notext -md sha256 -in mysql/server.csr -out mysql/server.crt -keyfile ca/mysql.key -cert ca/mysql.crt -outdir ca/mysql.certs/ -config ca/mysql.cfg cat mysql/server.crt ca/mysql.crt > mysql/server.cachain mkdir clients touch clients/.dir mkdir ca/client.certs touch ca/client.certs/.certdir touch ca/client.index.txt touch ca/client.index.txt.attr echo '00' > ca/client.serial openssl genrsa -out ca/client.key 2048 openssl req -new '-sha256' -key ca/client.key -out ca/client.csr -subj "/O=ACME/OU=DB/CN=Root CA - Client" openssl ca -batch -days 30 -notext -md sha256 -in ca/client.csr -out ca/client.crt -keyfile ca/root.key -cert ca/root.crt -outdir ca/root.certs/ -config ca/root.cfg -extensions v3_ca openssl genrsa -out clients/client.key 2048 openssl req -new '-sha256' -key clients/client.key -out clients/client.csr -subj "/CN=client" echo '[ ca ]' > ca/client.cfg echo 'default_ca = CA_default' >> ca/client.cfg echo '[ CA_default ]' >> ca/client.cfg echo 'dir = /home/baloo/work/dev/distdns/x509/temp/ca' >> ca/client.cfg echo 'certs = $dir/client.certs' >> ca/client.cfg echo 'database = $dir/client.index.txt' >> ca/client.cfg echo 'serial = $dir/client.serial' >> ca/client.cfg echo 'policy= policy_match' >> ca/client.cfg echo '[ policy_match ]' >> ca/client.cfg echo 'commonName = supplied' >> ca/client.cfg openssl ca -batch -days 30 -notext -md sha256 -in clients/client.csr -out clients/client.crt -keyfile ca/client.key -cert ca/client.crt -outdir ca/client.certs/ -config ca/client.cfg cat clients/client.crt ca/client.crt > clients/client.cachain cat ca/client.crt ca/root.crt > ca/client.cachain ``` Resulting files you will need are: - ca/root.crt - ca/client.cachain - clients/client.cachain - clients/client.key - mysql/server.cachain - mysql/server.key relevant configuration: ``` [mysqld] ssl ssl_ca = ca/client.cachain ssl_cert = mysql/server.cachain ssl_key = mysql/server.key [mysql] ssl ssl-cert=clients/client.cachain ssl-key=clients/client.key ssl-ca=ca/root.crt ssl-verify-server-cert=1 ``` mysql --defaults-extra-file=/tmp/mysql -e 'show status like "Ssl_version"' -vvv Expected result: ``` -------------- show status like "Ssl_version" -------------- +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Ssl_version | TLSv1.2 | +---------------+---------+ 1 row in set (0.01 sec) Bye ``` Actual result: ``` ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1) ``` Suggested fix: See attached patch