func addIdentifiers(csvFilename string, dbMap *gorp.DbMap, stats statsd.Statter, statsRate float32) {
file, err := os.Open(csvFilename)
cmd.FailOnError(err, "Could not open the file for reading")
csvReader := csv.NewReader(file)
for {
record, err := csvReader.Read()
if err == io.EOF {
break
} else if err != nil {
fmt.Println("Error:", err)
return
}
identifierData := core.IdentifierData{
ReversedName: record[1],
CertSHA1: record[0],
}
importStart := time.Now()
err = dbMap.Insert(&identifierData)
stats.TimingDuration("ExistingCert.DomainImportTime", time.Since(importStart), statsRate)
stats.Inc("ExistingCert.DomainsImported", 1, statsRate)
}
}
func removeInvalidCerts(csvFilename string, dbMap *gorp.DbMap, stats statsd.Statter, statsRate float32) {
file, err := os.Open(csvFilename)
cmd.FailOnError(err, "Could not open the file for reading")
csvReader := csv.NewReader(file)
for {
record, err := csvReader.Read()
if err == io.EOF {
break
} else if err != nil {
fmt.Println("Error:", err)
return
}
identifierData := core.IdentifierData{
CertSHA1: record[0],
}
externalCert := core.ExternalCert{
SHA1: record[0],
}
deleteStart := time.Now()
_, err = dbMap.Delete(&identifierData)
stats.TimingDuration("ExistingCert.DomainDeleteTime", time.Since(deleteStart), statsRate)
_, err = dbMap.Delete(&externalCert)
stats.TimingDuration("ExistingCert.CertDeleteTime", time.Since(deleteStart), statsRate)
stats.Inc("ExistingCert.CertsDeleted", 1, statsRate)
}
}
func addCerts(csvFilename string, dbMap *gorp.DbMap, stats statsd.Statter, statsRate float32) {
file, err := os.Open(csvFilename)
cmd.FailOnError(err, "Could not open the file for reading")
csvReader := csv.NewReader(file)
for {
record, err := csvReader.Read()
if err == io.EOF {
break
} else if err != nil {
fmt.Println("Error:", err)
return
}
notAfter, err := time.Parse(datestampFormat, record[3])
spkiBytes, err := hex.DecodeString(record[4])
certDER, err := hex.DecodeString(record[7])
externalCert := core.ExternalCert{
SHA1: record[0],
Issuer: record[1],
Subject: record[2],
NotAfter: notAfter,
SPKI: spkiBytes,
Valid: record[5] == "1",
EV: record[6] == "1",
CertDER: certDER,
}
importStart := time.Now()
err = dbMap.Insert(&externalCert)
stats.TimingDuration("ExistingCert.CertImportTime", time.Since(importStart), statsRate)
stats.Inc("ExistingCert.CertsImported", 1, statsRate)
}
}
func revokeAuthorizations(db *gorp.DbMap, tableName string, authIDs []string) (int64, error) {
stmtArgs := []interface{}{string(core.StatusRevoked)}
qmarks := []string{}
for _, id := range authIDs {
stmtArgs = append(stmtArgs, id)
qmarks = append(qmarks, "?")
}
idStmt := fmt.Sprintf("(%s)", strings.Join(qmarks, ", "))
result, err := db.Exec(
fmt.Sprintf(
`UPDATE %s
SET status = ?
WHERE id IN %s`,
tableName,
idStmt,
),
stmtArgs...,
)
if err != nil {
return 0, err
}
batchSize, err := result.RowsAffected()
if err != nil {
return 0, err
}
return batchSize, nil
}
func getAuthorizationIDsByDomain(db *gorp.DbMap, tableName string, ident string, now time.Time) ([]string, error) {
var allIDs []string
_, err := db.Select(
&allIDs,
fmt.Sprintf(
`SELECT id FROM %s
WHERE identifier = :ident AND
status != :invalid AND
status != :revoked AND
expires > :now
LIMIT :limit`,
tableName,
),
map[string]interface{}{
"ident": ident,
"invalid": string(core.StatusInvalid),
"revoked": string(core.StatusRevoked),
"now": now,
"limit": getAuthorizationIDsMax,
},
)
if err != nil {
return nil, err
}
return allIDs, nil
}
// SetSQLDebug enables/disables GORP SQL-level Debugging
func SetSQLDebug(dbMap *gorp.DbMap, state bool) {
dbMap.TraceOff()
if state {
// Enable logging
dbMap.TraceOn("SQL: ", &SQLLogger{blog.GetAuditLogger()})
}
}
// NewCertificateAuthorityDatabaseImpl constructs a Database for the
// Certificate Authority.
func NewCertificateAuthorityDatabaseImpl(dbMap *gorp.DbMap) (cadb core.CertificateAuthorityDatabase, err error) {
logger := blog.GetAuditLogger()
dbMap.AddTableWithName(SerialNumber{}, "serialNumber").SetKeys(true, "ID")
cadb = &CertificateAuthorityDatabaseImpl{
dbMap: dbMap,
log: logger,
}
return cadb, nil
}
// NewPolicyAuthorityDatabaseImpl constructs a Policy Authority Database (and
// creates tables if they are non-existent)
func NewPolicyAuthorityDatabaseImpl(dbMap *gorp.DbMap) (padb *PolicyAuthorityDatabaseImpl, err error) {
logger := blog.GetAuditLogger()
dbMap.AddTableWithName(BlacklistRule{}, "blacklist").SetKeys(false, "Host")
dbMap.AddTableWithName(WhitelistRule{}, "whitelist").SetKeys(false, "Host")
padb = &PolicyAuthorityDatabaseImpl{
dbMap: dbMap,
log: logger,
}
return padb, nil
}
// initTables constructs the table map for the ORM. If you want to also create
// the tables, call CreateTablesIfNotExists on the DbMap.
func initTables(dbMap *gorp.DbMap) {
regTable := dbMap.AddTableWithName(core.Registration{}, "registrations").SetKeys(true, "ID")
regTable.SetVersionCol("LockCol")
regTable.ColMap("Key").SetMaxSize(1024).SetNotNull(true).SetUnique(true)
pendingAuthzTable := dbMap.AddTableWithName(pendingauthzModel{}, "pending_authz").SetKeys(false, "ID")
pendingAuthzTable.SetVersionCol("LockCol")
pendingAuthzTable.ColMap("Challenges").SetMaxSize(1536)
authzTable := dbMap.AddTableWithName(authzModel{}, "authz").SetKeys(false, "ID")
authzTable.ColMap("Challenges").SetMaxSize(1536)
dbMap.AddTableWithName(core.Certificate{}, "certificates").SetKeys(false, "Serial")
dbMap.AddTableWithName(core.CertificateStatus{}, "certificateStatus").SetKeys(false, "Serial").SetVersionCol("LockCol")
dbMap.AddTableWithName(core.OCSPResponse{}, "ocspResponses").SetKeys(true, "ID")
dbMap.AddTableWithName(core.CRL{}, "crls").SetKeys(false, "Serial")
dbMap.AddTableWithName(core.DeniedCSR{}, "deniedCSRs").SetKeys(true, "ID")
}
// 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) {
regTable := dbMap.AddTableWithName(regModel{}, "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.DeniedCSR{}, "deniedCSRs").SetKeys(true, "ID")
dbMap.AddTableWithName(core.SignedCertificateTimestamp{}, "sctReceipts").SetKeys(true, "ID").SetVersionCol("LockCol")
}