So you have been tasked to write a cron job to warn when your site's SSL certificate(s) will soon expire and send email to the responsible person. An expired SSL certificate is at best embarrassing and at worst can cause significant business disruption. There are various ways to do this task.
Nagios has a plugin to check and warn about imminently expiring certificates.
If you have the openssl tools installed you can do it from the command line as shown in this blog post. You should be able to parse the returned date string with the GNU date command and convert it to a number of seconds since the Unix epoch for comparison with the current date to check if expiry is imminent.
I preferred to use Perl. There is a very useful Perl module Net::SSL::ExpireDate by Masaaki Hirose which provided exactly what I wanted. The example is self-explanatory.
On a Debian system other CPAN modules are required, fortunately all of them already in Debian so you only have install those with apt-get, then build, test and install Net::SSL::ExpireDate. Here are the modules I had to install, however there may be other dependencies that were already installed on my system, so this list may not be exhaustive.
libclass-accessor-perl
libcrypt-openssl-x509-perl
libdatetime-perl
libtimedate-perl
libtime-duration-parse-perl
libuniversal-require-perl
The Perl script I wrote was 13 lines long.
I needed to set up SSL for MySQL on RHEL because the remote backup solution I chose, MySQL-zrm requires secure connections.
Most of the information about creating self-signed certificates and keys is here on this page. You can use the script in example 2, but I discovered a couple of things:
First, the script creates both server and client certs and keys. You only need to do one or the other, not both, to have secure connections. The advantage of client certs and keys is that you can use them in GRANT statements. I chose to have only server certs and keys. So I truncated the script after the commands related to the server.
Secondly, you might want add the option -days N, where N is the number of days of validity, to the openssl req and ca commands, to suit your needs.
Finally, you need to edit the source argument in the cp command to the actual location of the template openssl.cnf on your system. For my RHEL system, it is /etc/pki/tls/openssl.cnf. For RHEL 6, you also need to edit the replace command because the CA directory in openssl.cnf has changed from ./demoCA to /etc/pki/CA.
Put the script in a directory say /etc/mysql, and run it from the directory with ./mkcert.sh. It will create a subdirectory openssl for the results. You will be prompted twice for cert attributes, once for the CA and once for the server cert.
When you have finished, add these lines to /etc/my.cnf in the mysqld section:
ssl-ca=/etc/mysql/openssl/ca-cert.pem
ssl-cert=/etc/mysql/openssl/server-cert.pem
ssl-key=/etc/mysql/openssl/server-key.pem
You should chown, chmod and chcon (SELinux) the contents of /etc/mysql for security. I did:
chown -R mysql:mysql /etc/mysql
chmod -R g-w,o= /etc/mysql
chcon -R -u system_u -r object_r -t mysqld_etc_t /etc/mysql
Then restart the mysql service and look in /var/log/mysqld.log for any errors re the certs. If no errors, you can check if SSL is available with mysql:
mysql> show variables like '%ssl%';
Answer for have_ssl should be YES. If not, check the file paths.
If you need to start all over, it suffices to delete the directory /etc/mysql/openssl.
