github.com/mattermost/mattermost-server/server/v8@v8.0.0-20230610055354-a6d1d38b273d/config/database.go (about) 1 // Copyright (c) 2015-present Mattermost, Inc. All Rights Reserved. 2 // See LICENSE.txt for license information. 3 4 package config 5 6 import ( 7 "bytes" 8 "context" 9 "crypto/sha256" 10 "database/sql" 11 "embed" 12 "encoding/hex" 13 "encoding/json" 14 "fmt" 15 "path/filepath" 16 "strings" 17 18 "github.com/jmoiron/sqlx" 19 "github.com/pkg/errors" 20 21 // Load the MySQL driver 22 _ "github.com/go-sql-driver/mysql" 23 // Load the Postgres driver 24 _ "github.com/lib/pq" 25 26 "github.com/mattermost/morph" 27 28 "github.com/mattermost/mattermost-server/server/public/model" 29 "github.com/mattermost/mattermost-server/server/public/shared/mlog" 30 "github.com/mattermost/mattermost-server/server/v8/channels/store/sqlstore" 31 32 "github.com/mattermost/morph/drivers" 33 ms "github.com/mattermost/morph/drivers/mysql" 34 ps "github.com/mattermost/morph/drivers/postgres" 35 mbindata "github.com/mattermost/morph/sources/embedded" 36 ) 37 38 //go:embed migrations 39 var assets embed.FS 40 41 // MaxWriteLength defines the maximum length accepted for write to the Configurations or 42 // ConfigurationFiles table. 43 // 44 // It is imposed by MySQL's default max_allowed_packet value of 4Mb. 45 const MaxWriteLength = 4 * 1024 * 1024 46 47 // We use the something different from the default migration table name of morph 48 const migrationsTableName = "db_config_migrations" 49 50 // The timeout value for each migration file to run. 51 const migrationsTimeoutInSeconds = 100000 52 53 // DatabaseStore is a config store backed by a database. 54 // Not to be used directly. Only to be used as a backing store for config.Store 55 type DatabaseStore struct { 56 originalDsn string 57 driverName string 58 dataSourceName string 59 db *sqlx.DB 60 } 61 62 // NewDatabaseStore creates a new instance of a config store backed by the given database. 63 func NewDatabaseStore(dsn string) (ds *DatabaseStore, err error) { 64 driverName, dataSourceName, err := parseDSN(dsn) 65 if err != nil { 66 return nil, errors.Wrap(err, "invalid DSN") 67 } 68 69 db, err := sqlx.Open(driverName, dataSourceName) 70 if err != nil { 71 return nil, errors.Wrapf(err, "failed to connect to %s database", driverName) 72 } 73 // Set conservative connection configuration for configuration database. 74 db.SetMaxIdleConns(0) 75 db.SetMaxOpenConns(2) 76 77 defer func() { 78 if err != nil { 79 db.Close() 80 } 81 }() 82 83 ds = &DatabaseStore{ 84 driverName: driverName, 85 originalDsn: dsn, 86 dataSourceName: dataSourceName, 87 db: db, 88 } 89 if err = ds.initializeConfigurationsTable(); err != nil { 90 err = errors.Wrap(err, "failed to initialize") 91 return nil, err 92 } 93 94 return ds, nil 95 } 96 97 // initializeConfigurationsTable ensures the requisite tables in place to form the backing store. 98 // 99 // Uses MEDIUMTEXT on MySQL, and TEXT on sane databases. 100 func (ds *DatabaseStore) initializeConfigurationsTable() error { 101 assetsList, err := assets.ReadDir(filepath.Join("migrations", ds.driverName)) 102 if err != nil { 103 return err 104 } 105 106 assetNamesForDriver := make([]string, len(assetsList)) 107 for i, entry := range assetsList { 108 assetNamesForDriver[i] = entry.Name() 109 } 110 111 src, err := mbindata.WithInstance(&mbindata.AssetSource{ 112 Names: assetNamesForDriver, 113 AssetFunc: func(name string) ([]byte, error) { 114 return assets.ReadFile(filepath.Join("migrations", ds.driverName, name)) 115 }, 116 }) 117 if err != nil { 118 return err 119 } 120 121 var driver drivers.Driver 122 switch ds.driverName { 123 case model.DatabaseDriverMysql: 124 dataSource, rErr := sqlstore.ResetReadTimeout(ds.dataSourceName) 125 if rErr != nil { 126 return fmt.Errorf("failed to reset read timeout from datasource: %w", rErr) 127 } 128 129 dataSource, err = sqlstore.AppendMultipleStatementsFlag(dataSource) 130 if err != nil { 131 return err 132 } 133 134 var db *sqlx.DB 135 db, err = sqlx.Open(ds.driverName, dataSource) 136 if err != nil { 137 return errors.Wrapf(err, "failed to connect to %s database", ds.driverName) 138 } 139 140 driver, err = ms.WithInstance(db.DB) 141 142 defer db.Close() 143 case model.DatabaseDriverPostgres: 144 driver, err = ps.WithInstance(ds.db.DB) 145 default: 146 err = fmt.Errorf("unsupported database type %s for migration", ds.driverName) 147 } 148 if err != nil { 149 return err 150 } 151 152 opts := []morph.EngineOption{ 153 morph.WithLock("mm-config-lock-key"), 154 morph.SetMigrationTableName(migrationsTableName), 155 morph.SetStatementTimeoutInSeconds(migrationsTimeoutInSeconds), 156 } 157 engine, err := morph.New(context.Background(), driver, src, opts...) 158 if err != nil { 159 return err 160 } 161 defer engine.Close() 162 163 return engine.ApplyAll() 164 } 165 166 // parseDSN splits up a connection string into a driver name and data source name. 167 // 168 // For example: 169 // 170 // mysql://mmuser:mostest@localhost:5432/mattermost_test 171 // 172 // returns 173 // 174 // driverName = mysql 175 // dataSourceName = mmuser:mostest@localhost:5432/mattermost_test 176 // 177 // By contrast, a Postgres DSN is returned unmodified. 178 func parseDSN(dsn string) (string, string, error) { 179 // Treat the DSN as the URL that it is. 180 s := strings.SplitN(dsn, "://", 2) 181 if len(s) != 2 { 182 return "", "", errors.New("failed to parse DSN as URL") 183 } 184 185 scheme := s[0] 186 switch scheme { 187 case "mysql": 188 // Strip off the mysql:// for the dsn with which to connect. 189 dsn = s[1] 190 191 case "postgres", "postgresql": 192 // No changes required 193 194 default: 195 return "", "", errors.Errorf("unsupported scheme %s", scheme) 196 } 197 198 return scheme, dsn, nil 199 } 200 201 // Set replaces the current configuration in its entirety and updates the backing store. 202 func (ds *DatabaseStore) Set(newCfg *model.Config) error { 203 return ds.persist(newCfg) 204 } 205 206 // maxLength identifies the maximum length of a configuration or configuration file 207 func (ds *DatabaseStore) checkLength(length int) error { 208 if ds.db.DriverName() == "mysql" && length > MaxWriteLength { 209 return errors.Errorf("value is too long: %d > %d bytes", length, MaxWriteLength) 210 } 211 212 return nil 213 } 214 215 // persist writes the configuration to the configured database. 216 func (ds *DatabaseStore) persist(cfg *model.Config) error { 217 b, err := marshalConfig(cfg) 218 if err != nil { 219 return errors.Wrap(err, "failed to serialize") 220 } 221 222 value := string(b) 223 err = ds.checkLength(len(value)) 224 if err != nil { 225 return errors.Wrap(err, "marshalled configuration failed length check") 226 } 227 228 sum := sha256.Sum256(b) 229 230 // Skip the persist altogether if we're effectively writing the same configuration. 231 var oldValue string 232 var row *sql.Row 233 if ds.driverName == model.DatabaseDriverMysql { 234 // We use a sub-query to get the Id first because selecting the Id column using 235 // active uses the index, but selecting SHA column using active does not use the index. 236 // The sub-query uses the active index, and then the top-level query uses the primary key. 237 // This takes 2 queries, but it is actually faster than one slow query for MySQL 238 row = ds.db.QueryRow("SELECT SHA FROM Configurations WHERE Id = (select Id from Configurations Where Active)") 239 } else { 240 row = ds.db.QueryRow("SELECT SHA FROM Configurations WHERE Active") 241 } 242 if err = row.Scan(&oldValue); err != nil && err != sql.ErrNoRows { 243 return errors.Wrap(err, "failed to query active configuration") 244 } 245 246 // postgres retruns blank-padded therefore we trim the space 247 oldSum, err := hex.DecodeString(strings.TrimSpace(oldValue)) 248 if err != nil { 249 return errors.Wrap(err, "could not encode value") 250 } 251 252 // compare checksums, it's more efficient rather than comparing entire config itself 253 if bytes.Equal(oldSum, sum[0:]) { 254 return nil 255 } 256 257 tx, err := ds.db.Beginx() 258 if err != nil { 259 return errors.Wrap(err, "failed to begin transaction") 260 } 261 defer func() { 262 // Rollback after Commit just returns sql.ErrTxDone. 263 if err = tx.Rollback(); err != nil && err != sql.ErrTxDone { 264 mlog.Error("Failed to rollback configuration transaction", mlog.Err(err)) 265 } 266 }() 267 268 var oldId string 269 if ds.driverName == model.DatabaseDriverMysql { 270 // the query doesn't use active index if we query for value (mysql, no surprise) 271 // we select Id column which triggers using index hence we do quicker reads 272 // that's the reason we select id first then query against id to get the value. 273 row = tx.QueryRow("SELECT Id FROM Configurations WHERE Active") 274 if err = row.Scan(&oldId); err != nil && err != sql.ErrNoRows { 275 return errors.Wrap(err, "failed to query active configuration") 276 } 277 if oldId != "" { 278 if _, err := tx.NamedExec("UPDATE Configurations SET Active = NULL WHERE Id = :id", map[string]any{"id": oldId}); err != nil { 279 return errors.Wrap(err, "failed to deactivate current configuration") 280 } 281 } 282 } else { 283 if _, err := tx.Exec("UPDATE Configurations SET Active = NULL WHERE Active"); err != nil { 284 return errors.Wrap(err, "failed to deactivate current configuration") 285 } 286 } 287 288 params := map[string]any{ 289 "id": model.NewId(), 290 "value": value, 291 "create_at": model.GetMillis(), 292 "key": "ConfigurationId", 293 "sha": hex.EncodeToString(sum[0:]), 294 } 295 296 if _, err := tx.NamedExec("INSERT INTO Configurations (Id, Value, CreateAt, Active, SHA) VALUES (:id, :value, :create_at, TRUE, :sha)", params); err != nil { 297 return errors.Wrap(err, "failed to record new configuration") 298 } 299 300 if err := tx.Commit(); err != nil { 301 return errors.Wrap(err, "failed to commit transaction") 302 } 303 304 return nil 305 } 306 307 // Load updates the current configuration from the backing store. 308 func (ds *DatabaseStore) Load() ([]byte, error) { 309 var configurationData []byte 310 311 row := ds.db.QueryRow("SELECT Value FROM Configurations WHERE Active") 312 if err := row.Scan(&configurationData); err != nil && err != sql.ErrNoRows { 313 return nil, errors.Wrap(err, "failed to query active configuration") 314 } 315 316 // Initialize from the default config if no active configuration could be found. 317 if len(configurationData) == 0 { 318 configWithDB := model.Config{} 319 configWithDB.SqlSettings.DriverName = model.NewString(ds.driverName) 320 configWithDB.SqlSettings.DataSource = model.NewString(ds.dataSourceName) 321 return json.Marshal(configWithDB) 322 } 323 324 return configurationData, nil 325 } 326 327 // GetFile fetches the contents of a previously persisted configuration file. 328 func (ds *DatabaseStore) GetFile(name string) ([]byte, error) { 329 query, args, err := sqlx.Named("SELECT Data FROM ConfigurationFiles WHERE Name = :name", map[string]any{ 330 "name": name, 331 }) 332 if err != nil { 333 return nil, err 334 } 335 336 var data []byte 337 row := ds.db.QueryRowx(ds.db.Rebind(query), args...) 338 if err = row.Scan(&data); err != nil { 339 return nil, errors.Wrapf(err, "failed to scan data from row for %s", name) 340 } 341 342 return data, nil 343 } 344 345 // SetFile sets or replaces the contents of a configuration file. 346 func (ds *DatabaseStore) SetFile(name string, data []byte) error { 347 err := ds.checkLength(len(data)) 348 if err != nil { 349 return errors.Wrap(err, "file data failed length check") 350 } 351 params := map[string]any{ 352 "name": name, 353 "data": data, 354 "create_at": model.GetMillis(), 355 "update_at": model.GetMillis(), 356 } 357 358 result, err := ds.db.NamedExec("UPDATE ConfigurationFiles SET Data = :data, UpdateAt = :update_at WHERE Name = :name", params) 359 if err != nil { 360 return errors.Wrapf(err, "failed to update row for %s", name) 361 } 362 363 count, err := result.RowsAffected() 364 if err != nil { 365 return errors.Wrapf(err, "failed to count rows affected for %s", name) 366 } else if count > 0 { 367 return nil 368 } 369 370 _, err = ds.db.NamedExec("INSERT INTO ConfigurationFiles (Name, Data, CreateAt, UpdateAt) VALUES (:name, :data, :create_at, :update_at)", params) 371 if err != nil { 372 return errors.Wrapf(err, "failed to insert row for %s", name) 373 } 374 375 return nil 376 } 377 378 // HasFile returns true if the given file was previously persisted. 379 func (ds *DatabaseStore) HasFile(name string) (bool, error) { 380 query, args, err := sqlx.Named("SELECT COUNT(*) FROM ConfigurationFiles WHERE Name = :name", map[string]any{ 381 "name": name, 382 }) 383 if err != nil { 384 return false, err 385 } 386 387 var count int64 388 row := ds.db.QueryRowx(ds.db.Rebind(query), args...) 389 if err = row.Scan(&count); err != nil { 390 return false, errors.Wrapf(err, "failed to scan count of rows for %s", name) 391 } 392 393 return count != 0, nil 394 } 395 396 // RemoveFile remoevs a previously persisted configuration file. 397 func (ds *DatabaseStore) RemoveFile(name string) error { 398 _, err := ds.db.NamedExec("DELETE FROM ConfigurationFiles WHERE Name = :name", map[string]any{ 399 "name": name, 400 }) 401 if err != nil { 402 return errors.Wrapf(err, "failed to remove row for %s", name) 403 } 404 405 return nil 406 } 407 408 // String returns the path to the database backing the config, masking the password. 409 func (ds *DatabaseStore) String() string { 410 // This is called during the running of MM, so we expect the parsing of DSN 411 // to be successful. 412 sanitized, _ := sqlstore.SanitizeDataSource(ds.driverName, ds.originalDsn) 413 return sanitized 414 } 415 416 // Close cleans up resources associated with the store. 417 func (ds *DatabaseStore) Close() error { 418 return ds.db.Close() 419 } 420 421 // removes configurations from database if they are older than threshold. 422 func (ds *DatabaseStore) cleanUp(thresholdCreatAt int) error { 423 if _, err := ds.db.NamedExec("DELETE FROM Configurations Where CreateAt < :timestamp", map[string]any{"timestamp": thresholdCreatAt}); err != nil { 424 return errors.Wrap(err, "unable to clean Configurations table") 425 } 426 427 return nil 428 }