Re: "Failed to connect to Postgres database"

On 9/27/19 11:02 AM, Marco Ippolito wrote:
> 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? :

Can you show the contents of pg_hba.conf file for the 11/fabmnet
cluster. The file will be in:

/etc/postgresql/11/fabmnet/

More below.

>
> 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

According to the fabric-ca docs, allow is not one of the valid values:

https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#postgresql

"Specifying sslmode configures the type of SSL authentication. Valid
values for sslmode are:

Mode Description
disable No SSL
require Always SSL (skip verification)
verify-ca Always SSL (verify that the certificate presented by the
server was signed by a trusted CA)
verify-full Same as verify-ca AND verify that the certificate presented
by the server was signed by a trusted CA and the server hostname matches
the one in the certificate

"

>       tls:
>           enabled: false
>           certfiles:
>           client:
>             certfile:
>             keyfile:
>
>     where sslmode=allow means "first try a non-SSL connection; if that
> fails, try an SSL connection"

>
>     /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

The above tells me that the start is ignoring sslmode=allow and rolling
over into a verification mode and there are no certs specified. Please
do as requested as try sslmode=require.

More below.

>
>   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:
>
>

>
>     /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

The fabmnet_ca database has already been created.

>
> 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?

No you connected to localhost, though without SSL. Try again with
sslmode=require and I am pretty sure you will connect with SSL, but no
cert verification.

>
> Marco
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com