Merge changes I46ae11f8,I6d71e70e,Ie19e3229
authorBenny Baumann <BenBE1987@gmx.net>
Tue, 7 Nov 2017 19:55:40 +0000 (20:55 +0100)
committerGerrit Code Review <gigi-system@dogcraft.de>
Tue, 7 Nov 2017 19:55:40 +0000 (20:55 +0100)
* changes:
  chg: remove csr_name and crt_name columns from certs
  chg: use certificate attachment to store CRT and CSR files
  add: text-attachments for certificates

18 files changed:
src/club/wpia/gigi/database/DatabaseConnection.java
src/club/wpia/gigi/database/tableStructure.sql
src/club/wpia/gigi/database/upgrade/from_30.sql [new file with mode: 0644]
src/club/wpia/gigi/database/upgrade/from_32.sql [new file with mode: 0644]
src/club/wpia/gigi/dbObjects/Certificate.java
src/club/wpia/gigi/output/CertificateIterable.java
src/club/wpia/gigi/pages/account/certs/Certificates.java
src/club/wpia/gigi/pages/admin/support/SupportRevokeCertificatesForm.java
src/club/wpia/gigi/util/CertExporter.java
src/club/wpia/gigi/util/KeyStorage.java [deleted file]
tests/club/wpia/gigi/TestSeparateSessionScope.java
tests/club/wpia/gigi/api/ImportCATSResult.java
tests/club/wpia/gigi/dbObjects/TestCertificate.java
tests/club/wpia/gigi/pages/account/TestCertificateAdd.java
tests/club/wpia/gigi/pages/main/KeyCompromiseTest.java
tests/club/wpia/gigi/pages/main/KeyCompromiseTestMessage.java
util-testing/club/wpia/gigi/localisation/TranslationCollector.java
util-testing/club/wpia/gigi/util/SimpleSigner.java

index 3f0acd8..20125a8 100644 (file)
@@ -1,8 +1,12 @@
 package club.wpia.gigi.database;
 
+import java.io.File;
+import java.io.FileInputStream;
 import java.io.IOException;
 import java.io.InputStream;
+import java.io.InputStreamReader;
 import java.io.PrintWriter;
+import java.io.Reader;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
@@ -19,9 +23,64 @@ import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 
 import club.wpia.gigi.database.SQLFileManager.ImportType;
+import club.wpia.gigi.dbObjects.Certificate;
+import club.wpia.gigi.dbObjects.Certificate.AttachmentType;
 
 public class DatabaseConnection {
 
+    public static final class Upgrade32 {
+
+        public static void execute() throws IOException {
+            // "csr_name" varchar(255) NOT NULL DEFAULT '',
+            // "csr_type" "csrType" NOT NULL,
+            // "crt_name" varchar(255) NOT NULL DEFAULT '',
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `id`, `csr_name`, `crt_name` FROM `certs`")) {
+                GigiResultSet rs = ps.executeQuery();
+                while (rs.next()) {
+                    // Load CSR
+                    load(rs.getInt(1), rs.getString(2), Certificate.AttachmentType.CSR);
+                    load(rs.getInt(1), rs.getString(3), Certificate.AttachmentType.CRT);
+                }
+            }
+        }
+
+        private static void load(int id, String file, AttachmentType type) throws IOException {
+            if ("".equals(file) && type == AttachmentType.CRT) {
+                // this is ok, certificates might be in DRAFT state
+                return;
+            }
+            File f = new File(file);
+            System.out.println("Upgrade 32: loading " + f);
+            if (f.exists()) {
+                StringBuilder sb = new StringBuilder();
+                try (Reader r = new InputStreamReader(new FileInputStream(f), "UTF-8")) {
+                    int len;
+                    char[] buf = new char[4096];
+                    while ((len = r.read(buf)) > 0) {
+                        sb.append(buf, 0, len);
+                    }
+                }
+                String csrS = sb.toString();
+                try (GigiPreparedStatement ps1 = new GigiPreparedStatement("INSERT INTO `certificateAttachment` SET `certid`=?, `type`=?::`certificateAttachmentType`, `content`=?")) {
+                    ps1.setInt(1, id);
+                    ps1.setEnum(2, type);
+                    ps1.setString(3, csrS);
+                    ps1.execute();
+                }
+                f.delete();
+            } else {
+                try (GigiPreparedStatement ps1 = new GigiPreparedStatement("SELECT 1 FROM `certificateAttachment` WHERE `certid`=? AND `type`=?::`certificateAttachmentType`")) {
+                    ps1.setInt(1, id);
+                    ps1.setEnum(2, type);
+                    GigiResultSet rs1 = ps1.executeQuery();
+                    if ( !rs1.next()) {
+                        throw new Error("file " + f + " not found, and attachment is missing as well.");
+                    }
+                }
+            }
+        }
+    }
+
     public static class Link implements AutoCloseable {
 
         private DatabaseConnection target;
@@ -122,7 +181,7 @@ public class DatabaseConnection {
 
     }
 
-    public static final int CURRENT_SCHEMA_VERSION = 30;
+    public static final int CURRENT_SCHEMA_VERSION = 33;
 
     public static final int CONNECTION_TIMEOUT = 24 * 60 * 60;
 
@@ -278,10 +337,10 @@ public class DatabaseConnection {
                 while (version < CURRENT_SCHEMA_VERSION) {
                     addUpgradeScript(Integer.toString(version), s);
                     version++;
+                    System.out.println("UPGRADING Database to version " + version);
+                    s.addBatch("UPDATE \"schemeVersion\" SET version='" + version + "'");
+                    s.executeBatch();
                 }
-                s.addBatch("UPDATE \"schemeVersion\" SET version='" + version + "'");
-                System.out.println("UPGRADING Database to version " + version);
-                s.executeBatch();
                 System.out.println("done.");
             }
         } catch (SQLException e) {
@@ -292,11 +351,15 @@ public class DatabaseConnection {
     }
 
     private static void addUpgradeScript(String version, Statement s) throws Error, IOException, SQLException {
-        try (InputStream resourceAsStream = DatabaseConnection.class.getResourceAsStream("upgrade/from_" + version + ".sql")) {
-            if (resourceAsStream == null) {
-                throw new Error("Upgrade script from version " + version + " was not found.");
+        if (version.equals("31")) {
+            Upgrade32.execute();
+        } else {
+            try (InputStream resourceAsStream = DatabaseConnection.class.getResourceAsStream("upgrade/from_" + version + ".sql")) {
+                if (resourceAsStream == null) {
+                    throw new Error("Upgrade script from version " + version + " was not found.");
+                }
+                SQLFileManager.addFile(s, resourceAsStream, ImportType.PRODUCTION);
             }
-            SQLFileManager.addFile(s, resourceAsStream, ImportType.PRODUCTION);
         }
     }
 
index 416d6a1..bf457af 100644 (file)
@@ -156,9 +156,7 @@ CREATE TABLE "certs" (
   "profile" int NOT NULL,
   "caid" int NULL DEFAULT NULL,
 
-  "csr_name" varchar(255) NOT NULL DEFAULT '',
   "csr_type" "csrType" NOT NULL,
-  "crt_name" varchar(255) NOT NULL DEFAULT '',
   "created" timestamp NULL DEFAULT NULL,
   "modified" timestamp NULL DEFAULT NULL,
 
@@ -181,7 +179,6 @@ CREATE INDEX ON "certs" ("created");
 CREATE INDEX ON "certs" ("memid");
 CREATE INDEX ON "certs" ("serial");
 CREATE INDEX ON "certs" ("expire");
-CREATE INDEX ON "certs" ("crt_name");
 
 DROP TABLE IF EXISTS "certAvas";
 CREATE TABLE "certAvas" (
@@ -381,7 +378,7 @@ CREATE TABLE "schemeVersion" (
   "version" smallint NOT NULL,
   PRIMARY KEY ("version")
 );
-INSERT INTO "schemeVersion" (version)  VALUES(30);
+INSERT INTO "schemeVersion" (version)  VALUES(33);
 
 DROP TABLE IF EXISTS `passwordResetTickets`;
 CREATE TABLE `passwordResetTickets` (
@@ -686,3 +683,15 @@ CREATE TABLE "nameParts" (
   "type" "namePartType" NOT NULL,
   "value" varchar(255) NOT NULL
 );
+
+
+DROP TABLE IF EXISTS "certificateAttachment";
+DROP TYPE IF EXISTS "certificateAttachmentType";
+CREATE TYPE "certificateAttachmentType" AS ENUM ('CSR','CRT');
+
+CREATE TABLE "certificateAttachment" (
+  "certid" int NOT NULL,
+  "type" "certificateAttachmentType" NOT NULL,
+  "content" text NOT NULL,
+  PRIMARY KEY ("certid", "type")
+);
diff --git a/src/club/wpia/gigi/database/upgrade/from_30.sql b/src/club/wpia/gigi/database/upgrade/from_30.sql
new file mode 100644 (file)
index 0000000..bc9ccd4
--- /dev/null
@@ -0,0 +1,8 @@
+CREATE TYPE "certificateAttachmentType" AS ENUM ('CSR','CRT');
+
+CREATE TABLE "certificateAttachment" (
+  "certid" int NOT NULL,
+  "type" "certificateAttachmentType" NOT NULL,
+  "content" text NOT NULL,
+  PRIMARY KEY ("certid", "type")
+);
diff --git a/src/club/wpia/gigi/database/upgrade/from_32.sql b/src/club/wpia/gigi/database/upgrade/from_32.sql
new file mode 100644 (file)
index 0000000..6cee9bb
--- /dev/null
@@ -0,0 +1,2 @@
+ALTER TABLE "certs" DROP COLUMN "csr_name";
+ALTER TABLE "certs" DROP COLUMN "crt_name";
index bd1e774..51bf41b 100644 (file)
@@ -1,10 +1,7 @@
 package club.wpia.gigi.dbObjects;
 
-import java.io.File;
-import java.io.FileInputStream;
-import java.io.FileOutputStream;
+import java.io.ByteArrayInputStream;
 import java.io.IOException;
-import java.io.InputStream;
 import java.security.GeneralSecurityException;
 import java.security.cert.CertificateFactory;
 import java.security.cert.X509Certificate;
@@ -24,7 +21,7 @@ import club.wpia.gigi.database.GigiResultSet;
 import club.wpia.gigi.output.template.Outputable;
 import club.wpia.gigi.output.template.TranslateCommand;
 import club.wpia.gigi.pages.account.certs.CertificateRequest;
-import club.wpia.gigi.util.KeyStorage;
+import club.wpia.gigi.util.PEM;
 
 public class Certificate implements IdCachable {
 
@@ -47,6 +44,15 @@ public class Certificate implements IdCachable {
         }
     }
 
+    public enum AttachmentType implements DBEnum {
+        CSR, CRT;
+
+        @Override
+        public String getDBName() {
+            return toString();
+        }
+    }
+
     public enum SANType implements DBEnum {
         EMAIL("email"), DNS("DNS");
 
@@ -142,10 +148,6 @@ public class Certificate implements IdCachable {
 
     private Digest md;
 
-    private String csrName;
-
-    private String crtName;
-
     private String csr = null;
 
     private CSRType csrType;
@@ -204,7 +206,7 @@ public class Certificate implements IdCachable {
         this.sans = Arrays.asList(sans);
         synchronized (Certificate.class) {
 
-            try (GigiPreparedStatement inserter = new GigiPreparedStatement("INSERT INTO certs SET md=?::`mdType`, csr_type=?::`csrType`, crt_name='', memid=?, profile=?")) {
+            try (GigiPreparedStatement inserter = new GigiPreparedStatement("INSERT INTO certs SET md=?::`mdType`, csr_type=?::`csrType`, memid=?, profile=?")) {
                 inserter.setString(1, md.toString().toLowerCase());
                 inserter.setString(2, this.csrType.toString());
                 inserter.setInt(3, owner.getId());
@@ -230,17 +232,7 @@ public class Certificate implements IdCachable {
                     insertAVA.execute();
                 }
             }
-            File csrFile = KeyStorage.locateCsr(id);
-            csrName = csrFile.getPath();
-            try (FileOutputStream fos = new FileOutputStream(csrFile)) {
-                fos.write(this.csr.getBytes("UTF-8"));
-            }
-            try (GigiPreparedStatement updater = new GigiPreparedStatement("UPDATE `certs` SET `csr_name`=? WHERE id=?")) {
-                updater.setString(1, csrName);
-                updater.setInt(2, id);
-                updater.execute();
-            }
-
+            addAttachment(AttachmentType.CSR, csr);
             cache.put(this);
         }
     }
@@ -249,8 +241,6 @@ public class Certificate implements IdCachable {
         this.id = rs.getInt("id");
         dnString = rs.getString("subject");
         md = Digest.valueOf(rs.getString("md").toUpperCase());
-        csrName = rs.getString("csr_name");
-        crtName = rs.getString("crt_name");
         owner = CertificateOwner.getById(rs.getInt("memid"));
         profile = CertificateProfile.getById(rs.getInt("profile"));
         this.serial = rs.getString("serial");
@@ -302,20 +292,19 @@ public class Certificate implements IdCachable {
     }
 
     public synchronized CertificateStatus getStatus() {
-        try (GigiPreparedStatement searcher = new GigiPreparedStatement("SELECT crt_name, created, revoked, serial, caid FROM certs WHERE id=?")) {
+        try (GigiPreparedStatement searcher = new GigiPreparedStatement("SELECT created, revoked, serial, caid FROM certs WHERE id=?")) {
             searcher.setInt(1, id);
             GigiResultSet rs = searcher.executeQuery();
             if ( !rs.next()) {
                 throw new IllegalStateException("Certificate not in Database");
             }
 
-            crtName = rs.getString(1);
-            serial = rs.getString(4);
-            if (rs.getTimestamp(2) == null) {
+            serial = rs.getString(3);
+            if (rs.getTimestamp(1) == null) {
                 return CertificateStatus.DRAFT;
             }
             ca = CACertificate.getById(rs.getInt("caid"));
-            if (rs.getTimestamp(2) != null && rs.getTimestamp(3) == null) {
+            if (rs.getTimestamp(1) != null && rs.getTimestamp(2) == null) {
                 return CertificateStatus.ISSUED;
             }
             return CertificateStatus.REVOKED;
@@ -367,23 +356,16 @@ public class Certificate implements IdCachable {
         return ca;
     }
 
-    public X509Certificate cert() throws IOException, GeneralSecurityException {
+    public X509Certificate cert() throws IOException, GeneralSecurityException, GigiApiException {
         CertificateStatus status = getStatus();
         if (status != CertificateStatus.REVOKED && status != CertificateStatus.ISSUED) {
             throw new IllegalStateException(status + " is not wanted here.");
         }
-        InputStream is = null;
-        X509Certificate crt = null;
-        try {
-            is = new FileInputStream(crtName);
+        String crtS = getAttachment(AttachmentType.CRT);
+        try (ByteArrayInputStream bais = new ByteArrayInputStream(PEM.decode("CERTIFICATE", crtS))) {
             CertificateFactory cf = CertificateFactory.getInstance("X.509");
-            crt = (X509Certificate) cf.generateCertificate(is);
-        } finally {
-            if (is != null) {
-                is.close();
-            }
+            return (X509Certificate) cf.generateCertificate(bais);
         }
-        return crt;
     }
 
     public Certificate renew() {
@@ -426,7 +408,7 @@ public class Certificate implements IdCachable {
         if (serial == null || "".equals(serial)) {
             return null;
         }
-        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as `subject`, `md`, `csr_name`, `crt_name`,`memid`, `profile`, `certs`.`serial` FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=`certs`.`id` WHERE `serial`=? GROUP BY `certs`.`id`")) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as `subject`, `md`,`memid`, `profile`, `certs`.`serial` FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=`certs`.`id` WHERE `serial`=? GROUP BY `certs`.`id`")) {
             ps.setString(1, serial);
             GigiResultSet rs = ps.executeQuery();
             if ( !rs.next()) {
@@ -452,7 +434,7 @@ public class Certificate implements IdCachable {
         }
 
         try {
-            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as subject, md, csr_name, crt_name,memid, profile, certs.serial FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=certs.id WHERE certs.id=? GROUP BY certs.id")) {
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as subject, md, memid, profile, certs.serial FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=certs.id WHERE certs.id=? GROUP BY certs.id")) {
                 ps.setInt(1, id);
                 GigiResultSet rs = ps.executeQuery();
                 if ( !rs.next()) {
@@ -553,4 +535,35 @@ public class Certificate implements IdCachable {
         }
         return certs;
     }
+
+    public void addAttachment(AttachmentType tp, String data) throws GigiApiException {
+        if (getAttachment(tp) != null) {
+            throw new GigiApiException("Cannot override attachment");
+        }
+        if (data == null) {
+            throw new GigiApiException("Attachment must not be null");
+        }
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `certificateAttachment` SET `certid`=?, `type`=?::`certificateAttachmentType`, `content`=?")) {
+            ps.setInt(1, getId());
+            ps.setEnum(2, tp);
+            ps.setString(3, data);
+            ps.execute();
+        }
+    }
+
+    public String getAttachment(AttachmentType tp) throws GigiApiException {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `content` FROM `certificateAttachment` WHERE `certid`=? AND `type`=?::`certificateAttachmentType`")) {
+            ps.setInt(1, getId());
+            ps.setEnum(2, tp);
+            GigiResultSet rs = ps.executeQuery();
+            if ( !rs.next()) {
+                return null;
+            }
+            String s = rs.getString(1);
+            if (rs.next()) {
+                throw new GigiApiException("Invalid database state");
+            }
+            return s;
+        }
+    }
 }
index 15abbf0..cb4b352 100644 (file)
@@ -6,6 +6,7 @@ import java.security.cert.X509Certificate;
 import java.util.Date;
 import java.util.Map;
 
+import club.wpia.gigi.GigiApiException;
 import club.wpia.gigi.dbObjects.Certificate;
 import club.wpia.gigi.dbObjects.Certificate.CertificateStatus;
 import club.wpia.gigi.localisation.Language;
@@ -68,6 +69,8 @@ public class CertificateIterable implements IterableDataset {
             if (st == CertificateStatus.REVOKED) {
                 vars.put("revoked", c.getRevocationDate());
             }
+        } catch (GigiApiException e) {
+            e.printStackTrace();
         } catch (IOException e) {
             e.printStackTrace();
         } catch (GeneralSecurityException e) {
index 5abf20e..78d8a09 100644 (file)
@@ -13,6 +13,7 @@ import javax.servlet.ServletOutputStream;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 
+import club.wpia.gigi.GigiApiException;
 import club.wpia.gigi.dbObjects.Certificate;
 import club.wpia.gigi.dbObjects.Certificate.CertificateStatus;
 import club.wpia.gigi.dbObjects.Certificate.SubjectAlternateName;
@@ -93,6 +94,9 @@ public class Certificates extends Page implements HandlesMixedRequest {
         } catch (IllegalArgumentException e) {
             resp.sendError(404);
             return true;
+        } catch (GigiApiException e) {
+            resp.sendError(404);
+            return true;
         } catch (GeneralSecurityException e) {
             resp.sendError(404);
             return true;
@@ -226,6 +230,8 @@ public class Certificates extends Page implements HandlesMixedRequest {
                 vars.put("login", c.isLoginEnabled());
             } catch (GeneralSecurityException e) {
                 e.printStackTrace();
+            } catch (GigiApiException e) {
+                e.format(out, l);
             }
             certDisplay.output(out, getLanguage(req), vars);
 
index f3d725c..bf4c009 100644 (file)
@@ -10,9 +10,9 @@ import javax.servlet.http.HttpServletRequest;
 
 import club.wpia.gigi.GigiApiException;
 import club.wpia.gigi.dbObjects.Certificate;
+import club.wpia.gigi.dbObjects.Certificate.CertificateStatus;
 import club.wpia.gigi.dbObjects.CertificateProfile;
 import club.wpia.gigi.dbObjects.SupportedUser;
-import club.wpia.gigi.dbObjects.Certificate.CertificateStatus;
 import club.wpia.gigi.localisation.Language;
 import club.wpia.gigi.output.template.Form;
 import club.wpia.gigi.output.template.IterableDataset;
@@ -71,7 +71,7 @@ public class SupportRevokeCertificatesForm extends Form {
                         certs[i].cert().checkValidity();
                         lastExpire = Math.max(lastExpire, certs[i].cert().getNotAfter().getTime());
                         valid++;
-                    } catch (GeneralSecurityException | IOException e) {
+                    } catch (GeneralSecurityException | IOException | GigiApiException e) {
                         continue;
                     }
                 }
index cfe97ef..06102fc 100644 (file)
@@ -14,6 +14,7 @@ import java.util.Set;
 
 import javax.servlet.ServletOutputStream;
 
+import club.wpia.gigi.GigiApiException;
 import club.wpia.gigi.dbObjects.CACertificate;
 import club.wpia.gigi.dbObjects.Certificate;
 import sun.security.pkcs.ContentInfo;
@@ -29,7 +30,7 @@ public class CertExporter {
 
     private CertExporter() {}
 
-    public static void writeCertCrt(Certificate c, ServletOutputStream out, boolean doChain, boolean includeAnchor, boolean includeLeaf) throws IOException, GeneralSecurityException {
+    public static void writeCertCrt(Certificate c, ServletOutputStream out, boolean doChain, boolean includeAnchor, boolean includeLeaf) throws IOException, GeneralSecurityException, GigiApiException {
         X509Certificate cert = c.cert();
         if (includeLeaf) {
             out.println(PEM.encode("CERTIFICATE", cert.getEncoded()));
@@ -46,7 +47,7 @@ public class CertExporter {
         }
     }
 
-    public static void writeCertCer(Certificate c, ServletOutputStream out, boolean doChain, boolean includeAnchor) throws IOException, GeneralSecurityException {
+    public static void writeCertCer(Certificate c, ServletOutputStream out, boolean doChain, boolean includeAnchor) throws IOException, GeneralSecurityException, GigiApiException {
         X509Certificate cert = c.cert();
         if (doChain) {
             PKCS7 p7 = toP7Chain(c);
@@ -56,7 +57,7 @@ public class CertExporter {
         }
     }
 
-    private static PKCS7 toP7Chain(Certificate c) throws IOException, GeneralSecurityException {
+    private static PKCS7 toP7Chain(Certificate c) throws IOException, GeneralSecurityException, GigiApiException {
         LinkedList<X509Certificate> ll = getChain(c);
         PKCS7 p7 = new PKCS7(new AlgorithmId[0], new ContentInfo(ContentInfo.DATA_OID, null), ll.toArray(new X509Certificate[ll.size()]), new SignerInfo[0]) {
 
@@ -151,7 +152,7 @@ public class CertExporter {
         return p7;
     }
 
-    private static LinkedList<X509Certificate> getChain(Certificate c) throws IOException, GeneralSecurityException {
+    private static LinkedList<X509Certificate> getChain(Certificate c) throws IOException, GeneralSecurityException, GigiApiException {
         LinkedList<X509Certificate> ll = new LinkedList<>();
         ll.add(c.cert());
         CACertificate ca = c.getParent();
diff --git a/src/club/wpia/gigi/util/KeyStorage.java b/src/club/wpia/gigi/util/KeyStorage.java
deleted file mode 100644 (file)
index b87df9a..0000000
+++ /dev/null
@@ -1,26 +0,0 @@
-package club.wpia.gigi.util;
-
-import java.io.File;
-
-public class KeyStorage {
-
-    private static final File csr = new File("keys/csr");
-
-    private static final File crt = new File("keys/crt");
-
-    public static File locateCrt(int id) {
-        File parent = new File(crt, (id / 1000) + "");
-        if ( !parent.exists() && !parent.mkdirs()) {
-            throw new Error("cert folder could not be created");
-        }
-        return new File(parent, id + ".crt");
-    }
-
-    public static File locateCsr(int id) {
-        File parent = new File(csr, (id / 1000) + "");
-        if ( !parent.exists() && !parent.mkdirs()) {
-            throw new Error("csr folder could not be created");
-        }
-        return new File(parent, id + ".csr");
-    }
-}
index e3ad928..02635be 100644 (file)
@@ -6,9 +6,7 @@ import java.io.IOException;
 import java.net.HttpURLConnection;
 import java.net.URL;
 import java.security.GeneralSecurityException;
-import java.security.KeyManagementException;
 import java.security.KeyPair;
-import java.security.NoSuchAlgorithmException;
 import java.security.PrivateKey;
 import java.security.cert.X509Certificate;
 import java.sql.SQLException;
@@ -69,7 +67,7 @@ public class TestSeparateSessionScope extends ManagedTest {
 
     }
 
-    private void checkCertLogin(Certificate c2, final PrivateKey pk, String scookie, int expected) throws IOException, NoSuchAlgorithmException, KeyManagementException, GeneralSecurityException {
+    private void checkCertLogin(Certificate c2, final PrivateKey pk, String scookie, int expected) throws IOException, GeneralSecurityException, GigiApiException {
         URL u = new URL("https://" + getSecureServerName() + SECURE_REFERENCE);
         HttpURLConnection huc = (HttpURLConnection) u.openConnection();
         authenticateClientCert(pk, c2.cert(), huc);
index 18f1ae9..c345257 100644 (file)
@@ -14,15 +14,13 @@ import java.security.NoSuchAlgorithmException;
 import org.junit.Test;
 
 import club.wpia.gigi.GigiApiException;
-import club.wpia.gigi.api.CATSImport;
-import club.wpia.gigi.api.CATSResolve;
+import club.wpia.gigi.dbObjects.CATS.CATSType;
 import club.wpia.gigi.dbObjects.Certificate;
+import club.wpia.gigi.dbObjects.Certificate.CSRType;
+import club.wpia.gigi.dbObjects.Certificate.SANType;
 import club.wpia.gigi.dbObjects.CertificateProfile;
 import club.wpia.gigi.dbObjects.Digest;
 import club.wpia.gigi.dbObjects.User;
-import club.wpia.gigi.dbObjects.CATS.CATSType;
-import club.wpia.gigi.dbObjects.Certificate.CSRType;
-import club.wpia.gigi.dbObjects.Certificate.SANType;
 import club.wpia.gigi.testUtils.IOUtils;
 import club.wpia.gigi.testUtils.RestrictedApiTest;
 import club.wpia.gigi.util.ServerConstants;
@@ -89,7 +87,7 @@ public class ImportCATSResult extends RestrictedApiTest {
         return doApi(CATSImport.PATH, query);
     }
 
-    private String apiLookup(Certificate target) throws IOException, GeneralSecurityException {
+    private String apiLookup(Certificate target) throws IOException, GeneralSecurityException, GigiApiException {
         HttpURLConnection connection = doApi(CATSResolve.PATH, "serial=" + target.cert().getSerialNumber().toString(16).toLowerCase());
         if (connection.getResponseCode() != 200) {
             throw new Error(connection.getResponseMessage());
index 519bd59..fe35729 100644 (file)
@@ -9,8 +9,7 @@ import java.security.KeyPair;
 import org.junit.Test;
 
 import club.wpia.gigi.GigiApiException;
-import club.wpia.gigi.dbObjects.Certificate;
-import club.wpia.gigi.dbObjects.Digest;
+import club.wpia.gigi.dbObjects.Certificate.AttachmentType;
 import club.wpia.gigi.dbObjects.Certificate.CSRType;
 import club.wpia.gigi.testUtils.ClientBusinessTest;
 
@@ -32,4 +31,40 @@ public class TestCertificate extends ClientBusinessTest {
         c.setLoginEnabled(false);
         assertFalse(c.isLoginEnabled());
     }
+
+    @Test
+    public void testAttachment() throws GeneralSecurityException, IOException, GigiApiException {
+        KeyPair kp = generateKeypair();
+        String key = generatePEMCSR(kp, "CN=testmail@example.com");
+        Certificate c = new Certificate(u, u, Certificate.buildDN("CN", "testmail@example.com"), Digest.SHA256, key, CSRType.CSR, getClientProfile());
+        assertNull(c.getAttachment(AttachmentType.CRT));
+        assertEquals(key, c.getAttachment(AttachmentType.CSR));
+        try {
+            c.addAttachment(AttachmentType.CSR, "different CSR");
+            fail("double add attachment must fail");
+        } catch (GigiApiException e) {
+            // expected
+        }
+        assertNull(c.getAttachment(AttachmentType.CRT));
+        assertEquals(key, c.getAttachment(AttachmentType.CSR));
+        try {
+            c.addAttachment(AttachmentType.CRT, null);
+            fail("attachment must not be null");
+        } catch (GigiApiException e) {
+            // expected
+        }
+        assertNull(c.getAttachment(AttachmentType.CRT));
+        assertEquals(key, c.getAttachment(AttachmentType.CSR));
+        c.addAttachment(AttachmentType.CRT, "b");
+        assertEquals(key, c.getAttachment(AttachmentType.CSR));
+        assertEquals("b", c.getAttachment(AttachmentType.CRT));
+        try {
+            c.addAttachment(AttachmentType.CRT, "different CRT");
+            fail("double add attachment must fail");
+        } catch (GigiApiException e) {
+            // expected
+        }
+        assertEquals(key, c.getAttachment(AttachmentType.CSR));
+        assertEquals("b", c.getAttachment(AttachmentType.CRT));
+    }
 }
index 8d46cf2..bac6217 100644 (file)
@@ -88,7 +88,6 @@ public class TestCertificateAdd extends ClientTest {
         }, new DNSName(uniq + ".tld"));
 
         String pem = generatePEMCSR(kp, "CN=a." + uniq + ".tld", atts);
-
         String[] res = fillOutForm("CSR=" + URLEncoder.encode(pem, "UTF-8"));
         assertArrayEquals(new String[] {
                 "server", CertificateRequest.DEFAULT_CN, "dns:a." + uniq + ".tld\ndns:" + uniq + ".tld\n", Digest.SHA512.toString()
index 46ffe0d..49ffd80 100644 (file)
@@ -127,7 +127,7 @@ public class KeyCompromiseTest extends ClientTest {
         };
     }
 
-    private String getQuery(String data) {
+    private String getQuery(String data) throws GigiApiException {
         String cData = null;
         {
             Pattern challenge = Pattern.compile(" data-challenge=\"([a-zA-Z0-9]+)\"");
index 7dfb415..0cc355d 100644 (file)
@@ -105,7 +105,7 @@ public class KeyCompromiseTestMessage extends ClientTest {
         assertEquals(CertificateStatus.ISSUED, cert.getStatus());
     }
 
-    private TestMail reportCompromiseAndCheck(String params) throws IOException, UnsupportedEncodingException, CertificateEncodingException, GeneralSecurityException {
+    private TestMail reportCompromiseAndCheck(String params) throws IOException, UnsupportedEncodingException, CertificateEncodingException, GeneralSecurityException, GigiApiException {
         HttpURLConnection huc = reportCompromise(params);
         assertThat(IOUtils.readURL(huc), hasNoError());
         TestMail rc = getMailReceiver().receive(email);
@@ -115,7 +115,7 @@ public class KeyCompromiseTestMessage extends ClientTest {
         return rc;
     }
 
-    private HttpURLConnection reportCompromise(String params) throws IOException, UnsupportedEncodingException, CertificateEncodingException, GeneralSecurityException {
+    private HttpURLConnection reportCompromise(String params) throws IOException, UnsupportedEncodingException, CertificateEncodingException, GeneralSecurityException, GigiApiException {
         if ( !params.isEmpty() && !params.startsWith("&")) {
             params = "&" + params;
         }
index 239c32f..2883efd 100644 (file)
@@ -219,7 +219,10 @@ public class TranslationCollector {
                     throw new Error();
                 }
             }
-
+            // Skip Database connection as some statements need to be run on older DB scheme version.
+            if(new String(parsedUnit.getFileName()).endsWith("/src/club/wpia/gigi/database/DatabaseConnection.java")) {
+                continue;
+            }
             if (parsedUnit.types == null) {
                 System.err.println("No types");
 
index 45d86a7..b1c3150 100644 (file)
@@ -1,5 +1,6 @@
 package club.wpia.gigi.util;
 
+import java.io.ByteArrayInputStream;
 import java.io.File;
 import java.io.FileInputStream;
 import java.io.FileNotFoundException;
@@ -39,11 +40,14 @@ import java.util.TimeZone;
 
 import javax.security.auth.x500.X500Principal;
 
+import club.wpia.gigi.GigiApiException;
 import club.wpia.gigi.crypto.SPKAC;
 import club.wpia.gigi.database.DatabaseConnection;
 import club.wpia.gigi.database.DatabaseConnection.Link;
 import club.wpia.gigi.database.GigiPreparedStatement;
 import club.wpia.gigi.database.GigiResultSet;
+import club.wpia.gigi.dbObjects.Certificate;
+import club.wpia.gigi.dbObjects.Certificate.AttachmentType;
 import club.wpia.gigi.dbObjects.Certificate.CSRType;
 import club.wpia.gigi.dbObjects.Certificate.SANType;
 import club.wpia.gigi.dbObjects.Certificate.SubjectAlternateName;
@@ -125,7 +129,7 @@ public class SimpleSigner {
             @Override
             public void run() {
                 try (Link l = DatabaseConnection.newLink(false)) {
-                    readyCerts = new GigiPreparedStatement("SELECT certs.id AS id, certs.csr_name, jobs.id AS jobid, csr_type, md, `executeFrom`, `executeTo`, profile FROM jobs " + //
+                    readyCerts = new GigiPreparedStatement("SELECT certs.id AS id, jobs.id AS jobid, csr_type, md, `executeFrom`, `executeTo`, profile FROM jobs " + //
                             "INNER JOIN certs ON certs.id=jobs.`targetId` " + //
                             "INNER JOIN profiles ON profiles.id=certs.profile " + //
                             "WHERE jobs.state='open' " + //
@@ -134,10 +138,10 @@ public class SimpleSigner {
                     getSANSs = new GigiPreparedStatement("SELECT contents, type FROM `subjectAlternativeNames` " + //
                             "WHERE `certId`=?");
 
-                    updateMail = new GigiPreparedStatement("UPDATE certs SET crt_name=?," + " created=NOW(), serial=?, caid=?, expire=? WHERE id=?");
+                    updateMail = new GigiPreparedStatement("UPDATE certs SET created=NOW(), serial=?, caid=?, expire=? WHERE id=?");
                     warnMail = new GigiPreparedStatement("UPDATE jobs SET warning=warning+1, state=CASE WHEN warning<3 THEN 'open'::`jobState` ELSE 'error'::`jobState` END WHERE id=?");
 
-                    revoke = new GigiPreparedStatement("SELECT certs.id, certs.csr_name,jobs.id FROM jobs INNER JOIN certs ON jobs.`targetId`=certs.id" + " WHERE jobs.state='open' AND task='revoke'");
+                    revoke = new GigiPreparedStatement("SELECT certs.id, jobs.id FROM jobs INNER JOIN certs ON jobs.`targetId`=certs.id" + " WHERE jobs.state='open' AND task='revoke'");
                     revokeCompleted = new GigiPreparedStatement("UPDATE `certs` SET revoked=NOW() WHERE id=?");
 
                     finishJob = new GigiPreparedStatement("UPDATE jobs SET state='done' WHERE id=?");
@@ -200,7 +204,7 @@ public class SimpleSigner {
             System.out.println("Revoke faked: " + id);
             revokeCompleted.setInt(1, id);
             revokeCompleted.executeUpdate();
-            finishJob.setInt(1, rs.getInt(3));
+            finishJob.setInt(1, rs.getInt(2));
             finishJob.executeUpdate();
         }
         if (worked) {
@@ -240,13 +244,12 @@ public class SimpleSigner {
         Calendar c = Calendar.getInstance();
         c.setTimeZone(TimeZone.getTimeZone("UTC"));
         while (rs.next()) {
-            String csrname = rs.getString("csr_name");
             int id = rs.getInt("id");
-            System.out.println("sign: " + csrname);
+            System.out.println("sign: " + id);
             try {
+                Certificate crt = Certificate.getById(id);
                 String csrType = rs.getString("csr_type");
                 CSRType ct = CSRType.valueOf(csrType);
-                File crt = KeyStorage.locateCrt(id);
 
                 Timestamp from = rs.getTimestamp("executeFrom");
                 String length = rs.getString("executeTo");
@@ -315,7 +318,7 @@ public class SimpleSigner {
                 System.out.println(subj);
 
                 PublicKey pk;
-                byte[] data = IOUtils.readURL(new FileInputStream(csrname));
+                byte[] data = crt.getAttachment(AttachmentType.CSR).getBytes("UTF-8");
                 if (ct == CSRType.SPKAC) {
                     String dt = new String(data, "UTF-8");
                     if (dt.startsWith("SPKAC=")) {
@@ -350,13 +353,13 @@ public class SimpleSigner {
 
                 X509Certificate root = (X509Certificate) CertificateFactory.getInstance("X509").generateCertificate(new FileInputStream("signer/ca/" + ca + "/ca.crt"));
                 byte[] cert = generateCert(pk, i, subj, root.getSubjectX500Principal(), altnames, fromDate, toDate, Digest.valueOf(rs.getString("md").toUpperCase()), caP.getProperty("eku"));
-                PrintWriter out = new PrintWriter(crt);
-                out.println("-----BEGIN CERTIFICATE-----");
-                out.println(Base64.getMimeEncoder().encodeToString(cert));
-                out.println("-----END CERTIFICATE-----");
-                out.close();
+                StringBuilder b = new StringBuilder();
+                b.append("-----BEGIN CERTIFICATE-----\r\n");
+                b.append(Base64.getMimeEncoder().encodeToString(cert));
+                b.append("-----END CERTIFICATE-----\r\n");
+                crt.addAttachment(AttachmentType.CRT, b.toString());
 
-                try (InputStream is = new FileInputStream(crt)) {
+                try (InputStream is = new ByteArrayInputStream(cert)) {
                     locateCA.setString(1, ca);
                     GigiResultSet caRs = locateCA.executeQuery();
                     if ( !caRs.next()) {
@@ -366,11 +369,10 @@ public class SimpleSigner {
                     CertificateFactory cf = CertificateFactory.getInstance("X.509");
                     X509Certificate crtp = (X509Certificate) cf.generateCertificate(is);
                     BigInteger serial = crtp.getSerialNumber();
-                    updateMail.setString(1, crt.getPath());
-                    updateMail.setString(2, serial.toString(16));
-                    updateMail.setInt(3, caRs.getInt("id"));
-                    updateMail.setTimestamp(4, new Timestamp(toDate.getTime()));
-                    updateMail.setInt(5, id);
+                    updateMail.setString(1, serial.toString(16));
+                    updateMail.setInt(2, caRs.getInt("id"));
+                    updateMail.setTimestamp(3, new Timestamp(toDate.getTime()));
+                    updateMail.setInt(4, id);
                     updateMail.executeUpdate();
 
                     finishJob.setInt(1, rs.getInt("jobid"));
@@ -385,6 +387,8 @@ public class SimpleSigner {
                 e.printStackTrace();
             } catch (ParseException e) {
                 e.printStackTrace();
+            } catch (GigiApiException e) {
+                e.printStackTrace();
             }
             System.out.println("Error with: " + id);
             warnMail.setInt(1, rs.getInt("jobid"));