The Puppet Labs Issue Tracker has Moved: https://tickets.puppetlabs.com

MySQL SSL Using Puppet Certs

CAUTION: This is a partial recipe, intended to be added to your full MySQL recipe. Only the SSL relevant sections are included.

Global Settings

In using the Puppet certs throughout, you probably want to create the following block in your site.pp for easy reference:

$cacert = "/var/lib/puppet/ssl/certs/ca.pem"
$signedcert = "/var/lib/puppet/ssl/certs/${fqdn}.pem"
$certkey = "/var/lib/puppet/ssl/private_keys/${fqdn}.pem"

MySQL Configuration

Most Debian based systems now include the following line in the my.cnf:

!includedir /etc/mysql/conf.d/

This recipe assumes that line is present. If not, you will want to use the Recipes/Simple Text custom type to insure that line is present.

To support SSL, the following lines should be added to your init.pp for MySQL:

$configdir = "/etc/mysql/conf.d"
$ssldir = "/etc/mysql/ssl"
$ssldirca = "$ssldir/ca.pem"
$ssldircert = "$ssldir/${fqdn}.pem"
$ssldirkey = "$ssldir/key-${fqdn}.pem"

file { 
    "/etc/mysql":
        ensure => directory,
        owner => mysql, group => mysql, mode => 644;
    $configdir:
        ensure => directory,
        owner => mysql, group => mysql, mode => 644,
        require => Package["mysql-server"];
    $ssldir:
        ensure => directory,
        owner => mysql, group => mysql, mode => 600;
    $ssldirca:
        ensure => present,
        source => $cacert,
        owner => mysql, group => mysql, mode => 600;
    $ssldircert:
        ensure => present,
        source => $signedcert,
        owner => mysql, group => mysql, mode => 600;
    $ssldirkey:
        ensure => present,
        source => $certkey,
        owner => mysql, group => mysql, mode => 600;
}

This copies the Puppet certs to the /etc/mysql/ssl directory, and makes them read-only by MySQL. MySQL, when running, cannot access /var/lib/puppet/ssl/private_keys as user mysql, so we need alternate versions.

ssl.conf SSL Configuration

The following template can create /etc/mysql/conf.d/ssl.conf, which provides the mysql command line the SSL parameters to connect remotely to the system, and the MySQL daemon accept these connections.

[mysqld]
ssl-ca=<%= ssldirca %>
ssl-cert=<%= ssldircert %>
ssl-key=<%= ssldirkey %>

[client]
ssl-ca=<%= ssldirca %>
ssl-cert=<%= ssldircert %>
ssl-key=<%= ssldirkey %>

Granting SSL Permission on the Master

On the master, it’s time to create a user for remote access. For the following examples, we will assume the Puppet Master is running on master.example.com and the client is client.example.com.

To grant the permissions, we need know the details of the Puppet certificates. Let’s assume you want to grant a client permissions to replicate with the master. On the Puppet Master, type in:

puppetca --print client.example.com

Here’s an example of what you might see:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 11 (0xb)
        Signature Algorithm: sha1WithRSAEncryption
        Issuer: CN=master.example.com
        Validity
            Not Before: Nov  3 01:37:21 2008 GMT
            Not After : Nov  2 01:37:21 2013 GMT
        Subject: CN=client.example.com

On the master, we need to grant permissions to the client to replicate with the master datebase (in this example):

grant select, replication slave on *.* 
    to 'MYREMOTEUSER'@'MYREMOTEHOST' 
    identified by 'MYREMOTEPASS' 
    require issuer '/CN=master.example.com' subject '/CN=client.example.com';

If you want looser permissions, but still restrict to just clients of your installation, you can:

grant select, replication slave on *.* 
    to 'MYREMOTEUSER'@'MYREMOTEHOST' 
    identified by 'MYREMOTEPASS' 
    require issuer '/CN=master.example.com';
flush privileges;

More details here on the syntax for the GRANT command.

Testing Permission

To test your permissions, try executing:

mysql --ssl -h MYREMOTEHOST -u MYREMOTEUSER -p

If all goes well, you should get a standard mysql> prompt.

Configurating the Client for Replication

Assuming you have the master set up for replication, it’s time to configure the slave to access the host via SSL. Here’s the command to run on the client. We are assuming you understand setting up replication.

change master to 
    master_host="MYREMOTEHOST", master_user="MYREMOTEUSER", master_password="MYREMOTEPASS", master_port=3306, 
    master_log_file="mysql-bin.000###", master_log_pos=#####, 
    master_ssl=1, 
    master_ssl_ca="/etc/mysql/ssl/ca.pem", 
    master_ssl_cert="/etc/mysql/ssl/master.example.com.pem", 
    master_ssl_key="/etc/mysql/ssl/key-master.example.com.pem";

by drmikecrowe (ping me on IRC with questions)