github.com/hyperledger-gerrit-archive/fabric-ca@v2.0.0-alpha.0.20190916143245-4cd4192f0366+incompatible/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 "fmt" 12 "regexp" 13 "strings" 14 15 "github.com/cloudflare/cfssl/log" 16 "github.com/hyperledger/fabric-ca/lib/server/db" 17 "github.com/hyperledger/fabric-ca/lib/server/db/util" 18 "github.com/hyperledger/fabric-ca/lib/tls" 19 "github.com/jmoiron/sqlx" 20 _ "github.com/lib/pq" // import to support Postgres 21 "github.com/pkg/errors" 22 ) 23 24 // Postgres defines PostgreSQL database 25 type Postgres struct { 26 SqlxDB db.FabricCADB 27 TLS *tls.ClientTLSConfig 28 CAName string 29 Metrics *db.Metrics 30 31 datasource string 32 dbName string 33 } 34 35 // NewDB create a PosgreSQL database 36 func NewDB( 37 datasource, 38 caName string, 39 clientTLSConfig *tls.ClientTLSConfig, 40 metrics *db.Metrics, 41 ) *Postgres { 42 log.Debugf("Using postgres database, connecting to database...") 43 return &Postgres{ 44 datasource: datasource, 45 TLS: clientTLSConfig, 46 CAName: caName, 47 Metrics: metrics, 48 } 49 } 50 51 // Connect connects to a PostgreSQL server 52 func (p *Postgres) Connect() error { 53 clientTLSConfig := p.TLS 54 55 p.dbName = util.GetDBName(p.datasource) 56 dbName := p.dbName 57 log.Debugf("Database Name: %s", dbName) 58 59 if strings.Contains(dbName, "-") || strings.HasSuffix(dbName, ".db") { 60 return errors.Errorf("Database name '%s' cannot contain any '-' or end with '.db'", dbName) 61 } 62 63 if clientTLSConfig.Enabled { 64 if len(clientTLSConfig.CertFiles) == 0 { 65 return errors.New("No trusted root certificates for TLS were provided") 66 } 67 68 root := clientTLSConfig.CertFiles[0] 69 p.datasource = fmt.Sprintf("%s sslrootcert=%s", p.datasource, root) 70 71 cert := clientTLSConfig.Client.CertFile 72 key := clientTLSConfig.Client.KeyFile 73 p.datasource = fmt.Sprintf("%s sslcert=%s sslkey=%s", p.datasource, cert, key) 74 } 75 76 dbNames := []string{dbName, "postgres", "template1"} 77 var sqlxdb *sqlx.DB 78 var err error 79 80 for _, dbName := range dbNames { 81 connStr := getConnStr(p.datasource, dbName) 82 log.Debugf("Connecting to PostgreSQL server, using connection string: %s", util.MaskDBCred(connStr)) 83 84 sqlxdb, err = sqlx.Connect("postgres", connStr) 85 if err == nil { 86 break 87 } 88 log.Warningf("Failed to connect to database '%s'", dbName) 89 } 90 91 if err != nil { 92 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) 93 } 94 95 p.SqlxDB = db.New(sqlxdb, p.CAName, p.Metrics) 96 return nil 97 } 98 99 // PingContext pings the database 100 func (p *Postgres) PingContext(ctx context.Context) error { 101 err := p.SqlxDB.PingContext(ctx) 102 if err != nil { 103 return errors.Wrap(err, "Failed to ping to Postgres database") 104 } 105 return nil 106 } 107 108 // Create creates database and tables 109 func (p *Postgres) Create() (*db.DB, error) { 110 db, err := p.CreateDatabase() 111 if err != nil { 112 return nil, err 113 } 114 err = p.CreateTables() 115 if err != nil { 116 return nil, err 117 } 118 return db, nil 119 } 120 121 // CreateDatabase creates database 122 func (p *Postgres) CreateDatabase() (*db.DB, error) { 123 dbName := p.dbName 124 err := p.createDatabase() 125 if err != nil { 126 return nil, errors.Wrap(err, "Failed to create Postgres database") 127 } 128 129 log.Debugf("Connecting to database '%s', using connection string: '%s'", dbName, util.MaskDBCred(p.datasource)) 130 sqlxdb, err := sqlx.Open("postgres", p.datasource) 131 if err != nil { 132 return nil, errors.Wrapf(err, "Failed to open database '%s' in Postgres server", dbName) 133 } 134 p.SqlxDB = db.New(sqlxdb, p.CAName, p.Metrics) 135 136 return p.SqlxDB.(*db.DB), nil 137 } 138 139 // CreateTables creates table 140 func (p *Postgres) CreateTables() error { 141 err := p.createTables() 142 if err != nil { 143 return errors.Wrap(err, "Failed to create Postgres tables") 144 } 145 return nil 146 } 147 148 func (p *Postgres) createDatabase() error { 149 dbName := p.dbName 150 log.Debugf("Creating Postgres Database (%s) if it does not exist...", dbName) 151 152 query := "CREATE DATABASE " + dbName 153 _, err := p.SqlxDB.Exec("CreateDatabase", query) 154 if err != nil { 155 if !strings.Contains(err.Error(), fmt.Sprintf("database \"%s\" already exists", dbName)) { 156 return errors.Wrap(err, "Failed to execute create database query") 157 } 158 } 159 160 return nil 161 } 162 163 // createPostgresDB creates postgres database 164 func (p *Postgres) createTables() error { 165 db := p.SqlxDB 166 log.Debug("Creating users table if it does not exist") 167 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)"); err != nil { 168 return errors.Wrap(err, "Error creating users table") 169 } 170 log.Debug("Creating affiliations table if it does not exist") 171 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 { 172 return errors.Wrap(err, "Error creating affiliations table") 173 } 174 log.Debug("Creating certificates table if it does not exist") 175 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 { 176 return errors.Wrap(err, "Error creating certificates table") 177 } 178 log.Debug("Creating credentials table if it does not exist") 179 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 { 180 return errors.Wrap(err, "Error creating credentials table") 181 } 182 log.Debug("Creating revocation_authority_info table if it does not exist") 183 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 { 184 return errors.Wrap(err, "Error creating revocation_authority_info table") 185 } 186 log.Debug("Creating nonces table if it does not exist") 187 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 { 188 return errors.Wrap(err, "Error creating nonces table") 189 } 190 log.Debug("Creating properties table if it does not exist") 191 if _, err := db.Exec("CreatePropertiesTable", "CREATE TABLE IF NOT EXISTS properties (property VARCHAR(255), value VARCHAR(256), PRIMARY KEY(property))"); err != nil { 192 return errors.Wrap(err, "Error creating properties table") 193 } 194 _, 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')")) 195 if err != nil { 196 if !strings.Contains(err.Error(), "duplicate key") { 197 return err 198 } 199 } 200 return nil 201 } 202 203 // GetConnStr gets connection string without database 204 func getConnStr(datasource string, dbname string) string { 205 re := regexp.MustCompile(`(dbname=)([^\s]+)`) 206 connStr := re.ReplaceAllString(datasource, fmt.Sprintf("dbname=%s", dbname)) 207 return connStr 208 }