0

I have started a project using MySQL in the cloud and I need to set up an SSL connection to it for the MySQL command line client.

I received the ca.pem file from the MySQL server which has a ton of certificates in there.

I already sorted out the SSL connection in MySQL Workbench and in Java, after a certain amount of trying different things. I've all the certificates now in my Java installation's cacerts trust store, and I've configured Java and Workbench to find my public / private SSL keys in my client.jks store.

None of that though helps with the command line MySQL client. According to the MySQL docs Configuring MySQL to use Secure Connections, this is the way to launch it:

mysql --ssl-ca=ca.pem \
   --ssl-cert=client-cert.pem \
   --ssl-key=client-key.pem

and these params can be put in my.cnf to make the command line easier.

And this told me some of what I needed to know: MySQL on Amazon RDS i.e. how to point mysql on the command line to the server certificate. That was easy, considering the Java procedure required me to import the whole lot into the Java certificate store using a command line tool that only reads the first certificate and silently ignores the remainder.

So after all faff with Java, and from the beginning having a public/private key pair for ssh, it occurred to me it would be sensible to keep all my keys together and in fact even re-use the same key to create the other formats I need.

According to Converting a Java Keystore into PEM Format I should be able to do that, or maybe I should instead use the SSH keys? They all share the same Country/State/Locality/Org/Unit/Name/Email.

The MySQL docs Creating SSL Certificates and Keys Using OpenSSL says I need to do this:

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
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

This is problematic because I didn't generate the AWS certificates and I only got one pem file from there. Maybe the AWS ca.pem and server-cert.pem are both in the one pem file - guess I could check but I'll have to go and dig out the info about which CA signing authority they use so I could recognise the CA cert. And I guess the 'server-cert' is the public key from the actual host I've got.

But it doesn't work, I get the following error:

$ openssl.exe x509 -req -in client-req.pem -days 3600 
        -CA rds-combined-ca-bundle.pem -CAkey rds-combined-ca-bundle.pem 
        -set_serial 01 -out client-cert.pem
Signature ok
subject=/C=UK/ST=LN/L=LN/O=X/OU=XYZ/CN=xyz/emailAddress=adam@me.com
Getting CA Private Key
unable to load CA Private Key
4294956672:error:0906D06C:PEM routines:PEM_read_bio:no start 
        line:pem_lib.c:707:Expecting: ANY PRIVATE KEY
Community
  • 1
  • 1
Adam
  • 5,215
  • 5
  • 51
  • 90

1 Answers1

0

Turns out the SSL certificates are a red herring. You don't need them. All you need is this:

mysql -h myinstance.c9akciq32.rds-us-east-1.amazonaws.com
    --ssl-ca=[full path]rds-combined-ca-bundle.pem --ssl-verify-server-cert

It's Cygwin and the standard Windows MySQL client don't play well together, hence the hanging when trying to start: connecting to mysql from cygwin

Adam
  • 5,215
  • 5
  • 51
  • 90