// initTables constructs the table map for the ORM.
// NOTE: For tables with an auto-increment primary key (SetKeys(true, ...)),
// it is very important to declare them as a such here. It produces a side
// effect in Insert() where the inserted object has its id field set to the
// autoincremented value that resulted from the insert. See
// https://godoc.org/github.com/coopernurse/gorp#DbMap.Insert
func initTables(dbMap *gorp.DbMap) {
var regTable *gorp.TableMap
if features.Enabled(features.AllowAccountDeactivation) {
regTable = dbMap.AddTableWithName(regModelv2{}, "registrations").SetKeys(true, "ID")
} else {
regTable = dbMap.AddTableWithName(regModelv1{}, "registrations").SetKeys(true, "ID")
}
regTable.SetVersionCol("LockCol")
regTable.ColMap("Key").SetNotNull(true)
regTable.ColMap("KeySHA256").SetNotNull(true).SetUnique(true)
pendingAuthzTable := dbMap.AddTableWithName(pendingauthzModel{}, "pendingAuthorizations").SetKeys(false, "ID")
pendingAuthzTable.SetVersionCol("LockCol")
dbMap.AddTableWithName(authzModel{}, "authz").SetKeys(false, "ID")
dbMap.AddTableWithName(challModel{}, "challenges").SetKeys(true, "ID").SetVersionCol("LockCol")
dbMap.AddTableWithName(issuedNameModel{}, "issuedNames").SetKeys(true, "ID")
dbMap.AddTableWithName(core.Certificate{}, "certificates").SetKeys(false, "Serial")
dbMap.AddTableWithName(core.CertificateStatus{}, "certificateStatus").SetKeys(false, "Serial").SetVersionCol("LockCol")
dbMap.AddTableWithName(core.CRL{}, "crls").SetKeys(false, "Serial")
dbMap.AddTableWithName(core.SignedCertificateTimestamp{}, "sctReceipts").SetKeys(true, "ID").SetVersionCol("LockCol")
dbMap.AddTableWithName(core.FQDNSet{}, "fqdnSets").SetKeys(true, "ID")
// TODO(@cpu): Delete these table maps when the `CertStatusOptimizationsMigrated` feature flag is removed
if features.Enabled(features.CertStatusOptimizationsMigrated) {
dbMap.AddTableWithName(certStatusModelv2{}, "certificateStatus").SetKeys(false, "Serial").SetVersionCol("LockCol")
} else {
dbMap.AddTableWithName(certStatusModelv1{}, "certificateStatus").SetKeys(false, "Serial").SetVersionCol("LockCol")
}
}
// oldOCSPResponsesTick looks for certificates with stale OCSP responses and
// generates/stores new ones
func (updater *OCSPUpdater) oldOCSPResponsesTick(ctx context.Context, batchSize int) error {
tickStart := updater.clk.Now()
statuses, err := updater.findStaleOCSPResponses(tickStart.Add(-updater.ocspMinTimeToExpiry), batchSize)
if err != nil {
updater.stats.Inc("Errors.FindStaleResponses", 1)
updater.log.AuditErr(fmt.Sprintf("Failed to find stale OCSP responses: %s", err))
return err
}
tickEnd := updater.clk.Now()
updater.stats.TimingDuration("oldOCSPResponsesTick.QueryTime", tickEnd.Sub(tickStart))
// If the CertStatusOptimizationsMigrated flag is set then we need to
// opportunistically update the certificateStatus `isExpired` column for expired
// certificates we come across
if features.Enabled(features.CertStatusOptimizationsMigrated) {
for _, s := range statuses {
if !s.IsExpired && tickStart.After(s.NotAfter) {
err := updater.markExpired(s)
if err != nil {
return err
}
}
}
}
return updater.generateOCSPResponses(ctx, statuses, updater.stats.NewScope("oldOCSPResponsesTick"))
}
// newReg creates a reg model object from a core.Registration
func registrationToModel(r *core.Registration) (interface{}, error) {
key, err := json.Marshal(r.Key)
if err != nil {
return nil, err
}
sha, err := core.KeyDigest(r.Key)
if err != nil {
return nil, err
}
if r.InitialIP == nil {
return nil, fmt.Errorf("initialIP was nil")
}
if r.Contact == nil {
r.Contact = &[]string{}
}
rm := regModelv1{
ID: r.ID,
Key: key,
KeySHA256: sha,
Contact: *r.Contact,
Agreement: r.Agreement,
InitialIP: []byte(r.InitialIP.To16()),
CreatedAt: r.CreatedAt,
}
if features.Enabled(features.AllowAccountDeactivation) {
return ®Modelv2{
regModelv1: rm,
Status: string(r.Status),
}, nil
}
return &rm, nil
}
// GetRegistrationByKey obtains a Registration by JWK
func (ssa *SQLStorageAuthority) GetRegistrationByKey(ctx context.Context, key *jose.JsonWebKey) (core.Registration, error) {
const query = "WHERE jwk_sha256 = ?"
var model interface{}
var err error
if key == nil {
return core.Registration{}, fmt.Errorf("key argument to GetRegistrationByKey must not be nil")
}
sha, err := core.KeyDigest(key.Key)
if err != nil {
return core.Registration{}, err
}
if features.Enabled(features.AllowAccountDeactivation) {
model, err = selectRegistrationv2(ssa.dbMap, query, sha)
} else {
model, err = selectRegistration(ssa.dbMap, query, sha)
}
if err == sql.ErrNoRows {
msg := fmt.Sprintf("No registrations with public key sha256 %s", sha)
return core.Registration{}, core.NoSuchRegistrationError(msg)
}
if err != nil {
return core.Registration{}, err
}
return modelToRegistration(model)
}
// MergeUpdate copies a subset of information from the input Registration
// into the Registration r. It returns true if an update was performed and the base object
// was changed, and false if no change was made.
func mergeUpdate(r *core.Registration, input core.Registration) bool {
var changed bool
// Note: we allow input.Contact to overwrite r.Contact even if the former is
// empty in order to allow users to remove the contact associated with
// a registration. Since the field type is a pointer to slice of pointers we
// can perform a nil check to differentiate between an empty value and a nil
// (e.g. not provided) value
if input.Contact != nil && !contactsEqual(r, input) {
r.Contact = input.Contact
changed = true
}
// If there is an agreement in the input and it's not the same as the base,
// then we update the base
if len(input.Agreement) > 0 && input.Agreement != r.Agreement {
r.Agreement = input.Agreement
changed = true
}
if features.Enabled(features.AllowKeyRollover) && input.Key != nil {
sameKey, _ := core.PublicKeysEqual(r.Key.Key, input.Key.Key)
if !sameKey {
r.Key = input.Key
changed = true
}
}
return changed
}
// UpdateRegistration stores an updated Registration
func (ssa *SQLStorageAuthority) UpdateRegistration(ctx context.Context, reg core.Registration) error {
const query = "WHERE id = ?"
var model interface{}
var err error
if features.Enabled(features.AllowAccountDeactivation) {
model, err = selectRegistrationv2(ssa.dbMap, query, reg.ID)
} else {
model, err = selectRegistration(ssa.dbMap, query, reg.ID)
}
if err == sql.ErrNoRows {
msg := fmt.Sprintf("No registrations with ID %d", reg.ID)
return core.NoSuchRegistrationError(msg)
}
updatedRegModel, err := registrationToModel(®)
if err != nil {
return err
}
// Since registrationToModel has to return an interface so that we can use either model
// version we need to cast both the updated and existing model to their proper types
// so that we can copy over the LockCol from one to the other. Once we have copied
// that field we reassign to the interface so gorp can properly update it.
if features.Enabled(features.AllowAccountDeactivation) {
erm := model.(*regModelv2)
urm := updatedRegModel.(*regModelv2)
urm.LockCol = erm.LockCol
updatedRegModel = urm
} else {
erm := model.(*regModelv1)
urm := updatedRegModel.(*regModelv1)
urm.LockCol = erm.LockCol
updatedRegModel = urm
}
n, err := ssa.dbMap.Update(updatedRegModel)
if err != nil {
return err
}
if n == 0 {
msg := fmt.Sprintf("Requested registration not found %d", reg.ID)
return core.NoSuchRegistrationError(msg)
}
return nil
}
// GetCertificateStatus takes a hexadecimal string representing the full 128-bit serial
// number of a certificate and returns data about that certificate's current
// validity.
func (ssa *SQLStorageAuthority) GetCertificateStatus(ctx context.Context, serial string) (core.CertificateStatus, error) {
if !core.ValidSerial(serial) {
err := fmt.Errorf("Invalid certificate serial %s", serial)
return core.CertificateStatus{}, err
}
var status core.CertificateStatus
if features.Enabled(features.CertStatusOptimizationsMigrated) {
statusObj, err := ssa.dbMap.Get(certStatusModelv2{}, serial)
if err != nil {
return status, err
}
if statusObj == nil {
return status, nil
}
statusModel := statusObj.(*certStatusModelv2)
status = core.CertificateStatus{
Serial: statusModel.Serial,
SubscriberApproved: statusModel.SubscriberApproved,
Status: statusModel.Status,
OCSPLastUpdated: statusModel.OCSPLastUpdated,
RevokedDate: statusModel.RevokedDate,
RevokedReason: statusModel.RevokedReason,
LastExpirationNagSent: statusModel.LastExpirationNagSent,
OCSPResponse: statusModel.OCSPResponse,
NotAfter: statusModel.NotAfter,
IsExpired: statusModel.IsExpired,
LockCol: statusModel.LockCol,
}
} else {
statusObj, err := ssa.dbMap.Get(certStatusModelv1{}, serial)
if err != nil {
return status, err
}
if statusObj == nil {
return status, nil
}
statusModel := statusObj.(*certStatusModelv1)
status = core.CertificateStatus{
Serial: statusModel.Serial,
SubscriberApproved: statusModel.SubscriberApproved,
Status: statusModel.Status,
OCSPLastUpdated: statusModel.OCSPLastUpdated,
RevokedDate: statusModel.RevokedDate,
RevokedReason: statusModel.RevokedReason,
LastExpirationNagSent: statusModel.LastExpirationNagSent,
OCSPResponse: statusModel.OCSPResponse,
LockCol: statusModel.LockCol,
}
}
return status, nil
}
func modelToRegistration(ri interface{}) (core.Registration, error) {
var rm *regModelv1
if features.Enabled(features.AllowAccountDeactivation) {
r2 := ri.(*regModelv2)
rm = &r2.regModelv1
} else {
rm = ri.(*regModelv1)
}
k := &jose.JsonWebKey{}
err := json.Unmarshal(rm.Key, k)
if err != nil {
err = fmt.Errorf("unable to unmarshal JsonWebKey in db: %s", err)
return core.Registration{}, err
}
var contact *[]string
// Contact can be nil when the DB contains the literal string "null". We
// prefer to represent this in memory as a pointer to an empty slice rather
// than a nil pointer.
if rm.Contact == nil {
contact = &[]string{}
} else {
contact = &rm.Contact
}
r := core.Registration{
ID: rm.ID,
Key: k,
Contact: contact,
Agreement: rm.Agreement,
InitialIP: net.IP(rm.InitialIP),
CreatedAt: rm.CreatedAt,
}
if features.Enabled(features.AllowAccountDeactivation) {
r2 := ri.(*regModelv2)
r.Status = core.AcmeStatus(r2.Status)
}
return r, nil
}
// missingReceiptsTick looks for certificates without the correct number of SCT
// receipts and retrieves them
func (updater *OCSPUpdater) missingReceiptsTick(ctx context.Context, batchSize int) error {
now := updater.clk.Now()
since := now.Add(-updater.oldestIssuedSCT)
serials, err := updater.getSerialsIssuedSince(since, batchSize)
if err != nil {
updater.log.AuditErr(fmt.Sprintf("Failed to get certificate serials: %s", err))
return err
}
for _, serial := range serials {
// First find the logIDs that have provided a SCT for the serial
logIDs, err := updater.getSubmittedReceipts(serial)
if err != nil {
updater.log.AuditErr(fmt.Sprintf(
"Failed to get CT log IDs of SCT receipts for certificate: %s", err))
continue
}
// Next, check if any of the configured CT logs are missing from the list of
// logs that have given SCTs for this serial
missingLogs := updater.missingLogs(logIDs)
if len(missingLogs) == 0 {
// If all of the logs have provided a SCT we're done for this serial
continue
}
// Otherwise, we need to get the certificate from the SA & submit it to each
// of the missing logs to obtain SCTs.
cert, err := updater.sac.GetCertificate(ctx, serial)
if err != nil {
updater.log.AuditErr(fmt.Sprintf("Failed to get certificate: %s", err))
continue
}
// If the feature flag is enabled, only send the certificate to the missing
// logs using the `SubmitToSingleCT` endpoint that was added for this
// purpose
if features.Enabled(features.ResubmitMissingSCTsOnly) {
for _, log := range missingLogs {
_ = updater.pubc.SubmitToSingleCT(ctx, log.uri, log.key, cert.DER)
}
} else {
// Otherwise, use the classic behaviour and submit the certificate to
// every log to get SCTS using the pre-existing `SubmitToCT` endpoint
_ = updater.pubc.SubmitToCT(ctx, cert.DER)
}
}
return nil
}
// GetRegistration obtains a Registration by ID
func (ssa *SQLStorageAuthority) GetRegistration(ctx context.Context, id int64) (core.Registration, error) {
const query = "WHERE id = ?"
var model interface{}
var err error
if features.Enabled(features.AllowAccountDeactivation) {
model, err = selectRegistrationv2(ssa.dbMap, query, id)
} else {
model, err = selectRegistration(ssa.dbMap, query, id)
}
if err == sql.ErrNoRows {
return core.Registration{}, core.NoSuchRegistrationError(
fmt.Sprintf("No registrations with ID %d", id),
)
}
if err != nil {
return core.Registration{}, err
}
return modelToRegistration(model)
}
func (updater *OCSPUpdater) findRevokedCertificatesToUpdate(batchSize int) ([]core.CertificateStatus, error) {
const query = "WHERE status = ? AND ocspLastUpdated <= revokedDate LIMIT ?"
var statuses []core.CertificateStatus
var err error
if features.Enabled(features.CertStatusOptimizationsMigrated) {
statuses, err = sa.SelectCertificateStatusesv2(
updater.dbMap,
query,
string(core.OCSPStatusRevoked),
batchSize,
)
} else {
statuses, err = sa.SelectCertificateStatuses(
updater.dbMap,
query,
string(core.OCSPStatusRevoked),
batchSize,
)
}
return statuses, err
}
func (updater *OCSPUpdater) getCertificatesWithMissingResponses(batchSize int) ([]core.CertificateStatus, error) {
const query = "WHERE ocspLastUpdated = 0 LIMIT ?"
var statuses []core.CertificateStatus
var err error
if features.Enabled(features.CertStatusOptimizationsMigrated) {
statuses, err = sa.SelectCertificateStatusesv2(
updater.dbMap,
query,
batchSize,
)
} else {
statuses, err = sa.SelectCertificateStatuses(
updater.dbMap,
query,
batchSize,
)
}
if err == sql.ErrNoRows {
return statuses, nil
}
return statuses, err
}
// AddCertificate stores an issued certificate and returns the digest as
// a string, or an error if any occurred.
func (ssa *SQLStorageAuthority) AddCertificate(ctx context.Context, certDER []byte, regID int64) (string, error) {
parsedCertificate, err := x509.ParseCertificate(certDER)
if err != nil {
return "", err
}
digest := core.Fingerprint256(certDER)
serial := core.SerialToString(parsedCertificate.SerialNumber)
cert := &core.Certificate{
RegistrationID: regID,
Serial: serial,
Digest: digest,
DER: certDER,
Issued: ssa.clk.Now(),
Expires: parsedCertificate.NotAfter,
}
var certStatusOb interface{}
if features.Enabled(features.CertStatusOptimizationsMigrated) {
certStatusOb = &certStatusModelv2{
certStatusModelv1: certStatusModelv1{
SubscriberApproved: false,
Status: core.OCSPStatus("good"),
OCSPLastUpdated: time.Time{},
OCSPResponse: []byte{},
Serial: serial,
RevokedDate: time.Time{},
RevokedReason: 0,
LockCol: 0,
},
NotAfter: parsedCertificate.NotAfter,
}
} else {
certStatusOb = &certStatusModelv1{
SubscriberApproved: false,
Status: core.OCSPStatus("good"),
OCSPLastUpdated: time.Time{},
OCSPResponse: []byte{},
Serial: serial,
RevokedDate: time.Time{},
RevokedReason: 0,
LockCol: 0,
}
}
tx, err := ssa.dbMap.Begin()
if err != nil {
return "", err
}
// Note: will fail on duplicate serials. Extremely unlikely to happen and soon
// to be fixed by redesign. Reference issue
// https://github.com/letsencrypt/boulder/issues/2265 for more
err = tx.Insert(cert)
if err != nil {
return "", Rollback(tx, err)
}
err = tx.Insert(certStatusOb)
if err != nil {
return "", Rollback(tx, err)
}
err = addIssuedNames(tx, parsedCertificate)
if err != nil {
return "", Rollback(tx, err)
}
err = addFQDNSet(
tx,
parsedCertificate.DNSNames,
serial,
parsedCertificate.NotBefore,
parsedCertificate.NotAfter,
)
if err != nil {
return "", Rollback(tx, err)
}
return digest, tx.Commit()
}
// MarkCertificateRevoked stores the fact that a certificate is revoked, along
// with a timestamp and a reason.
func (ssa *SQLStorageAuthority) MarkCertificateRevoked(ctx context.Context, serial string, reasonCode revocation.Reason) error {
var err error
if _, err = ssa.GetCertificate(ctx, serial); err != nil {
return fmt.Errorf(
"Unable to mark certificate %s revoked: cert not found.", serial)
}
if _, err = ssa.GetCertificateStatus(ctx, serial); err != nil {
return fmt.Errorf(
"Unable to mark certificate %s revoked: cert status not found.", serial)
}
tx, err := ssa.dbMap.Begin()
if err != nil {
return err
}
const statusQuery = "WHERE serial = ?"
var statusObj interface{}
if features.Enabled(features.CertStatusOptimizationsMigrated) {
statusObj, err = SelectCertificateStatusv2(tx, statusQuery, serial)
} else {
statusObj, err = SelectCertificateStatus(tx, statusQuery, serial)
}
if err == sql.ErrNoRows {
err = fmt.Errorf("No certificate with serial %s", serial)
err = Rollback(tx, err)
return err
}
if err != nil {
err = Rollback(tx, err)
return err
}
var n int64
now := ssa.clk.Now()
if features.Enabled(features.CertStatusOptimizationsMigrated) {
status := statusObj.(certStatusModelv2)
status.Status = core.OCSPStatusRevoked
status.RevokedDate = now
status.RevokedReason = reasonCode
n, err = tx.Update(&status)
} else {
status := statusObj.(certStatusModelv1)
status.Status = core.OCSPStatusRevoked
status.RevokedDate = now
status.RevokedReason = reasonCode
n, err = tx.Update(&status)
}
if err != nil {
err = Rollback(tx, err)
return err
}
if n == 0 {
err = errors.New("No certificate updated. Maybe the lock column was off?")
err = Rollback(tx, err)
return err
}
return tx.Commit()
}
// WillingToIssue determines whether the CA is willing to issue for the provided
// identifier. It expects domains in id to be lowercase to prevent mismatched
// cases breaking queries.
//
// We place several criteria on identifiers we are willing to issue for:
//
// * MUST self-identify as DNS identifiers
// * MUST contain only bytes in the DNS hostname character set
// * MUST NOT have more than maxLabels labels
// * MUST follow the DNS hostname syntax rules in RFC 1035 and RFC 2181
// In particular:
// * MUST NOT contain underscores
// * MUST NOT contain IDN labels (xn--)
// * MUST NOT match the syntax of an IP address
// * MUST end in a public suffix
// * MUST have at least one label in addition to the public suffix
// * MUST NOT be a label-wise suffix match for a name on the black list,
// where comparison is case-independent (normalized to lower case)
//
// If WillingToIssue returns an error, it will be of type MalformedRequestError.
func (pa *AuthorityImpl) WillingToIssue(id core.AcmeIdentifier) error {
if id.Type != core.IdentifierDNS {
return errInvalidIdentifier
}
domain := id.Value
if domain == "" {
return errEmptyName
}
for _, ch := range []byte(domain) {
if !isDNSCharacter(ch) {
return errInvalidDNSCharacter
}
}
if len(domain) > maxDNSIdentifierLength {
return errNameTooLong
}
if ip := net.ParseIP(domain); ip != nil {
return errIPAddress
}
if strings.HasSuffix(domain, ".") {
return errNameEndsInDot
}
labels := strings.Split(domain, ".")
if len(labels) > maxLabels {
return errTooManyLabels
}
if len(labels) < 2 {
return errTooFewLabels
}
for _, label := range labels {
if len(label) < 1 {
return errLabelTooShort
}
if len(label) > maxLabelLength {
return errLabelTooLong
}
if !dnsLabelRegexp.MatchString(label) {
return errInvalidDNSCharacter
}
if label[len(label)-1] == '-' {
return errInvalidDNSCharacter
}
if punycodeRegexp.MatchString(label) {
if features.Enabled(features.IDNASupport) {
// We don't care about script usage, if a name is resolvable it was
// registered with a higher power and they should be enforcing their
// own policy. As long as it was properly encoded that is enough
// for us.
_, err := idna.ToUnicode(label)
if err != nil {
return errMalformedIDN
}
} else {
return errIDNNotSupported
}
}
}
// Names must end in an ICANN TLD, but they must not be equal to an ICANN TLD.
icannTLD, err := extractDomainIANASuffix(domain)
if err != nil {
return errNonPublic
}
if icannTLD == domain {
return errICANNTLD
}
// Require no match against blacklist
if err := pa.checkHostLists(domain); err != nil {
return err
}
return nil
}
func (updater *OCSPUpdater) findStaleOCSPResponses(oldestLastUpdatedTime time.Time, batchSize int) ([]core.CertificateStatus, error) {
var statuses []core.CertificateStatus
// TODO(@cpu): Once the notafter-backfill cmd has been run & completed then
// the query below can be rewritten to use `AND NOT cs.isExpired`.
now := updater.clk.Now()
maxAgeCutoff := now.Add(-updater.ocspStaleMaxAge)
// If CertStatusOptimizationsMigrated is enabled then we can do this query
// using only the `certificateStatus` table, saving an expensive JOIN and
// improving performance substantially
var err error
if features.Enabled(features.CertStatusOptimizationsMigrated) {
_, err = updater.dbMap.Select(
&statuses,
`SELECT
cs.serial,
cs.status,
cs.revokedDate,
cs.notAfter
FROM certificateStatus AS cs
WHERE cs.ocspLastUpdated > :maxAge
AND cs.ocspLastUpdated < :lastUpdate
AND NOT cs.isExpired
ORDER BY cs.ocspLastUpdated ASC
LIMIT :limit`,
map[string]interface{}{
"lastUpdate": oldestLastUpdatedTime,
"maxAge": maxAgeCutoff,
"limit": batchSize,
},
)
// If the migration hasn't been applied we don't have the `isExpired` or
// `notAfter` fields on the certificate status table to use and must do the
// expensive JOIN on `certificates`
} else {
_, err = updater.dbMap.Select(
&statuses,
`SELECT
cs.serial,
cs.status,
cs.revokedDate
FROM certificateStatus AS cs
JOIN certificates AS cert
ON cs.serial = cert.serial
WHERE cs.ocspLastUpdated > :maxAge
AND cs.ocspLastUpdated < :lastUpdate
AND cert.expires > now()
ORDER BY cs.ocspLastUpdated ASC
LIMIT :limit`,
map[string]interface{}{
"lastUpdate": oldestLastUpdatedTime,
"maxAge": maxAgeCutoff,
"limit": batchSize,
},
)
}
if err == sql.ErrNoRows {
return statuses, nil
}
return statuses, err
}
func (m *mailer) findExpiringCertificates() error {
now := m.clk.Now()
// E.g. m.nagTimes = [2, 4, 8, 15] days from expiration
for i, expiresIn := range m.nagTimes {
left := now
if i > 0 {
left = left.Add(m.nagTimes[i-1])
}
right := now.Add(expiresIn)
m.log.Info(fmt.Sprintf("expiration-mailer: Searching for certificates that expire between %s and %s and had last nag >%s before expiry",
left.UTC(), right.UTC(), expiresIn))
// First we do a query on the certificateStatus table to find certificates
// nearing expiry meeting our criteria for email notification. We later
// sequentially fetch the certificate details. This avoids an expensive
// JOIN.
var serials []string
var err error
if features.Enabled(features.CertStatusOptimizationsMigrated) {
_, err = m.dbMap.Select(
&serials,
`SELECT
cs.serial
FROM certificateStatus AS cs
WHERE cs.notAfter > :cutoffA
AND cs.notAfter <= :cutoffB
AND cs.status != "revoked"
AND COALESCE(TIMESTAMPDIFF(SECOND, cs.lastExpirationNagSent, cs.notAfter) > :nagCutoff, 1)
ORDER BY cs.notAfter ASC
LIMIT :limit`,
map[string]interface{}{
"cutoffA": left,
"cutoffB": right,
"nagCutoff": expiresIn.Seconds(),
"limit": m.limit,
},
)
} else {
_, err = m.dbMap.Select(
&serials,
`SELECT
cert.serial
FROM certificates AS cert
JOIN certificateStatus AS cs
ON cs.serial = cert.serial
AND cert.expires > :cutoffA
AND cert.expires <= :cutoffB
AND cs.status != "revoked"
AND COALESCE(TIMESTAMPDIFF(SECOND, cs.lastExpirationNagSent, cert.expires) > :nagCutoff, 1)
ORDER BY cert.expires ASC
LIMIT :limit`,
map[string]interface{}{
"cutoffA": left,
"cutoffB": right,
"nagCutoff": expiresIn.Seconds(),
"limit": m.limit,
},
)
}
if err != nil {
m.log.AuditErr(fmt.Sprintf("expiration-mailer: Error loading certificate serials: %s", err))
return err
}
// Now we can sequentially retrieve the certificate details for each of the
// certificate status rows
var certs []core.Certificate
for _, serial := range serials {
var cert core.Certificate
err := m.dbMap.SelectOne(&cert,
`SELECT
cert.*
FROM certificates AS cert
WHERE serial = :serial`,
map[string]interface{}{
"serial": serial,
},
)
if err != nil {
m.log.AuditErr(fmt.Sprintf("expiration-mailer: Error loading cert %q: %s", cert.Serial, err))
return err
}
certs = append(certs, cert)
}
m.log.Info(fmt.Sprintf("Found %d certificates expiring between %s and %s", len(certs),
left.Format("2006-01-02 03:04"), right.Format("2006-01-02 03:04")))
if len(certs) == 0 {
continue // nothing to do
}
// If the `certs` result was exactly `m.limit` rows we need to increment
// a stat indicating that this nag group is at capacity based on the
// configured cert limit. If this condition continually occurs across mailer
// runs then we will not catch up, resulting in under-sending expiration
// mails. The effects of this were initially described in issue #2002[0].
//
// 0: https://github.com/letsencrypt/boulder/issues/2002
if len(certs) == m.limit {
m.log.Info(fmt.Sprintf(
"nag group %s expiring certificates at configured capacity (cert limit %d)\n",
expiresIn.String(),
m.limit))
statName := fmt.Sprintf("Errors.Nag-%s.AtCapacity", expiresIn.String())
m.stats.Inc(statName, 1)
}
processingStarted := m.clk.Now()
m.processCerts(certs)
processingEnded := m.clk.Now()
elapsed := processingEnded.Sub(processingStarted)
m.stats.TimingDuration("ProcessingCertificatesLatency", elapsed)
}
return nil
}