Re: "Failed to connect to Postgres database"

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
>