SSL connection to MySQL

[Melakh]

Could someone point me towards how I can configure this on the ProcessWire end, every attempt I've made so far at enforcing SSL connections from PW to the database have failed.

[kongondo]

Hi @Melakh,

Welcome to the forums.

Sorry, not an answer to your question. However, since this question has been asked two or three times before, but without a solution, I am going to be naughty  and tag probably two of the most knowledgeable people in these forums with respect to databases.

Sorry both! @BitPoet, @LostKobrakai, for tagging you like this. Could you please weigh in on this issue if you can? Many thanks. ? 

Here are two previous similar questions:

 

[BitPoet]

Using SSL should be quite straight forward, assuming that everything is configured correctly on the server side. The enforcing happens on the server the moment you issue an

ALTER USER your-processwire-user@your-mysql-server REQUIRE SSL

The moment you do that, you'll get a database error when you access your site. To enable PHP to talk over an encrypted MySQL connection, you now need to point it to the MySQL server's CA certificate. Copy that to a location where the web server can read it and add an entry in site/config.php (adapt the path to match your ca cert location):

$config->dbOptions = array(
	\PDO::MYSQL_ATTR_SSL_CA => 'C:/temp/mysql-ca.pem'
);

There may be scenarios where the name you use to access the server doesn't match the name in the certificate and you get the error "SQLSTATE[HY000] [2002]". The same error occurs when you use a self-signed certificate in the server (that's the case when you leave things to default after installing MySQL on most distributions). In that case, you need at least one of the following PHP versions:

• PHP 7.2, 7.3, 7.4 or 8 all versions
• PHP 7.1 >= 7.1.4
• PHP 7.0 >= 7.0.18

The reason is that earlier versions of the MySQL PDO module didn't have the flag to disable certificate verification. You need to expand your entry in site/config.php:

$config->dbOptions = array(
	\PDO::MYSQL_ATTR_SSL_CA => 'C:/temp/mysql-ca.pem',
	\PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
);

Most (hopefully all) PW modules should be using the PDO interface by now, but you may stumble upon one that still makes use of the old mysqli wrapper. Those won't work with an SSL connection.

[Melakh]

Thank you very much for the comprehensive reply, I’ll try this out tomorrow 

[Melakh]

This mostly worked, but it's left me with an error in /wire/core/Database.php - pretty much as you said in your last line!

Exception: DB connect error 9002 - SSL connection is required. Please specify SSL options and retry. (in D:\home\site\wwwroot\wire\core\Database.php line 79)

Can this be fed the same SSL override config setting?

 

[BitPoet]

26 minutes ago, Melakh said:

This mostly worked, but it's left me with an error in /wire/core/Database.php - pretty much as you said in your last line!

Exception: DB connect error 9002 - SSL connection is required. Please specify SSL options and retry. (in D:\home\site\wwwroot\wire\core\Database.php line 79)

Can this be fed the same SSL override config setting?

Unfortunately not. The mysqli wrapper uses the object oriented interface, so there isn't even an easy point to add that part in the core library between instantiating the module and invoking mysqli_real_connect(). Your best bet is to look at the stack trace to see which module causes the dump (enable $config->debug in site/config.php) and either find a replacement or post an issue in the module's git repo to get it converted to PDO.

[Melakh]

Will look into it, thanks for your help @BitPoet, really appreciated.