Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Sunday, November 19, 2023

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.

at No comments:
Labels: , , , , ,

Tuesday, July 5, 2022

Accessing MySQL using Go Example

In this example, I will be accessing a relational database (MySQL) using the Go programming language. I have followed the tutorial from the official Go website. Unfortunately, on my first try, I wasn't able to connect to my database. I'm writing this to supplement what was written on the Go website.

First of all, I will not be talking about setup and installation of MySQL and Go. I am assuming that the reader has sufficient knowledge in setting up MySQL and Go. I'm using Visual Studio Code along with it's Go extensions to edit my code.

MySQL setup

Create a database called recordings. In the recordings schema, create the table and values as seen below. Would be easier if you have MySQL Workbench but you can also go command line or however you like. Don't forget to use recordings. 


DROP TABLE IF EXISTS album;
CREATE TABLE album (
  id         INT AUTO_INCREMENT NOT NULL,
  title      VARCHAR(128) NOT NULL,
  artist     VARCHAR(255) NOT NULL,
  price      DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO album
  (title, artist, price)
VALUES
  ('Blue Train', 'John Coltrane', 56.99),
  ('Giant Steps', 'John Coltrane', 63.99),
  ('Jeru', 'Gerry Mulligan', 17.99),
  ('Sarah Vaughan', 'Sarah Vaughan', 34.98);

Next is to create your username and password. I have created "golang" as my username and password in MySQL.

main.go


package main

import (
	"database/sql"
	"fmt"
	"log"

	"github.com/go-sql-driver/mysql"
)

var db *sql.DB

func main() {
	// Capture connection properties.
	cfg := mysql.Config{
		User:   os.Getenv("DBUSER"),
		Passwd: os.Getenv("DBPASS"),
		Net:    "tcp",
		Addr:                 "127.0.0.1:3306",
		DBName:               "recordings",
		AllowNativePasswords: true, // if not included, mysql native password authentication error is generated
	}
	// Get a database handle.
	var err error
	db, err = sql.Open("mysql", cfg.FormatDSN())
	if err != nil {
		log.Fatal(err)
	}

	pingErr := db.Ping()
	if pingErr != nil {
		log.Fatal(pingErr)
	}
	fmt.Println("Connected!")
}

The above code is the same as the one in the Go Dev Tutorial. Except for one line, AllowNativePasswords: true. This line was missing in the Go Dev Tutorial and caused an error on my first go run .. This led me to some googling around. I found interesting stuff and I've collated them here. Hopefully it will help others who are starting to learn the Go languange. By the way, this should show Connected!.

Environment Variables

I'm on a Windows 10 machine so to set an environment variable on the command line, do set DBUSER=golang. If you don't want to use the command line because you are using Visual Studio Code. You'd want to run the code on the VSCode terminal, do $env:DBUSER = "golang". That's how to set an environment variable in powershell.

Addr

So I've tried this code using two MySQL databases. On MySQL 5.7.33, Addr: "127.0.0.1:3306" works fine. On MySQL 5.6.43, Addr: "localhost:3306" works fine. But if you swap the hostnames, you get and access denied for user error.

AllowNativePasswords

If you don't want to use AllowNativePasswords then you could replace cfg := mysql.Config() with cfg := mysql.NewConfig(). Something like below: 


cfg := mysql.NewConfig()
cfg.User = os.Getenv("DBUSER")
cfg.Passwd = os.Getenv("DBPASS")
cfg.Net = "tcp"
cfg.Addr = "127.0.0.1:3306"
cfg.DBName = "recordings"

There you have it. Now you don't have to do too much googling around. The complete project can be cloned from github.com/jpllosa/go-relational-database.

at No comments:
Labels: ,
Subscribe to: Posts (Atom)