Connecting to MySQL over SSL (external database)

[frostbitten]

Hey all! Long time since I've been in this kitchen. It's looking fresh. Just set up the latest release (after utilizing my v3 setup for the longest, so no v4 experience 'till now) and ran into a little issue with connecting to my database.

I use an external database configured to only allow ssl connections. (I will refer to the userfrosting installation as the "client" that connects to the database.) To accomplish this connection with Capsule I'm used to adding an extra options parameter to the $config passed to $capsule->addConnection($config). A full config (with faux credentials) might look like:

[
    'driver' => 'mysql',
    'host' => 'db.website.com',
    'database' => 'datas',
    'username' => 'notrootipromise',
    'password' => 'supersecret', 
    'charset' => 'utf8',
    'collation' => 'utf8_general_ci',
    'prefix' => 'myapp_',
    'options'   => array(
        \PDO::MYSQL_ATTR_SSL_KEY    => 'C:/ssl_certs/project1/client-key.pem',
        \PDO::MYSQL_ATTR_SSL_CERT    => 'C:/ssl_certs/project1/client-cert.pem',
        \PDO::MYSQL_ATTR_SSL_CA    => 'C:/ssl_certs/project1/ca.pem',
    )
]

During setup I was not able to provide the necessary options.

As a temporary solution (not recommended) I hardcoded the options param into SetupDbCommand and was able to finish baking my install successfully. This hacky edit didn't translate into the config file so I again manually added these lines. With that, the homepage and dashboard all load successfully. Given this was just to ensure I could connect and not a real solution I again recommend not doing this.

I've only recently started using this external DB and SSL for it so I know I may be going at this solution from the wrong direction. I had hoped there might be a setting on the client's system that would take care of this. I set the appropriate options in the [client] block of its mysql config. That did make the command line interface for mysql work just fine, but it didn't help PHP.

So, if there's no way to set up the client's system to automatically handle ssl connections to mysql, we may want to include an extra option during the installation/baking process.

[amosfolz]

'options' => array( \PDO::MYSQL_ATTR_SSL_KEY => 'C:/ssl_certs/project1/client-key.pem', \PDO::MYSQL_ATTR_SSL_CERT => 'C:/ssl_certs/project1/client-cert.pem', \PDO::MYSQL_ATTR_SSL_CA => 'C:/ssl_certs/project1/ca.pem', )

Just to clarify, adding the options array into the config file solved the problem?

If that worked, I think it may be better to just add some information to the docs rather than trying to add this into the bakery.