PDO connection to remote DB using SSL; FastCGI errors when verifying server cert

Question

I have a remote server with a MariaDB DB that only accepts SSL connections for a certain user, and have generated some self-signed SSL certificates using the following

# Create CA certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 
        -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 \
        -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 \
        -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 \
        -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

(taken from https://dev.mysql.com/doc/refman/5.6/en/creating-ssl-files-using-openssl.html)

I filled in the details for each certificate as follows

• Country Name: GB
• Locality Name: Company Town
• Organization Name: Company Name
• Common Name: DEV CA, DEV Server, DEV Client (respectively)

and left all the other fields left blank

---

The remote server has the following in my.cnf

[mariadb]
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

I can connect from my local machine's command line by including the following in its my.cnf

[client]
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/client-cert.pem
ssl-key=/path/to/client-key.pem

I can create a PDO connection from my local machine using the following

$pdo = new PDO(
    'mysql:host=xxx.xxx.xxx.xxx;dbname=database_1',
    'database_user',
    'database_password',
    [
       PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
       PDO::MYSQL_ATTR_SSL_KEY => '/path/to/client-key.pem',
       PDO::MYSQL_ATTR_SSL_CERT => '/path/to/client-cert.pem',
       PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca.pem',
    ]
);

---

Unfortunately, if I remove the line

PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,

I get an Internal Server Error and the following shows up in my MAMP PRO apache error log

… FastCGI: incomplete headers (0 bytes) received from server …

I get no errors in my PHP error log

I can only assume something goes wrong with the certificate verification,

• have I missed something? and/or
• is it safe to leave PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT set to false?

Answer 1

When a client validates the server certificate, following will be checked

• signature
• certificate validity period includes the current time
• certificate is not revoked (part of CRL)
• host name matches CN or alternative name(s)
• root ca

PDO with mysqlnd uses PHP streams, which checks the CN field only, but not Subject Alternative Names field(s). According to your code you specify an IP address to connect for, but not a name.

Unfortunately PHP also doesn't offer an additionally method for checking the sha finger print of peer certificate.

See also:

• RFC 5280

• PHP Bug 71845

Answer 2

is it safe to leave PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT set to false?

For the most part, if access to your server is secure it is safe, although not recommended, to do this.

The reasons this is failing is that a TLS(SSL) Certificate is granted to a host access address, where the host access address is the IP Address or the Hostname, not the physical host machine. So your Common Name should be either the IP address of the server or the Hostname; whichever is used is what you must use to connect to the Server.

So, as your connecting to the host xxx.xxx.xxx.xxx (from 'mysql:host=xxx.xxx.xxx.xxx;dbname=database_1'), the Common Name on your certificate needs to be xxx.xxx.xxx.xxx