Does not support connection to MySQL database which requires SSL

[bshaw]

It seems there's no support to for the worker to connect to a backend MySQL server which requires SSL. ...If I've missed something, please forgive me (and let me know the appropriate config!).

We use a remote database (Azure Database for MySQL), which has an option (default) to require SSL, and we'd like to use it to keep our traffic private. If using the mysql client (or workbench or whatever you use), you would need to pass these options for the client to enforce the connection and verify against the present CA certificate (which matches the server):

--ssl-mode=VERIFY_CA
--ssl-ca=/path/to/ca.crt.pem

In case it's remotely helpful, here's a sample PHP connection string provided by Azure:

$con=mysqli_init(); mysqli_ssl_set($con, NULL, NULL, {ca-cert filename}, NULL, NULL); mysqli_real_connect($con, "{database_ip_or_hostname}", "{your_user}", {your_password}, {your_database}, 3306);

A similar condition would exist for other cloud databases, such as AWS RDS MySQL, or people running their own databases with SSL required.

The UI would need support, too (I haven't made it that far yet, but can raise an issue over there, too, if I discover the same conditions).

[nook24]

Hi @bshaw, i never thought about this :/

Statusengine Worker (and the UI) are using PDO to connect to the database.

Hopefully it works, if you just patch the PDO connect in https://github.com/statusengine/worker/blob/f59e1785db1df6ca1686f2b12d581e108e3c6b18/src/Backends/MySQL/MySQL.php#L102-L110

Like so:

$this->Connection = new \PDO($this->getDsn(), $config['username'], $config['password'], [
    \PDO::ATTR_TIMEOUT        => 1,
    \PDO::MYSQL_ATTR_SSL_KEY  =>'/etc/mysql/ssl/client-key.pem',
    \PDO::MYSQL_ATTR_SSL_CERT =>'/etc/mysql/ssl/client-cert.pem',
    \PDO::MYSQL_ATTR_SSL_CA   =>'/etc/mysql/ssl/ca-cert.pem'
]);

See also: http://php.net/manual/en/ref.pdo-mysql.php#pdo-mysql.constants

I found this solution on a quick google search https://stackoverflow.com/a/9768753, but can't test it right now.

[bshaw]

Seems simple enough. I'll try to do some testing this week if I get around to it. I'll submit a PR if all goes well ;)

[nook24]

@bshaw did this solve your issue?

[bshaw]

I've not had a chance to even test it, unfortunately. For now, we just don't enforce an SSL connection to the DB.

I feel you've sufficiently answered my question... we can always implement the acutal solution later. Depending on how you want to handle the issue (close vs. keep open), I'd really like to find some time to test it out and submit a PR to integrate this as a configurable functionality if you want to keep it open (you can even assign me if you want).

[nook24]

No problem. I will keep this open until you had time to run your tests.

[bshaw]

Awesome, thanks! ...Sounds like I have something to do during my Christmast break.