"Failed to connect to Postgres database" - Mailing list pgsql-general

Affer removing the previous cert and key files,  I started again the fabric-ca server discovering that new cert and key files were created:

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/26 11:56:18 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 11:56:18 [INFO] Starting server in home directory: /home/marco/fabric/fabric-ca
2019/09/26 11:56:18 [INFO] Server Version: 1.4.4
2019/09/26 11:56:18 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 11:56:18 [WARNING] &{69 The specified CA certificate file /home/marco/fabric/fabric-ca/ca-cert.pem does not exist}
2019/09/26 11:56:18 [INFO] generating key: &{A:ecdsa S:256}
2019/09/26 11:56:18 [INFO] encoded CSR
2019/09/26 11:56:18 [INFO] signed certificate with serial number 542755587310273579559145444277178107021548224556
2019/09/26 11:56:18 [INFO] The CA key and certificate were generated for CA
2019/09/26 11:56:18 [INFO] The key was stored by BCCSP provider 'SW'
2019/09/26 11:56:18 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'postgres'
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'template1'
2019/09/26 11:56:18 [ERROR] Error occurred initializing database: Failed to connect to Postgres database. Postgres requires connecting to a specific database, the following databases were tried: [fabmnetdb postgres template1]. Please create one of these database before continuing
2019/09/26 11:56:18 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
2019/09/26 11:56:18 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 11:56:18 [INFO] Listening on http://0.0.0.0:7054

but, again, the corresponding log says "bad certificate" :

2019-09-26 11:55:04.514 CEST [4837] [unknown]@[unknown] LOG:  could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:55:04.517 CEST [4839] [unknown]@[unknown] LOG:  could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:55:04.518 CEST [4840] [unknown]@[unknown] LOG:  could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.967 CEST [4862] [unknown]@[unknown] LOG:  could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.969 CEST [4865] [unknown]@[unknown] LOG:  could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.971 CEST [4866] [unknown]@[unknown] LOG:  could not accept SSL connection: sslv3 alert bad certificate

So..how could it be "bad certificate" if it's just been created brand new by the execution of fabric-ca-server start?

Marco

Il giorno gio 26 set 2019 alle ore 00:43 Martin Gainty <mgainty@hotmail.com> ha scritto:

Hi Marco

not necessarily with PG but with all other servers i secure when i see that error
it means the certificate and key your provider is referencing are already stored in storage (in my case "truststore")
I would clean all storage locations of certificate and key
then I would allow BCCSP provider to push your cert and key into stores (identified by BCCSP config)

if that doesnt work I would disable hardcoded BCCSP Provider then manually import your certs and keys into your truststore

YMMV
martin

---

From: Marco Ippolito <ippolito.marco@gmail.com>
Sent: Wednesday, September 25, 2019 3:34 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: could not accept SSL connection: sslv3 alert bad certificate

 

Following the indications here: https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database I'm trying to understand how to correctly set Fabric-CA with a PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.
 
I created a postgresql-11 db to which I can connect with SSL:
 
    (base) marco@pc:~$ psql --cluster 11/fabmnet -h 127.0.0.1 -d fabmnetdb -U fabmnet_admin
    Password for user fabmnet_admin:
    psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
    SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
    Type "help" for help.

    fabmnetdb=> \l
                                    List of databases
       Name    |     Owner     | Encoding | Collate |  Ctype  |   Access privileges  
    -----------+---------------+----------+---------+---------+-----------------------
     fabmnetdb | fabmnet_admin | UTF8     | C.UTF-8 | C.UTF-8 |
     postgres  | postgres      | UTF8     | C.UTF-8 | C.UTF-8 |
     template0 | postgres      | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
               |               |          |         |         | postgres=CTc/postgres
     template1 | postgres      | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
               |               |          |         |         | postgres=CTc/postgres
    (4 rows)

    fabmnetdb=>
 

but when trying to start a fabric-ca-server :

    (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
    admin:adminpw
    2019/09/25 20:56:57 [INFO] Configuration file location: /home/marco/fabric
    /fabric-ca/fabric-ca-server-config.yaml
    2019/09/25 20:56:57 [INFO] Starting server in home directory: /home/marco
    /fabric/fabric-ca
    2019/09/25 20:56:57 [INFO] Server Version: 1.4.4
    2019/09/25 20:56:57 [INFO] Server Levels: &{Identity:2 Affiliation:1
    Certificate:1 Credential:1 RAInfo:1 Nonce:1}
    2019/09/25 20:56:57 [INFO] The CA key and certificate already exist
    2019/09/25 20:56:57 [INFO] The key is stored by BCCSP provider 'SW'
    2019/09/25 20:56:57 [INFO] The certificate is at: /home/marco/fabric
    /fabric-ca/ca-cert.pem
    2019/09/25 20:56:57 [WARNING] Failed to connect to database 'fabmnetdb'
    2019/09/25 20:56:57 [WARNING] Failed to connect to database 'postgres'
    2019/09/25 20:56:57 [WARNING] Failed to connect to database 'template1'
    2019/09/25 20:56:57 [ERROR] Error occurred initializing database: Failed
    to connect to Postgres database. Postgres requires connecting to a
    specific database, the following databases were tried: [fabmnetdb postgres
     template1]. Please create one of these database before continuing
    2019/09/25 20:56:57 [INFO] Home directory for default CA: /home/marco
    /fabric/fabric-ca
    2019/09/25 20:56:57 [INFO] Operation Server Listening on 127.0.0.1:9443
    2019/09/25 20:56:57 [INFO] Listening on http://0.0.0.0:7054

This is the corresponding part in /var/log/postgresql/postgresql-11-fabmnet.log :

    2019-09-25 20:51:52.655 CEST [1096] LOG:  listening on IPv6 address "::1",
    port 5433
    2019-09-25 20:51:52.673 CEST [1096] LOG:  listening on IPv4 address
    "127.0.0.1", port 5433
    2019-09-25 20:51:52.701 CEST [1096] LOG:  listening on Unix socket
    "/var/run/postgresql/.s.PGSQL.5433"
    2019-09-25 20:51:52.912 CEST [1171] LOG:  database system was interrupted;
     last known up at 2019-09-25 09:50:30 CEST
    2019-09-25 20:51:53.001 CEST [1171] LOG:  database system was not properly
     shut down; automatic recovery in progress
    2019-09-25 20:51:53.011 CEST [1171] LOG:  redo starts at 0/1668238
    2019-09-25 20:51:53.011 CEST [1171] LOG:  invalid record length at
    0/1668318: wanted 24, got 0
    2019-09-25 20:51:53.011 CEST [1171] LOG:  redo done at 0/16682E0
    2019-09-25 20:51:53.043 CEST [1096] LOG:  database system is ready to
    accept connections
    2019-09-25 20:51:53.569 CEST [1206] [unknown]@[unknown] LOG:  incomplete
    startup packet
    2019-09-25 20:56:57.540 CEST [4620] [unknown]@[unknown] LOG:  could not
    accept SSL connection: sslv3 alert bad certificate
    2019-09-25 20:56:57.543 CEST [4622] [unknown]@[unknown] LOG:  could not
    accept SSL connection: sslv3 alert bad certificate
    2019-09-25 20:56:57.544 CEST [4623] [unknown]@[unknown] LOG:  could not
    accept SSL connection: sslv3 alert bad certificate

   
This is how I set the pg_hba.conf file in the fabmnet postgresql cluster :
 
    (base) marco@pc:~$ sudo -su postgres
    (base) postgres@pc:~$ nano /etc/postgresql/11/fabmnet/pg_hba.conf
    Unable to create directory /home/marco/.local/share/nano/: Permission denied
    It is required for saving/loading search history or cursor positions.

    Press Enter to continue
 
    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # Database administrative login by Unix domain socket
    local   all             postgres                                peer

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     peer
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5

    # Allow connections from 10.1.2.0/24 subnet only to fabric_ca_db for fabric_ca_user
    hostssl fabmnetdb    fabmnet_admin      10.1.2.0/24             cert

    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    local   replication     all                                     peer
    host    replication     all             127.0.0.1/32            md5
    host    replication     all             ::1/128                 md5
 
    And this is the db's configuration in (base) marco@pc:~$ nano ./fabric/fabric-ca/fabric-ca-
    server-config.yaml :
 
    db:
      type: postgres
      datasource: host=localhost port=5433 user=fabmnet_admin password=pwd dbname=fabmnetdb    
    sslmode=verify-full
 

How to correctly set up SSL connection to PostgresSQL-11 db?

Looking forward to your kind help
Marco