Use PHP to connect to MySQL on a remote server using SSL with a private CA’s certificate

PHP files on my server cannot connect to MySQL on a remote server using SSL with a private CA’s certificate.

Both servers are using:

Ubuntu server 22.04
MySQL 8.0
PHP 8.1

I have a php file on server1 which is trying to connect to MySQL on server2 using PDO:

$pdo = new PDO('mysql:host=<server2_ip>;dbname=<dbname>', '<user>', ‘<password>,
      array(
       PDO::MYSQL_ATTR_SSL_KEY    =>'<path>/client-key.pem',
       PDO::MYSQL_ATTR_SSL_CERT=>'<path>/client-cert.pem',
       PDO::MYSQL_ATTR_SSL_CA    =>'<path>/ca.pem'
       )
    );  
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec('SET NAMES "utf8"');

A private SSL certificate was generated on server2 and shared with server1 using the method outlined in https://www.digitalocean.com/community/tutorials/how-to-configure-ssl-tls-for-mysql-on-ubuntu-18-04.

I can access the database on server2 from the command line on server1 using:

mysql -u <user> -h <server2_ip> -p

But when I try to connect to server2 using the php file on server1 I get the PDOExeption:

failed loading cafile stream

I searched for similar issues and I thought it might be caused by my privately generated certificates not being recognised by PHP. I tried manually adding my ca.pem file to /etc/ssl/certs using the method outlined in How do I add a Certificate Authority to PHP so the file() function trusts certificates signed by it? and updating

PDO::MYSQL_ATTR_SSL_CA =>'<path>/ca.pem'

PDO::MYSQL_ATTR_SSL_CA =>'/etc/ssl/certs/cacert.pem'

But this gave me the error:

SSL operation failed with code 1. OpenSSL Error messages: error:0200008A:rsa routines::invalid padding error:02000072:rsa routines::padding check failed error:1C880004:Provider routines::RSA lib error:06880006:asn1 encoding routines::EVP lib error:0A000086:SSL routines::certificate verify failed

The issue seems to be that PHP on server1 is having trouble recognising the private certificate authority I created on server2.

Is there any way to fix this?

BTW I have read similar stackoverflow issues that suggest adding the following to the PDO object:

PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false

This does allow me to connect to the server2 database from the server1 php file, but this prevents PHP from verifying the server certificates - doesn’t this defeat the purpose of SSL? If the server certificates are not being checked wouldn’t this leave me vulnerable to man-in-the-middle attacks?

Solution

I figured out what was causing the problem. There were two issues:

the Common Name assigned to my certificates, keys and ca file were not compatible with the PDO object
I hadn’t actually added my ca.pem file to my list of trusted certificates on server1

I orignally used the mysql_ssl_rsa_setup feature to generate my certificates and keys, however this automatically sets the Common Names to something like “MySQL_Server_XXX_Auto_Generated_CA_Certificate”. This won’t work as the host used in the PDO object needs to match the Common Name (in my case <server2_ip>).

I manually generated new keys and certificates on server2 using the method described in https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/creating-ssl-files-using-openssl.html and used the server2 IP address for the Common Names.

I deleted the mysql default keys and certificates and added my new ones:

cd ~/<dir containing new keys/certs>

sudo systemctl stop mysql

sudo rm /var/lib/mysql/ca.pem /var/lib/mysql/client-key.pem /var/lib/mysql/client-cert.pem /var/lib/mysql/server-key.pem /var/lib/mysql/server-cert.pem /var/lib/mysql/private_key.pem /var/lib/mysql/public_key.pem /var/lib/mysql/ca-key.pem

sudo cp ca.pem client-key.pem client-cert.pem server-key.pem server-cert.pem /var/lib/mysql
sudo chown mysql /var/lib/mysql/ca.pem /var/lib/mysql/client-key.pem /var/lib/mysql/client-cert.pem /var/lib/mysql/server-key.pem /var/lib/mysql/server-cert.pem
sudo chgrp mysql /var/lib/mysql/ca.pem /var/lib/mysql/client-key.pem /var/lib/mysql/client-cert.pem /var/lib/mysql/server-key.pem /var/lib/mysql/server-cert.pem

sudo systemctl start mysql

I then copied the ca.pem, client-key.pem and client-cert.pem to server1 as I had done originally and checked I could access mysql on server2 from server1:

mysql -u <server2 user> -h <server2_ip> -p

Then I added the ca.pem file to the trusted certificates directory using the update-ca-certificates function. Copying the file directly into /etc/ssl/certs does not work, the file needs to be copied into /usr/local/share/ca-certificates and the file extension needs to be updated to .crt.

sudo cp ca.pem /usr/local/share/ca-certificates/ca.crt
sudo update-ca-certificates

I then updated updated the CA path in my PDO object to:

PDO::MYSQL_ATTR_SSL_CA =>'/etc/ssl/certs/ca.pem

I could then connect to the MySQL database on server2 from the PHP script on server1 without using the “PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false” statement.