Month: August 2021

MySQL Encryption In-Transit Does NOT Require Client-Side Certificates

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

Conclusion:

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.

Best Practices

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

Installing Composer Packages with custom SSH Identities

Several posts around the Internet describe how to use a specific SSH Identity composer packages, but I can never find them when needed.

This is how I use a specific SSH identity file for packages deployed with GitHub via Deploy keys. GitHub allows a deploy key to be used with only a single repository, so if you have multiple repositories, you need a separate SSH key for each.

Create the SSH Key

ssh-keygen -t ed25519 -f ~/.ssh/repo-foobar -N '' -C "foobar-deploy"

Copy the contents of ~/.ssh/repo-foobar.pub into the “Deploy Key” section of the Repository settings in GitHub.

Now, you can script a deploy, including a composer install that includes that repository with the command

Use a custom GIT_SSH_COMMAND during composer install

cd /path/to/codebase
export GIT_SSH_COMMAND="ssh -i /home/username/.ssh/repo-foobar -o 'IdentitiesOnly yes '"
COMPOSER_HOME="/home/username/" composer install

The composer_install command uses the defined SSH command (instead of just plain ssh). In doing so, it uses the identity only from the specified key.

Have multiple repos included in your composer.json file that each need a separate identity?

You’ll need to create the SSH key and upload a separate key to GitHub for each repo. However, you can only specify one SSH key to use during the composer install. While there are more elegant solutions, I’ve found the simplest is just to run composer install multiple times, one for each package, and change the identity file between each one. The first execution will fail, but will keep the downloaded code in the composer cache. The second one won’t need to re-download the first again since it is already in the cache, and if you with as many packages as you have, it will eventually succeed, having downloaded each of them.