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

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

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
>