Re: "Failed to connect to Postgres database" : No usage specified for certificate (update)

Hi Adrian,
important update.

After adding in fabric-ca-server-config.yaml

ca:
# Name of this CA
name: fabric_ca
# Key file (is only used to import a private key into BCCSP)
keyfile: /etc/ssl/private/fabric_ca.key
# Certificate file (default: ca-cert.pem)
certfile: /etc/ssl/certs/fabric_ca.pem
# Chain file
chainfile:

Now I get this message:

(base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
2019/09/30 18:10:41 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/30 18:10:41 [INFO] Server Version: 1.4.4
2019/09/30 18:10:41 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/30 18:10:41 [INFO] The CA key and certificate files already exist
2019/09/30 18:10:41 [INFO] Key file location: /etc/ssl/private/fabric_ca.key
2019/09/30 18:10:41 [INFO] Certificate file location:
/etc/ssl/certs/fabric_ca.pem
2019/09/30 18:10:41 [FATAL] Initialization failure: Validation of
certificate and key failed: Invalid certificate in file
'/etc/ssl/certs/fabric_ca.pem': No usage specified for certificate

This is the start of /etc/ssl/certs/fabric_ca.pem:

-----BEGIN CERTIFICATE-----
MIIDlTCCAn2gAwIBAgIUCm243lybs0PNfAEdgbuw0chmjWkwDQYJKoZIhvcNAQEL

and this is its end:
xNItFJulgsA1
-----END CERTIFICATE-----

What does it mean "No usage specified for certificate" ?

Il giorno lun 30 set 2019 alle ore 18:01 Marco Ippolito <
ippolito(dot)marco(at)gmail(dot)com> ha scritto:

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