Re: "Failed to connect to Postgres database"

Following the indications found here:
https://joelonsql.com/2013/04/27/securing-postgresql-using-hostssl-cert-clientcert1/
I created and modified these files:
CA:

root(at)pc:/home/marco# ls -lah /etc/ssl/private/fabric_ca.key
-rw-r----- 1 root ssl-cert 1.8K Sep 30 14:50 /etc/ssl/private/fabric_ca.key

(base) marco(at)pc:~$ ls -lah /usr/local/share/ca-certificates/fabric_ca.crt
-rw-r--r-- 1 root root 1.3K Sep 30 15:43
/usr/local/share/ca-certificates/fabric_ca.crt

(base) marco(at)pc:~$ ls -lah /etc/ssl/certs/fabric_ca.pem
lrwxrwxrwx 1 root root 46 Sep 30 15:45 /etc/ssl/certs/fabric_ca.pem ->
/usr/local/share/ca-certificates/fabric_ca.crt
(base) marco(at)pc:~$

PostgreSQL-Server:

(base) postgres(at)pc:~$ ls -lah /var/lib/postgresql/11/fabmnet/server.key
-r-------- 1 postgres postgres 1.7K Sep 30 16:05
/var/lib/postgresql/11/fabmnet/server.key

(base) postgres(at)pc:~$ ls -lah /var/lib/postgresql/11/fabmnet/server.crt
-rw-r--r-- 1 postgres postgres 1.2K Sep 30 16:34
/var/lib/postgresql/11/fabmnet/server.crt

(base) postgres(at)pc:~$ ls -lah /var/lib/postgresql/11/fabmnet/root.crt
-rw------- 1 postgres postgres 1.4K Sep 30 13:39
/var/lib/postgresql/11/fabmnet/root.crt

(base) marco(at)pc:~$ ls -ltr /usr/local/share/ca-certificates/fabric_ca.crt
-rw-r--r-- 1 root root 1302 Sep 30 15:43
/usr/local/share/ca-certificates/fabric_ca.crt

(base) marco(at)pc:~$ ls -ltr
/usr/local/share/ca-certificates/fabric_ca_postgresql.crt
-rw------- 1 root root 1354 Sep 30 17:12
/usr/local/share/ca-certificates/fabric_ca_postgresql.crt

(base) marco(at)pc:~$ ls -ltr /etc/ssl/certs/fabric_ca.pem
lrwxrwxrwx 1 root root 46 Sep 30 15:45 /etc/ssl/certs/fabric_ca.pem ->
/usr/local/share/ca-certificates/fabric_ca.crt

(base) marco(at)pc:~$ ls -ltr /etc/ssl/certs/fabric_ca_postgresql.pem
lrwxrwxrwx 1 root root 57 Sep 30 17:12
/etc/ssl/certs/fabric_ca_postgresql.pem ->
/usr/local/share/ca-certificates/fabric_ca_postgresql.crt

I set /etc/postgresql/11/fabmnet/pg_hba.conf in this way:

# 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
clientcert
hostssl fabmnet_ca +ssl_fabric_ca_certusers 192.168.1.0/24
cert clientcert=1

# 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

PostgreSQL-client :

(base) marco(at)pc:~$ ls -ltr ~/.postgresql/root.crt
-rw------- 1 postgres postgres 1354 Sep 30 17:22
/home/marco/.postgresql/root.crt

(base) marco(at)pc:~$ ls -ltr ~/.postgresql/postgresql.key
-r-------- 1 postgres postgres 887 Sep 30 17:23
/home/marco/.postgresql/postgresql.key

(base) marco(at)pc:~$ ls -ltr ~/.postgresql/postgresql.crt
-rw-r--r-- 1 postgres postgres 1001 Sep 30 17:25
/home/marco/.postgresql/postgresql.crt

If I put in fabric-ca-server-config.yaml:

db:
type: postgres
datasource: host=localhost port=5433 user=postgres password=1234
dbname=fabmnet_ca sslmode=require
tls:
enabled: true
certfiles:
client:
certfile: /var/lib/postgresql/11/fabmnet/server.crt
keyfile: /var/lib/postgresql/11/fabmnet/server.key

(base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
2019/09/30 17:54:02 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/30 17:54:02 [INFO] Server Version: 1.4.4
2019/09/30 17:54:02 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/30 17:54:02 [INFO] The CA key and certificate already exist
2019/09/30 17:54:02 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/30 17:54:02 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/30 17:54:02 [ERROR] Error occurred initializing database: No
trusted root certificates for TLS were provided
2019/09/30 17:54:02 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/30 17:54:02 [INFO] Initialization was successful

If I put in fabric-ca-server-config.yaml:

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/30 17:56:22 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/30 17:56:22 [INFO] Server Version: 1.4.4
2019/09/30 17:56:22 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/30 17:56:22 [INFO] The CA key and certificate already exist
2019/09/30 17:56:22 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/30 17:56:22 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/30 17:56:22 [WARNING] Failed to connect to database 'fabmnet_ca'
2019/09/30 17:56:22 [WARNING] Failed to connect to database 'postgres'
2019/09/30 17:56:22 [WARNING] Failed to connect to database 'template1'
2019/09/30 17:56:22 [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/30 17:56:22 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/30 17:56:22 [INFO] Initialization was successful

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

2019-09-30 17:56:22.760 CEST [10651] [unknown](at)[unknown] LOG: incomplete
startup packet
2019-09-30 17:56:22.760 CEST [10650] [unknown](at)[unknown] LOG: incomplete
startup packet
2019-09-30 17:56:22.760 CEST [10649] [unknown](at)[unknown] LOG: incomplete
startup packet

What could it mean?

Marco

Il giorno sab 28 set 2019 alle ore 23:49 Adrian Klaver <
adrian(dot)klaver(at)aklaver(dot)com> ha scritto:

> On 9/28/19 12:07 AM, Marco Ippolito wrote:
> > Hi Adrian,
> >
> > Il giorno ven 27 set 2019 alle ore 21:39 Adrian Klaver
> > <adrian(dot)klaver(at)aklaver(dot)com <mailto: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 <http://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 <http://127.0.0.1/32>
>
> > md5
> > host replication all ::1/128 md5
> >
>
> > 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:
>
> You are not including the certs or setting tls.enabled: true. Not sure
> that is the root cause at the moment.
>
> I would try just going through psql for the time being to take the
> fabric server out of the loop. Something like:
>
> psql "host=localhost port=5433 dbname=fabmnet_ca user=postgres
> sslmode=require"
>
> From below I am guessing you do not have the SSL certs setup properly
> for the fabmnet Postgres instance(the one on port 5433) and/or on the
> client. Take a look at:
>
> https://www.postgresql.org/docs/11/libpq-ssl.html
>
> >
> >
> > (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
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>