Securing a Secure Web Application Example

How do you secure an already secure web application? Well, the title is that because we are going to build upon Spring's Securing a Web Application guide. You can follow the Spring guide to build your secure web app and then come back here to make it more secure. Or you can just keep on reading, I'm going to share the repo anyway.

Tools

Before we begin, these are the tools I used to make this example:

• IntelliJ IDEA 2023.3.4 (Community Edition)
• openjdk-17.0.10
• Spring Boot v3.3.3
• Windows 11
• Ubuntu 22.04.3 LTS (GNU/Linux 5.15.153.1-microsoft-standard-WSL2)
• OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022)

Prerequisite

Alright. I'm assuming you followed the Spring guide to securing a web app and got it running. What you did was add Spring Security on your Spring Boot app which then protects your resource endpoints. You have secured it with a login page and only authorized users can access certain resources. You should have something like below.

I have added 127.0.0.1 jpllosa.tech on the Windows hosts (C:\Windows\System32\drivers\etc) file. We can't use localhost for our certificate later.

Requirement

And now the problem we are going to solve. Imagine this secure web app is runnning on our company network. As it stands anyone can see the website on the browser (e.g. by going to http://jpllosa.tech:8080). They would see the welcome and login pages. Now, we don't want just anybody to see the website. We only want, let's say level 1 clearance personnel and machine to see it. What do we do? Any ideas? Yes, we are going to serve the web app over HTTPS (Hypertext Transfer Protocol Secure) and then what's next? Yes, install our own certificate authority as a trusted certificate on the browser of the level 1 personnel's laptop (e.g. an IT services job). Is the requirement clear enough?

Create Self-signed Certificate

We're going to do PEM (Privacy Enhanced Mail) certificates. Thankfully, Spring Boot supports this. We don't have to deal with the Java specific JKS (Java KeyStore) format, which can be tricky to configure sometimes.

Let's start by creating a private key. This is the most important component of our certificate and helps to enable encryption. So open your WSL and generate a private key like so.

  
$ openssl genrsa -out jpllosa.tech.key 2048
$ ls
jpllosa.tech.key
  

Second, create a certificate signing request. A certificate signing request (CSR) includes the public key and some additional information like organization and country. We need this because we want our certificate signed. Create the CSR with our private key like below. I've left the challenge password and optional company name blank. The important field is Common Name, which should be the fully qualified domain name (FQDN) of our domain.

  
$ openssl req -key jpllosa.tech.key -new -out jpllosa.tech.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:AU
State or Province Name (full name) [Some-State]:Some-State
Locality Name (eg, city) []:City
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Internet Widgets
Organizational Unit Name (eg, section) []:section
Common Name (e.g. server FQDN or YOUR name) []:jpllosa.tech
Email Address []:webmaster@jpllosa.tech

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
$ ls
jpllosa.tech.csr  jpllosa.tech.key
  

Next, we create a self-signed certificate with our private key and CSR. This is a certificate signed with its own private key. This certificate isn't trusted since it is self-signed. Create a self-signed certificate like so.

  
$ openssl x509 -signkey jpllosa.tech.key -in jpllosa.tech.csr -req -days 365 -out jpllosa.tech.crt
Certificate request self-signature ok
subject=C = AU, ST = Some-State, L = City, O = Internet Widgets, OU = section, CN = jpllosa.tech, emailAddress = webmaster@jpllosa.tech
$ ls
jpllosa.tech.crt  jpllosa.tech.csr  jpllosa.tech.key
  

Now we need something for the browser (i.e. client side) as we will need to install a trusted certificate for it. We need a self-signed root certificate authority (CA) certificate. We can do this by being our own certificate authority. Let's create a self-signed root CA like so.

  
$ openssl req -x509 -sha256 -days 1825 -newkey rsa:2048 -keyout myCA.key -out myCA.crt
.....+...+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.+....+........+...+...+.+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*....+..+.............+.................+..........+..+............+.+........+.+..............+....+..+......+.......+..+.............+..+.........+............+....+..............+......+...+...............+...+....+...........+..........+..............+....+...............+..+....+..............+......+.+...+.........+.....+...+.........+....+...........+...+.+.........+..+......+...+....+.....+.+...........+...................+..+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
.................+..........+.....+.......+...+...+..+..........+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*..+..+.............+..+.+...+..+.......+..+...+.+...........+....+.....+.........+.+.....+.......+..+.......+......+..+.+.....+....+.....+...+.......+..+....+.....+.......+.....+....+........+......+....+.....+...+.......+......+.....+.......+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.+...+...+......+...........+.......+...+..+...+.......+..+....+..+.......+........+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Enter PEM pass phrase: password
Verifying - Enter PEM pass phrase: password
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:AU
State or Province Name (full name) [Some-State]:Some-State
Locality Name (eg, city) []:City
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Internet Widgets
Organizational Unit Name (eg, section) []:Section
Common Name (e.g. server FQDN or YOUR name) []:my.com
Email Address []:webmaster@my.com
$ ls
jpllosa.tech.crt  jpllosa.tech.csr  jpllosa.tech.key  myCA.crt  myCA.key
  

Next, we sign our CSR with our root CA. The result will be, the CA-signed certificate will be in the jpllosa.tech.crt file. This is a working certificate. Create like so.

  
$ openssl x509 -req -CA myCA.crt -CAkey myCA.key -in jpllosa.tech.csr -out jpllosa.tech.crt -days 365 -CAcreateserial
Certificate request self-signature ok
subject=C = AU, ST = Some-State, L = City, O = Internet Widgets, OU = section, CN = jpllosa.tech, emailAddress = webmaster@jpllosa.tech
Enter pass phrase for myCA.key: password
$ ls
jpllosa.tech.crt  jpllosa.tech.csr  jpllosa.tech.key  myCA.crt  myCA.key
  

Even though we have created a working certificate, it will still be flagged by the browser. What we need is to add the subjectAltName. We need a SAN extension. X.509 certificates need information about the domain for which this particular certificate is issued for. To align with SAN extension stadards, we need to create a configuration text file then add the configuration to the certificate. Like so. How's your vi skills?

  
$ vi jpllosa.tech.ext
$ cat jpllosa.tech.ext
authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:false
subjectAltName = @alt_names
[alt_names]
DNS.1 = jpllosa.tech
$ openssl x509 -req -CA myCA.crt -CAkey myCA.key -in jpllosa.tech.csr -out jpllosa.tech.crt -days 365 -CAcreateserial -extfile jpllosa.tech.ext
Certificate request self-signature ok
subject=C = AU, ST = Some-State, L = City, O = Internet Widgets, OU = section, CN = jpllosa.tech, emailAddress = webmaster@jpllosa.tech
Enter pass phrase for myCA.key: password
$ ls
jpllosa.tech.crt  jpllosa.tech.csr  jpllosa.tech.ext  jpllosa.tech.key  myCA.crt  myCA.key
  

Finally, we got a working certificate that meets all the SAN requirements. SAN makes the certificates more secure and it allows the definition of several domains or IP addresses and we can use a single certificate across multiple domains. View the certificate like so. Spot the important bit, Subject Alternative Name.

  
$ openssl x509 -text -noout -in jpllosa.tech.crt
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            78:db:cd:5a:18:8d:46:0f:74:55:16:86:dc:f2:74:95:1b:c4:58:0e
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = AU, ST = Some-State, L = City, O = Internet Widgets, OU = Section, CN = my.com, emailAddress = webmaster@my.com
        Validity
            Not Before: Oct  5 16:41:02 2024 GMT
            Not After : Oct  5 16:41:02 2025 GMT
        Subject: C = AU, ST = Some-State, L = City, O = Internet Widgets, OU = section, CN = jpllosa.tech, emailAddress = webmaster@jpllosa.tech
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:ac:70:3c:57:da:fc:f4:b4:f6:4f:f4:3d:64:9f:
                    27:ca:09:d9:b6:4e:4d:89:2f:db:4b:6e:7c:18:4b:
                    af:5c:b4:80:cc:42:0a:cd:1e:15:1e:2d:be:71:e4:
                    6c:59:40:82:c5:ac:29:3b:fa:51:0b:6b:20:11:e5:
                    7d:1e:92:f7:e9:9d:f4:15:31:47:64:ec:1b:a5:14:
                    00:6e:c0:98:76:be:71:9d:c6:97:14:47:aa:30:b1:
                    ef:c4:b4:6b:b0:31:22:25:65:21:ab:35:21:ac:7b:
                    6a:f7:c0:78:d2:90:7d:33:d0:3c:dc:db:21:8e:75:
                    ff:04:83:bd:e6:9a:5d:79:70:a2:59:21:ff:51:20:
                    ea:74:d1:78:89:61:49:f6:6c:87:85:e2:0f:0c:f7:
                    b4:be:2b:79:88:28:fc:f7:50:ef:c1:e6:63:3e:a4:
                    0e:3c:71:18:97:55:5c:76:18:80:67:af:84:0a:16:
                    98:79:aa:00:00:77:a4:1b:97:bd:9c:41:50:13:89:
                    0c:63:29:51:84:7f:95:67:b7:f0:94:2b:b4:bb:50:
                    5e:6f:66:d1:06:4a:97:d6:3a:ac:6e:90:59:22:2c:
                    d3:09:a1:4b:e7:a1:3c:96:f6:b5:9c:25:5f:5b:cb:
                    be:a5:41:11:da:dc:a5:1b:cd:86:4d:a1:bd:44:1c:
                    44:c5
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Authority Key Identifier:
                17:B0:3C:87:D6:06:3C:54:21:F9:0B:8D:94:46:EF:F9:FA:A9:5C:8C
            X509v3 Basic Constraints:
                CA:FALSE
            X509v3 Subject Alternative Name:
                DNS:jpllosa.tech
            X509v3 Subject Key Identifier:
                93:B7:A9:CB:E3:56:A7:38:C1:8A:E6:6E:0A:0B:4B:4C:FE:1A:B7:FA
    Signature Algorithm: sha256With
...snipped...
  

Configure Spring Boot with PEM Certificates for TLS/SSL Communication

And now the easy part. Thanks to Spring Boot. All we have to do is add some properties on the application.properties file to enable SSL communication. Update the properties file like so.

  
server.port=8443
server.ssl.certificate=classpath:jpllosa.tech.crt
server.ssl.certificate-private-key=classpath:jpllosa.tech.key
  

Place jpllosa.tech.crt and jpllosa.tech.key under /src/main/resources so it can be picked up in the classpath. Try running the web app and go to the site. What do you see? Do you see something like below?

Our HTTPS site is still getting a warning. You could advance and accept the risk but what we want is for the browser to trust the site.

Browser Configuration

We need to import the root CA into the browser so our site can be trusted. These steps are specific to Firefox. Go to Settings > Privacy & Security > View Certificates.

Import myCA.crt and trust it to identify websites. You should be able to see Internet Widgets installed.

Let's try accessing the web app again. What does it say now? The warning is gone and you can go to the welcome and login pages.

Securing a Secure Web Application Summary

What a journey. To secure a Spring Boot app with PEM certificates for SSL communication was the easy bit. Just a few lines in the properties file. The arduous bit was the creation of the PEM certificates. Lastly we had to import our root CA to the browser so our site can be trusted. There you have it. We got to secure a secured web app. Was the requirement satisfied? . As usual, entire code is available at github.com/jpllosa/cert-web-app. Thank you for reading.

Go TLS MySQL Example

Nowadays, it is vital to keep data secure. If your connection to a database goes through an untrusted network, it is prudent to encrypt data going through the wire. Fortunately, we can make use of MySQL's internal SSL (Secure Sockets Layer) support to make the connection secure. Read on and learn how to secure a connection to MySQL in Golang.

SSL is a standard technology for securing internet connection by encrypting data sent between a client and server. SSL and TLS (Transport Layer Security) are sometimes used interchangeably but TLS is an updated, more secure version of SSL. TLS fixes existing SSL vulnerabilities.

Creating SSL Certificates

The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. On my Windows 10 machine, I'm using OpenSSL 3.1.2 1 Aug 2023 to generate the below certificates and keys. Create a clean directory and generate the certificates and keys (e.g. mkdir pems).

Create CA certificate

1. openssl genrsa 2048 > my-ca-key.pem
2. openssl req -new -x509 -nodes -days 3600 -key my-ca-key.pem -out my-ca.pem -addext "subjectAltName = DNS:localhost, IP:127.0.0.1"

$ openssl req -new -x509 -nodes -days 3600 -key my-ca-key.pem -out my-ca.pem -addext "subjectAltName = DNS:localhost, IP:127.0.0.1"
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:CA
Email Address []:

Check the contents of the certificate, openssl x509 -in my-ca.pem -text.

$ openssl x509 -in my-ca.pem -text
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            59:76:52:41:c9:d8:7a:d2:51:2a:05:3a:a6:f9:d8:3e:9d:a3:3d:a3
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = CA
        Validity
            Not Before: Nov  5 09:49:14 2023 GMT
            Not After : Sep 13 09:49:14 2033 GMT
        Subject: C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = CA
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:a8:cc:c5:14:d9:ef:90:07:43:81:b1:80:f7:42:
                    ...snipped...
                    16:75:45:05:69:5a:73:24:b3:f2:93:cb:5f:3b:8f:
                    31:15
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Key Identifier:
                B6:1A:8E:CA:32:F7:AF:A9:35:EF:27:5F:BF:DE:BA:A2:4B:66:F4:39
            X509v3 Authority Key Identifier:
                B6:1A:8E:CA:32:F7:AF:A9:35:EF:27:5F:BF:DE:BA:A2:4B:66:F4:39
            X509v3 Basic Constraints:
                CA:TRUE
            X509v3 Subject Alternative Name:
                DNS:localhost, IP Address:127.0.0.1
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        04:7a:75:59:80:fb:85:58:76:4a:c8:4d:69:d9:d3:72:42:fd:
		...snipped...

Create server certificate, key, and sign it. Create extfile.cnf

1. openssl req -newkey rsa:2048 -nodes -keyout my-server-key.pem -out my-server-req.pem
2. openssl rsa -traditional -in my-server-key.pem -out my-server-key.pem
3. openssl x509 -req -in my-server-req.pem -days 3600 -CA my-ca.pem -CAkey my-ca-key.pem -set_serial 01 -out my-server-cert.pem -extfile extfile.cnf

In this example, I was using MySQL v5.7.21 which did not support the PKCS #8 format for the key so I had to add the -traditional option to make the key in PKCS #1 format. On a later version of MySQL, I didn't need to use the -traditional option (e.g. MySQL v5.7.33). Simply put, for PKCS #1 (Public-Key Cryptography Standard), your key would start with something like --- BEGIN RSA PRIVATE KEY --- and end with --- END RSA PRIVATE KEY ---. For PKCS #8, it's --- BEGIN PRIVATE KEY --- and end with --- END PRIVATE KEY ---. Please google it for more details.

$ openssl req -newkey rsa:2048 -nodes -keyout my-server-key.pem -out my-server-req.pem
.......+.+..............+......+...+..........+...........+....+...+...+.....+.......+..+.+.....+.+.....+......+....+......+..+......+....+....................+.......+.........+..+++++++++++++++++++++++++++++++++++++++*.+.+............+..+...+...+.+...+.....+..................+.........+.+.....+...+.+++++++++++++++++++++++++++++++++++++++*...+.....+....+.....+....+.....+.+..............+.......+...+...+..............+.+..+...............+...+.......+.....+......+.......+...+..+...+.........+..........+.....+.+..............+.......+........................+..+...+....+..+.+........+....+..+.+...+...............+......+........+.......+...........+...+....+...+...+............+..+...+...+.......+........+.+......+............+..+.+.....+....+.....+...+.+..+..................................+......+..+...+....+........+...+.......+........+...................+...........+...+.........+...+.......+...+..+......+.+.....+.+..............+......+.............+.....+.........+............+..........+.........+.........+......+...........+.+..+.+.....+.......+.........+......+...+.....+......+.+..+...+.......+......+.....+.......+.....+..........+..+............+...+.+..+....+......+..+.............+...+.....+......+..........+.........+.........+.....+...+.......+......+.....+......+........................+.+..............+....+..+..................+...+...+.+......+..+.+..+.........+.+..............+...+...+...+.......+........+.+......+.....+...+....+........+....+..................+...+...+..+.......+...+..................+.....+....+.....+...+....+.....+....+..+.......+............+.....+.........+.+......+...+......+...+...+..+.............+........+....+..+.............+..+.............+..+....+.........+..+.........+....+...+..+...+...+.+......+..+.+...........+......+....+...........+...+.......++++++
...+...+..+............+...+..........+...+...........+.+..+............+.+...+...........+.+..+..........+...........+....+.....+.+......+++++++++++++++++++++++++++++++++++++++*........+...+..+...+....+.....+....+......+........+.......+.....+...................+++++++++++++++++++++++++++++++++++++++*........+.......+......+.....+.+.....++++++
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:Server
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

$ openssl rsa -traditional -in my-server-key.pem -out my-server-key.pem
writing RSA key

Create extfile.cnf with the following contents.

subjectAltName = DNS:localhost, IP:127.0.0.1

$ openssl x509 -req -in my-server-req.pem -days 3600 -CA my-ca.pem -CAkey my-ca-key.pem -set_serial 01 -out my-server-cert.pem -extfile extfile.cnf
Certificate request self-signature ok
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = Server

Check the contents of the certificate, openssl x509 -in my-server-cert.pem -text.

$ openssl x509 -in my-server-cert.pem -text
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 1 (0x1)
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = CA
        Validity
            Not Before: Nov  5 09:54:01 2023 GMT
            Not After : Sep 13 09:54:01 2033 GMT
        Subject: C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = Server
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:bd:36:70:95:7e:6e:e6:09:5d:34:f8:42:8d:ef:
                    ...snipped...
                    61:0e:10:12:80:8b:90:3b:e2:d2:d7:e0:c8:ba:c0:
                    ea:97
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Alternative Name:
                DNS:localhost, IP Address:127.0.0.1
            X509v3 Subject Key Identifier:
                EA:77:57:2D:B2:62:64:A5:FB:7C:71:7D:1E:00:C9:8D:71:6F:91:68
            X509v3 Authority Key Identifier:
                B6:1A:8E:CA:32:F7:AF:A9:35:EF:27:5F:BF:DE:BA:A2:4B:66:F4:39
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        a2:c4:99:58:a5:72:ad:3f:79:b4:e6:3e:e5:11:fd:8f:fd:9a:
		...snipped...

Create client certificate, key, and sign it.

1. openssl req -newkey rsa:2048 -nodes -keyout my-client-key.pem -out my-client-req.pem
2. openssl rsa -traditional -in my-client-key.pem -out my-client-key.pem
3. openssl x509 -req -in my-client-req.pem -days 3600 -CA my-ca.pem -CAkey my-ca-key.pem -set_serial 01 -out my-client-cert.pem

$ openssl req -newkey rsa:2048 -nodes -keyout my-client-key.pem -out my-client-req.pem
.....+....+...+......+..+++++++++++++++++++++++++++++++++++++++*...........+...+.+......+.................+.+..+++++++++++++++++++++++++++++++++++++++*..+....+..+.............+.....+......+.+..+.......+......+...............+........+.+.....+.+...+............+..+................+..............+.......+......+.........+.....+...+.......+...+..+.........+.+.....+....+.........+..+...+.......+........+...............+...+...+.+......+........+.+.....+.............+...+..+......+.......+.....+...+.......+..+.......+...............+...........+.......+.....+.+.................+....+........+.............+...+..+...+............+.......+..+......+...+....+...+..+......+.+.....+..........+........+.+..+.......+........+...+......+......+.............++++++
.....+...+.+..+++++++++++++++++++++++++++++++++++++++*...+....+...........+.........+.+.....+.........+....+..+......+++++++++++++++++++++++++++++++++++++++*.+...+.....+......+.............+..+.+..............+.+...+...........+...+...+....+.....+...+.......+...+........+...+.+...+............+...........++++++
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:Client
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

$ openssl rsa -traditional -in my-client-key.pem -out my-client-key.pem
writing RSA key

$ openssl x509 -req -in my-client-req.pem -days 3600 -CA my-ca.pem -CAkey my-ca-key.pem -set_serial 01 -out my-client-cert.pem
Certificate request self-signature ok
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = Client

Check the contents of the certificate, openssl x509 -in my-client-cert.pem -text.

$ openssl x509 -in my-client-cert.pem -text
Certificate:
    Data:
        Version: 1 (0x0)
        Serial Number: 1 (0x1)
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = CA
        Validity
            Not Before: Nov  5 09:59:50 2023 GMT
            Not After : Sep 13 09:59:50 2033 GMT
        Subject: C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = Client
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:a9:f1:74:07:eb:fa:96:d1:0f:c6:f0:32:c6:c6:
					...snipped...

At the end of all those commands, you should have something like below in your pems folder. Verify your certificates like below.

$ ls
extfile.cnf    my-ca.pem           my-client-key.pem  my-server-cert.pem  my-server-req.pem
my-ca-key.pem  my-client-cert.pem  my-client-req.pem  my-server-key.pem

$ openssl verify -CAfile my-ca.pem my-server-cert.pem my-client-cert.pem
my-server-cert.pem: OK
my-client-cert.pem: OK

Hooking Up the PEMs to MySQL

Copy my-ca.pem, my-server-cert.pem, and my-server-key.pem to your MySQL data directory. Run MySQL like so mysqld --console --ssl-ca=my-ca.pem --ssl-cert=my-server-cert.pem --ssl-key=my-server-key.pem and you should have something like below. The below tells us that MySQL is able to read our CA certificate and we should be able to do SSL connections.

2023-11-05T22:14:14.749469Z 0 [Note] Plugin 'FEDERATED' is disabled.
2023-11-05T22:14:14.751171Z 0 [Note] InnoDB: Loading buffer pool(s) from D:\mysql-5.7.21-winx64\data\ib_buffer_pool
2023-11-05T22:14:15.205534Z 0 [Warning] CA certificate my-ca.pem is self signed.
2023-11-05T22:14:15.218988Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2023-11-05T22:14:15.219894Z 0 [Note] IPv6 is available.
2023-11-05T22:14:15.220471Z 0 [Note]   - '::' resolves to '::';
2023-11-05T22:14:15.224511Z 0 [Note] Server socket created on IP: '::'.
2023-11-05T22:14:15.301522Z 0 [Note] Event Scheduler: Loaded 0 events
2023-11-05T22:14:15.302306Z 0 [Note] mysqld: ready for connections.
Version: '5.7.21'  socket: ''  port: 3306  MySQL Community Server (GPL)
2023-11-05T22:14:15.512362Z 0 [Note] InnoDB: Buffer pool(s) load completed at 231105 22:14:15

You can also set SSL to be database wide. Just set the MySQL config file like below. For this exercise we'll try setting SSL connections per user at the moment. When you can successfully connect then you can easily move up to securing it database wide. I'd recommend on a production system to secure it database wide.

ssl_ca=my-ca.pem
ssl_cert=my-server-cert.pem
ssl_key=my-server-key.pem
require_secure_transport=ON

Trusting the Certificates

You may or may not have to do this bit. For MySQL v5.7.21 on my Windows 10 Pro 19045 build and Go v1.18.3, I didn't have to do this. But on my other Windows 10 machine running MySQL v5.7.33, Go v1.18.2, I had to make the target machine trust the self-signed certificates by manually importing it using Microsoft Management Console (MMC). Start command prompt as Administrator then run mmc. Google how to manually import self-signed certificates for more details. In summary, I had to manually import the certificates under Console Root > Certificates > Personal > Certificates.

Testing the Connection

For this example, I was on MySQL Workbench v6.3. I should be on v8 really but it's on my other machine. Anyway, add your PEM (Privacy Enhanced Mail) files in the appropriate boxes. PEM files contain the public certificate or may include the entire certificate chain including public key, private key and root certificates.

For a successful test connection, you should have something like below:

Secure the User

I'm utilizing my past article, Accessing MySQL using Go Example. Granting you followed that example, then you most likely have a username "golang" that you have used to connect to the database. Let us make golang do a secure connection. Run the SQL query to require SSL on golang

ALTER USER golang@localhost REQUIRE SSL;

To remove SSL on golang, run.

ALTER USER golang@localhost REQUIRE NONE;

Check what TLS version our MySQL v5.7.21 server supports. As you can see below, it only supports TLSv1 and TLSv1.1. Our Go crypto/tls library supports TLSv1.3 by default. We'll need to configure our connection to go down a couple of versions lower. Otherwise we'll have an error, something like this, tls: server selected unsupported protocol version 302.

SHOW GLOBAL VARIABLES LIKE 'tls_version';

Coding Time

I've made changes to the original connection implementation from Accessing MySQL using Go Example and I patterned the TLS connection from the MySQL Golang RegisterTLSConfig API documentation.

// ... code snipped...

rootCertPool := x509.NewCertPool()
pem, err := ioutil.ReadFile("../pems/my-ca.pem")
if err != nil {
	log.Fatalf("configuration: reading CA pem file: %v", err)
}
if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
	log.Fatalf("configuration: failed to append pem file: %v", err)
}
clientCert := make([]tls.Certificate, 0, 1)
certs, err := tls.LoadX509KeyPair("../pems/my-client-cert.pem", "../pems/my-client-key.pem")
if err != nil {
	log.Fatalf("configuration: failed to load key pair: %v", err)
}
clientCert = append(clientCert, certs)
mysql.RegisterTLSConfig("secure", &tls.Config{
	RootCAs:      rootCertPool,
	Certificates: clientCert,
	MinVersion:   tls.VersionTLS10, //without this defaults tls1.3 which not supported by our mysql
	MaxVersion:   tls.VersionTLS11,
})
cfg.TLSConfig = "secure"

// ... code snipped ...

fmt.Println("Securely connected!")

rows, err := db.Query("SELECT * FROM album")
if err != nil {
	fmt.Errorf("database query: %v", err)
}
defer rows.Close()

fmt.Printf("%2s %15s %15s %6s \n", "ID", "Title", "Artist", "Price")
for rows.Next() {
	var alb Album
	err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price)
	if err != nil {
		fmt.Errorf("row scan: %v", err)
	} else {
		fmt.Printf("%2d %15s %15s %6.2f \n", alb.ID, alb.Title, alb.Artist, alb.Price)
	}
}

The notable difference from the API documentation is the addition of the version support by our MySQL server. On another note, if you followed the Creating SSL Certificates and Keys Using openssl from MySQL's documentation, chances are, you didn't specify a subjectAltName. You'll possibly get an IP SANS error when connecting. I later found out that you can just add a server name in tls.Config and it should connect. Somelike below:

mysql.RegisterTLSConfig("secure", &tls.Config{
	RootCAs:      rootCertPool,
	Certificates: clientCert,
	MinVersion:   tls.VersionTLS10,
	MaxVersion:   tls.VersionTLS11,
	ServerName:   "InsertYourServerNameHere",
})

After we are connected, we query the database and print out the records in a neat table format. Running the code above in VS Code via launch.json, you should have something like below:

DAP server listening at: 127.0.0.1:53186
Type 'dlv help' for list of commands.
Securely connected!
ID           Title          Artist  Price
 1      Blue Train   John Coltrane  56.99 
 2     Giant Steps   John Coltrane  63.99 
 3            Jeru  Gerry Mulligan  17.99 
 4   Sarah Vaughan   Sarah Vaughan  34.98
Process 6508 has exited with status 0
Detaching

Go TLS MySQL Wrap Up

There you have it. Hope you enjoyed reading and trying out the example as much as I did. The complete project can be cloned from github.com/jpllosa/go-relational-database/tree/tls-config. This piece of code is under the tls-config branch.