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

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@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@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]@[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.klaver@aklaver.com