Technology Sharing | MySQL : Analysis of SSL Connection

Author: Hu Chengqing

A member of the DBA team of Aikesheng, good at failure analysis and performance optimization, personal blog: https://www.jianshu.com/u/a95ec11f67a8 , welcome to discuss.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

TLS or SSL ?

SSL (Secure Socket Layer) is a protocol encryption layer based on HTTPS. It was originally developed by Netscape and was later standardized by IETF (The Internet Engineering Task Force). RFCRequest For Comments), RFCs contain specifications for many Internet technologies.

At first, it was because HTTP uses plaintext when transmitting data (although it is said that the data submitted by POST cannot be seen in the body of the message, but it can still be stolen through packet capture tools), it is not safe. In order to solve this hidden danger Netscape launched the SSL secure socket protocol layer. SSL is a protocol layer based on HTTP and above TCP. It is based on HTTP standards and encrypts data when TCP transmits data. Therefore, HTTPS is the abbreviation of HTTP+SSL/TCP.

Since the launch of HTTPS was welcomed by many people, when SSL was updated to 3.0, IETF standardized SSL3.0 and added a few mechanisms (but almost no difference from SSL3.0), and the standardized IETF was renamed TLS1 .0 (Transport Layer Security), it can be said that TLS is the new version 3.1 of SSL.

TLS (Transport Layer Security) is a more secure upgraded version of SSL. But the term SSL is more commonly used, and in fact MySQL uses the TLS protocol, not the SSL protocol.

1. TLS handshake process

Want to figure out what the pile of files below do? Then you must first understand the TLS handshake process

├── ca-key.pem
├── ca.pem
├── client-cert.pem
├── client-key.pem
├── server-cert.pem
└── server-key.pem

1.1 TLS handshake process

The process of TLS handshake is actually the process of key exchange, which is also the most complicated part of the entire TLS encryption technology. Refer to an online picture as follows:

1.2 Key algorithm

Symmetric key algorithm: The same key is used for data encryption and decryption.

Asymmetric key algorithm: Different keys are used for data encryption and decryption, one is the public public key, and the other is the private key kept secretly by the user. Data encrypted with a public key (or private key) can only be decrypted with the corresponding private key (or public key).

Compared with the asymmetric key algorithm, the symmetric key algorithm has the advantage of fast calculation speed, and is usually used to encrypt a large amount of information (such as encrypting all messages); the asymmetric key algorithm is generally used for digital signature and encryption. Encrypt less information.

Note: The SSL/TLS protocol uses a symmetric key algorithm for data encryption and an asymmetric key algorithm for "symmetric key" encryption. The process is:

1. In the above figure, the server sends the public key to the client, and the private key is stored by itself. This is the public key and private key pair in the asymmetric key algorithm;
2. The client will create a key, which is the key in the symmetric encryption algorithm. Then encrypt the key with the public key of the server and send it to the server;
3. The server receives the encrypted key sent by the client, decrypts it with its own private key, and obtains the key before encryption;
4. The transmitted data is then encrypted and decrypted using a "symmetric key".

This is to use the asymmetric key algorithm to ensure the security of the symmetric key itself.

1.3 Digital certificate - how to ensure the authenticity of the public key?

If an attacker forges the public key of the server and sends the client concurrently, the client will visit the fake website and steal information. Obviously, ensuring that the public key on the server side received by the client is true is the first line of defense to ensure the reliability of the entire encrypted connection.

A digital certificate is issued by an authority CA (Certification Authority). The issuance process is as follows:

1. The user first generates his own key pair, and transmits the public key and some personal identification information to the CA;
2. The CA verifies the user's identity (the necessary steps will be performed to ensure that the request is indeed sent by the user);
3. The CA performs Hash calculation on all the user's information (public key, owner, validity period...) to obtain a Hash value, and then uses the private key to encrypt the Hash value to obtain a signature, and then a digital certificate is obtained. The certificate contains information such as the user's public key, owner, and validity period, as well as CA's signature information.

The verification process of the digital certificate:

1. The client will use the same Hash algorithm to obtain the Hash value H1 of the digital certificate;
2. Usually the browser and the operating system integrate the CA certificate (including the CA public key and owner), the client obtains the CA certificate, uses the CA public key to decrypt the signature, and obtains a Hash value H2;
3. Compare H1 and H2, if the values are the same, the digital certificate is trusted.

The above issuance and verification process is shown in the following figure (refer to the network):

If the CA certificate is not in the trusted zone of the browser and operating system, such a CA certificate is often referred to as a self-signed CA certificate (self-signed certificates are automatically generated by MySQL, see below for details). To complete the verification of the digital certificate, you must put the self-signed CA certificate on the client in advance, and specify the CA certificate file when the client initiates a connection; or import the self-signed CA certificate into the client's operating system trusted zone in advance, In this way, the CA certificate can also be automatically obtained during the TLS handshake process.

In addition: the verification certificate is not necessarily required in the SSL/TLS protocol. For example, the mysql client can only verify the CA certificate when --ssl-mode=VERIFY_CA or --ssl-mode=VERIFY_IDENTITY is specified. If --ssl-mode=REQUIRED, the CA certificate is not verified, and only the MySQL server is required to send the public key to the client, which cannot guarantee that the public key of the server really belongs to the MySQL server. See below for details.

2. MySQL SSL encrypted connection

2.1 MySQL server configuration

Startup parameters:

• --ssl: Indicates that the MySQL server allows encrypted connections. This startup parameter is enabled by default in MySQL 8.0

System variables:

• require_secure_transport: Specifies whether to require clients to use encrypted connections. The default value is OFF. If ON, it means that the client must use an encrypted connection. If the client closes ssl, the connection will report an error.

The following parameters specify the certificate and key files to use when encrypting connections:

ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem

MySQL8.0 will automatically generate the SSL certificate, key file, and RSA key pair file at startup; or use the mysql_ssl_rsa_setup program to generate the above files. It can also be generated manually:

## SSL证书和密钥文件
certs
├── ca-key.pem
├── ca.pem
├── client-cert.pem
├── client-key.pem
├── server-cert.pem
└── server-key.pem

# Create CA certificate
# 创建CA证书（包含CA公钥）和CA私钥
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 \
        -key ca-key.pem -out ca.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
# 先生成服务器公钥、私钥
# 使用CA私钥对服务器公钥签名，得到服务器证书 server-cert.pem，证书中包含公钥、所有者、有效期等明文信息，也有经过 CA 私钥加密对公钥、所有者、有效期...加密后的签名
openssl req -newkey rsa:2048 -days 3600 \
        -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 \
        -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
# 先生成客户端公钥、私钥
# 使用CA私钥对客户端公钥签名，得到客户端证书 client-cert.pem，一般不需要验证客户端身份，这些文件就不需要用到。当然如果要同时验证 MySQL Server 身份和客户端身份，就需要用到这些文件了。
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 x509 -text -in ca.pem

# 验证CA证书（“使用CA证书验证数字证书”更恰当？）
openssl verify -CAfile ca.pem server-cert.pem
# 输出结果：server-cert.pem: OK

2.2 MySQL client configuration

When the MySQL client connects to the Server, specify it through the --ssl-mode parameter:

• --ssl-mode=PREFFERED, the default behavior, the client side attempts to connect using encryption, and falls back to an unencrypted connection if an encrypted connection cannot be built
• --ssl-mode=REQUIRED, the client needs to encrypt the connection, if the connection cannot be established, the client will fail
• --ssl-mode=DISABLED, client uses unencrypted connection
• --ssl-mode=VERIFY_CA, the client needs to encrypt the connection and also verify the CA certificate
• --ssl-mode=VERIFY_IDENTITY, the client requires an encrypted connection and also performs verification against the CA certificate and the server hostname in its certificate

Note: Hostname authentication VERIFY_IDENTITY does not apply to self-signed CA certificates created automatically by the server or manually using mysql_ssl_rsa_setup.

The test is as follows:

##当指定 --ssl-mode=REQUIRED，仅要求加密连接，不需要验证 MySQL Server 身份，所以会直接信任 MySQL Server 发送给客户端的公钥（即 server-cert.pem 数字证书中的明文公钥，忽略其中的数字签名信息）
[root@172-16-21-5 /]# /opt/mysql/base/8.0.21/bin/mysql -h172.16.21.4 -P3306 -utest -ptestpass --ssl-mode=REQUIRED -e "select 1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+

##当指定 --ssl-mode=VERIFY_CA，需要验证 CA 证书，因为这个 CA 证书是自签发的，所以不在浏览器和操作系统的可信任区，无法从浏览器和操作系统的可信任区这个公共渠道获取 CA 证书，所以报错：
[root@172-16-21-5 /]# /opt/mysql/base/8.0.21/bin/mysql -h172.16.21.4 -P3306 -utest -ptestpass --ssl-mode=VERIFY_CA
ERROR 2026 (HY000): SSL connection error: CA certificate is required if ssl-mode is VERIFY_CA or VERIFY_IDENTITY

##将MySQL服务端的 ca.pem 拷贝到客户端
scp ca.pem 172.16.21.5:/tmp/

##--ssl-mode=VERIFY_CA，指定需要验证 CA 证书，因为这个 CA 证书是自签发的，所以不在浏览器和操作系统的可信任区，则必须要将 CA 证书拷贝到客户端，并指定 CA 证书文件
##TLS 握手过程中，MySQL Server 发送服务器数字证书 server-cert.pem 给客户端，客户端使用 CA 证书中的 CA 公钥解密 server-cert.pem 中的签名，验证通过，才可以正常登陆:
[root@localhost ~]# mysql -h10.186.61.173 -P3308 -uhucq -p'1qaz@WSX' \
--ssl-ca="/tmp/ca.pem" \
--ssl-mode=VERIFY_CA \
-e "select 1"
+---+
| 1 |
+---+
| 1 |
+---+

##由于MySQL自动生成的CA证书是自签名证书，而 --ssl-mode=VERIFY_IDENTITY 不适用于由服务器自动创建或使用 mysql_ssl_rsa_setup手动创建的自签名CA证书，即使指定本地的CA证书文件，连接也会失败
[root@localhost ~]# mysql -h10.186.61.173 -P3308 -uhucq -p'1qaz@WSX' \
--ssl-ca="/tmp/ca.pem" \
--ssl-mode=VERIFY_IDENTITY \
-e "select 1"
ERROR 2026 (HY000): SSL connection error: Failed to verify the server certificate via X509 certificate matching functions

2.3 TLS handshake process in MySQL SSL connection

The above example has been explained in detail, here is a brief summary:

1. The client initiates a ssl connection request;
2. MySQL Server sends the digital certificate server-cert.pem to the client (server-cert.pem contains: server public key, CA signature information);
3. The client uses the CA certificate ca.pem (because this is a MySQL self-signed CA certificate, it cannot be obtained from the trusted zone of the operating system (not here at all), so the CA certificate file must be saved locally on the client beforehand) CA in The public key decrypts the signature in server-cert.pem for verification;
4. After the verification is passed, generate a symmetric key, use the public key in server-cert.pem to encrypt the "symmetric key", and send it to MySQL Server;
5. MySQL Server decrypts using its own private key server-key.pem to obtain a "symmetric key";
6. The transmitted data is then encrypted and decrypted using a "symmetric key".

If you specify only --ssl-mode=REQUIRED, and do not specify --ssl-mode=VERIFY_CA or --ssl-mode=VERIFY_IDENTITY, step 3 is not required.

3. JDBC how to set up SSL connection

First, the MySQL Server side must generate the SSL certificate and key file, and specify the startup parameter at startup: --ssl (usually written to my.cnf). When MySQL8.0 is started, the SSL certificate and key files are automatically generated, and the --ssl parameter is used by default.

JDBC close ssl connection example: jdbc:mysql://localhost:3306/hucq?useSSL=false

If MySQL Server uses caching_sha2_password (the default authentication plug-in for MySQL 8.0) and sha256_password authentication plug-in, you must also specify AllowPublicKeyRetrieval=True, because the caching_sha2_password plug-in requires RSA public key encryption when exchanging passwords (in the case of not using SSL encrypted connections) ), the function of the AllowPublicKeyRetrieval=True parameter is to request the MySQL server to send the RSA public key to the client. If the RSA public key is not requested and the client's local RSA public key file is not specified (first copy the RSA public key from the MySQL server to the local) , the connection will report an error. Should be configured: jdbc:mysql://localhost:3306/hucq?useSSL=false&AllowPublicKeyRetrieval=True

JDBC opens an SSL connection, which means that a secure connection is required, so you should enable CA certificate authentication. Like the mysql client, you need to import MySQL's self-signed CA certificate to the client, or put it on ftp, and then specify the CA certificate through JDBC parameters. The path is more complicated, please refer to the official document: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html

References

1. https://tangyuxian.com/2021/05/19/%E5%90%8E%E7%AB%AF/%E7%AE%97%E6%B3%95/%E7%AE%97%E6%B3%95-TLS-SSL%E6%8F%A1%E6%89%8B%E8%BF%87%E7%A8%8B/
2. https://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html