From b93a39014fe39b0b3527849acc0e57e5b7772f1b Mon Sep 17 00:00:00 2001 From: =?utf8?q?Felix=20D=C3=B6rre?= Date: Fri, 18 Jul 2014 10:58:21 +0200 Subject: [PATCH] New Signer Job Table. --- doc/tableStructure.sql | 31 ++++- src/org/cacert/gigi/Certificate.java | 127 ++++++------------ .../pages/account/IssueCertificateForm.java | 3 +- src/org/cacert/gigi/util/Job.java | 55 ++++++++ tests/org/cacert/gigi/TestCertificate.java | 21 ++- util/org/cacert/gigi/util/SimpleSigner.java | 34 +++-- 6 files changed, 152 insertions(+), 119 deletions(-) create mode 100644 src/org/cacert/gigi/util/Job.java diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql index 88ba34e0..ceb11b3c 100644 --- a/doc/tableStructure.sql +++ b/doc/tableStructure.sql @@ -94,21 +94,20 @@ CREATE TABLE `emailcerts` ( `subject` text NOT NULL, `keytype` char(2) NOT NULL DEFAULT 'NS', `codesign` tinyint(1) NOT NULL DEFAULT '0', + `md` enum('md5','sha1','sha256','sha512') NOT NULL DEFAULT 'sha512', + `rootcert` int(2) NOT NULL DEFAULT '1', + `type` enum('client', 'server') DEFAULT NULL, + `csr_name` varchar(255) NOT NULL DEFAULT '', `crt_name` varchar(255) NOT NULL DEFAULT '', `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `revoked` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `expire` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `warning` tinyint(1) NOT NULL DEFAULT '0', `renewed` tinyint(1) NOT NULL DEFAULT '0', - `rootcert` int(2) NOT NULL DEFAULT '1', - `md` enum('md5','sha1','sha256','sha512') NOT NULL DEFAULT 'sha512', - `type` tinyint(4) DEFAULT NULL, `disablelogin` int(1) NOT NULL DEFAULT '0', `pkhash` char(40) DEFAULT NULL, `certhash` char(40) DEFAULT NULL, - `coll_found` tinyint(1) NOT NULL, `description` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `emailcerts_pkhash` (`pkhash`), @@ -120,6 +119,28 @@ CREATE TABLE `emailcerts` ( KEY `emailcrt` (`crt_name`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; +DROP TABLE IF EXISTS `clientcerts`; +CREATE TABLE `clientcerts` ( + `id` int(11) NOT NULL, + `disablelogin` int(1) NOT NULL DEFAULT '0', + + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; + + + +DROP TABLE IF EXISTS `jobs`; +CREATE TABLE `jobs` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `targetId` int(11) NOT NULL, + `task` enum('sign','revoke') NOT NULL, + `state` enum('open', 'done', 'error') NOT NULL DEFAULT 'open', + `warning` int(2) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`), + KEY `state` (`state`) +) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; + + DROP TABLE IF EXISTS `notary`; CREATE TABLE `notary` ( `id` int(11) NOT NULL AUTO_INCREMENT, diff --git a/src/org/cacert/gigi/Certificate.java b/src/org/cacert/gigi/Certificate.java index 2df5e395..9e4e0fa7 100644 --- a/src/org/cacert/gigi/Certificate.java +++ b/src/org/cacert/gigi/Certificate.java @@ -13,6 +13,8 @@ import java.sql.ResultSet; import java.sql.SQLException; import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.util.Job; +import org.cacert.gigi.util.Job.JobType; import org.cacert.gigi.util.KeyStorage; import org.cacert.gigi.util.Notary; @@ -60,47 +62,25 @@ public class Certificate { * This certificate is not in the database, has no id and only exists as * this java object. */ - DRAFT(false), - /** - * The certificate has been written to the database and is waiting for - * the signer to sign it. - */ - SIGNING(true), + DRAFT(), /** * The certificate has been signed. It is stored in the database. * {@link Certificate#cert()} is valid. */ - ISSUED(false), - /** - * The cetrificate is about to be revoked by the signer bot. - */ - BEING_REVOKED(true), + ISSUED(), /** * The certificate has been revoked. */ - REVOKED(false), + REVOKED(), /** * If this certificate cannot be updated because an error happened in * the signer. */ - ERROR(false); + ERROR(); - private boolean unstable; - - private CertificateStatus(boolean unstable) { - this.unstable = unstable; - } - - /** - * Checks, iff this certificate stage will be left by signer actions. - * - * @return True, iff this certificate stage will be left by signer - * actions. - */ - public boolean isUnstable() { - return unstable; + private CertificateStatus() { } } @@ -110,84 +90,57 @@ public class Certificate { return CertificateStatus.DRAFT; } PreparedStatement searcher = DatabaseConnection.getInstance().prepare( - "SELECT crt_name, created, revoked, warning, serial FROM emailcerts WHERE id=?"); + "SELECT crt_name, created, revoked, serial FROM emailcerts WHERE id=?"); searcher.setInt(1, id); ResultSet rs = searcher.executeQuery(); if (!rs.next()) { throw new IllegalStateException("Certificate not in Database"); } - if (rs.getInt(4) >= 3) { - return CertificateStatus.ERROR; - } - if (rs.getString(2) == null) { - return CertificateStatus.SIGNING; - } crtName = rs.getString(1); - serial = rs.getString(5); + serial = rs.getString(4); + if (rs.getTime(2) == null) { + return CertificateStatus.DRAFT; + } if (rs.getTime(2) != null && rs.getTime(3) == null) { return CertificateStatus.ISSUED; } - if (rs.getTime(2) != null && rs.getString(3).equals("1970-01-01 00:00:00.0")) { - return CertificateStatus.BEING_REVOKED; - } return CertificateStatus.REVOKED; } - public void issue() throws IOException { - try { - if (getStatus() != CertificateStatus.DRAFT) { - throw new IllegalStateException(); - } - Notary.writeUserAgreement(ownerId, "CCA", "issue certificate", "", true, 0); - - PreparedStatement inserter = DatabaseConnection.getInstance().prepare( - "INSERT INTO emailcerts SET md=?, subject=?, coll_found=0, crt_name='', memid=?"); - inserter.setString(1, md); - inserter.setString(2, dn); - inserter.setInt(3, ownerId); - inserter.execute(); - id = DatabaseConnection.lastInsertId(inserter); - File csrFile = KeyStorage.locateCsr(id); - csrName = csrFile.getPath(); - FileOutputStream fos = new FileOutputStream(csrFile); - fos.write(csr.getBytes()); - fos.close(); - - PreparedStatement updater = DatabaseConnection.getInstance().prepare( - "UPDATE emailcerts SET csr_name=? WHERE id=?"); - updater.setString(1, csrName); - updater.setInt(2, id); - updater.execute(); - } catch (SQLException e) { - e.printStackTrace(); + public Job issue() throws IOException, SQLException { + if (getStatus() != CertificateStatus.DRAFT) { + throw new IllegalStateException(); } + Notary.writeUserAgreement(ownerId, "CCA", "issue certificate", "", true, 0); + + PreparedStatement inserter = DatabaseConnection.getInstance().prepare( + "INSERT INTO emailcerts SET md=?, subject=?, crt_name='', memid=?"); + inserter.setString(1, md); + inserter.setString(2, dn); + inserter.setInt(3, ownerId); + inserter.execute(); + id = DatabaseConnection.lastInsertId(inserter); + File csrFile = KeyStorage.locateCsr(id); + csrName = csrFile.getPath(); + FileOutputStream fos = new FileOutputStream(csrFile); + fos.write(csr.getBytes()); + fos.close(); + + PreparedStatement updater = DatabaseConnection.getInstance().prepare( + "UPDATE emailcerts SET csr_name=? WHERE id=?"); + updater.setString(1, csrName); + updater.setInt(2, id); + updater.execute(); + return Job.submit(this, JobType.SIGN); } - public boolean waitFor(int max) throws SQLException, InterruptedException { - long start = System.currentTimeMillis(); - while (getStatus().isUnstable()) { - if (max != 0 && System.currentTimeMillis() - start > max) { - return false; - } - Thread.sleep((long) (2000 + Math.random() * 2000)); - } - return true; - } - - public void revoke() { - try { - if (getStatus() != CertificateStatus.ISSUED) { - throw new IllegalStateException(); - } - PreparedStatement inserter = DatabaseConnection.getInstance().prepare( - "UPDATE emailcerts SET revoked = '1970-01-01' WHERE id=?"); - inserter.setInt(1, id); - inserter.execute(); - } catch (SQLException e) { - e.printStackTrace(); + public Job revoke() throws SQLException { + if (getStatus() != CertificateStatus.ISSUED) { + throw new IllegalStateException(); } + return Job.submit(this, JobType.REVOKE); } diff --git a/src/org/cacert/gigi/pages/account/IssueCertificateForm.java b/src/org/cacert/gigi/pages/account/IssueCertificateForm.java index a3184080..29565e0e 100644 --- a/src/org/cacert/gigi/pages/account/IssueCertificateForm.java +++ b/src/org/cacert/gigi/pages/account/IssueCertificateForm.java @@ -95,9 +95,8 @@ public class IssueCertificateForm extends Form { System.out.println("issuing " + selectedDigest); result = new Certificate(LoginPage.getUser(req).getId(), "/commonName=CAcert WoT User", selectedDigest.toString(), this.csr); - result.issue(); try { - result.waitFor(60000); + result.issue().waitFor(60000); return true; } catch (SQLException e) { e.printStackTrace(); diff --git a/src/org/cacert/gigi/util/Job.java b/src/org/cacert/gigi/util/Job.java new file mode 100644 index 00000000..70c9d569 --- /dev/null +++ b/src/org/cacert/gigi/util/Job.java @@ -0,0 +1,55 @@ +package org.cacert.gigi.util; + +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; + +import org.cacert.gigi.Certificate; +import org.cacert.gigi.database.DatabaseConnection; + +public class Job { + int id; + + private Job(int id) { + this.id = id; + } + + public static enum JobType { + SIGN("sign"), REVOKE("revoke"); + private final String name; + + private JobType(String name) { + this.name = name; + } + + public String getName() { + return name; + } + } + + public static Job submit(Certificate targetId, JobType type) throws SQLException { + PreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `jobs` SET targetId=?, task=?"); + ps.setInt(1, targetId.getId()); + ps.setString(2, type.getName()); + ps.execute(); + return new Job(DatabaseConnection.lastInsertId(ps)); + } + + public boolean waitFor(int max) throws SQLException, InterruptedException { + long start = System.currentTimeMillis(); + PreparedStatement ps = DatabaseConnection.getInstance().prepare( + "SELECT 1 FROM `jobs` WHERE id=? AND state='open'"); + ps.setInt(1, id); + ResultSet rs = ps.executeQuery(); + while (rs.next()) { + rs.close(); + if (max != 0 && System.currentTimeMillis() - start > max) { + return false; + } + Thread.sleep((long) (2000 + Math.random() * 2000)); + rs = ps.executeQuery(); + } + rs.close(); + return true; + } +} diff --git a/tests/org/cacert/gigi/TestCertificate.java b/tests/org/cacert/gigi/TestCertificate.java index f0fe0209..e6cef97b 100644 --- a/tests/org/cacert/gigi/TestCertificate.java +++ b/tests/org/cacert/gigi/TestCertificate.java @@ -32,9 +32,9 @@ public class TestCertificate extends ManagedTest { String[] key1 = generateCSR("/CN=testmail@example.com"); Certificate c = new Certificate(1, "/CN=testmail@example.com", "sha256", key1[1]); final PrivateKey pk = PemKey.parsePEMPrivateKey(key1[0]); - c.issue(); - c.waitFor(60000); + c.issue().waitFor(60000); final X509Certificate ce = c.cert(); + System.out.println(ce); testLogin(pk, ce, true); } @@ -100,18 +100,12 @@ public class TestCertificate extends ManagedTest { final PrivateKey pk = PemKey.parsePEMPrivateKey(key1[0]); testFails(CertificateStatus.DRAFT, c); - c.issue(); - - testFails(CertificateStatus.SIGNING, c); - c.waitFor(60000); + c.issue().waitFor(60000); testFails(CertificateStatus.ISSUED, c); X509Certificate cert = c.cert(); testLogin(pk, cert, true); - c.revoke(); - - testFails(CertificateStatus.BEING_REVOKED, c); - c.waitFor(60000); + c.revoke().waitFor(60000); testFails(CertificateStatus.REVOKED, c); testLogin(pk, cert, false); @@ -120,10 +114,11 @@ public class TestCertificate extends ManagedTest { private void testFails(CertificateStatus status, Certificate c) throws IOException, GeneralSecurityException, SQLException { + assertEquals(status, c.getStatus()); if (status != CertificateStatus.ISSUED) { try { c.revoke(); - fail("is in invalid state"); + fail(status + " is in invalid state"); } catch (IllegalStateException ise) { } @@ -131,7 +126,7 @@ public class TestCertificate extends ManagedTest { if (status != CertificateStatus.DRAFT) { try { c.issue(); - fail("is in invalid state"); + fail(status + " is in invalid state"); } catch (IllegalStateException ise) { } @@ -139,7 +134,7 @@ public class TestCertificate extends ManagedTest { if (status != CertificateStatus.ISSUED) { try { c.cert(); - fail("is in invalid state"); + fail(status + " is in invalid state"); } catch (IllegalStateException ise) { } diff --git a/util/org/cacert/gigi/util/SimpleSigner.java b/util/org/cacert/gigi/util/SimpleSigner.java index 0a5c14a8..36e94bdc 100644 --- a/util/org/cacert/gigi/util/SimpleSigner.java +++ b/util/org/cacert/gigi/util/SimpleSigner.java @@ -25,6 +25,7 @@ public class SimpleSigner { private static PreparedStatement readyMail; private static PreparedStatement revoke; private static PreparedStatement revokeCompleted; + private static PreparedStatement finishJob; private static boolean running = true; private static Thread runner; @@ -51,21 +52,25 @@ public class SimpleSigner { throw new IllegalStateException("already running"); } running = true; - readyMail = DatabaseConnection.getInstance().prepare( - "SELECT id, csr_name, subject FROM emailcerts" + " WHERE csr_name is not null AND csr_name != ''"// - + " AND created=0"// - + " AND crt_name=''"// - + " AND warning<3"); + readyMail = DatabaseConnection + .getInstance() + .prepare( + "SELECT emailcerts.id,emailcerts.csr_name,emailcerts.subject, jobs.id FROM jobs INNER JOIN emailcerts ON emailcerts.id=jobs.targetId" + + " WHERE jobs.state='open'"// + + " AND task='sign'"); updateMail = DatabaseConnection.getInstance().prepare( "UPDATE emailcerts SET crt_name=?," + " created=NOW(), serial=? WHERE id=?"); - warnMail = DatabaseConnection.getInstance().prepare("UPDATE emailcerts SET warning=warning+1 WHERE id=?"); + warnMail = DatabaseConnection.getInstance().prepare( + "UPDATE jobs SET warning=warning+1, state=IF(warning<3, 'open','error') WHERE id=?"); revoke = DatabaseConnection.getInstance().prepare( - "SELECT id, csr_name FROM emailcerts" + " WHERE csr_name is not null"// - + " AND created != 0"// - + " AND revoked = '1970-01-01'"); + "SELECT emailcerts.id, emailcerts.csr_name,jobs.id FROM jobs INNER JOIN emailcerts ON jobs.targetId=emailcerts.id" + + " WHERE jobs.state='open' AND task='revoke'"); revokeCompleted = DatabaseConnection.getInstance().prepare("UPDATE emailcerts SET revoked=NOW() WHERE id=?"); + + finishJob = DatabaseConnection.getInstance().prepare("UPDATE jobs SET state='done' WHERE id=?"); + runner = new Thread() { @Override public void run() { @@ -118,6 +123,8 @@ public class SimpleSigner { worked = true; revokeCompleted.setInt(1, id); revokeCompleted.execute(); + finishJob.setInt(1, rs.getInt(3)); + finishJob.execute(); } else { System.out.println("Failed"); } @@ -174,13 +181,16 @@ public class SimpleSigner { updateMail.setString(2, serial.toString(16)); updateMail.setInt(3, id); updateMail.execute(); - System.out.println("sign: " + id); + + finishJob.setInt(1, rs.getInt(4)); + finishJob.execute(); + System.out.println("signed: " + id); continue; } catch (GeneralSecurityException e) { e.printStackTrace(); } System.out.println("ERROR Afterwards: " + id); - warnMail.setInt(1, id); + warnMail.setInt(1, rs.getInt(4)); warnMail.execute(); } else { BufferedReader br = new BufferedReader(new InputStreamReader(p1.getErrorStream())); @@ -190,7 +200,7 @@ public class SimpleSigner { } System.out.println(Arrays.toString(call)); System.out.println("ERROR: " + id); - warnMail.setInt(1, id); + warnMail.setInt(1, rs.getInt(4)); warnMail.execute(); } -- 2.39.2