| // Copyright 2015 The Vanadium Authors. All rights reserved. |
| // Use of this source code is governed by a BSD-style |
| // license that can be found in the LICENSE file. |
| |
| // Package dbutil implements utilities for opening and configuring connections |
| // to MySQL-like databases, with optional TLS support. |
| // |
| // Functions in this file are not thread-safe. However, the returned *sql.DB is. |
| // Sane defaults are assumed: utf8mb4 encoding, UTC timezone, parsing date/time |
| // into time.Time. |
| package dbutil |
| |
| import ( |
| "crypto/sha256" |
| "crypto/tls" |
| "crypto/x509" |
| "database/sql" |
| "encoding/hex" |
| "encoding/json" |
| "fmt" |
| "io/ioutil" |
| "net/url" |
| "path/filepath" |
| "strings" |
| |
| "github.com/go-sql-driver/mysql" |
| ) |
| |
| // SQL statement suffix to be appended when creating tables. |
| const SqlCreateTableSuffix = "CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" |
| |
| // Description of the SQL configuration file format. |
| const SqlConfigFileDescription = `File must contain a JSON object of the following form: |
| { |
| "dataSourceName": "[username[:password]@][protocol[(address)]]/dbname", (the connection string required by go-sql-driver; database name must be specified, query parameters are not supported) |
| "tlsDisable": "false|true", (defaults to false; if set to true, uses an unencrypted connection; otherwise, the following fields are mandatory) |
| "tlsServerName": "serverName", (the domain name of the SQL server for TLS) |
| "rootCertPath": "[/]path/server-ca.pem", (the root certificate of the SQL server for TLS) |
| "clientCertPath": "[/]path/client-cert.pem", (the client certificate for TLS) |
| "clientKeyPath": "[/]path/client-key.pem" (the client private key for TLS) |
| } |
| Paths must be either absolute or relative to the configuration file directory.` |
| |
| // SqlConfig holds the fields needed to connect to a SQL instance and to |
| // configure TLS encryption of the information sent over the wire. It must be |
| // activated via Activate() before use. |
| type SqlConfig struct { |
| // DataSourceName is the connection string as required by go-sql-driver: |
| // "[username[:password]@][protocol[(address)]]/dbname"; |
| // database name must be specified, query parameters are not supported. |
| DataSourceName string `json:"dataSourceName"` |
| // TLSDisable, if set to true, uses an unencrypted connection; |
| // otherwise, the following fields are mandatory. |
| TLSDisable bool `json:"tlsDisable"` |
| // TLSServerName is the domain name of the SQL server for TLS. |
| TLSServerName string `json:"tlsServerName"` |
| // RootCertPath is the root certificate of the SQL server for TLS. |
| RootCertPath string `json:"rootCertPath"` |
| // ClientCertPath is the client certificate for TLS. |
| ClientCertPath string `json:"clientCertPath"` |
| // ClientKeyPath is the client private key for TLS. |
| ClientKeyPath string `json:"clientKeyPath"` |
| } |
| |
| // ActiveSqlConfig represents a SQL configuration that has been activated |
| // by registering the TLS configuration (if applicable). It can be used for |
| // opening SQL database connections. |
| type ActiveSqlConfig struct { |
| // cfg is a copy of the SqlConfig that was activated. |
| cfg *SqlConfig |
| // tlsConfigIdentifier is the identifier under which the TLS configuration |
| // is registered with go-sql-driver. It is computed as a secure hash of the |
| // SqlConfig after resolving any relative paths. |
| tlsConfigIdentifier string |
| } |
| |
| // Parses the SQL configuration file pointed to by sqlConfigFile (format |
| // described in SqlConfigFileDescription; also see links below). |
| // https://github.com/go-sql-driver/mysql/#dsn-data-source-name |
| // https://github.com/go-sql-driver/mysql/#tls |
| func ParseSqlConfigFromFile(sqlConfigFile string) (*SqlConfig, error) { |
| configJSON, err := ioutil.ReadFile(sqlConfigFile) |
| if err != nil { |
| return nil, fmt.Errorf("failed reading SQL config file %q: %v", sqlConfigFile, err) |
| } |
| var config SqlConfig |
| if err = json.Unmarshal(configJSON, &config); err != nil { |
| // TODO(ivanpi): Parsing errors might leak the SQL password into error |
| // logs, depending on standard library implementation. |
| return nil, fmt.Errorf("failed parsing SQL config file %q: %v", sqlConfigFile, err) |
| } |
| return &config, nil |
| } |
| |
| // Activates the SQL configuration by registering the TLS configuration with |
| // go-mysql-driver (if TLSDisable is not set). |
| // Certificate paths from SqlConfig that aren't absolute are interpreted relative |
| // to certBaseDir. |
| // For more information see https://github.com/go-sql-driver/mysql/#tls |
| func (sc *SqlConfig) Activate(certBaseDir string) (*ActiveSqlConfig, error) { |
| if sc.TLSDisable { |
| return &ActiveSqlConfig{ |
| cfg: sc.normalizePaths(""), |
| }, nil |
| } |
| cbdAbs, err := filepath.Abs(certBaseDir) |
| if err != nil { |
| return nil, fmt.Errorf("failed resolving certificate base directory %q: %v", certBaseDir, err) |
| } |
| scn := sc.normalizePaths(cbdAbs) |
| configId := scn.hash() |
| if err = registerSqlTLSConfig(scn, configId); err != nil { |
| return nil, fmt.Errorf("failed registering TLS config: %v", err) |
| } |
| return &ActiveSqlConfig{ |
| cfg: scn, |
| tlsConfigIdentifier: configId, |
| }, nil |
| } |
| |
| // Convenience function to parse and activate the SQL configuration file. |
| // Certificate paths that aren't absolute are interpreted relative to the |
| // directory containing sqlConfigFile. |
| func ActivateSqlConfigFromFile(sqlConfigFile string) (*ActiveSqlConfig, error) { |
| cfg, err := ParseSqlConfigFromFile(sqlConfigFile) |
| if err != nil { |
| return nil, err |
| } |
| activeCfg, err := cfg.Activate(filepath.Dir(sqlConfigFile)) |
| if err != nil { |
| return nil, fmt.Errorf("failed activating SQL config from file %q: %v", sqlConfigFile, err) |
| } |
| return activeCfg, nil |
| } |
| |
| // Opens a connection to the SQL database using the provided configuration. |
| // Sets the specified transaction isolation (see link below). |
| // https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_tx_isolation |
| func (sqlConfig *ActiveSqlConfig) NewSqlDBConn(txIsolation string) (*sql.DB, error) { |
| return openSqlDBConn(configureSqlDBConn(sqlConfig, txIsolation)) |
| } |
| |
| // Convenience function to parse and activate the configuration file and open |
| // a connection to the SQL database. If multiple connections with the same |
| // configuration are needed, a single ActivateSqlConfigFromFile() and multiple |
| // NewSqlDbConn() calls are recommended instead. |
| func NewSqlDBConnFromFile(sqlConfigFile, txIsolation string) (*sql.DB, error) { |
| config, err := ActivateSqlConfigFromFile(sqlConfigFile) |
| if err != nil { |
| return nil, err |
| } |
| return config.NewSqlDBConn(txIsolation) |
| } |
| |
| func configureSqlDBConn(sqlConfig *ActiveSqlConfig, txIsolation string) string { |
| params := url.Values{} |
| // Setting charset is unneccessary when collation is set, according to |
| // https://github.com/go-sql-driver/mysql/#charset |
| params.Set("collation", "utf8mb4_general_ci") |
| // Maps SQL date/time values into time.Time instead of strings. |
| params.Set("parseTime", "true") |
| params.Set("loc", "UTC") |
| params.Set("time_zone", "'+00:00'") |
| if !sqlConfig.cfg.TLSDisable { |
| params.Set("tls", sqlConfig.tlsConfigIdentifier) |
| } |
| params.Set("tx_isolation", "'"+txIsolation+"'") |
| return sqlConfig.cfg.DataSourceName + "?" + params.Encode() |
| } |
| |
| func openSqlDBConn(dataSrcName string) (*sql.DB, error) { |
| // Prevent leaking the SQL password into error logs. |
| sanitizedDSN := dataSrcName[strings.LastIndex(dataSrcName, "@")+1:] |
| db, err := sql.Open("mysql", dataSrcName) |
| if err != nil { |
| return nil, fmt.Errorf("failed opening database connection at %q: %v", sanitizedDSN, err) |
| } |
| if err := db.Ping(); err != nil { |
| return nil, fmt.Errorf("failed connecting to database at %q: %v", sanitizedDSN, err) |
| } |
| return db, nil |
| } |
| |
| // registerSqlTLSConfig sets up the SQL connection to use TLS encryption |
| // and registers the configuration under configId. |
| // For more information see https://github.com/go-sql-driver/mysql/#tls |
| func registerSqlTLSConfig(cfg *SqlConfig, configId string) error { |
| rootCertPool := x509.NewCertPool() |
| pem, err := ioutil.ReadFile(cfg.RootCertPath) |
| if err != nil { |
| return fmt.Errorf("failed reading root certificate: %v", err) |
| } |
| if ok := rootCertPool.AppendCertsFromPEM(pem); !ok { |
| return fmt.Errorf("failed to append PEM to cert pool") |
| } |
| ckpair, err := tls.LoadX509KeyPair(cfg.ClientCertPath, cfg.ClientKeyPath) |
| if err != nil { |
| return fmt.Errorf("failed loading client key pair: %v", err) |
| } |
| clientCert := []tls.Certificate{ckpair} |
| return mysql.RegisterTLSConfig(configId, &tls.Config{ |
| RootCAs: rootCertPool, |
| Certificates: clientCert, |
| ServerName: cfg.TLSServerName, |
| // SSLv3 is more vulnerable than TLSv1.0, see https://en.wikipedia.org/wiki/POODLE |
| // TODO(ivanpi): Increase when Cloud SQL starts supporting higher TLS versions. |
| MinVersion: tls.VersionTLS10, |
| ClientAuth: tls.RequireAndVerifyClientCert, |
| }) |
| } |
| |
| // Computes a secure hash of the SqlConfig. Paths are canonicalized before |
| // hashing. |
| func (sc *SqlConfig) hash() string { |
| scn := sc.normalizePaths("") |
| fieldsToHash := []interface{}{ |
| scn.DataSourceName, scn.TLSDisable, scn.TLSServerName, |
| scn.RootCertPath, scn.ClientCertPath, scn.ClientKeyPath, |
| } |
| hashAcc := make([]byte, 0, len(fieldsToHash)*sha256.Size) |
| for _, field := range fieldsToHash { |
| fieldHash := sha256.Sum256([]byte(fmt.Sprintf("%v", field))) |
| hashAcc = append(hashAcc, fieldHash[:]...) |
| } |
| structHash := sha256.Sum256(hashAcc) |
| return hex.EncodeToString(structHash[:]) |
| } |
| |
| // Returns a copy of the SqlConfig with certificate paths canonicalized and |
| // resolved relative to certBaseDir. Blank paths remain blank. |
| func (sc *SqlConfig) normalizePaths(certBaseDir string) *SqlConfig { |
| scn := *sc |
| for _, path := range []*string{&scn.RootCertPath, &scn.ClientCertPath, &scn.ClientKeyPath} { |
| *path = normalizePath(*path, certBaseDir) |
| } |
| return &scn |
| } |
| |
| // If path is not absolute, resolves path relative to baseDir. Otherwise, |
| // canonicalizes path. Blank paths are not resolved or canonicalized. |
| func normalizePath(path, baseDir string) string { |
| if strings.TrimSpace(path) == "" { |
| return "" |
| } |
| if filepath.IsAbs(path) { |
| return filepath.Clean(path) |
| } |
| return filepath.Join(baseDir, path) |
| } |