Configure Mysql to use Lets Encrypt Certificates

You have already deployed Lets Encrypt certificates for your web server and you have a mysql server hosted on the same domain and wish to also leverage this certificate for TLS connections to your mysql instance. Read on.

Set Folder Permissions

Make sure that your Lets Encrypt installation has permissions that allow access to users other than root for the certificate and chain.

• as of this writing (2023-12-21 13:00 UTC), the documentation of Lets Encrypt: https://eff-certbot.readthedocs.io/en/latest/using.html#where-are-my-certificates informs you that you should set the permissions for the live and archive directories in your installation to 755 if you don’t intend to downgrade versions — this should be an easy yes for most people, as if you find yourself in a situation where you intend to downgrade your certbot, then you presumably know what you’re in for.

chmod 0755 /etc/letsencrypt/{live,archive}

Allow Mysql User to Read the privkey.pem

Find out which user your mysql is running as. This can be found in your mysqld.conf or similar, for example:

#...
[mysqld]
#
# * Basic Settings
#
user            = mysql 
#...

On my machine, the configuration indicates that mysql is running as a user named mysql.

Users are typically created together with the same name group. I can use ACLs to grant Mysql group access to the private key generated by Lets Encrypt for my domain with the following command without opening the file up to everyone:

setfacl -m g:mysql:r-x /etc/letsencrypt/live/nucco.org/privkey.pem

Just as a quick explainer, Lets Encrypt will put the currently active version of the certificate and private key etc in the /etc/letsencrypt/live/$domain/ directory, and this naturally will vary by your domain. If this is not the case, you should look at the current Lets Encrypt documentation for where it is placing files as this could change in the future. The goal here is to make sure that Mysql is allowed to read the private key. You don’t want to change the file permissions to be permissive because the private key is a very sensitive file and you don’t want this falling into the wrong hands.

Create the CA File for Lets Encrypt

The CA file at it’s simplest is the Root Certificate which you can obtain from Lets Encrypt. We will create a file containing the Root (self-signed) and the Intermediate (self-signed) certificates for Lets Encrypt which you can download from: https://letsencrypt.org/certificates/ .

Use a text editor of your choice and place the Root Certificate for Lets Encrypt AND the intermediate certicate into a single file named for the purpose of this exercise, ‘ca-cert.pem’, which I have chosen to place in /etc/letsencrypt/. As of now, the current CA files I used are: https://letsencrypt.org/certs/isrgrootx1.pem AND https://letsencrypt.org/certs/isrg-root-x2.pem.

The wget commands below will create this file if you supply the valid URLs to the current CA certs.

wget https://letsencrypt.org/certs/isrgrootx1.pem -O ->> /etc/letsencrypt/ca-cert.pem

wget https://letsencrypt.org/certs/isrg-root-x2.pem -O ->> /etc/letsencrypt/ca-cert.pem

chmod 755 /etc/letsencrypt/ca-cert.pem
 

Ensure that AppArmor is not blocking Mysql

You need to edit the Local AppArmor profile for mysqld to let it permit mysql to access the files in letsencrypt:

nano /etc/apparmor.d/local/usr/.sbin.mysqld

Add the configuration to permit read access to the letsencrypt files

  /etc/letsencrypt/live/nucco.org/* r,
  /etc/letsencrypt/archive/nucco.org/* r,
  /etc/letsentcypt/ca-cert.pem r,

Note that the path above depends on your environment. My example is using my domain. You may wish to for example, have Mysqld have access to all live letsencrypt certs, and in such a case, you would need /etc/letsencrypt/live/** r, and /etc/letsencrypt/archive/** r, instead, which grants access recursively to all files and subdirectories in the live directory. We need access to the archive because ultimately, the files in live are merely symlinks to the files in archive.

After modifying an AppArmor profile, you need to reload it:

apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld 

Configure Mysql to Enable TLS

That is a little outside the scope of this post, but there are plenty of resources out there to guide you, for example: https://dev.mysql.com/doc/refman/8.2/en/using-encrypted-connections.html .

The key things you need to do differently from are:

• ssl-cert should point to the cert.pem file. You cannot use the fullchain.pem because Mysql doesn’t select the correct certificate from a chain as of my current version 8.0.35. Instead, you can create a CA file as described above that contains all the certificates needed to establish a chain up to the trusted root.
• ssl-key should point tothe private key to the privkey.pem
• ssl-ca should point to the /etc/letsencrypt/ca-cert.pem file you generated earlier using the Root certificates for

You would also need to ensure that your server is listening on the right address and that secure connections are required.