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  }