From d836a4496de7b24a9d3317e274800d35053a04f6 Mon Sep 17 00:00:00 2001 From: Niall Sheridan Date: Sun, 5 Aug 2018 23:00:58 +0100 Subject: Manage db schema with rubenv/sql-migrate It's currently hard to make changes to the database schema. Use sql-migrate to make incremental changes. Stop hard-coding the database name (the default is still "certs" for backward-compatibility) The `automigrate()` function will automatically run pending migrations. Use a different migration directory per database driver. This carries a cost of duplication, but is easier than creating migrations which will cleanly execute in both SQLite and MySQL. Migrations are shipped using the packr utility. --- server/store/a_store-packr.go | 12 ++++++ .../mysql/20180626224600_create_issued_certs.sql | 15 +++++++ .../sqlite3/20180626224600_create_issued_certs.sql | 15 +++++++ server/store/sqldb.go | 48 +++++++++++++++++----- 4 files changed, 80 insertions(+), 10 deletions(-) create mode 100644 server/store/a_store-packr.go create mode 100644 server/store/migrations/mysql/20180626224600_create_issued_certs.sql create mode 100644 server/store/migrations/sqlite3/20180626224600_create_issued_certs.sql (limited to 'server') diff --git a/server/store/a_store-packr.go b/server/store/a_store-packr.go new file mode 100644 index 0000000..9f06e42 --- /dev/null +++ b/server/store/a_store-packr.go @@ -0,0 +1,12 @@ +// Code generated by github.com/gobuffalo/packr. DO NOT EDIT + +package store + +import "github.com/gobuffalo/packr" + +// You can use the "packr clean" command to clean up this, +// and any other packr generated files. +func init() { + packr.PackJSONBytes("migrations", "mysql/20180626224600_create_issued_certs.sql", "\"H4sIAAAAAAAA/5SRUWuzMBSG7/MrDr2p8lWIH5Sx9crNFGSuLVZhZQwTzGELrlZi1uq/H3Htpr0b5OrkeTnP4fU8+LdXb1oYhKwmDwkLUgZpcB8ziJawWqfAnqNtugWumuYTZV6gNg0HhwDwErtcSQ5HoYt3oZ3/87nbZ1ZZHM8sUWtVFaoWH80VFbJlkMUpTF5eJz1ZaBQGZS4MBykMGrXHH2rq395Qj/oe9YHSO/v8aR/DtlYam7/GNB4PJUoORlWdqozj/yrRb0Kc8hI7DgZbYyebJHoKkh08sh04l9Nd+2MnXMk2H8o4Q7Uxdl5+hV+UZqOjXOIuCBnWFB5OFQmT9eZc07iYBfkKAAD///LIlt7TAQAA\"") + packr.PackJSONBytes("migrations", "sqlite3/20180626224600_create_issued_certs.sql", "\"H4sIAAAAAAAA/5SR0UrDMBSG7/MUh92swxVSYYjuqtoMirUbXQsbIk1oDhrqupLGrX17aV21TkGEXIXv//nOObYNFzv1rIVBSEpyFzE3ZhC7twEDfwHhMga28dfxGriqqjeUaYbaVBwsAsBzbFIlORyEzl6Eti5ns0mXCZMgmLZEqVWRqVK8VmeUxxZuEsQwenwadWSmURiUqTAcpDBo1A4/qbFzfUVt6tjUAUpv2ueMuxjWpdJY/Tem8bDPUXIwqmhUYSznS4l+EOKY5thwMFib9mcV+Q9utIV7tgWrH31CJvN+a37osQ1wJet0aLUMf+xuaP1r/qT3Z08/xhTOKsnwsN7+WBAvWq5Oh/1eMyfvAQAA//+OXEmHBQIAAA==\"") +} diff --git a/server/store/migrations/mysql/20180626224600_create_issued_certs.sql b/server/store/migrations/mysql/20180626224600_create_issued_certs.sql new file mode 100644 index 0000000..c5b80e0 --- /dev/null +++ b/server/store/migrations/mysql/20180626224600_create_issued_certs.sql @@ -0,0 +1,15 @@ +-- +migrate Up +CREATE TABLE IF NOT EXISTS `issued_certs` ( + `key_id` varchar(255) NOT NULL, + `principals` varchar(255) DEFAULT "[]", + `created_at` datetime DEFAULT '1970-01-01 00:00:01', + `expires_at` datetime DEFAULT '1970-01-01 00:00:01', + `revoked` tinyint(1) DEFAULT 0, + `raw_key` text, + PRIMARY KEY (`key_id`) +); +CREATE INDEX `idx_expires_at` ON `issued_certs` (`expires_at`); +CREATE INDEX `idx_revoked_expires_at` ON `issued_certs` (`revoked`, `expires_at`); + +-- +migrate Down +DROP TABLE `issued_certs`; diff --git a/server/store/migrations/sqlite3/20180626224600_create_issued_certs.sql b/server/store/migrations/sqlite3/20180626224600_create_issued_certs.sql new file mode 100644 index 0000000..c5b80e0 --- /dev/null +++ b/server/store/migrations/sqlite3/20180626224600_create_issued_certs.sql @@ -0,0 +1,15 @@ +-- +migrate Up +CREATE TABLE IF NOT EXISTS `issued_certs` ( + `key_id` varchar(255) NOT NULL, + `principals` varchar(255) DEFAULT "[]", + `created_at` datetime DEFAULT '1970-01-01 00:00:01', + `expires_at` datetime DEFAULT '1970-01-01 00:00:01', + `revoked` tinyint(1) DEFAULT 0, + `raw_key` text, + PRIMARY KEY (`key_id`) +); +CREATE INDEX `idx_expires_at` ON `issued_certs` (`expires_at`); +CREATE INDEX `idx_revoked_expires_at` ON `issued_certs` (`revoked`, `expires_at`); + +-- +migrate Down +DROP TABLE `issued_certs`; diff --git a/server/store/sqldb.go b/server/store/sqldb.go index 0b91023..3526a2b 100644 --- a/server/store/sqldb.go +++ b/server/store/sqldb.go @@ -2,15 +2,20 @@ package store import ( "fmt" + "log" "net" + "path" "time" "golang.org/x/crypto/ssh" "github.com/go-sql-driver/mysql" + "github.com/gobuffalo/packr" + multierror "github.com/hashicorp/go-multierror" "github.com/jmoiron/sqlx" "github.com/nsheridan/cashier/server/config" "github.com/pkg/errors" + migrate "github.com/rubenv/sql-migrate" ) var _ CertStorer = (*SQLStore)(nil) @@ -42,20 +47,24 @@ func NewSQLStore(c config.Database) (*SQLStore, error) { m.User = c["username"] m.Passwd = c["password"] m.Addr = address - m.DBName = "certs" + m.Net = "tcp" + m.DBName = c["dbname"] + if m.DBName == "" { + m.DBName = "certs" // Legacy database name + } m.ParseTime = true dsn = m.FormatDSN() case "sqlite": driver = "sqlite3" dsn = c["filename"] } - conn, err := sqlx.Open(driver, dsn) + + conn, err := sqlx.Connect(driver, dsn) if err != nil { return nil, fmt.Errorf("SQLStore: could not get a connection: %v", err) } - if err := conn.Ping(); err != nil { - conn.Close() - return nil, fmt.Errorf("SQLStore: could not establish a good connection: %v", err) + if err := autoMigrate(driver, conn); err != nil { + return nil, fmt.Errorf("SQLStore: could not update schema: %v", err) } db := &SQLStore{ @@ -71,7 +80,7 @@ func NewSQLStore(c config.Database) (*SQLStore, error) { if db.listAll, err = conn.Preparex("SELECT * FROM issued_certs"); err != nil { return nil, fmt.Errorf("SQLStore: prepare listAll: %v", err) } - if db.listCurrent, err = conn.Preparex("SELECT * FROM issued_certs WHERE ? <= expires_at"); err != nil { + if db.listCurrent, err = conn.Preparex("SELECT * FROM issued_certs WHERE expires_at >= ?"); err != nil { return nil, fmt.Errorf("SQLStore: prepare listCurrent: %v", err) } if db.revoked, err = conn.Preparex("SELECT * FROM issued_certs WHERE revoked = 1 AND ? <= expires_at"); err != nil { @@ -80,6 +89,25 @@ func NewSQLStore(c config.Database) (*SQLStore, error) { return db, nil } +func autoMigrate(driver string, conn *sqlx.DB) error { + log.Print("Executing any pending schema migrations") + var err error + migrate.SetTable("schema_migrations") + srcs := &migrate.PackrMigrationSource{ + Box: packr.NewBox(path.Join("migrations", driver)), + } + n, err := migrate.Exec(conn.DB, driver, srcs, migrate.Up) + if err != nil { + err = multierror.Append(err) + return err + } + log.Printf("Executed %d migrations", n) + if err != nil { + log.Fatalf("Errors were found running migrations: %v", err) + } + return nil +} + // rowScanner is implemented by sql.Row and sql.Rows type rowScanner interface { Scan(dest ...interface{}) error @@ -88,7 +116,7 @@ type rowScanner interface { // Get a single *CertRecord func (db *SQLStore) Get(id string) (*CertRecord, error) { if err := db.conn.Ping(); err != nil { - return nil, err + return nil, errors.Wrap(err, "unable to connect to database") } r := &CertRecord{} return r, db.get.Get(r, id) @@ -102,7 +130,7 @@ func (db *SQLStore) SetCert(cert *ssh.Certificate) error { // SetRecord records a *CertRecord func (db *SQLStore) SetRecord(rec *CertRecord) error { if err := db.conn.Ping(); err != nil { - return err + return errors.Wrap(err, "unable to connect to database") } _, err := db.set.Exec(rec.KeyID, rec.Principals, rec.CreatedAt, rec.Expires, rec.Raw) return err @@ -112,7 +140,7 @@ func (db *SQLStore) SetRecord(rec *CertRecord) error { // By default only active certs are returned. func (db *SQLStore) List(includeExpired bool) ([]*CertRecord, error) { if err := db.conn.Ping(); err != nil { - return nil, err + return nil, errors.Wrap(err, "unable to connect to database") } recs := []*CertRecord{} if includeExpired { @@ -140,7 +168,7 @@ func (db *SQLStore) Revoke(ids []string) error { // GetRevoked returns all revoked certs func (db *SQLStore) GetRevoked() ([]*CertRecord, error) { if err := db.conn.Ping(); err != nil { - return nil, err + return nil, errors.Wrap(err, "unable to connect to database") } var recs []*CertRecord if err := db.revoked.Select(&recs, time.Now().UTC()); err != nil { -- cgit v1.2.3