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
- openssl genrsa 2048 > my-ca-key.pem
- 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
- openssl req -newkey rsa:2048 -nodes -keyout my-server-key.pem -out my-server-req.pem
- openssl rsa -traditional -in my-server-key.pem -out my-server-key.pem
- 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-traditionaloption to make the key in PKCS #1 format. On a later version of MySQL, I didn't need to use the-traditionaloption (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.
- openssl req -newkey rsa:2048 -nodes -keyout my-client-key.pem -out my-client-req.pem
- openssl rsa -traditional -in my-client-key.pem -out my-client-key.pem
- 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.
