From 60d005f8d174d21162cab2b029f74cfe2925acab Mon Sep 17 00:00:00 2001 From: Niall Sheridan Date: Tue, 7 Aug 2018 23:43:23 +0100 Subject: Change the primary key on the issued_certs table In retrospect a primary key that has no relation to the certificate is preferred to using the certificate KeyID. The KeyID is also very large for a primary index. This is a moderately tricky migration, especially for SQLite which has no means of altering the table in this fashion - it involves creating the new table and copying the data. Order of commands also matters - index names are global in SQLite, so the `idx_expires_at` index needs to be created at the correct stages. For MySQL migration the necessary steps are run as a single alter statement to minimise the risk of leaving the migration in an incomplete state if anything aborts. When tested on a table with 250,000 rows (MySQL 5.7) the migration took 3 seconds to complete. As certificates will be requested infrequently the risk of prolonged locking is minimal. --- server/store/a_store-packr.go | 4 ++- server/store/migrations/migrations_test.go | 1 + .../mysql/20180807224200_new_primary_key.sql | 11 ++++++++ .../sqlite3/20180807224200_new_primary_key.sql | 32 ++++++++++++++++++++++ server/store/store.go | 1 + 5 files changed, 48 insertions(+), 1 deletion(-) create mode 100644 server/store/migrations/mysql/20180807224200_new_primary_key.sql create mode 100644 server/store/migrations/sqlite3/20180807224200_new_primary_key.sql (limited to 'server') diff --git a/server/store/a_store-packr.go b/server/store/a_store-packr.go index 2d8deb8..1ccf57b 100644 --- a/server/store/a_store-packr.go +++ b/server/store/a_store-packr.go @@ -7,9 +7,11 @@ 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", "migrations_test.go", "\"H4sIAAAAAAAA/5xWXW+bSBe+hl9xhPS+gi6FSrtXWfkijd1ud/PRBqfaVRS5Y3OwR4EZcmZwalX+76szgCFpa3V7Y8NwPp7znK+pxeperBEquSZhpVbG92VVa7IQ+l6QCyuWwmBqHsrA94KisvwndSp1Y6U7q4TdpCRUzi/atL9pY5D4sRZ20/+nhSyxPyAsSlw5cxaNlWod+L4XrKXdNMtkpat0rV+ah/JlTnKLlFa7FsMCxjKVsFZ1ktLir4HvtaHgEzFqlqi2HMXL7nPw1JexhHa1odRhKXapMAbJBn7k+0WjVjBHY7MP59LixYGq0MKLDnsyj+CL75lmmUuCkwkEA6B8GQOSOzUPZXJVowpbwRiCkworTbuTIPK91mdyqWdEmkLr1CLfy7FAgnyZnJXaYBj5HjVqDCMG9tHajPz9CPLFLvtwfgyxLECb5C1aVNswuPgn+3C+mM+yeRDBZAJBwDKeTbJ7WYfBpYZBAlBtJWlVobKwFSTFskQOY/+Ehz5vuVH87l6TS3w806qQaw4mNyq5RAsTCOyqbkWT94IMzmWFMAFLDbanp3lOMPk24sUfVwy7VzfmMf+u6PvTLGPRJoYFo+JqTc4aIlSWIckCmKYbgxSDvmcRbZJzre+bevbM1k02uw6i31mMuWLvrMe4OxO+twcsDT7/3rgHJSp0pB2rE9Z6o6kSdppdhtH3i8X3SOWOaHGP4e3dcmcxht+4ZoTKk2sUeUgqjzhHRSE/s2hR2SSrSSpbhMHif5+DGIwlqdZOMvK9RQtswjU4+4yrMDi7np3OZzA9nZ++Ps1mo/mx4KgD+AVa+0fK+murNz9p6Wg7eAfz0+ur9wPkd29g9ve7bJ4ddTnqur6vnnkbdRQ7hhecvOnrGHJJuLKadh2brt8qJvz/3RBK3shymCeZbmiFXCNTSSeDeuyqg2enORRIP0mTt6Vehu7pTy1VOChB8CLhzuP8pSl8RJLFDuxGWLAbJARpQFgoURgLWo1WAFjNMY7otrOqtjsm1qE4kolnnjqbag1NDY+a7o3vqUMQPQsuO5yyEfoqPny+qQeH56gOMGJQP45EcD0YfGh4WlGjYCMMKD1efD2ynwI2e2hEyd5f/SdYhFskwwQNQJ4R9cNwpvpRHWFq7/tp6pZCx0hZjnLem5RoYKWVFVJxnYARFYJBC7oYQ3Rmk9GekWv3ZdojO9PKorLf2Dg870w7oerbtjPubu/ahy9731t1mocyaa8abnRNJYVBcmxXFppgEYNkRRJqjdDbc9NXFiCTd4btODCe1+2poYVkcikqDKOhg1is43HxdfO1Q8bzLMmqQjd7x+F4PaRigOSMte4PahMQdY0qD7uDeHDzWhgMi8h5cRYdhbc90Du3Ip2S7wT2rsxuDLq+RmVpBwXpyiX0k1P+xOyDMCCA+HaBaoXAQLkqWEzzkDAJzx0ylqG3TrtrW/JRlA1eFaE7jZILUf+FOxNGt6/ukqxdHtHdIR3bIfY2/Rz7uHOcOdMWLBkbw9ZdJPb+vwEAAP//wureGqEKAAA=\"") + packr.PackJSONBytes("migrations", "migrations_test.go", "\"H4sIAAAAAAAA/5xWXW+bSBe+hl9xhPS+gi6FSrtXWfkijd1ud/PRBqfaVRS5Y3OwR4EZcmZwalX+76szgKFpY3V7Y8NwPp7znK+pxeperBEquSZhpVbG92VVa7IQ+l6QCyuWwmBqHsrA94KisvwndSp1Y6U7q4TdpCRUzi/atL9pY5D4sRZ20/+nhSyxPyAsSlw5cxaNlWod+L4XrKXdNMtkpat0rV+ah/JlTnKLlFa7FsMCxjKVsFZ1ktLir4HvtaHgV2LULFFtOYqX3efga1/GEtrVhlKHpdilwhgkG/iR7xeNWsEcjc0+nEuLFweqQgsvOuzJPIIvvmeaZS4JTiYQDIDyZQxI7tQ8lMlVjSpsBWMITiqsNO1Ogsj3Wp/JpZ4RaQqtU4t8L8cCCfJlclZqg2Hke9SoMYwY2EdrM/L3I8gXu+zD+THEsgBtkrdoUW3D4OKf7MP5Yj7L5kEEkwkEAct4NsnuZR0GlxoGCUC1laRVhcrCVpAUyxI5jP1XPPR5y43id/eaXOLjmVaFXHMwuVHJJVqYQGBXdSuavBdkcC4rhAlYarA9Pc1zgsn3ES/+uGLYvboxj/mzou9Ps4xFmxgWjIqrNTlriFBZhiQLYJpuDFIM+p5FtEnOtb5v6tkTWzfZ7DqIfmcx5oq9sx7j7kz43h6wNPj0e+MelKjQkXasTljrjaZK2Gl2GUbPF4vvkcod0eIew9u75c5iDL9xzQiVJ9co8pBUHnGOikJ+ZtGisklWk1S2CIPF/z4HMRhLUq2dZOR7ixbYhGtw9hlXYXB2PTudz2B6Oj99fZrNRvNjwVEH8Au09o+U9bdWb37S0tF28A7mp9dX7wfI797A7O932Tw76nLUdX1fPfE26ih2DC84edPXMeSScGU17To2Xb9VTPj/uyGUvJHlME8y3dAKuUamkk4G9dhVB89OcyiQfpImb0u9DN3Tn1qqcFCC4EXCncf5S1P4iCSLHdiNsGA3SAjSgLBQojAWtBqtALCaYxzRbWdVbXdMrENxJBNPPHU21RqaGh413RvfU4cgehZcdjhlI/RVfPh8Uw8Oz1EdYMSgfhyJ4How+NDwtKJGwUYYUHq8+HpkPwVs9tCIkr2/+k+wCLdIhgkagDwh6ofhTPXjMc/PMbj3/TR1y6JjqixHtdC7kmhgpZUVUnH9gBEVgkELuhhDd2aT0f6Ra/dl2iM+08qist/ZRDwHTTu56tu2Y+5u79qHL3vfW3Wah/JpryBupE0lhUFybIcWmmARg2RFEmqN0NtzU1kWIJN3hu04MJ7X7a+htWRyKSoMo6GzWKzjcfFtU7bDx/MsyapCN5PH4Xg9pGKA5Iy17g9qExB1jSoPu4N4cPNaGAyLyHlxFh2Ftz3QO7c6nZLvBPau/G4Mun5HZWkHBenKJfSTU/7E7IMwIID41oFqhcBAuSpYTPPwMAnPIzKWobdOu+tc8lGUDV4VoTuNkgtR/4U7E0a3r+6SrF0q0d0hHdsh9jb9HPu4o5w50xYsGRvD1l0w9v6/AQAA///JYIMFuQoAAA==\"") 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", "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\"") } diff --git a/server/store/migrations/migrations_test.go b/server/store/migrations/migrations_test.go index ad2259b..1283668 100644 --- a/server/store/migrations/migrations_test.go +++ b/server/store/migrations/migrations_test.go @@ -75,6 +75,7 @@ func runMigrations(t *testing.T, db *sql.DB, directory string) { assert.NoError(t, err) // Verify that reversing migrations works n, err = migrate.Exec(db, directory, m, migrate.Down) + assert.NoError(t, err) assert.Len(t, files, n) } diff --git a/server/store/migrations/mysql/20180807224200_new_primary_key.sql b/server/store/migrations/mysql/20180807224200_new_primary_key.sql new file mode 100644 index 0000000..ed6a3c2 --- /dev/null +++ b/server/store/migrations/mysql/20180807224200_new_primary_key.sql @@ -0,0 +1,11 @@ +-- +migrate Up +ALTER TABLE `issued_certs` + DROP PRIMARY KEY, + ADD COLUMN `id` INT PRIMARY KEY AUTO_INCREMENT FIRST, + ADD UNIQUE INDEX `idx_key_id` (`key_id`); + +-- +migrate Down +ALTER TABLE `issued_certs` + DROP PRIMARY KEY, + DROP COLUMN `id`, + ADD PRIMARY KEY (`key_id`); diff --git a/server/store/migrations/sqlite3/20180807224200_new_primary_key.sql b/server/store/migrations/sqlite3/20180807224200_new_primary_key.sql new file mode 100644 index 0000000..40f333c --- /dev/null +++ b/server/store/migrations/sqlite3/20180807224200_new_primary_key.sql @@ -0,0 +1,32 @@ +-- +migrate Up +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`); + +-- +migrate Down +CREATE TABLE IF NOT EXISTS `issued_certs_old` ( + `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`) +); + +INSERT INTO `issued_certs_old` (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_old` 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/store.go b/server/store/store.go index b620e6d..c93680b 100644 --- a/server/store/store.go +++ b/server/store/store.go @@ -36,6 +36,7 @@ type CertStorer interface { // A CertRecord is a representation of a ssh certificate used by a CertStorer. type CertRecord struct { + ID int `json:"-" db:"id"` KeyID string `json:"key_id" db:"key_id"` Principals StringSlice `json:"principals" db:"principals"` CreatedAt time.Time `json:"created_at" db:"created_at"` -- cgit v1.2.3