MariaDB over SSL not working, "certificate verify failed"

Question

Using this guide I'm attempting to set up MariaDB (mysql) for using SSL between dbserver and appclient.

I created the server and client certificates on the server, per the guide. I then copied the three necessary client files to appclient and set ownership and permissions:

[root@appclient mysql]# ll /etc/pki/tls/certs/
drwxr-xr-x. 2 mysql mysql   88 Feb  9 13:31 mysql

[root@appclient mysql]# ll /etc/pki/tls/certs/mysql/
-rw-------. 1 mysql mysql 1372 Feb  9 13:31 ca-cert.pem
-rw-------. 1 mysql mysql 1230 Feb  9 14:16 client-cert.pem
-rw-------. 1 mysql mysql 1705 Feb  9 14:16 client-key.pem

Here's the full my.cnf on appclient:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

[client]
ssl-ca=/etc/pki/tls/certs/mysql/ca-cert.pem
ssl-cert=/etc/pki/tls/certs/mysql/client-cert.pem
ssl-key=/etc/pki/tls/certs/mysql/client-key.pem

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

Next, I tested that port 3306 is open on dbserver:

[root@appclient mysql]# telnet dbserver 3306
Connected to dbserver.
Escape character is '^]'.
R
5.5.52-MariaDB

Next I checked MariaDB (mysql) ssl variables on dbserver:

MariaDB [(none)]> show variables like '%ssl%';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| have_openssl  | YES                                      |
| have_ssl      | YES                                      |
| ssl_ca        | /etc/pki/tls/certs/mysql/ca-cert.pem     |
| ssl_capath    |                                          |
| ssl_cert      | /etc/pki/tls/certs/mysql/server-cert.pem |
| ssl_cipher    |                                          |
| ssl_key       | /etc/pki/tls/certs/mysql/server-key.pem  |
+---------------+------------------------------------------+

Next I checked MariaDB (mysql) ssl variables on appclient:

MariaDB [(none)]> show variables LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)

That looks like the start/source of the problem.

If I try to connect to dbserver from appclient anyway:

[root@appclient mysql]# mysql -h dbserver -u ssluser -p 
Enter password: 
ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

No bueno.

Checking appclient's certs with openssl...

[root@appclient mysql]# cd /etc/pki/tls/certs/mysql/
[root@appclient mysql]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Error opening certificate file server-cert.pem
139864320337824:error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen('server-cert.pem','r')
139864320337824:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:
unable to load certificate
client-cert.pem: OK

For kicks, I ran the same openssl test on dbserver:

[root@dbserver mysql]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: C = XX, ST = XX, L = CityName, O = MyOrganization, OU = MyGroup, CN = dbserver
error 18 at 0 depth lookup:self signed certificate
OK
client-cert.pem: OK

The tutorial only mentions copying ca-cert.pem, client-cert.pem and client-key.pem to the client, yet the failure above points to a missing server-cert.pem on the client.

Do I need to create the server-*.pem files on the client also? If so, where do these go in the /etc/my.cnf file?

Is that a self-signed certificate? Since openssl tells you that certificate verification failed, that indicates that authority you used to sign the certificate should be added to the OS you're running this on. I don't know if there's a flag that disables verifying the cert though, maybe some googling in that direction could help? — N.B., Feb 09 '17 at 20:13
It is self signed. All of the certs were generated on dbserver per documentation. Note that openssl failed the test due to missing `server-cert.pem`, which isn't described or addressed in the tutorial. That's the part that I'm stuck on. — a coder, Feb 09 '17 at 20:20
Made a little progress by `cat server-cert.pem client-cert.pem > ca.pem` then updating the appclient's my.cnf to use `ssl-ca=ca.pem`. Instead of the SSL error, I'm getting an authentication error. — a coder, Feb 09 '17 at 20:41
Got it all sorted. Wrote up a complete MariaDB/SSL how-to and posted as an answer below. — a coder, Feb 09 '17 at 22:53
Well, it's rare to see such an elaborate question and even more detailed answer. Excellent job, +1 from me! — N.B., Feb 09 '17 at 22:59

a coder · Accepted Answer · 2017-02-10T00:37:37.883

The missing ingredient from several of the MySQL/MariaDB SSL setup guides is making sure the ssl-ca certificate file contains both server and client ca's.

Here's a step by step guide which worked for me:

This answer assumes two servers:

dbserver (where our database lives)
appclient (where our applications live)

FWIW, both servers are SELinux enforcing.

First, log on to dbserver

Create a temporary directory for creating the certificates.

mkdir /root/certs/mysql/ && cd /root/certs/mysql/

Create the server certificates

openssl genrsa 2048 > ca-key.pem
openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem > server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -sha1 -req -in server-req.pem -days 730  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Move server certificates to /etc/pki/tls/certs/mysql/ Directory path assumes CentOS or RHEL (adjust as needed for other distros):

mkdir /etc/pki/tls/certs/mysql/
cp /root/certs/mysql/* /etc/pki/tls/certs/mysql/

Be sure to set permissions on the folder and files. mysql needs full ownership and access.

chown -R mysql:mysql /etc/pki/tls/certs/mysql

Now configure MySQL/MariaDB

# vi /etc/my.cnf
# i
[mysqld]
bind-address=*
ssl-ca=/etc/pki/tls/certs/ca-cert.pem
ssl-cert=/etc/pki/tls/certs/server-cert.pem
ssl-key=/etc/pki/tls/certs/server-key.pem
# :wq

Then

systemctl restart mariadb

Don't forget to open your firewall to allow connections from appclient (using IP 1.2.3.4)

firewall-cmd --zone=drop --permanent --add-rich-rule 'rule family="ipv4" source address="1.2.3.4" service name="mysql" accept'
# I force everything to the drop zone.  Season the above command to taste.

Now restart firewalld

service firewalld restart

Next, log in to dbserver's mysql server:

mysql -uroot -p

Issue the following to create a user for the client. note REQUIRE SSL in GRANT statement.

GRANT ALL PRIVILEGES ON *.* TO ‘iamsecure’@’appclient’ IDENTIFIED BY ‘dingdingding’ REQUIRE SSL;
FLUSH PRIVILEGES; 
# quit mysql

You should still be in /root/certs/mysql from the first step. If not, cd back to it for one of the commands below.

Create the client certificates

openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem > client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Note: I used the same common name for both server and client certificates. YMMV.

Be sure you're still /root/certs/mysql/ for this next command

Combine server and client CA certificate into a single file:

cat server-cert.pem client-cert.pem > ca.pem

Make sure you see two certificates:

cat ca.pem

END OF SERVER SIDE WORK FOR NOW.

Open another terminal and

ssh appclient

As before, create a permanent home for the client certificates

mkdir /etc/pki/tls/certs/mysql/

Now, place the client certificates (created on dbserver) on appclient. You can either scp them over, or just copy and paste the files one by one.

scp dbserver
# copy files from dbserver to appclient
# exit scp

Again, be sure to set permissions on the folder and files. mysql needs full ownership and access.

chown -R mysql:mysql /etc/pki/tls/certs/mysql

You should have three files, each owned by user mysql:

/etc/pki/tls/certs/mysql/ca.pem
/etc/pki/tls/certs/mysql/client-cert.pem
/etc/pki/tls/certs/mysql/client-key.pem

Now edit appclient's MariaDB/MySQL config in the [client] section.

vi /etc/my.cnf
# i
[client]
ssl-ca=/etc/pki/tls/certs/mysql/ca.pem
ssl-cert=/etc/pki/tls/certs/mysql/client-cert.pem
ssl-key=/etc/pki/tls/certs/mysql/client-key.pem
# :wq

Restart appclient's mariadb service:

systemctl restart mariadb

still on the client here

This should return: ssl TRUE

mysql --ssl --help

Now, log in to appclient's mysql instance

mysql -uroot -p

Should see YES to both variables below

show variables LIKE '%ssl';
    have_openssl    YES
    have_ssl              YES

Initially I saw

 have_openssl NO

A quick look into mariadb.log revealed:

SSL error: Unable to get certificate from '/etc/pki/tls/certs/mysql/client-cert.pem'

The problem was that root owned client-cert.pem and the containing folder. The solution was to set ownership of /etc/pki/tls/certs/mysql/ to mysql.

chown -R mysql:mysql /etc/pki/tls/certs/mysql

Restart mariadb if needed from the step immediately above

NOW WE ARE READY TO TEST THE SECURE CONNECTION

We're still on appclient here

Attempt to connect to dbserver's mysql instance using the account created above.

mysql -h dbserver -u iamsecure -p
# enter password dingdingding (hopefully you changed that to something else)

With a little luck you should be logged in without error.

To confirm you are connected with SSL enabled, issue the following command from the MariaDB/MySQL prompt:

\s

That's a backslash s, aka status

That will show the status of your connection, which should look something like this:

Connection id:      4
Current database:   
Current user:       iamsecure@appclient
SSL:            Cipher in use is DHE-RSA-AES256-GCM-SHA384
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server:         MariaDB
Server version:     5.X.X-MariaDB MariaDB Server
Protocol version:   10
Connection:     dbserver via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         42 min 13 sec

If you get permission denied errors on your connection attempt, check your GRANT statement above to make sure there aren't any stray characters or ' marks.

If you have SSL errors, go back through this guide to make sure the steps are orderly.

This worked on RHEL7 and will likely work on CentOS7, too. Cannot confirm whether these exact steps will work elsewhere.

Hope this saves someone else a little time and aggravation.

The thing that continually puzzles me about these guides is why the client needs the server's secret key, or any certificate. Isn't the whole point of TLS certificates to prove server identity? — Michael Cordingley, Apr 17 '18 at 18:27

score 5 · Answer 2 · answered Apr 25 '17 at 09:43

5

According to mentioned guide, you have to make sure that "Common name" is different for all three certificates.

I was getting the same error, because I had used same CN for all three certificates. After re-generating certs with different CNs, the error is gone.

answered Apr 25 '17 at 09:43

Hennadii Mashentsev

176
2
5

MariaDB on CentOS 7: can confirm this. After using the FQ host name as CN it failed, but when using Vivek's clearly marked suggestions it worked just fine, once session started it correctly reports using a SSL cipher (status query). – David Ramirez Feb 06 '18 at 18:43