github.com/hyperledger-gerrit-archive/fabric-ca@v2.0.0-alpha.0.20190916143245-4cd4192f0366+incompatible/lib/certdbaccessor.go (about) 1 /* 2 Copyright IBM Corp. All Rights Reserved. 3 4 SPDX-License-Identifier: Apache-2.0 5 */ 6 7 package lib 8 9 import ( 10 "fmt" 11 "math/big" 12 "strings" 13 "time" 14 15 "github.com/cloudflare/cfssl/certdb" 16 certsql "github.com/cloudflare/cfssl/certdb/sql" 17 "github.com/cloudflare/cfssl/log" 18 cr "github.com/hyperledger/fabric-ca/lib/server/certificaterequest" 19 "github.com/hyperledger/fabric-ca/lib/server/db" 20 cadb "github.com/hyperledger/fabric-ca/lib/server/db" 21 dbutil "github.com/hyperledger/fabric-ca/lib/server/db/util" 22 "github.com/hyperledger/fabric-ca/util" 23 "github.com/jmoiron/sqlx" 24 "github.com/kisielk/sqlstruct" 25 "github.com/pkg/errors" 26 ) 27 28 const ( 29 insertSQL = ` 30 INSERT INTO certificates (id, serial_number, authority_key_identifier, ca_label, status, reason, expiry, revoked_at, pem, level) 31 VALUES (:id, :serial_number, :authority_key_identifier, :ca_label, :status, :reason, :expiry, :revoked_at, :pem, :level);` 32 33 selectSQLbyID = ` 34 SELECT %s FROM certificates 35 WHERE (id = ?);` 36 37 selectSQL = ` 38 SELECT %s FROM certificates 39 WHERE (serial_number = ? AND authority_key_identifier = ?);` 40 41 updateRevokeSQL = ` 42 UPDATE certificates 43 SET status='revoked', revoked_at=CURRENT_TIMESTAMP, reason=:reason 44 WHERE (id = :id AND status != 'revoked');` 45 46 deleteCertificatebyID = ` 47 DELETE FROM certificates 48 WHERE (ID = ?);` 49 ) 50 51 // CertDBAccessor implements certdb.Accessor interface. 52 type CertDBAccessor struct { 53 level int 54 accessor certdb.Accessor 55 db cadb.FabricCADB 56 } 57 58 // NewCertDBAccessor returns a new Accessor. 59 func NewCertDBAccessor(db cadb.FabricCADB, level int) *CertDBAccessor { 60 return &CertDBAccessor{ 61 db: db, 62 accessor: certsql.NewAccessor(db.(*cadb.DB).DB.(*sqlx.DB)), 63 level: level, 64 } 65 } 66 67 func (d *CertDBAccessor) checkDB() error { 68 if d.db == nil { 69 return errors.New("Database is not set") 70 } 71 return nil 72 } 73 74 // SetDB changes the underlying sql.DB object Accessor is manipulating. 75 func (d *CertDBAccessor) SetDB(db *db.DB) { 76 d.db = db 77 } 78 79 // InsertCertificate puts a CertificateRecord into db. 80 func (d *CertDBAccessor) InsertCertificate(cr certdb.CertificateRecord) error { 81 82 log.Debug("DB: Insert Certificate") 83 84 err := d.checkDB() 85 if err != nil { 86 return err 87 } 88 id, err := util.GetEnrollmentIDFromPEM([]byte(cr.PEM)) 89 if err != nil { 90 return err 91 } 92 93 ip := new(big.Int) 94 ip.SetString(cr.Serial, 10) //base 10 95 96 serial := util.GetSerialAsHex(ip) 97 aki := strings.TrimLeft(cr.AKI, "0") 98 99 log.Debugf("Saved serial number as hex %s", serial) 100 101 record := &db.CertRecord{ 102 ID: id, 103 Level: d.level, 104 CertificateRecord: certdb.CertificateRecord{ 105 Serial: serial, 106 AKI: aki, 107 CALabel: cr.CALabel, 108 Status: cr.Status, 109 Reason: cr.Reason, 110 Expiry: cr.Expiry.UTC(), 111 RevokedAt: cr.RevokedAt.UTC(), 112 PEM: cr.PEM, 113 }, 114 } 115 116 res, err := d.db.NamedExec("InsertCertificate", insertSQL, record) 117 if err != nil { 118 return errors.Wrap(err, "Failed to insert record into database") 119 } 120 121 numRowsAffected, err := res.RowsAffected() 122 123 if numRowsAffected == 0 { 124 return errors.New("Failed to insert the certificate record; no rows affected") 125 } 126 127 if numRowsAffected != 1 { 128 return errors.Errorf("Expected to affect 1 entry in certificate database but affected %d", 129 numRowsAffected) 130 } 131 132 return err 133 } 134 135 // GetCertificatesByID gets a CertificateRecord indexed by id. 136 func (d *CertDBAccessor) GetCertificatesByID(id string) (crs []db.CertRecord, err error) { 137 log.Debugf("DB: Get certificate by ID (%s)", id) 138 err = d.checkDB() 139 if err != nil { 140 return nil, err 141 } 142 143 err = d.db.Select("GetCertificatesByID", &crs, fmt.Sprintf(d.db.Rebind(selectSQLbyID), sqlstruct.Columns(db.CertRecord{})), id) 144 if err != nil { 145 return nil, err 146 } 147 148 return crs, nil 149 } 150 151 // GetCertificate gets a CertificateRecord indexed by serial. 152 func (d *CertDBAccessor) GetCertificate(serial, aki string) (crs []certdb.CertificateRecord, err error) { 153 log.Debugf("DB: Get certificate by serial (%s) and aki (%s)", serial, aki) 154 crs, err = d.accessor.GetCertificate(serial, aki) 155 if err != nil { 156 return nil, dbutil.GetError(err, "certificate") 157 } 158 159 return crs, nil 160 } 161 162 // GetCertificateWithID gets a CertificateRecord indexed by serial and returns user too. 163 func (d *CertDBAccessor) GetCertificateWithID(serial, aki string) (crs db.CertRecord, err error) { 164 log.Debugf("DB: Get certificate by serial (%s) and aki (%s)", serial, aki) 165 166 err = d.checkDB() 167 if err != nil { 168 return crs, err 169 } 170 171 err = d.db.Get("GetCertificatesByID", &crs, fmt.Sprintf(d.db.Rebind(selectSQL), sqlstruct.Columns(db.CertRecord{})), serial, aki) 172 if err != nil { 173 return crs, dbutil.GetError(err, "Certificate") 174 } 175 176 return crs, nil 177 } 178 179 // GetUnexpiredCertificates gets all unexpired certificate from db. 180 func (d *CertDBAccessor) GetUnexpiredCertificates() (crs []certdb.CertificateRecord, err error) { 181 crs, err = d.accessor.GetUnexpiredCertificates() 182 if err != nil { 183 return nil, err 184 } 185 return crs, err 186 } 187 188 // GetRevokedCertificates returns revoked certificates 189 func (d *CertDBAccessor) GetRevokedCertificates(expiredAfter, expiredBefore, revokedAfter, revokedBefore time.Time) ([]certdb.CertificateRecord, error) { 190 log.Debugf("DB: Get revoked certificates that were revoked after %s and before %s that are expired after %s and before %s", 191 revokedAfter, revokedBefore, expiredAfter, expiredBefore) 192 err := d.checkDB() 193 if err != nil { 194 return nil, err 195 } 196 var crs []certdb.CertificateRecord 197 revokedSQL := "SELECT %s FROM certificates WHERE (WHERE_CLAUSE);" 198 whereConds := []string{"status='revoked' AND expiry > ? AND revoked_at > ?"} 199 args := []interface{}{expiredAfter, revokedAfter} 200 if !expiredBefore.IsZero() { 201 whereConds = append(whereConds, "expiry < ?") 202 args = append(args, expiredBefore) 203 } 204 if !revokedBefore.IsZero() { 205 whereConds = append(whereConds, "revoked_at < ?") 206 args = append(args, revokedBefore) 207 } 208 whereClause := strings.Join(whereConds, " AND ") 209 revokedSQL = strings.Replace(revokedSQL, "WHERE_CLAUSE", whereClause, 1) 210 err = d.db.Select("GetRevokedCertificates", &crs, fmt.Sprintf(d.db.Rebind(revokedSQL), 211 sqlstruct.Columns(certdb.CertificateRecord{})), args...) 212 if err != nil { 213 return crs, dbutil.GetError(err, "Certificate") 214 } 215 return crs, nil 216 } 217 218 // GetRevokedAndUnexpiredCertificates returns revoked and unexpired certificates 219 func (d *CertDBAccessor) GetRevokedAndUnexpiredCertificates() ([]certdb.CertificateRecord, error) { 220 crs, err := d.accessor.GetRevokedAndUnexpiredCertificates() 221 if err != nil { 222 return nil, err 223 } 224 return crs, err 225 } 226 227 // GetRevokedAndUnexpiredCertificatesByLabel returns revoked and unexpired certificates matching the label 228 func (d *CertDBAccessor) GetRevokedAndUnexpiredCertificatesByLabel(label string) ([]certdb.CertificateRecord, error) { 229 crs, err := d.accessor.GetRevokedAndUnexpiredCertificatesByLabel(label) 230 if err != nil { 231 return nil, err 232 } 233 return crs, err 234 } 235 236 // RevokeCertificatesByID updates all certificates for a given ID and marks them revoked. 237 func (d *CertDBAccessor) RevokeCertificatesByID(id string, reasonCode int) (crs []db.CertRecord, err error) { 238 log.Debugf("DB: Revoke certificate by ID (%s)", id) 239 240 err = d.checkDB() 241 if err != nil { 242 return nil, err 243 } 244 245 var record = new(db.CertRecord) 246 record.ID = id 247 record.Reason = reasonCode 248 249 err = d.db.Select("RevokeCertificatesByID", &crs, d.db.Rebind("SELECT * FROM certificates WHERE (id = ? AND status != 'revoked')"), id) 250 if err != nil { 251 return nil, err 252 } 253 254 _, err = d.db.NamedExec("RevokeCertificatesByID", updateRevokeSQL, record) 255 if err != nil { 256 return nil, err 257 } 258 259 return crs, err 260 } 261 262 // RevokeCertificate updates a certificate with a given serial number and marks it revoked. 263 func (d *CertDBAccessor) RevokeCertificate(serial, aki string, reasonCode int) error { 264 log.Debugf("DB: Revoke certificate by serial (%s) and aki (%s)", serial, aki) 265 266 err := d.accessor.RevokeCertificate(serial, aki, reasonCode) 267 return err 268 } 269 270 // InsertOCSP puts a new certdb.OCSPRecord into the db. 271 func (d *CertDBAccessor) InsertOCSP(rr certdb.OCSPRecord) error { 272 return d.accessor.InsertOCSP(rr) 273 } 274 275 // GetOCSP retrieves a certdb.OCSPRecord from db by serial. 276 func (d *CertDBAccessor) GetOCSP(serial, aki string) (ors []certdb.OCSPRecord, err error) { 277 return d.accessor.GetOCSP(serial, aki) 278 } 279 280 // GetUnexpiredOCSPs retrieves all unexpired certdb.OCSPRecord from db. 281 func (d *CertDBAccessor) GetUnexpiredOCSPs() (ors []certdb.OCSPRecord, err error) { 282 return d.accessor.GetUnexpiredOCSPs() 283 } 284 285 // UpdateOCSP updates a ocsp response record with a given serial number. 286 func (d *CertDBAccessor) UpdateOCSP(serial, aki, body string, expiry time.Time) error { 287 return d.accessor.UpdateOCSP(serial, aki, body, expiry) 288 } 289 290 // UpsertOCSP update a ocsp response record with a given serial number, 291 // or insert the record if it doesn't yet exist in the db 292 func (d *CertDBAccessor) UpsertOCSP(serial, aki, body string, expiry time.Time) error { 293 return d.accessor.UpsertOCSP(serial, aki, body, expiry) 294 } 295 296 // GetCertificates returns based on filter parameters certificates 297 func (d *CertDBAccessor) GetCertificates(req cr.CertificateRequest, callersAffiliation string) (*sqlx.Rows, error) { 298 log.Debugf("DB: Get Certificates") 299 300 err := d.checkDB() 301 if err != nil { 302 return nil, err 303 } 304 305 whereConds := []string{} 306 args := []interface{}{} 307 308 getCertificateSQL := "SELECT certificates.pem FROM certificates" // Base SQL query for getting certificates 309 310 // If caller's does not have root affiliation need to filter certificates based on affiliations of identities the 311 // caller is allowed to see 312 if callersAffiliation != "" { 313 getCertificateSQL = "SELECT certificates.pem FROM certificates INNER JOIN users ON users.id = certificates.id" 314 315 whereConds = append(whereConds, "(users.affiliation = ? OR users.affiliation LIKE ?)") 316 args = append(args, callersAffiliation) 317 args = append(args, callersAffiliation+".%") 318 } 319 320 // Apply further filters based on inputs 321 if req.GetID() != "" { 322 whereConds = append(whereConds, "certificates.id = ?") 323 args = append(args, req.GetID()) 324 } 325 if req.GetSerial() != "" { 326 serial := strings.TrimLeft(strings.ToLower(req.GetSerial()), "0") 327 whereConds = append(whereConds, "certificates.serial_number = ?") 328 args = append(args, serial) 329 } 330 if req.GetAKI() != "" { 331 aki := strings.TrimLeft(strings.ToLower(req.GetAKI()), "0") 332 whereConds = append(whereConds, "certificates.authority_key_identifier = ?") 333 args = append(args, aki) 334 } 335 336 if req.GetNotExpired() { // If notexpired is set to true, only return certificates that are not expired (expiration dates beyond the current time) 337 whereConds = append(whereConds, "certificates.expiry >= ?") 338 currentTime := time.Now().UTC() 339 args = append(args, currentTime) 340 } else { 341 // If either expired start time or end time is not nil, formulate the appropriate query parameters. If end is not nil and start is nil 342 // get all certificates that have an expiration date before the end date. If end is nil and start is not nil, get all certificates that 343 // have expiration date after the start date. 344 expiredTimeStart := req.GetExpiredTimeStart() 345 expiredTimeEnd := req.GetExpiredTimeEnd() 346 if expiredTimeStart != nil || expiredTimeEnd != nil { 347 if expiredTimeStart != nil { 348 whereConds = append(whereConds, "certificates.expiry >= ?") 349 args = append(args, expiredTimeStart) 350 } else { 351 whereConds = append(whereConds, "certificates.expiry >= ?") 352 args = append(args, time.Time{}) 353 } 354 if expiredTimeEnd != nil { 355 whereConds = append(whereConds, "certificates.expiry <= ?") 356 args = append(args, expiredTimeEnd) 357 } 358 } 359 } 360 361 if req.GetNotRevoked() { // If notrevoked is set to true, only return certificates that are not revoked (revoked date is set to zero time) 362 whereConds = append(whereConds, "certificates.revoked_at = ?") 363 args = append(args, time.Time{}) 364 } else { 365 // If either revoked start time or end time is not nil, formulate the appropriate query parameters. If end is not nil and start is nil 366 // get all certificates that have an revocation date before the end date. If end is nil and start is not nil, get all certificates that 367 // have revocation date after the start date. 368 revokedTimeStart := req.GetRevokedTimeStart() 369 revokedTimeEnd := req.GetRevokedTimeEnd() 370 if revokedTimeStart != nil || revokedTimeEnd != nil { 371 if revokedTimeStart != nil { 372 whereConds = append(whereConds, "certificates.revoked_at >= ?") 373 args = append(args, revokedTimeStart) 374 } else { 375 whereConds = append(whereConds, "certificates.revoked_at > ?") 376 args = append(args, time.Time{}) 377 } 378 if revokedTimeEnd != nil { 379 whereConds = append(whereConds, "certificates.revoked_at <= ?") 380 args = append(args, revokedTimeEnd) 381 } 382 } 383 } 384 385 if len(whereConds) > 0 { 386 whereClause := strings.Join(whereConds, " AND ") 387 getCertificateSQL = getCertificateSQL + " WHERE (" + whereClause + ")" 388 } 389 getCertificateSQL = getCertificateSQL + ";" 390 391 log.Debugf("Executing get certificates query: %s, with args: %s", getCertificateSQL, args) 392 rows, err := d.db.Queryx("GetCertificates", d.db.Rebind(getCertificateSQL), args...) 393 if err != nil { 394 return nil, dbutil.GetError(err, "Certificate") 395 } 396 397 return rows, nil 398 }