如何把fabric ca签发的证书重新导入fabric ca server数据库表中

以SQLLite数据库为例，每一个CA签发出来的证书都会在certificates表中存着一条数据。
有时候sqlite数据库坏掉了，如何根据已经签发的证书重新导入SQLite数据库表呢。

我们看certificates表的定义：

sqlite> .schema certificates
CREATE TABLE certificates (
    id VARCHAR(255),
    serial_number blob NOT NULL,
    authority_key_identifier blob NOT NULL,
    ca_label blob,
    status blob NOT NULL,
    reason int,
    expiry timestamp,
    revoked_at timestamp,
    pem blob NOT NULL,
    level INTEGER DEFAULT 0,
    PRIMARY KEY(serial_number, authority_key_identifier)
);

其中所有的关键数据，在证书本身里面都能够查到，这样导致从证书重新导入数据库表成为可能。

$ openssl x509 -in cert.pem -text -noout               
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            c8:9f:44:00:...:8b:8e                                                  <---- serial_number
    Signature Algorithm: ecdsa-with-SHA256
        Issuer: O=<XXX>, CN=<YYY>
        Validity
            Not Before: Aug  8 12:17:00 2018 GMT
            Not After : Aug  8 12:22:00 2019 GMT                                   <---- expiry
        Subject: CN=<AAA>                                                          <---- id
        Subject Public Key Info:
            Public Key Algorithm: id-ecPublicKey
                Public-Key: (256 bit)
                pub: 
                    04:f9:...:0c:
                    90:4d:...:c3
                ASN1 OID: prime256v1
                NIST CURVE: P-256
        X509v3 extensions:
            X509v3 Key Usage: critical
                Digital Signature
            X509v3 Basic Constraints: critical
                CA:FALSE
            X509v3 Subject Key Identifier: 
                82:D8:...:E7
            X509v3 Authority Key Identifier: 
                keyid:96:...:07                                                    <---- authority_key_identifier

            X509v3 Subject Alternative Name: 
                DNS:localhost
    Signature Algorithm: ecdsa-with-SHA256
         30:44:...:a7:
         54:54:...:c5

下面实现的代码就很简单了：
利用fabric-ca自带的库github.com/mattn/go-sqlite3和github.com/jmoiron/sqlx来访问SQLite数据库；完整例子代码大部都是从fabric-ca源代码里面提取出来的：

package main

import (
    "fmt"
    "flag"
    "time"
    "io/ioutil"
    "crypto/x509"
    "encoding/pem"

_   "github.com/mattn/go-sqlite3"   // import to support SQLite3
    "github.com/jmoiron/sqlx"
)

var (
    signcert   string
    dbpath     string
)

func init() {
    flag.StringVar(&signcert,   "signcert", "cert.pem",             "Path to file containing PEM-encoded sign certificate to be loaded")
    flag.StringVar(&dbpath,     "dbpath",   "fabic-ca-server.db",   "Path to sqlite3 DB file PEM-encoded signed certificate will be loaded to")
}

const (
    insertSQL = `
        INSERT INTO certificates ( id,  serial_number,  authority_key_identifier,  ca_label,  status,  reason,  expiry,  revoked_at,  pem,  level)
                          VALUES (:id, :serial_number, :authority_key_identifier, :ca_label, :status, :reason, :expiry, :revoked_at, :pem, :level);`
)

type CertRecord struct {
    ID        string    `db:"id"`
    Level     int       `db:"level"`
    Serial    string    `db:"serial_number"`
    AKI       string    `db:"authority_key_identifier"`
    CALabel   string    `db:"ca_label"`
    Status    string    `db:"status"`
    Reason    int       `db:"reason"`
    Expiry    time.Time `db:"expiry"`
    RevokedAt time.Time `db:"revoked_at"`
    PEM       string    `db:"pem"`
}

func main() {
    flag.Parse()

    certPEM, err := ioutil.ReadFile(signcert)
    if err != nil {
        fmt.Printf("Cannot read PEM file, err=[%v]\n", err)
        return
    }

    block, _ := pem.Decode(certPEM)
    if block == nil {
        fmt.Printf("Failed to parse certificate PEM\n")
        return
    }

    cert, err := x509.ParseCertificate(block.Bytes)
    if err != nil {
        fmt.Printf("Cannot read PEM file, err=[%v]\n", err)
        return
    }

    fmt.Printf("CommonName     = [%s]\n", cert.Subject.CommonName);
    fmt.Printf("SerialNumber   = [%v]\n", fmt.Sprintf("%x", cert.SerialNumber));
    fmt.Printf("AuthorityKeyId = [%x]\n", cert.AuthorityKeyId);
    fmt.Printf("Expiry         = [%s]\n", cert.NotAfter.UTC());

    var record       = new(CertRecord)
    record.ID        = cert.Subject.CommonName
    record.Serial    = fmt.Sprintf("%x", cert.SerialNumber)
    record.AKI       = fmt.Sprintf("%x", cert.AuthorityKeyId)
    record.CALabel   = "" 
    record.Status    = "good"
    record.Reason    = 0
    record.Expiry    = cert.NotAfter.UTC()
    record.RevokedAt = time.Time{}
    record.PEM       = string(certPEM)
    record.Level     = 0

    db, err := sqlx.Open("sqlite3", dbpath)
    if err != nil {
        fmt.Printf("Cannot open SQLite database, err=[%v]\n", err)
        return
    }
    defer db.Close()
    _, err = db.NamedExec(insertSQL, record)
    if err != nil {
        fmt.Printf("Cannot insert record into database, err=[%v]\n", err)
        return
    }

    fmt.Printf("Done\n");
}

运行输入两个参数一个是signcert文件路径，另一个是fabric-ca-server.db SQLite数据库文件，上述程序把证书解析出来，然后把内容插入到certificates表中的一条数据。