Re: "Failed to connect to Postgres database"

Hi Adrian,

Il giorno ven 27 set 2019 alle ore 21:39 Adrian Klaver <
adrian(dot)klaver(at)aklaver(dot)com> ha scritto:

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

/etc/postgresql/11/fabmnet/pg_hba.conf :

# Database administrative login by Unix domain socket
local all postgres peer

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5

# Allow connections from localhost only to fabmnet_ca for postgres user
hostssl fabmnet_ca postgres localhost cert

# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5

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

fabric-ca-server-config.yaml : sslmode=require
db:
type: postgres
datasource: host=localhost port=5433 user=postgres password=1234
dbname=fabmnet_ca sslmode=require
tls:
enabled: false
certfiles:
client:
certfile:
keyfile:

(base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
2019/09/28 09:00:08 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/28 09:00:08 [INFO] Server Version: 1.4.4
2019/09/28 09:00:08 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/28 09:00:08 [INFO] The CA key and certificate already exist
2019/09/28 09:00:08 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/28 09:00:08 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/28 09:00:08 [WARNING] Failed to connect to database 'fabmnet_ca'
2019/09/28 09:00:08 [ERROR] Error occurred initializing database: Failed to
create Postgres tables: Error creating users table: pq: client certificates
can only be checked if a root certificate store is available
2019/09/28 09:00:08 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/28 09:00:08 [INFO] Initialization was successful

/var/log/postgresql/postgresql-11-fabmnet.log :

2019-09-28 09:00:08.634 CEST [4226] postgres(at)fabmnet_ca FATAL: client
certificates can only be checked if a root certificate store is available
2019-09-28 09:00:08.641 CEST [4227] postgres(at)postgres ERROR: database
"fabmnet_ca" already exists
2019-09-28 09:00:08.641 CEST [4227] postgres(at)postgres STATEMENT: CREATE
DATABASE fabmnet_ca
2019-09-28 09:00:08.644 CEST [4228] postgres(at)fabmnet_ca FATAL: client
certificates can only be checked if a root certificate store is available
2019-09-28 09:00:08.650 CEST [4227] postgres(at)postgres LOG: could not
receive data from client: Connection reset by peer