There are many articles around the Internet that discuss enabling Encryption in-transit to MySQL servers. They all include instructions about creating Client Certificates, but they don’t clearly explain that Client-Side Certificates are not a requirement to achieve end-to-end encryption between client and server.
Creating Client certificates that can be authenticated by the server can be complicated. It is not even possible in some scenarios, such as using servers hosted by AWS RDS, since AWS runs its own Certificate Authority. But don’t let that stop you. Below, I will demonstrate that enabling SSL/TLS on the server, and using a client that supports encryption is sufficient to securely encrypt traffic between the two.
First, I set up a MySQL server on RDS using MySQL version 8.0.25. Nothing special here, except that I’m going to make it “Publicly Accessible” which gives is a Public IP Address so that I can access it over the Internet. My Security Group here already allows inbound port 3307 from my desired IP Addresses for testing:
aws rds create-db-instance \ --db-instance-identifier=encryption-tester \ --allocated-storage=20 \ --db-instance-class=db.t3.micro \ --engine=mysql \ --master-username=admin \ --master-user-password="thepasswordIchose" \ --vpc-security-group-ids="sg-0bf6fa7080100e55b" \ --backup-retention-period=0 \ --port=3307 \ --no-multi-az \ --engine-version=8.0.25 \ --publicly-accessible
It takes several minutes for my Database Instance to be created, then I can log into it with the command:
mysql -h encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com -u admin --port=3307 -p
I run the command
show status like 'ssl_cipher'; and look at that! My connection is encrypted already, as indicated by Cipher method present:
$ mysql -h encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com -u admin --port=3307 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 8.0.25 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> show status like 'ssl_cipher'; +---------------+-----------------------------+ | Variable_name | Value | +---------------+-----------------------------+ | Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 | +---------------+-----------------------------+ 1 row in set (0.01 sec)
I haven’t set up any client certificates or anything special, yet my connection is encrypted. But let’s not take the session variable’s word for it. Lets double-check by capturing and inspecting some packets.
I’ll run tcpdump with this command:
sudo tcpdump -i any host encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com -s 65535 -w /tmp/initial-connection.pcap
To make it quick, Instead of using a full packet-analysis program, I just run the strings command to look for text strings in the packet capture:
17:05 $ strings -8 /tmp/initial-connection.pcap =JpgP~eS mysql_native_password Washington1 Seattle1"0 Amazon Web Services, Inc.1 Amazon RDS1%0# Amazon RDS us-east-1 2019 CA0 210824170035Z 240822170850Z0 :encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com1 Amazon.com1 Seattle1 Washington1 Seattle1 Washington1"0 Amazon Web Services, Inc.1 Amazon RDS1 0 Amazon RDS Root 2019 CA :encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com0 Seattle1 Washington1"0 Amazon Web Services, Inc.1 Amazon RDS1 0 Amazon RDS Root 2019 CA0 190919181653Z 240822170850Z0 Washington1 Seattle1"0 Amazon Web Services, Inc.1 Amazon RDS1%0# Amazon RDS us-east-1 2019 CA0 HId0%aC>
Looks like a lot of stuff in that output about the certificate and SSL negotiation, but nothing containing the queries I executed.
I’m going to try it again and specifically disable encryption to see what the packets look like and ensure they contain the plain-text statements and responses I expect:
This is my SQL session:
17:05 $ mysql --ssl-mode=DISABLED -h encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com -u admin --port=3307 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32 Server version: 8.0.25 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> select version(); +-----------+ | version() | +-----------+ | 8.0.25 | +-----------+ 1 row in set (0.00 sec) mysql> show status like 'ssl_cipher'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Ssl_cipher | | +---------------+-------+ 1 row in set (0.01 sec) mysql> \q
The strings in the packets captured during that session clearly contain things relevant to the commands that I executed:
17:05 $ strings -5 /tmp/skip-ssl.pcap 1%aCu 1%a\u 8.0.25 *Bi|tm UkU-dsK mysql_native_password admin mysql_native_password Linux _client_name libmysql 24817 _client_version 5.7.35 _platform x86_64 program_name mysql select @@version_comment limit 1 @@version_comment Source distribution select version() version() 8.0.25 show status like 'ssl_cipher' performance_schema session_status session_status Variable_name Variable_name performance_schema session_status session_status Value Value Ssl_cipher
SSL Client-Certificates are NOT required for traffic to be encrypted to a MySQL server. In fact, with a modern client and server, SSL is preferred and is automatically enabled. Just like I’d expect for traffic to be encrypted by default in 2021.
So what is the purpose of Client Certificates during a MySQL Connection
Client Certificates are intended to verify the identity of the Client. They are an extra step of authentication beyond a typical username and password. By presenting a client certificate that has been properly signed by a recognized Certificate Authority, the client is proving that their identity or system has been verified by the Certificate Authority.
Because SSL is complicated and is not well understood, many well-meaning people have instructions for creating a client key and client certificate, and transmitting those to authorized users. While that does provide a second piece of information needed to authenticate to the server, it is not how a secure client should authenticate.
The proper, fully secure method for a client to get a certificate is for the client to create its own private key. It should never share that key, even with the Certificate Authority. With the private key created, it would then create a certificate signing request (CSR), and present only the certificate signing request to the Certificate Authority. The certificate authority takes whatever steps it requires to verify the authenticity of the client, then provide the client back with a Client Certificate signed by the Certificate Authority. That Client Certificate is the client’s evidence that it’s identity has been verified by the Certificate Authority. The Certificate Authority is able to provide the client certificate without ever having the client’s private key.
If you run a MySQL Server and want to require that all clients to connect via SSL/TLS, you can set the global setting require_secure_transport to true. To require SSL only for specific users, use the
CREATE USER ... REQUIRE SSL statement when creating the MySQL user