Record request reason in the db instead of logging

7 files changed, 35 insertions, 23 deletions

diff --git a/server/store/a_store-packr.go b/server/store/a_store-packr.go
index 1573697..2c1ad45 100644
--- a/server/store/a_store-packr.go
+++ b/server/store/a_store-packr.go
@@ -11,7 +11,9 @@ func init() {
 		packr.PackJSONBytes("migrations", "mysql/20180626224600_create_issued_certs.sql", "\"H4sIAAAAAAAA/5SR0UrDMBSG7/MUh92swxVSYYjuqtoMirUbXQsbIk1oDhrqupLGrX17aV21TkGEXIXv//nOObYNFzv1rIVBSEpyFzE3ZhC7twEDfwHhMga28dfxGriqqjeUaYbaVBwsAsBzbFIlORyEzl6Eti5ns0mXCZMgmLZEqVWRqVK8VmeUxxZuEsQwenwadWSmURiUqTAcpDBo1A4/qbFzfUVt6tjUAUpv2ueMuxjWpdJY/Tem8bDPUXIwqmhUYSznS4l+EOKY5thwMFib9mcV+Q9utIV7tgWrH31CJvN+a37osQ1wJet0aLUMf+xuaP1r/qT3Z08/xhTOKsnwsN7+WBAvWq5Oh/1eMyfvAQAA//+OXEmHBQIAAA==\"")
 		packr.PackJSONBytes("migrations", "mysql/20180807223808_idx_revoked_expires_at.sql", "\"H4sIAAAAAAAA/9LVVdDOzUwvSixJVQgt4HL0CXENUghxdPJxVUjILC4uTU2JT04tKilOUHAJ8g9Q8PRzcY1QSMhMqYgvSi3Lz05NiU+tKMgsSi2OTyxJsObiQjbPJb88D5+Jji4uBAxU0EiAiiboJCCJa1pzAQIAAP//O0rcq7kAAAA=\"")
 		packr.PackJSONBytes("migrations", "mysql/20180807224200_new_primary_key.sql", "\"H4sIAAAAAAAA/5TOzarCMBAF4H2e4izvRfsErmIzQrBNa0zArhKxQYr4Q1NR314qFrJ1Nwxz5nxZhtm5O/b7IcDeGC8MaRi+LAi+i/EeWncI/RA9A4SuatRallw3WFMzZwAXAnlV2FLBd62HVCY9AbemclLlmkpSBiupt2bKWSU3liCVoN2YfrpTeLnxy5//Tv8LxlKiuD4uvyM/u0Q5AVJoWvkOAAD//1KTCm8VAQAA\"")
+		packr.PackJSONBytes("migrations", "mysql/20180822204521_add_reason.sql", "\"H4sIAAAAAAAA/9LVVdDOzUwvSixJVQgt4HL0CXENUghxdPJxVUjILC4uTU2JT04tKilOUHB0cVFw9vcJ9fVTSMhNLS5OTE9NUAhxjQhR8PMPUfAL9fGx5uJCNs4lvzwPn4EuQf4BGCZaAwIAAP//am0hrZEAAAA=\"")
 		packr.PackJSONBytes("migrations", "sqlite3/20180626224600_create_issued_certs.sql", "\"H4sIAAAAAAAA/5SR0UrDMBSG7/MUh92swxVSYYjuqtoMirUbXQsbIk1oDhrqupLGrX17aV21TkGEXIXv//nOObYNFzv1rIVBSEpyFzE3ZhC7twEDfwHhMga28dfxGriqqjeUaYbaVBwsAsBzbFIlORyEzl6Eti5ns0mXCZMgmLZEqVWRqVK8VmeUxxZuEsQwenwadWSmURiUqTAcpDBo1A4/qbFzfUVt6tjUAUpv2ueMuxjWpdJY/Tem8bDPUXIwqmhUYSznS4l+EOKY5thwMFib9mcV+Q9utIV7tgWrH31CJvN+a37osQ1wJet0aLUMf+xuaP1r/qT3Z08/xhTOKsnwsN7+WBAvWq5Oh/1eMyfvAQAA//+OXEmHBQIAAA==\"")
 		packr.PackJSONBytes("migrations", "sqlite3/20180807223808_idx_revoked_expires_at.sql", "\"H4sIAAAAAAAA/9LVVdDOzUwvSixJVQgt4HIJ8g9Q8PRzcY1QSMhMqYgvSi3Lz05NiU+tKMgsSi2OTyxJsObiQtbkkl+ex+Uc5OoY4kpAo4K/n0JCZnFxaWpKfHJqUUlxgoJGAlRdgo5CApJSTWsuQAAAAP//Yo/PZJkAAAA=\"")
 		packr.PackJSONBytes("migrations", "sqlite3/20180807224200_new_primary_key.sql", "\"H4sIAAAAAAAA/9yTUWucQBSF3+dXXPISpS5oIZTWJ5u9W6TumI4jJITgDDq0wyaujNPs7r8vugaN3RaWUigFn5xz9Zxzv1ks4M2T/mqkVZA35JphxBF49DFBELptv6uqKJWxbVGrnQCHAAhdCYgpx0/I4IbF64jdwWe887qzjToU3fmzNOU3aZy3V1cu5DT+kiPQlAPNk6QXNkbXpW7kYzsTL3EV5QmHy/uHy15ZGiWtqgppBVTSKquf1KgK3r/zF36w8APw/Q/dExzH1L7RRrXnjhn1vN2oSoDV9UHX1gkmlvxBI3fFRh0EWLW3xA1JTDNkvCslPdnasRUPxtAejLE8GL16MBjwYPiLSwAyTPCaw598BlYsXb82J0KyZOnNqW2LkEQJR/ZrEhjSaI0wDyzCF4ZiusTbDpZ9MV1FSmcD4ExX5YaETJlcbnf1ayrjVQ8S3sYZz2bOto/VwOhJDs8D8OL+4eIfA9D/Gb/uzeQSgvMS3e3A/A2Zx67+LzL7TH+NzB8BAAD//5JBr+QsBQAA\"")
+		packr.PackJSONBytes("migrations", "sqlite3/20180822204521_add_reason.sql", "\"H4sIAAAAAAAA/6SSQWvbQBCF7/srHrnEpjJIhVBanVRrUkTlVbpZQUIp2sUa0sW1Ylbb2P73RbFdG+MeSmBv82b2vZlvMsG7pXvyNjDqlchKTQo6+1wSjOv739w2c/ahN8jyHNOqrGcSZsl9b5/YQNODhqw0ZF2WyOk2q0uNq6tUiNPJ+fO6E1NFmaZLw5uO1wYjARjXGhRS0xdSuFPFLFOP+EqP0VBb8LYZ6i/Wz39aP3p/czNGLYtvNf318CpcedfN3cr+6s/EB4PX339cvyrnnm3gtrHBoLWBg1vyUZV8/BBP4mQSJ4jjT8NLdm28WTnP/f+2eX55XnBrEFy3dV0YJSeW4r3GrpsFbw0Cb4IYp6KQ96T0sJTq4tZ2W4lwDB3hGCvC0WuEvYEI+1/GArinkqYabxmDW1XNznhJRa6qu4sopf/GbJdJkcxmhPPAJj0wVMicHgZYNs3pKSp5Du3o9FTj9E8AAAD//xeZkE/uAgAA\"")
 }
diff --git a/server/store/mem.go b/server/store/mem.go
index 9d5038d..8f27854 100644
--- a/server/store/mem.go
+++ b/server/store/mem.go
@@ -4,8 +4,6 @@ import (
 	"fmt"
 	"sync"
 	"time"
-
-	"golang.org/x/crypto/ssh"
 )
 
 var _ CertStorer = (*memoryStore)(nil)
@@ -27,11 +25,6 @@ func (ms *memoryStore) Get(id string) (*CertRecord, error) {
 	return r, nil
 }
 
-// SetCert parses a *ssh.Certificate and records it
-func (ms *memoryStore) SetCert(cert *ssh.Certificate) error {
-	return ms.SetRecord(parseCertificate(cert))
-}
-
 // SetRecord records a *CertRecord
 func (ms *memoryStore) SetRecord(record *CertRecord) error {
 	ms.Lock()
diff --git a/server/store/migrations/mysql/20180822204521_add_reason.sql b/server/store/migrations/mysql/20180822204521_add_reason.sql
new file mode 100644
index 0000000..85fdd4d
--- /dev/null
+++ b/server/store/migrations/mysql/20180822204521_add_reason.sql
@@ -0,0 +1,5 @@
+-- +migrate Up
+ALTER TABLE `issued_certs` ADD COLUMN `message` TEXT NOT NULL;
+
+-- +migrate Down
+ALTER TABLE `issued_certs` DROP COLUMN `message`;
\ No newline at end of file
diff --git a/server/store/migrations/sqlite3/20180822204521_add_reason.sql b/server/store/migrations/sqlite3/20180822204521_add_reason.sql
new file mode 100644
index 0000000..07e9d49
--- /dev/null
+++ b/server/store/migrations/sqlite3/20180822204521_add_reason.sql
@@ -0,0 +1,18 @@
+-- +migrate Up
+ALTER TABLE `issued_certs` ADD COLUMN `message` TEXT NOT NULL DEFAULT "";
+
+-- +migrate Down
+CREATE TABLE `issued_certs_new` (
+  `id` INTEGER PRIMARY KEY,
+  `key_id` varchar(255) UNIQUE 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
+);
+INSERT INTO `issued_certs_new` (key_id, principals, created_at, expires_at, revoked, raw_key)
+  SELECT key_id, principals, created_at, expires_at, revoked, raw_key FROM `issued_certs`;
+DROP TABLE `issued_certs`;
+ALTER TABLE `issued_certs_new` RENAME TO `issued_certs`;
+CREATE INDEX `idx_expires_at` ON `issued_certs` (`expires_at`);
\ No newline at end of file
diff --git a/server/store/sqldb.go b/server/store/sqldb.go
index e95f53b..d7b5e00 100644
--- a/server/store/sqldb.go
+++ b/server/store/sqldb.go
@@ -7,8 +7,6 @@ import (
 	"path"
 	"time"
 
-	"golang.org/x/crypto/ssh"
-
 	"github.com/go-sql-driver/mysql"
 	"github.com/gobuffalo/packr"
 	multierror "github.com/hashicorp/go-multierror"
@@ -71,7 +69,7 @@ func newSQLStore(c config.Database) (*sqlStore, error) {
 		conn: conn,
 	}
 
-	if db.set, err = conn.Preparex("INSERT INTO issued_certs (key_id, principals, created_at, expires_at, raw_key) VALUES (?, ?, ?, ?, ?)"); err != nil {
+	if db.set, err = conn.Preparex("INSERT INTO issued_certs (key_id, principals, created_at, expires_at, raw_key, message) VALUES (?, ?, ?, ?, ?, ?)"); err != nil {
 		return nil, fmt.Errorf("sqlStore: prepare set: %v", err)
 	}
 	if db.get, err = conn.Preparex("SELECT * FROM issued_certs WHERE key_id = ?"); err != nil {
@@ -117,17 +115,12 @@ func (db *sqlStore) Get(id string) (*CertRecord, error) {
 	return r, db.get.Get(r, id)
 }
 
-// SetCert parses a *ssh.Certificate and records it
-func (db *sqlStore) SetCert(cert *ssh.Certificate) error {
-	return db.SetRecord(parseCertificate(cert))
-}
-
 // SetRecord records a *CertRecord
 func (db *sqlStore) SetRecord(rec *CertRecord) error {
 	if err := db.conn.Ping(); err != nil {
 		return errors.Wrap(err, "unable to connect to database")
 	}
-	_, err := db.set.Exec(rec.KeyID, rec.Principals, rec.CreatedAt, rec.Expires, rec.Raw)
+	_, err := db.set.Exec(rec.KeyID, rec.Principals, rec.CreatedAt, rec.Expires, rec.Raw, rec.Message)
 	return err
 }
 
diff --git a/server/store/store.go b/server/store/store.go
index c93680b..88ec7ce 100644
--- a/server/store/store.go
+++ b/server/store/store.go
@@ -5,10 +5,9 @@ import (
 	"fmt"
 	"time"
 
-	"golang.org/x/crypto/ssh"
-
 	"github.com/nsheridan/cashier/lib"
 	"github.com/nsheridan/cashier/server/config"
+	"golang.org/x/crypto/ssh"
 )
 
 // New returns a new configured database.
@@ -26,7 +25,6 @@ func New(c config.Database) (CertStorer, error) {
 // revocation purposes.
 type CertStorer interface {
 	Get(id string) (*CertRecord, error)
-	SetCert(cert *ssh.Certificate) error
 	SetRecord(record *CertRecord) error
 	List(includeExpired bool) ([]*CertRecord, error)
 	Revoke(id []string) error
@@ -43,6 +41,7 @@ type CertRecord struct {
 	Expires    time.Time   `json:"expires" db:"expires_at"`
 	Revoked    bool        `json:"revoked" db:"revoked"`
 	Raw        string      `json:"-" db:"raw_key"`
+	Message    string      `json:"message" db:"message"`
 }
 
 // MarshalJSON implements the json.Marshaler interface for the CreatedAt and
@@ -66,7 +65,8 @@ func parseTime(t uint64) time.Time {
 	return time.Unix(int64(t), 0)
 }
 
-func parseCertificate(cert *ssh.Certificate) *CertRecord {
+// MakeRecord converts a Certificate to a CertRecord
+func MakeRecord(cert *ssh.Certificate) *CertRecord {
 	return &CertRecord{
 		KeyID:      cert.KeyId,
 		Principals: StringSlice(cert.ValidPrincipals),
diff --git a/server/store/store_test.go b/server/store/store_test.go
index 3fd900c..628c539 100644
--- a/server/store/store_test.go
+++ b/server/store/store_test.go
@@ -30,7 +30,7 @@ func TestParseCertificate(t *testing.T) {
 	}
 	s, _ := ssh.NewSignerFromKey(r)
 	c.SignCert(rand.Reader, s)
-	rec := parseCertificate(c)
+	rec := MakeRecord(c)
 
 	a.Equal(c.KeyId, rec.KeyID)
 	a.Equal(c.ValidPrincipals, []string(rec.Principals))
@@ -73,7 +73,8 @@ func testStore(t *testing.T, db CertStorer) {
 	cert := c.(*ssh.Certificate)
 	cert.ValidBefore = uint64(time.Now().Add(1 * time.Hour).UTC().Unix())
 	cert.ValidAfter = uint64(time.Now().Add(-5 * time.Minute).UTC().Unix())
-	if err := db.SetCert(cert); err != nil {
+	rec := MakeRecord(cert)
+	if err := db.SetRecord(rec); err != nil {
 		t.Error(err)
 	}
 
@@ -153,6 +154,6 @@ func TestMarshalCert(t *testing.T) {
 	if err != nil {
 		t.Error(err)
 	}
-	want := `{"key_id":"id","principals":["user"],"revoked":false,"created_at":"2017-04-10 13:00:00 +0000","expires":"2017-04-11 10:00:00 +0000"}`
+	want := `{"key_id":"id","principals":["user"],"revoked":false,"created_at":"2017-04-10 13:00:00 +0000","expires":"2017-04-11 10:00:00 +0000","message":""}`
 	a.JSONEq(want, string(b))
 }