aboutsummaryrefslogtreecommitdiff
path: root/server/store
diff options
context:
space:
mode:
Diffstat (limited to 'server/store')
-rw-r--r--server/store/a_store-packr.go12
-rw-r--r--server/store/migrations/mysql/20180626224600_create_issued_certs.sql15
-rw-r--r--server/store/migrations/sqlite3/20180626224600_create_issued_certs.sql15
-rw-r--r--server/store/sqldb.go48
4 files changed, 80 insertions, 10 deletions
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 {