database.go

Documentation: github.com/letsencrypt/boulder/sa

     1  package sa
     2  
     3  import (
     4  	"database/sql"
     5  	"fmt"
     6  	"time"
     7  
     8  	"github.com/go-sql-driver/mysql"
     9  	"github.com/letsencrypt/borp"
    10  	"github.com/prometheus/client_golang/prometheus"
    11  
    12  	"github.com/letsencrypt/boulder/cmd"
    13  	"github.com/letsencrypt/boulder/core"
    14  	boulderDB "github.com/letsencrypt/boulder/db"
    15  	blog "github.com/letsencrypt/boulder/log"
    16  )
    17  
    18  // DbSettings contains settings for the database/sql driver. The zero
    19  // value of each field means use the default setting from database/sql.
    20  // ConnMaxIdleTime and ConnMaxLifetime should be set lower than their
    21  // mariab counterparts interactive_timeout and wait_timeout.
    22  type DbSettings struct {
    23  	// MaxOpenConns sets the maximum number of open connections to the
    24  	// database. If MaxIdleConns is greater than 0 and MaxOpenConns is
    25  	// less than MaxIdleConns, then MaxIdleConns will be reduced to
    26  	// match the new MaxOpenConns limit. If n < 0, then there is no
    27  	// limit on the number of open connections.
    28  	MaxOpenConns int
    29  
    30  	// MaxIdleConns sets the maximum number of connections in the idle
    31  	// connection pool. If MaxOpenConns is greater than 0 but less than
    32  	// MaxIdleConns, then MaxIdleConns will be reduced to match the
    33  	// MaxOpenConns limit. If n < 0, no idle connections are retained.
    34  	MaxIdleConns int
    35  
    36  	// ConnMaxLifetime sets the maximum amount of time a connection may
    37  	// be reused. Expired connections may be closed lazily before reuse.
    38  	// If d < 0, connections are not closed due to a connection's age.
    39  	ConnMaxLifetime time.Duration
    40  
    41  	// ConnMaxIdleTime sets the maximum amount of time a connection may
    42  	// be idle. Expired connections may be closed lazily before reuse.
    43  	// If d < 0, connections are not closed due to a connection's idle
    44  	// time.
    45  	ConnMaxIdleTime time.Duration
    46  }
    47  
    48  // InitWrappedDb constructs a wrapped borp mapping object with the provided
    49  // settings. If scope is non-nil, Prometheus metrics will be exported. If logger
    50  // is non-nil, SQL debug-level logging will be enabled. The only required parameter
    51  // is config.
    52  func InitWrappedDb(config cmd.DBConfig, scope prometheus.Registerer, logger blog.Logger) (*boulderDB.WrappedMap, error) {
    53  	url, err := config.URL()
    54  	if err != nil {
    55  		return nil, fmt.Errorf("failed to load DBConnect URL: %s", err)
    56  	}
    57  
    58  	settings := DbSettings{
    59  		MaxOpenConns:    config.MaxOpenConns,
    60  		MaxIdleConns:    config.MaxIdleConns,
    61  		ConnMaxLifetime: config.ConnMaxLifetime.Duration,
    62  		ConnMaxIdleTime: config.ConnMaxIdleTime.Duration,
    63  	}
    64  
    65  	mysqlConfig, err := mysql.ParseDSN(url)
    66  	if err != nil {
    67  		return nil, err
    68  	}
    69  
    70  	dbMap, err := newDbMapFromMySQLConfig(mysqlConfig, settings, scope, logger)
    71  	if err != nil {
    72  		return nil, err
    73  	}
    74  
    75  	return dbMap, nil
    76  }
    77  
    78  // DBMapForTest creates a wrapped root borp mapping object. Create one of these for
    79  // each database schema you wish to map. Each DbMap contains a list of mapped
    80  // tables. It automatically maps the tables for the primary parts of Boulder
    81  // around the Storage Authority.
    82  func DBMapForTest(dbConnect string) (*boulderDB.WrappedMap, error) {
    83  	return DBMapForTestWithLog(dbConnect, nil)
    84  }
    85  
    86  // DBMapForTestWithLog does the same as DBMapForTest but also routes the debug logs
    87  // from the database driver to the given log (usually a `blog.NewMock`).
    88  func DBMapForTestWithLog(dbConnect string, log blog.Logger) (*boulderDB.WrappedMap, error) {
    89  	var err error
    90  	var config *mysql.Config
    91  
    92  	config, err = mysql.ParseDSN(dbConnect)
    93  	if err != nil {
    94  		return nil, err
    95  	}
    96  
    97  	return newDbMapFromMySQLConfig(config, DbSettings{}, nil, log)
    98  }
    99  
   100  // sqlOpen is used in the tests to check that the arguments are properly
   101  // transformed
   102  var sqlOpen = func(dbType, connectStr string) (*sql.DB, error) {
   103  	return sql.Open(dbType, connectStr)
   104  }
   105  
   106  // setMaxOpenConns is also used so that we can replace it for testing.
   107  var setMaxOpenConns = func(db *sql.DB, maxOpenConns int) {
   108  	if maxOpenConns != 0 {
   109  		db.SetMaxOpenConns(maxOpenConns)
   110  	}
   111  }
   112  
   113  // setMaxIdleConns is also used so that we can replace it for testing.
   114  var setMaxIdleConns = func(db *sql.DB, maxIdleConns int) {
   115  	if maxIdleConns != 0 {
   116  		db.SetMaxIdleConns(maxIdleConns)
   117  	}
   118  }
   119  
   120  // setConnMaxLifetime is also used so that we can replace it for testing.
   121  var setConnMaxLifetime = func(db *sql.DB, connMaxLifetime time.Duration) {
   122  	if connMaxLifetime != 0 {
   123  		db.SetConnMaxLifetime(connMaxLifetime)
   124  	}
   125  }
   126  
   127  // setConnMaxIdleTime is also used so that we can replace it for testing.
   128  var setConnMaxIdleTime = func(db *sql.DB, connMaxIdleTime time.Duration) {
   129  	if connMaxIdleTime != 0 {
   130  		db.SetConnMaxIdleTime(connMaxIdleTime)
   131  	}
   132  }
   133  
   134  // newDbMapFromMySQLConfig opens a database connection given the provided *mysql.Config, plus some Boulder-specific
   135  // required and default settings, plus some additional config in the sa.DbSettings object. The sa.DbSettings object
   136  // is usually provided from JSON config.
   137  //
   138  // This function also:
   139  //   - pings the database (and errors if it's unreachable)
   140  //   - wraps the connection in a borp.DbMap so we can use the handy Get/Insert methods borp provides
   141  //   - wraps that in a db.WrappedMap to get more useful error messages
   142  //
   143  // If logger is non-nil, it will receive debug log messages from borp.
   144  // If scope is non-nil, it will be used to register Prometheus metrics.
   145  func newDbMapFromMySQLConfig(config *mysql.Config, settings DbSettings, scope prometheus.Registerer, logger blog.Logger) (*boulderDB.WrappedMap, error) {
   146  	err := adjustMySQLConfig(config)
   147  	if err != nil {
   148  		return nil, err
   149  	}
   150  
   151  	db, err := sqlOpen("mysql", config.FormatDSN())
   152  	if err != nil {
   153  		return nil, err
   154  	}
   155  	if err = db.Ping(); err != nil {
   156  		return nil, err
   157  	}
   158  	setMaxOpenConns(db, settings.MaxOpenConns)
   159  	setMaxIdleConns(db, settings.MaxIdleConns)
   160  	setConnMaxLifetime(db, settings.ConnMaxLifetime)
   161  	setConnMaxIdleTime(db, settings.ConnMaxIdleTime)
   162  
   163  	if scope != nil {
   164  		err = initDBMetrics(db, scope, settings, config.Addr, config.User)
   165  		if err != nil {
   166  			return nil, fmt.Errorf("while initializing metrics: %w", err)
   167  		}
   168  	}
   169  
   170  	dialect := borp.MySQLDialect{Engine: "InnoDB", Encoding: "UTF8"}
   171  	dbmap := &borp.DbMap{Db: db, Dialect: dialect, TypeConverter: BoulderTypeConverter{}}
   172  
   173  	if logger != nil {
   174  		dbmap.TraceOn("SQL: ", &SQLLogger{logger})
   175  	}
   176  
   177  	initTables(dbmap)
   178  	return boulderDB.NewWrappedMap(dbmap), nil
   179  }
   180  
   181  // adjustMySQLConfig sets certain flags that we want on every connection.
   182  func adjustMySQLConfig(conf *mysql.Config) error {
   183  	// Required to turn DATETIME fields into time.Time
   184  	conf.ParseTime = true
   185  
   186  	// Required to make UPDATE return the number of rows matched,
   187  	// instead of the number of rows changed by the UPDATE.
   188  	conf.ClientFoundRows = true
   189  
   190  	if conf.Params == nil {
   191  		conf.Params = make(map[string]string)
   192  	}
   193  
   194  	// If a given parameter is not already set in conf.Params from the DSN, set it.
   195  	setDefault := func(name, value string) {
   196  		_, ok := conf.Params[name]
   197  		if !ok {
   198  			conf.Params[name] = value
   199  		}
   200  	}
   201  
   202  	// If a given parameter has the value "0", delete it from conf.Params.
   203  	omitZero := func(name string) {
   204  		if conf.Params[name] == "0" {
   205  			delete(conf.Params, name)
   206  		}
   207  	}
   208  
   209  	// Ensures that MySQL/MariaDB warnings are treated as errors. This
   210  	// avoids a number of nasty edge conditions we could wander into.
   211  	// Common things this discovers includes places where data being sent
   212  	// had a different type than what is in the schema, strings being
   213  	// truncated, writing null to a NOT NULL column, and so on. See
   214  	// <https://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sql-mode-strict>.
   215  	setDefault("sql_mode", "'STRICT_ALL_TABLES'")
   216  
   217  	// If a read timeout is set, we set max_statement_time to 95% of that, and
   218  	// long_query_time to 80% of that. That way we get logs of queries that are
   219  	// close to timing out but not yet doing so, and our queries get stopped by
   220  	// max_statement_time before timing out the read. This generates clearer
   221  	// errors, and avoids unnecessary reconnects.
   222  	// To override these values, set them in the DSN, e.g.
   223  	// `?max_statement_time=2`. A zero value in the DSN means these won't be
   224  	// sent on new connections.
   225  	if conf.ReadTimeout != 0 {
   226  		// In MariaDB, max_statement_time and long_query_time are both seconds.
   227  		// Note: in MySQL (which we don't use), max_statement_time is millis.
   228  		readTimeout := conf.ReadTimeout.Seconds()
   229  		setDefault("max_statement_time", fmt.Sprintf("%g", readTimeout*0.95))
   230  		setDefault("long_query_time", fmt.Sprintf("%g", readTimeout*0.80))
   231  	}
   232  
   233  	omitZero("max_statement_time")
   234  	omitZero("long_query_time")
   235  
   236  	// Finally, perform validation over all variables set by the DSN and via Boulder.
   237  	for k, v := range conf.Params {
   238  		err := checkMariaDBSystemVariables(k, v)
   239  		if err != nil {
   240  			return err
   241  		}
   242  	}
   243  
   244  	return nil
   245  }
   246  
   247  // SQLLogger adapts the Boulder Logger to a format borp can use.
   248  type SQLLogger struct {
   249  	blog.Logger
   250  }
   251  
   252  // Printf adapts the Logger to borp's interface
   253  func (log *SQLLogger) Printf(format string, v ...interface{}) {
   254  	log.Debugf(format, v...)
   255  }
   256  
   257  // initTables constructs the table map for the ORM.
   258  // NOTE: For tables with an auto-increment primary key (SetKeys(true, ...)),
   259  // it is very important to declare them as a such here. It produces a side
   260  // effect in Insert() where the inserted object has its id field set to the
   261  // autoincremented value that resulted from the insert. See
   262  // https://godoc.org/github.com/coopernurse/borp#DbMap.Insert
   263  func initTables(dbMap *borp.DbMap) {
   264  	regTable := dbMap.AddTableWithName(regModel{}, "registrations").SetKeys(true, "ID")
   265  
   266  	regTable.SetVersionCol("LockCol")
   267  	regTable.ColMap("Key").SetNotNull(true)
   268  	regTable.ColMap("KeySHA256").SetNotNull(true).SetUnique(true)
   269  	dbMap.AddTableWithName(issuedNameModel{}, "issuedNames").SetKeys(true, "ID")
   270  	dbMap.AddTableWithName(core.Certificate{}, "certificates").SetKeys(true, "ID")
   271  	dbMap.AddTableWithName(core.CertificateStatus{}, "certificateStatus").SetKeys(true, "ID")
   272  	dbMap.AddTableWithName(core.FQDNSet{}, "fqdnSets").SetKeys(true, "ID")
   273  	dbMap.AddTableWithName(orderModel{}, "orders").SetKeys(true, "ID")
   274  	dbMap.AddTableWithName(orderToAuthzModel{}, "orderToAuthz").SetKeys(false, "OrderID", "AuthzID")
   275  	dbMap.AddTableWithName(requestedNameModel{}, "requestedNames").SetKeys(false, "OrderID")
   276  	dbMap.AddTableWithName(orderFQDNSet{}, "orderFqdnSets").SetKeys(true, "ID")
   277  	dbMap.AddTableWithName(authzModel{}, "authz2").SetKeys(true, "ID")
   278  	dbMap.AddTableWithName(orderToAuthzModel{}, "orderToAuthz2").SetKeys(false, "OrderID", "AuthzID")
   279  	dbMap.AddTableWithName(recordedSerialModel{}, "serials").SetKeys(true, "ID")
   280  	dbMap.AddTableWithName(precertificateModel{}, "precertificates").SetKeys(true, "ID")
   281  	dbMap.AddTableWithName(keyHashModel{}, "keyHashToSerial").SetKeys(true, "ID")
   282  	dbMap.AddTableWithName(incidentModel{}, "incidents").SetKeys(true, "ID")
   283  	dbMap.AddTable(incidentSerialModel{})
   284  	dbMap.AddTableWithName(crlShardModel{}, "crlShards").SetKeys(true, "ID")
   285  	dbMap.AddTableWithName(revokedCertModel{}, "revokedCertificates").SetKeys(true, "ID")
   286  
   287  	// Read-only maps used for selecting subsets of columns.
   288  	dbMap.AddTableWithName(CertStatusMetadata{}, "certificateStatus")
   289  	dbMap.AddTableWithName(crlEntryModel{}, "certificateStatus")
   290  }
   291
View as plain text