Zero-Configuration SSL CA root bundles in Doctrine DBAL with Symfony

Today I found myself deploying a Symfony project to an Azure environment that requires SSL connections to MySQL. I figured this out the hard way when I started getting these errors:

In AbstractMySQLDriver.php line 106:
                                                                                                                             
  An exception occurred in driver: SQLSTATE[HY000] [9002] SSL connection is required. Please specify SSL options and retry.  
                                                                                                                             

In PDOConnection.php line 31:
                                                                                            
  SQLSTATE[HY000] [9002] SSL connection is required. Please specify SSL options and retry.  
                                                                                            

In PDOConnection.php line 27:
                                                                                            
  SQLSTATE[HY000] [9002] SSL connection is required. Please specify SSL options and retry.  

Although StackOverflow had some potential solutions, they were all a bit messy. At a minimum, each solution required you to somehow know and provide the path to the system’s CA root bundle in your project - either by hard-coding it (bad idea - it can be different per OS) or by making it a configurable parameter (requires developers to know where that file lives).

A Simple Solution

Thanks to the composer/ca-bundle package, we can fully automate the process of finding that CA root bundle. It intelligently scans the host system for that bundle, or falls back to a bundle included within the package if none can be found.

Install that package by running:

composer install composer/ca-bundle

Once installed, create a new compiler pass in Symfony which will scan through your Doctrine DBAL connections and automagically configure the PDO::MYSQL_ATTR_SSL_CA option for you:

<?php

namespace App\DepenedencyInjection\Compiler;

use Composer\CaBundle\CaBundle;
use Symfony\Component\DependencyInjection\Compiler\CompilerPassInterface;
use Symfony\Component\DependencyInjection\ContainerBuilder;

/**
 * Registers a CA root bundle with the PDO MySQL driver used by Doctrine DBAL
 *
 * This allows Doctrine to connect to MySQL instances that force SSL encryption, such as Azure's.
 */
final class RegisterCABundleWithPDOMysqlDriverPass implements CompilerPassInterface
{
    /**
     * @inheritDoc
     */
    public function process(ContainerBuilder $container)
    {
        $caPathOrFile = CaBundle::getSystemCaRootBundlePath();

        foreach ($container->getParameter('doctrine.connections') ?? [] as $connectionName) {
            $definition = $container->getDefinition($connectionName);
            $options = $definition->getArgument(0) ?? [];
            $options['driverOptions'][\PDO::MYSQL_ATTR_SSL_CA] = $caPathOrFile;
            $definition->setArgument(0, $options);
        }
    }
}

Lastly, register that compiler pass in your src/Kernel.php file like so:

    // ...

    /**
     * {@inheritDoc}
     */
    protected function build(ContainerBuilder $container)
    {
        $container->addCompilerPass(new RegisterCABundleWithPDOMysqlDriverPass());
    }

    // ...

And you should be good! Don’t forget to clear your cache so Symfony recompiles the container.