6

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?
Improve this question
3
  • To your last question, with self signed certificates you have to leave it false because the certificates can never be verified since no root CA was involved. For a single connection such as you describe it should be safe though the proper way to resolve the problem would be a "real" certificate signed by a root CA which would let you have that variable set to true. Commented Sep 3, 2019 at 19:01
  • What is the CN in your server certificate? You can check it e.g. with openssl x509 -noout -subject -in server.pem. Commented Sep 4, 2019 at 19:59
  • @GeorgRichter The CN for server-cert.pem is DEV Server.. I also have DEV Client and DEV CA for client-cert.pem and ca.pem respectively Commented Sep 5, 2019 at 8:29

2 Answers 2

Reset to default
9

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks, so are you suggesting that the verification fails because the IP doesn't match the CN? Would using the host IP as the server cert CN help? As the command line connection works, I'm guessing that that doesn't perform the same step..
Also, from the bug report - it almost implies that turning MYSQL_ATTR_SSL_VERIFY_SERVER_CERT to false is a correct solution. Any insight into how safe this is?
Disabling server cert verification is of course less secure, since you will not be able to detect a MTM-attack. If you are in a private/secured network this might be ok. According to php.net/manual/en/context.ssl.php it is possible since PHP 5.6.0 now to check the md5 or sha1 (preferred) finger print of the peer certificte, however I don't know if it's supported by PDO yet.
PDO connection is less secure, not insecure if the server cert validation is turned off. As George has already pointed out, if both servers are on the same private network, then this is not really an issue (unless the system has really stringent security requirements for some reason).
If you have self signed certificate, then just use the IP address of the server in common name. Usually CN is used for a full qualified domain name, but it could be used also for wildcard domain name or an IP address.l
|
1

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

Improve this answer

5 Comments

Thanks I can try that. The server is secure, but not on the same network, and the MariaDB user is set up with the specific IP of the machine making the connection ([email protected]). Why is the command line access happy with the certs, but not the PDO connection?
Since the command line client uses MySQL Connector/C, while PDO uses php streams. MySQL Connector/C checks CN, alternative names but also IP address: github.com/MariaDB/mariadb-connector-c/blob/3.1/libmariadb/…
You're saying MySQL connector and then linking to MariaDB Connector, MariaDB is not MySQL it's just MySQL compatible.
Also just to be 100% correct Certificates do not store IP Addresses, the Store Access Names (commonly referred to as Hostnames or host addresses). If a Certificate contains an IP address the IP Address is a hostname saved in Subject Alternative Name, using the IP Address in there is exactly as I said above.SAN these are effectively alternative CN's So it still a hostname or host address, that is used to talk to the machine,
Took me a while to get back to this, but setting the Common Name on the server-cert to the IP of the MySQL server did indeed fix the problem and allowed me to no longer set PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT to false. Thanks for the help :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.