Keep track of unique CRL URLs.

3 files changed, 50 insertions, 0 deletions

diff --git a/create_schema.sql b/create_schema.sql
index 162890a..9e7ec97 100644
--- a/create_schema.sql
+++ b/create_schema.sql
@@ -148,6 +148,35 @@ CREATE TABLE ca_certificate (
 CREATE INDEX cac_ca_cert
 	ON ca_certificate (CA_ID, CERTIFICATE_ID);
 
+CREATE TABLE crl (
+	CA_ID					integer,
+	DISTRIBUTION_POINT_URL	text,
+	THIS_UPDATE				timestamp,
+	NEXT_UPDATE				timestamp,
+	LAST_CHECKED			timestamp,
+	NEXT_CHECK_DUE			timestamp,
+	IS_ACTIVE				boolean,
+	ERROR_MESSAGE			text,
+	CRL_SHA256				bytea,
+	CONSTRAINT crl_pk
+		PRIMARY KEY (CA_ID, DISTRIBUTION_POINT_URL),
+	CONSTRAINT crl_ca_fk
+		FOREIGN KEY (CA_ID)
+		REFERENCES ca(ID)
+);
+
+CREATE INDEX crl_ia_lc
+	ON crl (IS_ACTIVE, NEXT_CHECK_DUE, DISTRIBUTION_POINT_URL);
+
+CREATE TABLE crl_revoked (
+	CA_ID					integer,
+	SERIAL_NUMBER			bytea,
+	REASON_CODE				smallint,
+	REVOCATION_DATE			timestamp,
+	LAST_SEEN_CHECK_DATE	timestamp,
+	CONSTRAINT crlr_pk
+		PRIMARY KEY (CA_ID, SERIAL_NUMBER)
+);
 
 CREATE TABLE ct_log (
 	ID						smallint,
@@ -541,6 +570,10 @@ GRANT SELECT ON certificate_identity TO crtsh;
 
 GRANT SELECT ON ca_certificate TO crtsh;
 
+GRANT SELECT ON crl TO httpd;
+
+GRANT SELECT ON crl_revoked TO httpd;
+
 GRANT SELECT ON ct_log TO crtsh;
 
 GRANT SELECT ON ct_log_entry TO crtsh;
diff --git a/drop_schema.sql b/drop_schema.sql
index 95e8699..b434414 100644
--- a/drop_schema.sql
+++ b/drop_schema.sql
@@ -67,6 +67,10 @@ DROP TABLE ct_log_entry;
 
 DROP TABLE ct_log;
 
+DROP TABLE crl_revoked;
+
+DROP TABLE crl;
+
 DROP TABLE ca_certificate;
 
 DROP TABLE certificate_identity;
diff --git a/import_cert.fnc b/import_cert.fnc
index 181e767..8db81ad 100644
--- a/import_cert.fnc
+++ b/import_cert.fnc
@@ -31,6 +31,7 @@ DECLARE
 	t_caID				ca.ID%TYPE;
 	t_lintingApplies	ca.LINTING_APPLIES%TYPE			:= TRUE;
 	l_ca				RECORD;
+	l_cdp				RECORD;
 BEGIN
 	IF cert_data IS NULL THEN
 		RETURN NULL;
@@ -139,6 +140,18 @@ BEGIN
 		PERFORM lint_cached(t_certificateID, 'x509lint');
 	END IF;
 
+	FOR l_cdp IN (
+				SELECT x509_crlDistributionPoints(cert_data) URL
+			) LOOP
+		INSERT INTO crl (
+				CA_ID, DISTRIBUTION_POINT_URL, NEXT_CHECK_DUE, IS_ACTIVE
+			)
+			VALUES (
+				t_issuerCAID, trim(l_cdp.URL), statement_timestamp(), TRUE
+			)
+			ON CONFLICT DO NOTHING;
+	END LOOP;
+
 	RETURN t_certificateID;
 
 EXCEPTION