Re: "Failed to connect to Postgres database"

Sorry again,
I was cheering up too quickly.
With this configuration in fabric-ca-server-config.yaml :
db:
type: postgres
datasource: host=localhost port=5433 user=postgres password=1234
dbname=fabmnet_ca sslmode=disable
tls:
enabled: false
certfiles:
client:
certfile:
keyfile:

the output of starting fabric-ca-server at first glance seems ok:

(base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/27 20:11:43 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/27 20:11:44 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca
2019/09/27 20:11:44 [INFO] Server Version: 1.4.4
2019/09/27 20:11:44 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/27 20:11:44 [INFO] The CA key and certificate already exist
2019/09/27 20:11:44 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/27 20:11:44 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/27 20:11:44 [INFO] Initialized postgres database at host=localhost
port=5433 user=**** password=**** dbname=fabmnet_ca sslmode=disable
2019/09/27 20:11:44 [INFO] The Idemix issuer public and secret key files
already exist
2019/09/27 20:11:44 [INFO] secret key file location:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
2019/09/27 20:11:44 [INFO] public key file location:
/home/marco/fabric/fabric-ca/IssuerPublicKey
2019/09/27 20:11:44 [INFO] The Idemix issuer revocation public and secret
key files already exist
2019/09/27 20:11:44 [INFO] private key file location:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
2019/09/27 20:11:44 [INFO] public key file location:
/home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
2019/09/27 20:11:44 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/27 20:11:44 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/27 20:11:44 [INFO] Listening on http://0.0.0.0:7054

but the /var/log/postgresql/postgresql-11-fabmnet.log gives us a different,
not so bright, perspective:

2019-09-27 20:11:44.012 CEST [3450] postgres(at)fabmnet_ca ERROR: database
"fabmnet_ca" already exists
2019-09-27 20:11:44.012 CEST [3450] postgres(at)fabmnet_ca STATEMENT: CREATE
DATABASE fabmnet_ca
2019-09-27 20:11:44.015 CEST [3451] postgres(at)fabmnet_ca ERROR: duplicate
key value violates unique constraint "properties_pkey"
2019-09-27 20:11:44.015 CEST [3451] postgres(at)fabmnet_ca DETAIL: Key
(property)=(identity.level) already exists.
2019-09-27 20:11:44.015 CEST [3451] postgres(at)fabmnet_ca STATEMENT: INSERT
INTO properties (property, value) VALUES ('identity.level', '0'),
('affiliation.level', '0'), ('certificate.level', '0'), ('cred$
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca ERROR: duplicate
key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca DETAIL: Key
(name)=(org2) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca ERROR: duplicate
key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca DETAIL: Key
(name)=(org2.department1) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca ERROR: duplicate
key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca DETAIL: Key
(name)=(org1) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca ERROR: duplicate
key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca DETAIL: Key
(name)=(org1.department1) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres(at)fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)

What do these continuous attempts to duplicate key value mean? It doesn't
look so good this fabric-ca-server connection with postgresql-11 's db ...

Marco

Il giorno ven 27 set 2019 alle ore 20:02 Marco Ippolito <
ippolito(dot)marco(at)gmail(dot)com> ha scritto:

> Thank you very much Adrian.
> Two things:
>
> 1)
> Why if I just specify through port the cluster and the host connection I
> connect correctly with SSL,
> but if I specify also the database and the user it connects it doesn't
> usel SSL connection, or at least it doesn't say it uses SSL? :
>
> (base) postgres(at)pc:~$ psql -p5433 -h localhost
> Password for user postgres:
> 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.
>
> postgres=# \conninfo
> You are connected to database "postgres" as user "postgres" on host
> "localhost" at port "5433".
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)
>
>
> (base) postgres(at)pc:~$ psql -p5433 -h localhost -d fabmnet_ca -U postgres
> Password for user postgres:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> Type "help" for help.
>
> fabmnet_ca=# \conninfo
> You are connected to database "fabmnet_ca" as user "postgres" on host
> "localhost" at port "5433".
> fabmnet_ca=#
>
> 2)
> In fabric-ca-server-config.yaml
>
> a) if I set:
>
> db:
> type: postgres
> datasource: host=localhost port=5433 user=postgres password=1234
> dbname=fabmnet_ca sslmode=allow
> tls:
> enabled: false
> certfiles:
> client:
> certfile:
> keyfile:
>
> where sslmode=allow means "first try a non-SSL connection; if that
> fails, try an SSL connection"
>
> (base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server init -b
> admin:adminpw
> 2019/09/27 19:37:46 [INFO] Configuration file location:
> /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/27 19:37:46 [INFO] Server Version: 1.4.4
> 2019/09/27 19:37:46 [INFO] Server Levels: &{Identity:2 Affiliation:1
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/27 19:37:46 [INFO] The CA key and certificate already exist
> 2019/09/27 19:37:46 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/27 19:37:46 [INFO] The certificate is at:
> /home/marco/fabric/fabric-ca/ca-cert.pem
> 2019/09/27 19:37:46 [WARNING] Failed to connect to database
> 'fabmnet_ca'
> 2019/09/27 19:37:46 [WARNING] Failed to connect to database 'postgres'
> 2019/09/27 19:37:46 [WARNING] Failed to connect to database 'template1'
> 2019/09/27 19:37:46 [ERROR] Error occurred initializing database:
> Failed to connect to Postgres database. Postgres requires connecting to a
> specific database, the following databases
> were tried: [fabmnet_ca postgres template1]. Please create one of
> these database before continuing
> 2019/09/27 19:37:46 [INFO] Home directory for default CA:
> /home/marco/fabric/fabric-ca
> 2019/09/27 19:37:46 [INFO] Initialization was successful
>
> /var/log/postgresql/postgresql-11-fabmnet.log :
> 2019-09-27 19:43:14.194 CEST [3213] postgres(at)fabmnet_ca FATAL:
> client certificates can only be checked if a root certificate store is
> available
>
> b) if I set:
> db:
> type: postgres
> datasource: host=localhost port=5433 user=postgres password=1234
> dbname=fabmnet_ca sslmode=disable
> tls:
> enabled: false
> certfiles:
> client:
> certfile:
> keyfile:
>
>
> (base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server init -b
> admin:adminpw
> 2019/09/27 19:55:03 [INFO] Configuration file location:
> /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/27 19:55:03 [INFO] Server Version: 1.4.4
> 2019/09/27 19:55:03 [INFO] Server Levels: &{Identity:2 Affiliation:1
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/27 19:55:03 [INFO] The CA key and certificate already exist
> 2019/09/27 19:55:03 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/27 19:55:03 [INFO] The certificate is at:
> /home/marco/fabric/fabric-ca/ca-cert.pem
> 2019/09/27 19:55:03 [INFO] Initialized postgres database at
> host=localhost port=5433 user=**** password=**** dbname=fabmnet_ca
> sslmode=disable
> 2019/09/27 19:55:03 [INFO] The Idemix issuer public and secret key
> files already exist
> 2019/09/27 19:55:03 [INFO] secret key file location:
> /home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
> 2019/09/27 19:55:03 [INFO] public key file location:
> /home/marco/fabric/fabric-ca/IssuerPublicKey
> 2019/09/27 19:55:03 [INFO] The Idemix issuer revocation public and
> secret key files already exist
> 2019/09/27 19:55:03 [INFO] private key file location:
> /home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
> 2019/09/27 19:55:03 [INFO] public key file location:
> /home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
> 2019/09/27 19:55:03 [INFO] Home directory for default CA:
> /home/marco/fabric/fabric-ca
> 2019/09/27 19:55:03 [INFO] Initialization was successful
>
> /var/log/postgresql/postgresql-11-fabmnet.log :
> 2019-09-27 19:55:03.691 CEST [3313] postgres(at)fabmnet_ca ERROR:
> database "fabmnet_ca" already exists
> 2019-09-27 19:55:03.691 CEST [3313] postgres(at)fabmnet_ca
> STATEMENT: CREATE DATABASE fabmnet_ca
>
> (base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server start -b
> admin:adminpw
> 2019/09/27 19:57:58 [INFO] Configuration file location:
> /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/27 19:57:58 [INFO] Starting server in home directory:
> /home/marco/fabric/fabric-ca
> 2019/09/27 19:57:58 [INFO] Server Version: 1.4.4
> 2019/09/27 19:57:58 [INFO] Server Levels: &{Identity:2 Affiliation:1
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/27 19:57:58 [INFO] The CA key and certificate already exist
> 2019/09/27 19:57:58 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/27 19:57:58 [INFO] The certificate is at:
> /home/marco/fabric/fabric-ca/ca-cert.pem
> 2019/09/27 19:57:58 [INFO] Initialized postgres database at
> host=localhost port=5433 user=**** password=**** dbname=fabmnet_ca
> sslmode=disable
> 2019/09/27 19:57:58 [INFO] The Idemix issuer public and secret key
> files already exist
> 2019/09/27 19:57:58 [INFO] secret key file location:
> /home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
> 2019/09/27 19:57:58 [INFO] public key file location:
> /home/marco/fabric/fabric-ca/IssuerPublicKey
> 2019/09/27 19:57:58 [INFO] The Idemix issuer revocation public and
> secret key files already exist
> 2019/09/27 19:57:58 [INFO] private key file location:
> /home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
> 2019/09/27 19:57:58 [INFO] public key file location:
> /home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
> 2019/09/27 19:57:58 [INFO] Home directory for default CA:
> /home/marco/fabric/fabric-ca
> 2019/09/27 19:57:58 [INFO] Operation Server Listening on
> 127.0.0.1:9443
> 2019/09/27 19:57:58 [INFO] Listening on http://0.0.0.0:7054
>
> Does it mean that in order to use postgresql-11 with fabric-ca I have to
> use only socket connection?
> And if this is the case, why?
>
> Marco
>
> Il giorno ven 27 set 2019 alle ore 18:37 Adrian Klaver <
> adrian(dot)klaver(at)aklaver(dot)com> ha scritto:
>
>> On 9/27/19 8:20 AM, Marco Ippolito wrote:
>> > Correction of my previous email :
>> >
>> > This is the correct ssl connection, not the one before via socket:
>>
>> A tip, when troubleshooting be as explicit as possible in your command
>> line usage. So for below explicitly state the -d postgres -U postgres.
>> This will save you issues with default values and environment values
>> that you don't know about changing the command. This is not the issue
>> here, just a heads up for future use.
>>
>> More below.
>>
>> >
>> > (base) postgres(at)pc:~$ psql -p5433 -h localhost
>> > Password for user postgres:
>> > 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)
>>
>> > fabmnet_ca=#
>> >
>> > Anyway, I'm still struggling in understanding how to configure the ssh
>> > connection of fabric-ca-server to fabmnet_ca database:
>> >
>> > This is what I set in fabric-ca-server-config.yaml :
>> >
>> > #db:
>> > # type: sqlite3
>> > # datasource: fabric-ca-server.db
>> > # tls:
>> > # enabled: false
>> > # certfiles:
>> > # client:
>> > # certfile:
>> > # keyfile:
>> >
>> >
>> > db:
>> > type: postgres
>> > datasource: host=localhost port=5433 user=postgres password=pwd
>> > dbname=fabmnet_ca sslmode=verify-full
>>
>> For now I would drop the sslmode or set it to require.
>> If I am following correctly, if you are cert authentication with
>> fabric-ca:
>>
>>
>> https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#postgresql
>>
>> Then you need to fill in the certfile(s) sections. I know you have
>> tls.enabled: false. I think that the server is taking the datasource as
>> priority and trying a verify-full without the necessary cert
>> information. That is why I suggested backing off on the SSL requirements
>> to see if you can make a connection. For what the sslmode options means
>> go here:
>>
>> https://www.postgresql.org/docs/11/libpq-connect.html#LIBPQ-PARAMKEYWORDS
>>
>> and search in page for sslmode.
>>
>> Plan B would be to fill in the certfile(s) information.
>>
>> As to your question below as to why the psql connection works. You are
>> not specifying an sslmode to the connection so it defaults to a sslmode
>> of:
>>
>> prefer (default)
>>
>> first try an SSL connection; if that fails, try a non-SSL connection
>>
>> There is no cert authentication going on in that case, so you connect.
>> The connection is done using SSL, it just does not verify the cert.
>>
>>
>>
>> > tls:
>> > enabled: false
>> > certfiles:
>> > client:
>> > certfile:
>> > keyfile:
>> >
>> > Initializing the fabric-ca-server gives "Failed to connect to Postgres
>> > database" and in postgresql-11-fabmnet.log : sslv3 alert bad certificate
>> >
>> > (base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server init -b
>> admin:adminpw
>> > 2019/09/27 17:07:27 [INFO] Configuration file location:
>> > /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
>> > 2019/09/27 17:07:27 [INFO] Server Version: 1.4.4
>> > 2019/09/27 17:07:27 [INFO] Server Levels: &{Identity:2 Affiliation:1
>> > Certificate:1 Credential:1 RAInfo:1 Nonce:1}
>> > 2019/09/27 17:07:27 [INFO] The CA key and certificate already exist
>> > 2019/09/27 17:07:27 [INFO] The key is stored by BCCSP provider 'SW'
>> > 2019/09/27 17:07:27 [INFO] The certificate is at:
>> > /home/marco/fabric/fabric-ca/ca-cert.pem
>> > 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'fabmnet_ca'
>> > 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'postgres'
>> > 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'template1'
>> > 2019/09/27 17:07:27 [ERROR] Error occurred initializing database:
>> Failed
>> > to connect to Postgres database. Postgres requires connecting to a
>> > specific database, the following databases were tried: [fabmnet_ca
>> > postgres template1]. Please create one of these database before
>> continuing
>> > 2019/09/27 17:07:27 [INFO] Home directory for default CA:
>> > /home/marco/fabric/fabric-ca
>> > 2019/09/27 17:07:27 [INFO] Initialization was successful
>> >
>> > /var/log/postgresql/postgresql-11-fabmnet.log : 2019-09-27 17:07:27.159
>> > CEST [6626] [unknown](at)[unknown] LOG: could not accept SSL connection:
>> > sslv3 alert bad certificate
>> >
>> > Why it says "sslv3 alert bad certificate" if it's exactly the same
>> > certificate used when connecting to the same database with ssl in
>> > postgres environment as shown above?
>> >
>> > Marco
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>>
>