How to encrypt database connection in Laravel on Heroku with ClearDB without putting SSL certifications or keys in source control

The Problem

We needed to have 100% SSL communication for a Laravel app on Heroku that uses a ClearDB MySQL database service. Laravel uses PDO which requires a physical path to the certs and keys. But we don't want these checked into source control as they contain sensitive information.

The Solution

We download the certification files after deployment from AWS S3 or similar service. To accomplish this we add a composer hook script that will call a custom Laravel artisan command we create.

Steps:

Get a copy of necessary .pem files

You will need to acquire these 3 files:

1. Certificate authority (CA) certificate from database server, named ca-cert.pem
2. Client certificate, named client-cert.pem
3. Client private key, named client-key.pem

You will probably need to remove the password on the private key because of the MySQL configuration used on Heroku. This can be done by running:

 

openssl rsa -in client-key.pem -out client-key-no-password.pem

 

Then delete client-key.pem and rename client-key-no-password.pem to client-key.pem

 

Decide where files should be downloaded

I decided to create a folder under the Laravel storage folder as app/storage/app/certs.

 

Add Laravel custom artisan command

Under the laravel folder, run:

 

php artisan command:make CopyCerts --command=deploy:copy-certs

 

You'll find CopyCerts.php under app/commands/CopyCerts.php. Open this up and edit the fire() function to download the 3 files into the app/storage/app/certs folder.

Then, make the command available by adding it to app/start/artisan.php:

 

Artisan::add(new CopyCerts());

 

Add composer.json post install command

Update the composer.json to add a post-install-cmd script entry to call this new artisan command:

 

"scripts": {
  ...
    "post-install-cmd": [
      ...
      "php artisan deploy:copy-certs"
      ...
    ],
  ...
},

 

Update database.php

Next you will update the 

 

$cert_base = realpath(dirname(__FILE__)."/../storage/app/certs");

 

Since the path needs to be absolute, we can derive it from the database.php file itself. Assuming you have been using the same folder structure, you would put this at the top of your database.php:

$cert_base = realpath(dirname(__FILE__)."/../storage/app/certs");

Then, under your connections, edit your connection to add PDO options to point at the 3 three SSL files. It would looks similar to this:

 

'your_connection' => array(
  'driver'  => 'mysql',
  'host'    => $host,
  'database'  => $database,
  'username'  => $username,
  'password'  => $password,
  'charset'   => 'utf8',
  'collation' => 'utf8_unicode_ci',
  'prefix'  => '',
  'options' => array(
    PDO::MYSQL_ATTR_SSL_KEY => $cert_base . '/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT => $cert_base . '/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA => $cert_base . '/ca-cert.pem'
  ),
),

 

Wrapping up

At this point, you could run php artisan deploy:copy-certs or composer install manually from your local environment and it should connect to the database if all the credentials are correct. If all goes well, check it in, deploy it on heroku, and relish in the satisfaction of having a more secure app!

 

Troubleshooting tips

1. PHP 5.6 currently has a bug with SSL. Try using 5.5 instead if you run into issues. You can define this in your composer.json. More info: https://devcenter.heroku.com/articles/php-support#selecting-a-runtime-php
2. Make sure folder app/storage/app/certs folder has the correct permissions.