"Failed to connect to Postgres database"

In order to restart from a clean situation and configuration, I removed the
previous fabric-ca folder, created a new one, and then initiated the
fabric-ca-server. With the default SQLite everything seem working fine. But
one I try to use the PostgreSQL-11 db I created before, errors appear:

(base) marco(at)pc:~/fabric$ rm -rf fabric-ca(base) marco(at)pc:~/fabric$
mkdir fabric-ca(base) marco(at)pc:~/fabric$ cd fabric-ca/(base)
marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server init -b
admin:adminpw(base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw2019/09/26 15:48:54 [INFO] Created default configuration
file at /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml2019/09/26
15:48:54 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca2019/09/26 15:48:54 [INFO] Server Version:
1.4.42019/09/26 15:48:54 [INFO] Server Levels: &{Identity:2
Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}2019/09/26 15:48:54
[WARNING] &{69 The specified CA certificate file
/home/marco/fabric/fabric-ca/ca-cert.pem does not exist}2019/09/26
15:48:54 [INFO] generating key: &{A:ecdsa S:256}2019/09/26 15:48:54
[INFO] encoded CSR2019/09/26 15:48:54 [INFO] signed certificate with
serial number 1625953039820960683388734809875126848203422536642019/09/26
15:48:54 [INFO] The CA key and certificate were generated for
CA 2019/09/26 15:48:54 [INFO] The key was stored by BCCSP provider
'SW'2019/09/26 15:48:54 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem2019/09/26 15:48:54 [INFO]
Initialized sqlite3 database at
/home/marco/fabric/fabric-ca/fabric-ca-server.db2019/09/26 15:48:54
[INFO] The issuer key was successfully stored. The public key is at:
/home/marco/fabric/fabric-ca/IssuerPublicKey, secret key is at:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey2019/09/26
15:48:54 [INFO] Idemix issuer revocation public and secret
keys were generated for CA ''2019/09/26 15:48:54 [INFO] The revocation
key was successfully stored.
The public key is at: /home/marco/fabric/fabric-
ca/IssuerRevocationPublicKey, private key is at:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey2019/09/26
15:48:54 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca2019/09/26 15:48:54 [INFO] Operation
Server Listening on 127.0.0.1:94432019/09/26 15:48:54 [INFO] Listening
on http://0.0.0.0:7054

I set the brand-new fabric-ca-server-config.yaml in this way:

#db:# type: sqlite3# datasource: fabric-ca-server.db# tls:#
enabled: false# certfiles:# client:# certfile:#
keyfile:

db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin
password=password dbname=fabmnetdb sslmode=verify-full

and in /etc/postgresql/11/fabmnet/postgresql.conf :

ssl = on
ssl_cert_file = '/home/marco/fabric/fabric-ca/ca-cert.pem'
ssl_key_file = '/home/marco/fabric/fabric-ca/msp/keystore
/IssuerSecretKey'

After systemctl restart postgresql, I tried to start the fabric-ca-server:

(base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw2019/09/26 15:56:50 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml2019/09/26
15:56:50 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca2019/09/26 15:56:50 [INFO] Server Version:
1.4.42019/09/26 15:56:50 [INFO] Server Levels: &{Identity:2
Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}2019/09/26 15:56:50 [INFO]
The CA key and certificate already exist2019/09/26 15:56:50 [INFO] The
key is stored by BCCSP provider 'SW'2019/09/26 15:56:50 [INFO] The
certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem2019/09/26
15:56:50 [WARNING] Failed to connect to database 'fabmnetdb'2019/09/26
15:56:50 [WARNING] Failed to connect to database 'postgres'2019/09/26
15:56:50 [WARNING] Failed to connect to database 'template1'2019/09/26
15:56:50 [ERROR] Error occurred initializing database: Failedto
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
continuing2019/09/26 15:56:50 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca2019/09/26 15:56:50 [INFO] Operation
Server Listening on 127.0.0.1:94432019/09/26 15:56:50 [INFO] Listening
on http://0.0.0.0:7054

Before I also removed all the previous content of
/var/log/postgresql/postgresql-11-fabmnet.log to have a clean situation.
But strangely now I do not get any new logging information in
postgresql-11-fabmnet.log

So. I think there must be something to fix in the interface between
fabric-ca-server and PostgreSQL-11 db. In fabric-ca-server-config.yaml, in
postgresql.conf, in both or somewhere else.

Il giorno gio 26 set 2019 alle ore 12:05 Marco Ippolito <
ippolito(dot)marco(at)gmail(dot)com> ha scritto:

> 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(at)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](at)[unknown] LOG: could not
> accept SSL connection: sslv3 alert bad certificate
> 2019-09-26 11:55:04.517 CEST [4839] [unknown](at)[unknown] LOG: could not
> accept SSL connection: sslv3 alert bad certificate
> 2019-09-26 11:55:04.518 CEST [4840] [unknown](at)[unknown] LOG: could not
> accept SSL connection: sslv3 alert bad certificate
> 2019-09-26 11:56:18.967 CEST [4862] [unknown](at)[unknown] LOG: could not
> accept SSL connection: sslv3 alert bad certificate
> 2019-09-26 11:56:18.969 CEST [4865] [unknown](at)[unknown] LOG: could not
> accept SSL connection: sslv3 alert bad certificate
> 2019-09-26 11:56:18.971 CEST [4866] [unknown](at)[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(at)hotmail(dot)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(dot)marco(at)gmail(dot)com>
>> *Sent:* Wednesday, September 25, 2019 3:34 PM
>> *To:* pgsql-general(at)lists(dot)postgresql(dot)org <
>> pgsql-general(at)lists(dot)postgresql(dot)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(at)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(at)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](at)[unknown] LOG:
>> incomplete
>> startup packet
>> 2019-09-25 20:56:57.540 CEST [4620] [unknown](at)[unknown] LOG: could
>> not
>> accept SSL connection: sslv3 alert bad certificate
>> 2019-09-25 20:56:57.543 CEST [4622] [unknown](at)[unknown] LOG: could
>> not
>> accept SSL connection: sslv3 alert bad certificate
>> 2019-09-25 20:56:57.544 CEST [4623] [unknown](at)[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(at)pc:~$ sudo -su postgres
>> (base) postgres(at)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(at)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
>>
>