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
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com