Repository: cloudflare/cfssl
Pull request #1195 (2021-06-17 18:55:17)

pick columns to get revoked/unexpired certs
Selecting all columns to get all revoked and unexpired certificates is expensive and unnecessary. 
This PR adds a function updating existing database accessor function to select only serial_number and revoked_at


Patch:
diff --git a/certdb/certdb.go b/certdb/certdb.go
index 9c3afb2b..cc80b585 100644
--- a/certdb/certdb.go
+++ b/certdb/certdb.go
@@ -77,6 +77,7 @@ type Accessor interface {
 	GetUnexpiredCertificates() ([]CertificateRecord, error)
 	GetRevokedAndUnexpiredCertificates() ([]CertificateRecord, error)
 	GetRevokedAndUnexpiredCertificatesByLabel(label string) ([]CertificateRecord, error)
+	GetRevokedAndUnexpiredCertificatesByLabelSelectColumns(label string) ([]CertificateRecord, error)
 	RevokeCertificate(serial, aki string, reasonCode int) error
 	InsertOCSP(rr OCSPRecord) error
 	GetOCSP(serial, aki string) ([]OCSPRecord, error)
diff --git a/certdb/sql/database_accessor.go b/certdb/sql/database_accessor.go
index b6d6273d..419d6b93 100644
--- a/certdb/sql/database_accessor.go
+++ b/certdb/sql/database_accessor.go
@@ -36,6 +36,10 @@ SELECT %s FROM certificates
 SELECT %s FROM certificates
 	WHERE CURRENT_TIMESTAMP < expiry AND status='revoked' AND ca_label= ?;`
 
+	selectRevokedAndUnexpiredWithLabelSQL = `
+SELECT serial_number, revoked_at FROM certificates
+	WHERE CURRENT_TIMESTAMP < expiry AND status='revoked' AND ca_label= ?;`
+
 	selectAllRevokedAndUnexpiredSQL = `
 SELECT %s FROM certificates
 	WHERE CURRENT_TIMESTAMP < expiry AND status='revoked';`
@@ -202,6 +206,21 @@ func (d *Accessor) GetRevokedAndUnexpiredCertificatesByLabel(label string) (crs
 	return crs, nil
 }
 
+// GetRevokedAndUnexpiredCertificatesSelectColumnsByLabel gets serial_number and revoed_at from all revoked and unexpired certificate from db (for CRLs) with specified ca_label.
+func (d *Accessor) GetRevokedAndUnexpiredCertificatesByLabelSelectColumns(label string) (crs []certdb.CertificateRecord, err error) {
+	err = d.checkDB()
+	if err != nil {
+		return nil, err
+	}
+
+	err = d.db.Select(&crs, d.db.Rebind(selectRevokedAndUnexpiredWithLabelSQL), label)
+	if err != nil {
+		return nil, wrapSQLError(err)
+	}
+
+	return crs, nil
+}
+
 // RevokeCertificate updates a certificate with a given serial number and marks it revoked.
 func (d *Accessor) RevokeCertificate(serial, aki string, reasonCode int) error {
 	err := d.checkDB()


Test patch:
diff --git a/certdb/sql/sql_test.go b/certdb/sql/sql_test.go
index 38238a3c..a01886ed 100644
--- a/certdb/sql/sql_test.go
+++ b/certdb/sql/sql_test.go
@@ -280,6 +280,17 @@ func testUpdateCertificateAndGetCertificate(ta TestAccessor, t *testing.T) {
 		want.PEM != got.PEM {
 		t.Errorf("want Certificate %+v, got %+v", want, got)
 	}
+
+	rets, err = ta.Accessor.GetRevokedAndUnexpiredCertificatesByLabelSelectColumns("")
+	if err != nil {
+		t.Fatal(err)
+	}
+
+	got = rets[0]
+	// reflection comparison with zero time objects are not stable as it seems
+	if want.Serial != got.Serial || got.RevokedAt.IsZero() {
+		t.Errorf("want Certificate %+v, got %+v", want, got)
+	}
 }
 
 func testInsertOCSPAndGetOCSP(ta TestAccessor, t *testing.T) {