gitee.com/zhaochuninhefei/fabric-ca-gm@v0.0.2/lib/server/db/postgres/postgres.go (about) 1 /* 2 Copyright IBM Corp. All Rights Reserved. 3 4 SPDX-License-Identifier: Apache-2.0 5 */ 6 7 package postgres 8 9 import ( 10 "context" 11 "database/sql" 12 "fmt" 13 "regexp" 14 "strings" 15 16 "gitee.com/zhaochuninhefei/fabric-ca-gm/lib/server/db" 17 "gitee.com/zhaochuninhefei/fabric-ca-gm/lib/server/db/util" 18 "gitee.com/zhaochuninhefei/fabric-ca-gm/lib/tls" 19 log "gitee.com/zhaochuninhefei/zcgolog/zclog" 20 "github.com/jmoiron/sqlx" 21 _ "github.com/lib/pq" // import to support Postgres 22 "github.com/pkg/errors" 23 ) 24 25 // Postgres defines PostgreSQL database 26 type Postgres struct { 27 SqlxDB db.FabricCADB 28 TLS *tls.ClientTLSConfig 29 CAName string 30 Metrics *db.Metrics 31 32 datasource string 33 dbName string 34 } 35 36 // NewDB create a PosgreSQL database 37 func NewDB( 38 datasource, 39 caName string, 40 clientTLSConfig *tls.ClientTLSConfig, 41 metrics *db.Metrics, 42 ) *Postgres { 43 log.Debugf("Using postgres database, connecting to database...") 44 return &Postgres{ 45 datasource: datasource, 46 TLS: clientTLSConfig, 47 CAName: caName, 48 Metrics: metrics, 49 } 50 } 51 52 // Connect connects to a PostgreSQL server 53 func (p *Postgres) Connect() error { 54 clientTLSConfig := p.TLS 55 56 p.dbName = util.GetDBName(p.datasource) 57 dbName := p.dbName 58 log.Debugf("Database Name: %s", dbName) 59 60 if strings.Contains(dbName, "-") || strings.HasSuffix(dbName, ".db") { 61 return errors.Errorf("Database name '%s' cannot contain any '-' or end with '.db'", dbName) 62 } 63 64 if clientTLSConfig.Enabled { 65 if len(clientTLSConfig.CertFiles) == 0 { 66 return errors.New("No trusted root certificates for TLS were provided") 67 } 68 69 root := clientTLSConfig.CertFiles[0] 70 p.datasource = fmt.Sprintf("%s sslrootcert=%s", p.datasource, root) 71 72 cert := clientTLSConfig.Client.CertFile 73 key := clientTLSConfig.Client.KeyFile 74 p.datasource = fmt.Sprintf("%s sslcert=%s sslkey=%s", p.datasource, cert, key) 75 } 76 77 dbNames := []string{dbName, "postgres", "template1"} 78 var sqlxdb *sqlx.DB 79 var err error 80 81 for _, dbName := range dbNames { 82 connStr := getConnStr(p.datasource, dbName) 83 log.Debugf("Connecting to PostgreSQL server, using connection string: %s", util.MaskDBCred(connStr)) 84 85 sqlxdb, err = sqlx.Connect("postgres", connStr) 86 if err == nil { 87 break 88 } 89 log.Warnf("Failed to connect to database '%s'", dbName) 90 } 91 92 if err != nil { 93 return errors.Errorf("Failed to connect to Postgres database. Postgres requires connecting to a specific database, the following databases were tried: %s. Please create one of these database before continuing", dbNames) 94 } 95 96 p.SqlxDB = db.New(sqlxdb, p.CAName, p.Metrics) 97 return nil 98 } 99 100 // PingContext pings the database 101 func (p *Postgres) PingContext(ctx context.Context) error { 102 err := p.SqlxDB.PingContext(ctx) 103 if err != nil { 104 return errors.Wrap(err, "Failed to ping to Postgres database") 105 } 106 return nil 107 } 108 109 // exists determines if the database has already been created 110 func exists(sqlxDB db.FabricCADB, dbName string) (bool, error) { 111 log.Debugf("Checking if Postgres CA database '%s' exists", dbName) 112 var exists bool 113 query := fmt.Sprintf("SELECT true as exists FROM pg_catalog.pg_database WHERE lower(datname) = lower('%s')", dbName) 114 err := sqlxDB.Get("CheckIfDatabaseExists", &exists, query) 115 if err != nil && err != sql.ErrNoRows { 116 return false, errors.Wrapf(err, "Failed to check if Postgres CA database '%s' exists", dbName) 117 } 118 return exists, nil 119 } 120 121 // Create creates database and tables 122 func (p *Postgres) Create() (*db.DB, error) { 123 db, err := p.CreateDatabase() 124 if err != nil { 125 return nil, err 126 } 127 err = p.CreateTables() 128 if err != nil { 129 return nil, err 130 } 131 return db, nil 132 } 133 134 // CreateDatabase creates database 135 func (p *Postgres) CreateDatabase() (*db.DB, error) { 136 err := p.createDatabase() 137 if err != nil { 138 return nil, errors.Wrap(err, "Failed to create Postgres database") 139 } 140 141 log.Debugf("Connecting to database '%s', using connection string: '%s'", p.dbName, util.MaskDBCred(p.datasource)) 142 sqlxdb, err := sqlx.Open("postgres", p.datasource) 143 if err != nil { 144 return nil, errors.Wrapf(err, "Failed to open database '%s' in Postgres server", p.dbName) 145 } 146 p.SqlxDB = db.New(sqlxdb, p.CAName, p.Metrics) 147 148 return p.SqlxDB.(*db.DB), nil 149 } 150 151 // CreateTables creates table 152 func (p *Postgres) CreateTables() error { 153 err := p.createTables() 154 if err != nil { 155 return errors.Wrap(err, "Failed to create Postgres tables") 156 } 157 return nil 158 } 159 160 func (p *Postgres) createDatabase() error { 161 exists, err := exists(p.SqlxDB, p.dbName) 162 if err != nil { 163 return err 164 } 165 if !exists { 166 log.Debugf("Creating Postgres Database '%s'", p.dbName) 167 _, err := p.SqlxDB.Exec("CreateDatabase", "CREATE DATABASE "+p.dbName) 168 if err != nil { 169 return errors.Wrap(err, "Failed to execute create database query") 170 } 171 } 172 return nil 173 } 174 175 // createPostgresDB creates postgres database 176 func (p *Postgres) createTables() error { 177 db := p.SqlxDB 178 log.Debug("Creating users table if it does not exist") 179 if _, err := db.Exec("CreateUsersTable", "CREATE TABLE IF NOT EXISTS users (id VARCHAR(255), token bytea, type VARCHAR(256), affiliation VARCHAR(1024), attributes TEXT, state INTEGER, max_enrollments INTEGER, level INTEGER DEFAULT 0, incorrect_password_attempts INTEGER DEFAULT 0, PRIMARY KEY (id))"); err != nil { 180 return errors.Wrap(err, "Error creating users table") 181 } 182 log.Debug("Creating users id index if it does not exist") 183 if _, err := db.Exec("UsersIdIndexPatch", "CREATE INDEX IF NOT EXISTS users_pkey ON users(id)"); err != nil { 184 return errors.Wrap(err, "Error creating users id index") 185 } 186 log.Debug("Creating affiliations table if it does not exist") 187 if _, err := db.Exec("CreateAffiliationTable", "CREATE TABLE IF NOT EXISTS affiliations (name VARCHAR(1024) NOT NULL UNIQUE, prekey VARCHAR(1024), level INTEGER DEFAULT 0)"); err != nil { 188 return errors.Wrap(err, "Error creating affiliations table") 189 } 190 log.Debug("Creating certificates table if it does not exist") 191 if _, err := db.Exec("CreateCertificatesTable", "CREATE TABLE IF NOT EXISTS certificates (id VARCHAR(255), serial_number bytea NOT NULL, authority_key_identifier bytea NOT NULL, ca_label bytea, status bytea NOT NULL, reason int, expiry timestamp, revoked_at timestamp, pem bytea NOT NULL, level INTEGER DEFAULT 0, PRIMARY KEY(serial_number, authority_key_identifier))"); err != nil { 192 return errors.Wrap(err, "Error creating certificates table") 193 } 194 log.Debug("Creating credentials table if it does not exist") 195 if _, err := db.Exec("CreateCredentialsTable", "CREATE TABLE IF NOT EXISTS credentials (id VARCHAR(255), revocation_handle bytea NOT NULL, cred bytea NOT NULL, ca_label bytea, status bytea NOT NULL, reason int, expiry timestamp, revoked_at timestamp, level INTEGER DEFAULT 0, PRIMARY KEY(revocation_handle))"); err != nil { 196 return errors.Wrap(err, "Error creating credentials table") 197 } 198 log.Debug("Creating revocation_authority_info table if it does not exist") 199 if _, err := db.Exec("CreateRevocationAuthorityTable", "CREATE TABLE IF NOT EXISTS revocation_authority_info (epoch INTEGER, next_handle INTEGER, lasthandle_in_pool INTEGER, level INTEGER DEFAULT 0, PRIMARY KEY(epoch))"); err != nil { 200 return errors.Wrap(err, "Error creating revocation_authority_info table") 201 } 202 log.Debug("Creating nonces table if it does not exist") 203 if _, err := db.Exec("CreateNoncesTable", "CREATE TABLE IF NOT EXISTS nonces (val VARCHAR(255) NOT NULL UNIQUE, expiry timestamp, level INTEGER DEFAULT 0, PRIMARY KEY (val))"); err != nil { 204 return errors.Wrap(err, "Error creating nonces table") 205 } 206 log.Debug("Creating properties table if it does not exist") 207 if _, err := db.Exec("CreatePropertiesTable", "CREATE TABLE IF NOT EXISTS properties (property VARCHAR(255), value VARCHAR(256), PRIMARY KEY(property))"); err != nil { 208 return errors.Wrap(err, "Error creating properties table") 209 } 210 _, err := db.Exec("CreatePropertiesTable", db.Rebind("INSERT INTO properties (property, value) VALUES ('identity.level', '0'), ('affiliation.level', '0'), ('certificate.level', '0'), ('credential.level', '0'), ('rcinfo.level', '0'), ('nonce.level', '0')")) 211 if err != nil { 212 if !strings.Contains(err.Error(), "duplicate key") { 213 return err 214 } 215 } 216 return nil 217 } 218 219 // GetConnStr gets connection string without database 220 func getConnStr(datasource string, dbname string) string { 221 re := regexp.MustCompile(`(dbname=)([^\s]+)`) 222 connStr := re.ReplaceAllString(datasource, fmt.Sprintf("dbname=%s", dbname)) 223 return connStr 224 }