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  }