Support for Optional TLS connections

[dveeden]

Issue description

On a large set of machines where:

• Most database servers support TLS and have a TLS certificate that is valid (CA, hostname, etc)
• Some database servers are running without TLS support
• Some database servers are running with require_secure_transport

And a go program that needs to connect to all of the servers (e.g. a monitoring application)
The program has a list with hostname, username and password for each server.

If it connects with tls=true or tls=skip-verify then this works for all servers except for those who don't support TLS.
If it connects without setting tls or by setting tls=false then it works for all servers except for those who run with require_secure_transport.

Example code

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
    // port 8011 runs with require_secure_transport
    // port 5724 runs with skip-ssl

	dsns := []string{
		"msandbox:msandbox@tcp([::1]:8011)/test",
		"msandbox:msandbox@tcp([::1]:8011)/test?tls=skip-verify",
		"msandbox:msandbox@tcp([::1]:5724)/test",
		"msandbox:msandbox@tcp([::1]:5724)/test?tls=skip-verify",
	}

	for _, dsn := range dsns {
		fmt.Printf("Trying %s\n", dsn)

		db, err := sql.Open("mysql", dsn)
		if err != nil {
			panic(err.Error())
		}
		defer db.Close()

		err = db.Ping()
		if err != nil {
			fmt.Printf("Result: %s\n", err.Error())
		} else {
			print("Result: ok\n")
		}
	}
}

Output:

Trying msandbox:msandbox@tcp([::1]:8011)/test
Result: Error 3159: Connections using insecure transport are prohibited while --require_secure_transport=ON.
Trying msandbox:msandbox@tcp([::1]:8011)/test?tls=skip-verify
Result: ok
Trying msandbox:msandbox@tcp([::1]:5724)/test
Result: ok
Trying msandbox:msandbox@tcp([::1]:5724)/test?tls=skip-verify
skip-verifyResult: TLS requested but server does not support TLS

Configuration

Driver version (or git SHA):
6be42e0

Go version: run go version in your console
go version go1.11.2 linux/amd64

Server version: E.g. MySQL 5.6, MariaDB 10.0.20
MySQL 8.0.11, MySQL 5.7.24

Server OS: E.g. Debian 8.1 (Jessie), Windows 10
Fedora 29 (but target is CentOS 7)

Possible solutions

1. Add a tls=optional option which results in TLS when the server has the SSL flag set and in clear-text when the server doesn't have this flag set.
2. Catch Error 3159 and re-connect with tls=true or tls=skip-verify in the driver or application.
3. Catch the ErrNoTLS error in the application and re-connect with tls=false in the application.

iirc option 2 is what https://github.com/github/orchestrator does at the moment.
Note that option 2 causes most connections to be clear-text and option 3 causes most connections to use TLS.

[methane]

I don't think we should support such a special use case.

You can try tls=true, and if it failed, retry tls=false.

[methane]

FYI, "PREFERRED" is used for official client. (And it's default for now)
https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html#using-encrypted-connections-client-side-configuration
https://dev.mysql.com/doc/refman/5.7/en/encrypted-connection-options.html#option_general_ssl-mode

[dveeden]

Yes this is what libmysqlclient (5.7+) has and how it maps to the go driver:

libmysqlclient	go-sql-driver	description
DISABLED	tls=false	Never use TLS
PREFERRED	tls=optional ( #900 )	Use TLS when advertised by the server
REQUIRED	tls=skip-verify	Use TLS, but don't check against a CA (but do check 'Not After' etc)
VERIFY_CA	Not available?	Verify CA, but ignore hostname mismatch
VERIFY_IDENTITY	tls=name	proper TLS

One of the reasons for making PREFERRED the default is the caching_sha2 plugin in MySQL 8.0 which requires a secure connection (TLS or a RSA keypair) for the initial authentication. This plugin is the default in 8.0.
Using caching_sha2 with anything else than VERIFY_IDENTITIY is something I consider insecure.

Note that MariaDB doesn't have an ssl-mode setting, but uses ssl and ssl-verify-server-cert instead. This is similar to what MySQL 5.6 did, but the ssl option now means it requires TLS instead of optional TLS, this is because CVE-2015-3152

[methane]

I don't like "optional". How about "preferred" or "auto"?

[julienschmidt]

Definitely not "auto" as there isn't anything automatic here. Let's go for "preferred" then.

[sszj987]

VERIFY_CA	Not available?	Verify CA, but ignore hostname mismatch

I want to know if this option supports？
thx

[dveeden]

VERIFY_CA Not available? Verify CA, but ignore hostname mismatch

I want to know if this option supports？
thx

I don't think it is. Best to use certificates with the right SubjectAlternativeName's to match your host. If you really can't do that then use tls=skip-verify.

[chusteven]

I don't think it is. Best to use certificates with the right SubjectAlternativeName's to match your host. If you really can't do that then use tls=skip-verify.

Hello! Sorry to treat this issue as a help desk, but I'm wondering how one could use this library + the tls.Config struct to get VERIFY_CA but not VERIFY_IDENTITY?

I have code that currently looks like below but am not sure if it does VERIFY_CA, or, if it effectively is DISABLED.

var rootCA = []byte(`-----BEGIN CERTIFICATE-----
...
`)

func init() {
	caCertPool := x509.NewCertPool()
	caCertPool.AppendCertsFromPEM(rootCA)
	tlsConnConfig := &tls.Config{RootCAs: caCertPool, InsecureSkipVerify: true}
	mysql.RegisterTLSConfig("custom-config", tlsConnConfig)
}

I ask because the tls.Config documentation reads as below, but all I need (right now, anyway) is to verify the certificate chain but not the host name. I vaguely suspect that VerifyConnection or VerifyPeerCertificate could help out but am pretty new to Go so not so sure 😅

	// InsecureSkipVerify controls whether a client verifies the server's
	// certificate chain and host name. If InsecureSkipVerify is true, crypto/tls
	// accepts any certificate presented by the server and any host name in that
	// certificate. In this mode, TLS is susceptible to machine-in-the-middle
	// attacks unless custom verification is used. This should be used only for
	// testing or in combination with VerifyConnection or VerifyPeerCertificate.
	InsecureSkipVerify [bool](https://pkg.go.dev/builtin#bool)