How to connect to MySQL with SSL connection

Question

I have Drupal 7.24 / PHP 5.3.27 and am using the PHP native MySQL drivers. PHPinfo shows that SSL is supported.

I can't seem to get Drupal to connect to MySQL with SSL. Works fine without it. I did find a patch 726192-arbitrary-pdo-options-attributes.patch that looked like it was committed back about 7.17. However I did not see the patch in my 7.24 that I downloaded. So I applied the patch. Still doesn't work. There are a lot of bad examples of configurations in others questioning how to connect. My best guess is this configuration. But again it does not work. I have test these SSL certs and connection Navicat and it does connect fine

Any suggestions on what to try or where to look??

$databases = array (
  'default' => 
   array (
    'default' => 
     array (
       'database' => 'drupaldb',
       'username' => 'drupaluser',
       'password' => 'password',
       'host' => '192.168.10.100',
       'port' => '3306',
       'driver' => 'mysql',
       'prefix' => '',
       'pdo attributes' => array(
           PDO::MYSQL_ATTR_SSL_KEY => '/path/client-key.pem',
           PDO::MYSQL_ATTR_SSL_CERT => '/path/client-cert.pem',
           PDO::MYSQL_ATTR_SSL_CA => '/path/ca-cert.pem',
        ),
     ),
   ),
);

Answer 1

Finally got it figured out. It's "driver options", not "PDO attributes".

  $databases = array (
    'default' => 
     array (
       'default' => 
        array (
          'database' => 'drupaldb',
          'username' => 'drupaluser',
          'password' => 'password',
          'host' => '192.168.10.100',
          'port' => '3306',
          'driver' => 'mysql',
          'prefix' => '',
          'driver options' => array(
              PDO::MYSQL_ATTR_SSL_KEY => '/path/client-key.pem',
              PDO::MYSQL_ATTR_SSL_CERT => '/path/client-cert.pem',
              PDO::MYSQL_ATTR_SSL_CA => '/path/ca-cert.pem',
           ),
       ),
    ),
  );

Answer 2

I did not find a way to setup a new site using SSL. So during the setup phase you cannot use SSL. That means, after turning on SSL you should change your passwords to ensure data safety and hope that no one use the small window during setup and change of password in a malicious way.

After you have your drupal site up and running, update settings.php as follows:

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'drupal7',
      'username' => 'drupaladmin',
      'password' => 'P@ssw0rd',
      'host' => '12.23.34.45',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
      'pdo' => array (
            PDO::MYSQL_ATTR_SSL_KEY => '/etc/pki/tls/private/client-key.pem',
            PDO::MYSQL_ATTR_SSL_CERT => '/etc/pki/tls/certs/client-cert.pem',
            PDO::MYSQL_ATTR_SSL_CA => '/etc/pki/tls/certs/ca-cert.pem',
                     ),
      ),
  ),
);

Adjust the paths and names to your certificates as needed. Restart apache or what ever webserver you are using.

On the MYSQL server use

GRANT USAGE ON database.* TO 'USERNAME'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

to force use of SSL.

If you should need to revoke that, you can use the following command:

GRANT USAGE ON database.* TO 'USERNAME'@'%' REQUIRE NONE;
FLUSH PRIVILEGES;

I hope that helps.