Friday 6 September 2019

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

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?


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

No comments:

Post a Comment