Amazon's relational database service recently had to rotate SSL certificates for all their RDS instances when the existing certificate expired, as they all eventually do. This got me a little curious since I remembered that MySQL can make use of SSL certificates to both secure connections and also to authenticate users.
I decided to see if I could use client-based certificates to authenticate clients in RDS so I spun up a quick RDS instance (perhaps I'll write a post about that later) and started to tinker. However, I forgot to fully consider the problem and focused instead on what I had set out as the requirement for my project - simply making client certificates work in RDS.
First, a little theory...
What is this SSL thing anyway?
In a word, encryption.In a few more words it is encryption that includes a method of both doing the key exchange through use of asymmetric ciphers such as RSA as well as the more efficient block cipher for actual transport once a key and cipher-suite has been negotiated. Now, it's not just that (the Confidentiality by means of encryption) but also includes the HMAC (hashed message authentication code, or a digest to verify the validity of the message) for the Integrity part and also a means of Authentication by means of verifying identities using a trusted issuer of the certificates that signs them so you know they are who they say (provided you remember to check the signature and can also trust that third party).
If you want more than that there is this awesome site called Wikipedia that has many more words and some diagrams and nerdy stuff. It is not my intention to explain how awesome RSA is, cause it's cool.
Here's the high-level of how it all fits together, though.
First, there is this entity called a Certificate Authority. This is someone everybody involved can trust. For example, when you're dealing with websites you don't know this might be VeriSign or DigiCert or someone like that. Your web browser typically comes with some Certificate Authority certificates built-in so that when you browse to a site that has a certificate that is signed by or issued by one of those authorities your browser can check the signature and tell that it's a valid certificate and should be trusted.
Next, one party that wants to be trusted too will generate a key plus a certificate signing request. He or she will keep the key private and never share that, then send the certificate signing request that has all the details about him or her to the certificate authority to sign. The most important information in the request is the "Subject" line which contains details about the who, where, etc of the person / server.
The Certificate Authority (CA) then decides if it's willing to trust and certify that person or server who made the request (if they are a commercial CA like VeriSign then they usually want some money to convince them that they trust you). So, thinking over the CA whips out that private key it keeps tightly tucked away for just such occasions and signs the request. Now it has not just the Subject line but an Issuer line of information included in it. This is given back to the requester who now has a proper pair of keys, private and public (called a key and a cert here).
Now, how does MySQL use these in a normal setup?
On the server side you have the CA cert and a key/cert pair for the server itself, configured with the following parameters:--ssl-ca=ca.pem
--ssl-cert=server-cert.pem
--ssl-key=server-key.pem
So, here is where I should point out that you need those in "PEM" format instead of some other weird thing like PKCS#7 or something that nobody cares about.
At this point you're good to go from the server side and your client can connect. Now, the MySQL client will currently ignore the fact that the server has a certificate configured unless you explicitly tell it to care (now changed in 5.7.7) so you don't have to worry about something terrible happening to all your normal connections. However, you also can now connect to the server with an SSL encrypted connection! All you need is a copy of the public CA cert from the server. So, let's try it -
# mysql -uroot --ssl-ca=ca.pem
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Ok, cool, it didn't error, but how do we know we're using SSL here? The simple method is to run the 'status' command
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.24-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 22 min 12 sec
Threads: 1 Questions: 12 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.009
--------------
See that lovely orange line that's terribly hard to miss when it's highlighted? Yep, that tells us that we're using SSL and what the negotiated cipher-suite is for this connection.
But wait, there's more! In a standard MySQL setup and following that manual, we can also give one of these nifty certs to the client. Why? So not only can we verify the server is trusted but so that the server can verify that we the client are trusted, too. Just repeat the process of creating the server's key/cert pair before except call it the client key/cert. Then make a user and require one of three things from the user as part of the create user / grant statement. Now use some more flags on the command line when connecting:
GRANT SELECT ON *.* TO 'bjim'@'localhost' REQUIRE X509;
# mysql -ubjim --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
"Require X509" just means that the client should have a properly formatted cert signed by the CA specified. Kind of like having a company badge. Nobody ever looks at the details, but if you have on that looks like the company made it then you're good to go.
The other options are to require either the Issuer or Subject string to match. Remember those from a few paragraphs ago? You can do things like this:
GRANT SELECT ON *.* TO 'bjim'@'localhost' IDENTIFIED BY 'pass123' REQUIRE ISSUER '/C=US/ST=Texas/L=Dallas/O=Banana Corp/CN=CA/emailAddress=ca@example.com';
Or
GRANT SELECT ON *.* TO 'bjim'@'localhost' IDENTIFIED BY 'pass123' REQUIRE SUBJECT '/C=US/ST=Texas/L=Dallas/O=Banana Stand/CN=client/emailAddress=client@example.com';
Those just match the properties of the certificate and they can be combined. Cool, right? There is something deceptive here that is simply not stated but which leads you to make a terrible assumption which is where I went wrong which we'll see later...
Why do we want to secure MySQL connections?
Well, now that's an excellent question. Basically, it's mostly to encrypt the network traffic to prevent the dangers of someone snooping on the traffic or playing the fun little Man-in-the-Middle game. Most of the time you rely on other options for "securing" the network aspects of MySQL such as keeping your databases in a private network in their own VLAN and allowing access from certain controlled, or seemingly controlled, servers such as your web / app servers or some bastion servers you might hop through for administrative or monitoring purposes with connections to that being secured by means of a VPN or SSH. So what does SSL bring to the table past that? Well, not a ton, in my opinion, just the option to have something akin to 2-factor authentication.Enough talk, let's do this!
How to generate all the certs
We're basically going to follow the standard method of generating a CA cert, then using it to sign our own server and client certs which we will also generate. Anyone who has ever done this for a web server is already familiar with the process. Just to make sure we're doing it the MySQL way we'll follow the manual.You fill in your own blanks when prompted
# make a CA key
openssl genrsa 2048 > ca-key.pem
# make the CA cert
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
# make the Server's key and CSR
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
# Fix some formatting (necessary for newer versions of OpenSSL)
openssl rsa -in server-key.pem -out server-key.pem
# sign the Server's CSR and generate the Cert
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# make the Client's key and CSR
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
# Fix the formatting on this one, too
openssl rsa -in client-key.pem -out client-key.pem
# Sign the client's CSR and produce the client's cert
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Verify the issuer and subject (here's what mine say):
# openssl x509 -subject -noout < client-cert.pem
subject= /C=US/ST=TX/L=Dallas/O=Hopscotch Pros/OU=Analytics/CN=client1.example.com/emailAddress=analysts@example.com
# openssl x509 -issuer -noout < client-cert.pem
issuer= /C=US/ST=TX/L=Dallas/O=examples cert authority/OU=mr it man/CN=ca.example.com/emailAddress=root@example.com
So I make the user:
GRANT SELECT ON *.* TO 'jim'@'localhost' IDENTIFIED BY 'pass123' REQUIRE SUBJECT '/C=US/ST=TX/L=Dallas/O=Hopscotch Pros/OU=Analytics/CN=client1.example.com/emailAddress=analysts@example.com';
# mysql -ujim -ppass123 --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Yay!
Your MySQL connection id is 3
Server version: 5.6.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Yay!
Errors!?!
.jpg)
Ok, ok, I got this! Now let's create the same user on my RDS instance (except with the host part as % instead of localhost) and try with the same certs:
# mysql -ujim -ppass123 -h myinstance.myid.us-west2.rds.amazonaws.com --ssl-ca=rds-combined-ca-bundle.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
ERROR 2026 (HY000): SSL connection error: protocol version mismatch
Wait, what? What does that even mean?
After some digging around I found that there are some issues when using newer versions of OpenSSL because by default it uses a slightly different header on the pem file. Solution is to convert from pem to pem, which is what we did in our creation steps as you may now note. So that wasn't it.
Some more digging and newer versions of OpenSSL also default to sha256 instead of sha1 (for decent reasons) as the MAC on the certificate. Ok, so I check and yes, the CA cert here has sha1 and my generated certs are using sha256. That could be causing a problem (though, honestly, you'd think each cert could be validated independently, regardless of actual format of the cert itself)
# openssl x509 -text -noout < rds-combined-ca-bundle.pem | grep sha
Signature Algorithm: sha1WithRSAEncryption
Signature Algorithm: sha1WithRSAEncryption
# openssl x509 -text -noout < client-cert.pem | grep sha
Signature Algorithm: sha256WithRSAEncryption
Signature Algorithm: sha256WithRSAEncryption
Trying again... and again...
Ok, I can fix this. Redo the whole cert generation process with the additional option passed to OpenSSL each time '-sha1'. Now my certs use the same old hashing algorithm sha1 as the CA and RDS's server certs. Let's try again:
# mysql -ujim -ppass123 -h myinstance.myid.us-west2.rds.amazonaws.com --ssl-ca=rds-combined-ca-bundle.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
ERROR 2026 (HY000): SSL connection error: protocol version mismatch
Son of a caterpillar! What is going on here? More googling. Couple of bugs, nothing helpful.
At this point I try locally (see above working solution) and it works fine. Huh. Then it dawns on me - the one thing that's pretty different here between what I'm doing locally and against RDS is that I am generating the keys from the CA cert on the server (it's all done with the same CA cert locally). With the RDS instance I don't have that option since I don't have access to the CA key. Still, I should at least be able to generate a cert with a new CA and try locally. So, I create a new CA key/cert pair different from the one used in the my.cnf file and use that to create and sign a new client key/cert pair. Testing that locally, but which CA cert are you supposed to reference on the CLI? Manual says it's the one for the server's cert. Ok.
# mysql -ujim -ppass123 --ssl-ca=ca.pem --ssl-cert=client-cert2.pem --ssl-key=client-key2.pem
ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation
Well, that doesn't help. What if I use the new CA cert?
# mysql -ujim -ppass123 --ssl-ca=ca2.pem --ssl-cert=client-cert2.pem --ssl-key=client-key2.pem
ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation
Alright. This isn't working. Maybe it's because I'm using OpenSSL to generate these certs and MySQL is using yaSSL (default in all community builds). The Enterprise Edition of MySQL uses OpenSSL still for some reason (Still curious about that difference, Oracle...) so I install that for testing and try it.
mysql -ujim -ppass123 --ssl-ca=ca.pem --ssl-cert=client-cert2.pem --ssl-key=client-key2.pem
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)
Oh, now I see why yaSSL is nice. OpenSSL based builds give even more cryptic errors.
More googling, even less useful stuff.
The "well duh" moment
What the manual forgets to mention but what is obvious when you stop to think about it as I was forced to do when I hit this wall and had to slow down and use my own head for a few minutes is this: It needs to be the same CA for both server and client certs. Yes, needs to be. The whole point is trust. MySQL allows you to specify an Issuer as a requirement which led me to initially assume that you could let any old client cert work regardless of origin. However, as you've seen above you can actually input ANYTHING into those fields when generating a CA cert then sign stuff. Regardless of actual validity you can spoof any Issuer line. You can use any self-made CA cert to sign any self-generated client certificate as well thus spoofing any Subject line, too. This becomes really kind of pointless when it comes to security. As user 'bob' on the instance I can just do a SHOW GRANTS FOR 'jim'@'%'; command and see what to spoof in the Subject line if that's what is required.
Conclusion - a single CA certificate must be used to sign both the server's certificate AND the client's certificate or it will not work.
What this means for RDS.
Since RDS comes with a certificate provided by AWS and it is not a setting that can be changed, in other words, you cannot upload your own certs) and everyone has the same CA cert it means that what you're verifying when you specify the RDS CA cert is that you're connecting to an RDS MySQL instance. Since you don't have the CA key for AWS you cannot create the matching client certificates to use. Generating your own does you no good. In the end, if authentication is part of your goal for MySQL then you must use the passwords and you should really be using network level access control by means of VPC / EC2 Security Groups.Moral of the story
1 - If the RDS manual doesn't explicitly say you can do something with a MySQL feature you can probably assume that it either doesn't work or has some caveats that you'll come across eventually.
2 - Just because you can't enjoy all the security benefits of SSL doesn't mean you can't enjoy some of them. If you like nicely encrypted connections then go for it.
3 - The most important lesson - think about what you're doing and why you're doing it. Does it accomplish what you are asking? Does what you're asking to do even make sense? I fear too often that we paint ourselves into intractable corners because we skip this. I know I'm guilty of it as I've shown with my headlong attempt to do something nobody was doing without first really thinking about why.
The point of this story, and indeed this entire blog, is learning. If I had to learn it then someone else will have to learn it, too. I hope this saves someone some frustration.
That said, if someone is able to make this work with two separate CA's for server and client and disprove my conclusion I am excited to hear about it. Please let me know. I am certainly not above being wrong.
No comments:
Post a Comment