Why am I connecting securely to MySQL when I don't have client certificates?

Question

I don't understand the MySQL documentation for encrypted connections. The server is configured with certificates, and unless I am misreading it, the documentation says that the client must have certificates as well.

But, if I start the client with 'mysql --ssl' then \s reports that the connection is SSL encrypted using DHE-RSA-AES256-GCM-SHA384 even though I haven't used any certificates with the client. /etc/my.cnf.d/client.cnf does not have any certificates in the [client] section, and I don't have a .my.cnf

How is that possible that I am connected with SSL? Is it not really encrypted?

Answer 1

Yes the connection is encrypted. I think you are getting a few things mixed up here. Let me try to simplify it for you:

SSL is a protocol for exchanging data across a secure network, and in order to establish a connection, the server must have a certificate to use. In order to obtain a certificate you must be issued one. This is generally done by a Certificate Authority (CA) or by using a self-signed one, but we will keep it simple here and stick with CA's.

If the certificate you used when setting up SSL is valid, and is trusted by the client attempting to establish a connection, then the connection attempt should succeed. The client does not need to present a certificate here, but it does need to trust the issuer of the certificate. This is generally done by having a copy of the CA certificate on your machine and verifying that the certificate presented by the server is signed/issued by the CA.This corresponds roughly to the --ssl-mode=VERIFY_CA and --ssl-mode=VERIFY_IDENTITY (which checks that the host name in the certificate matches the server you are trying to connect to).

MySQL allows the client to select the certificate to use when verifying the identity of the server using the --ssl-ca, --ssl-cert and --ssl-key options and in order for the transport connection to be successful, the certificate presented by the client must be issued by the same CA that issued the server certificate. This is optional, and if you don't present one then the client will look for a corresponding certificate in its store.

Additionally, once you have established an SSL connection you can authenticate an account using certificates (generally using some kind of PKI technology) which would essentially replace using passwords to log in - but the key thing to understand here is that by the time the process gets to the point where you are attempting to log in, the SSL connection has already been established. When you use this method, you generally find that the client certificates have been issued by the same CA as the one that issued the server certificate.

I use the following analogy to explain it to people I teach:

When my daughter was born the only people she knew she could trust was her mother and me. Then as she got older and more people came into her life we told her who she could and couldn't trust. In this scenario, my wife and I act as the Certificate Authority verifying the trustworthiness of others. If we have not told our daughter she can trust someone then she will not speak to them, much like a how a client will act if it does not trust a server.

The topic of certificates is quite extensive but the key for you is to understand the difference between the client trusting the server enough to establish a secure connection and you then authenticating across that connection using a client certificate.

Answer 2

SSL communication starts with a handshake phase, during which a public-crypto-key is being send to client for future use. So you don't have to have it, your client will get it anyway.

Then why do we need these Public SSL certificate?

Public SSL certificate does include public-crypto-key so you actually can compare the public-crypto-key coming from the server with public-crypto-key from your Public SSL certificate. This does not really make sense by itself unless you also want to check if this public-crypto-key was issued by some specific Certificate Authority (CA).

Anyway, why do we need to check if this public-crypto-key was issued by some specific Certificate Authority (CA)? And you really don't care about it when you connect your MYSQL server to your DB client by IP. Because you know an IP of your MySQL server and you already do trust it.

Certificate Authority (CA) is a kind of legacy coming from another use of SSL secured protocols known HTTPS. In HTTPS you connect to unknown IP address, but there is one thing you should be sure about - a domain name. The domain names can be reassigned to a new IP addresses every minute. And it can be reassigned to your own localhost (via configuration of the 'hosts' file). Just imagines a sophisticated computer virus that comes to your computer, deploys a web server and reassign your "your-bank.com" web domain to that web server. Next time you open your browser, you enter your bank account credentials, but instead of giving you access to your bank account the web server sends your credentials to hackers. It would be upsetting, right? To avoid this scenario people invented HTTPS.

In HTTPS your browser doing some checks for you. It requires a public-crypto-key from a web server just to compare it with a list of known Public SSL certificates from trusted Certificate Authority (CA). Then it checks the domain name assigned to this Public SSL certificates and establishes if this web server indeed allowed to serve content for the provided domain.

But then, why our "sophisticated virus" cannot parse a handshake and extract a public-crypto-key from the real web site just to give it to your browser?

The answer is - "it can". And it can allow HTTPS authorisation to pass, but then the fraudulent web server would need to "talk" to your browser. And to be able to "talk" the fraudulent web server would need to use private-crypto-key to encrypt a server messages, but private-crypto-key is difficult to achieve.

See, we won.

The end.

Except for one thing, AWS uses domain names to access RDS, not the IP addresses. So we do need Public SSL certificates. But MySQL_Workbench is not a web browser so, you would think, it probably doesn't know much about Certificate Authorities. But in reality MySQL_Workbench does know about Certificate Authority (CA) and to activate it you need to go to "SSL" tab and set "Use SSL" drop down list to "Require and Verify CA" - in this case your would need to provide your.pem file to "SSL CA File:" field.

Back to your question: "why the client reports that the connection is SSL encrypted when I provided no certificate?"

Because it is encrypted with some certificate and it is up to your client to either go with the certificate provided by MySQL server or to verify CA and maybe reject it. Now you tell me, why have you setup your DB client to accept any certificates from the MySQL server bypassing the verification of CA?