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

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@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@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@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@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@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@fabmnet_ca ERROR:  database "fabmnet_ca" already exists
        2019-09-27 19:55:03.691 CEST [3313] postgres@fabmnet_ca STATEMENT:  CREATE DATABASE fabmnet_ca

    (base) marco@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.klaver@aklaver.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@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