Howto connect to MySQL using a client certificate

More and more people are requesting how they could connect to MySQL without using a password but using a SSL certificate. Known as X509.

CA Certificate

A CA certificate is a digital certificate issued by a certificate authority (CA). It’s used by clients to verify the SSL certificates sign by this CA.

Such certificates is usually paid and needs to be manually installed with MySQL Server. But by default, MySQL generates a self-signed certificate and provides its own CA.

For obvious reason, I will use the certificates that have been auto-generated by MySQL on my system. However, for production, I encourage you to have a real CA signed certificate.

The CA certificate is called ca.pem and is located in MySQL’s datadir (/var/lib/mysql/ca.pem on Oracle Linux, RHEL, Fedora, CentOS, …).

In case you don’t know where your ca.pem is located, you can check in the global variables of your MySQL Server:

SQL> select * from performance_schema.global_variables
     where variable_name like 'ssl_ca';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| ssl_ca        | ca.pem         |
+---------------+----------------+

The server and the client must use the same CA.

Client Key

MySQL also generates a client key (client-key.pem) but we will generate one per client.

We need of course to use openSSL to generates and verify our certificates.

We start by creating a client certificate, remove the passphrase and sign it:

$ openssl req -newkey rsa:2048 -days 365 -nodes -keyout user1-key.pem -out user1-req.pem
Ignoring -days without -x509; not generating a certificate
..........+...+..... and plenty others ;-)....++++++...
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:BE
State or Province Name (full name) []:Hainaut
Locality Name (eg, city) [Default City]:Maurage
Organization Name (eg, company) [Default Company Ltd]:MySQL
Organizational Unit Name (eg, section) []:Community
Common Name (eg, your name or your server's hostname) []:user1
Email Address []:user1@oracle.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

You have now to new files:

• user1-key.pem – the user’s private key
• user1-req.pem – the user’s PEM certificate request

And now we need to generate the public key (x509 certificate) that will also use the same CA certificates of MySQL. I will generate a certificate valid for 1 year (365 days):

$ sudo openssl x509 -req -in user1-req.pem -days 365 -CA /var/lib/mysql/ca.pem \
     -CAkey /var/lib/mysql/ca-key.pem -set_serial 01 -out user1-cert.pem
Certificate request self-signature ok
subject=C = BE, ST = Hainaut, L = Maurage, O = MySQL, OU = Community,
        CN = user1, emailAddress = user1@oracle.com

The “subject” output is very important as this is what we will use in MySQL for the user credentials.

Verifying the Certificate

We can now already verify the certificate we generated:

$ openssl verify -CAfile /var/lib/mysql/ca.pem /var/lib/mysql/server-cert.pem \
  user1-cert.pem 
/var/lib/mysql/server-cert.pem: OK
user1-cert.pem: OK

MySQL User Creation

We need to create a MySQL user that will use the certificate. By default, with the loaded password policy, we also need to provide a password:

SQL> CREATE USER user1 IDENTIFIED BY 'Passw0rd!' REQUIRE 
     SUBJECT '/C=BE/ST=Hainaut/L=Maurage/O=MySQL/OU=Community/CN=user1/emailAddress=user1@oracle.com';
Query OK, 0 rows affected (0.0114 sec)

If we don’t want to use a password but only the certificate, it’s possible the remove “identified by ‘Passw0rd!’, but you need to uninstall the component validate_password and re-install it just after.

UNINSTALL COMPONENT 'file://component_validate_password';

Even with the privilege PASSWORDLESS_USER_ADMIN, if the component is installed, the password must comply with the policy.

Connecting

We can now connect using the certificate and the key:

The same certificate and key can be used in MySQL Shell for Visual Studio Code:

Conclusion

It’s possible to use X509 certificates (self-signed or not) to connect to MySQL. With or without a password. This method is working with the old mysql client, with MySQL Shell for classic and X protocol and also with MySQL Shell for Visual Studio Code.

You can find more information related to MySQL and Encrypted Connections in the manual.

Enjoy MySQL !