Thread: could not accept SSL connection: sslv3 alert bad certificate

could not accept SSL connection: sslv3 alert bad certificate

From

Marco Ippolito

Date:

25 September 2019, 19:34:19

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw
2019/09/25 20:56:57 [INFO] Configuration file location: /home/marco/fabric
/fabric-ca/fabric-ca-server-config.yaml
2019/09/25 20:56:57 [INFO] Starting server in home directory: /home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Server Version: 1.4.4
2019/09/25 20:56:57 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/25 20:56:57 [INFO] The CA key and certificate already exist
2019/09/25 20:56:57 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/25 20:56:57 [INFO] The certificate is at: /home/marco/fabric
/fabric-ca/ca-cert.pem
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'postgres'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'template1'
2019/09/25 20:56:57 [ERROR] Error occurred initializing database: Failed
to connect to Postgres database. Postgres requires connecting to a
specific database, the following databases were tried: [fabmnetdb postgres
template1]. Please create one of these database before continuing
2019/09/25 20:56:57 [INFO] Home directory for default CA: /home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/25 20:56:57 [INFO] Listening on http://0.0.0.0:7054

This is the corresponding part in /var/log/postgresql/postgresql-11-fabmnet.log :

2019-09-25 20:51:52.655 CEST [1096] LOG: listening on IPv6 address "::1",
port 5433
2019-09-25 20:51:52.673 CEST [1096] LOG: listening on IPv4 address
"127.0.0.1", port 5433
2019-09-25 20:51:52.701 CEST [1096] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5433"
2019-09-25 20:51:52.912 CEST [1171] LOG: database system was interrupted;
last known up at 2019-09-25 09:50:30 CEST
2019-09-25 20:51:53.001 CEST [1171] LOG: database system was not properly
shut down; automatic recovery in progress
2019-09-25 20:51:53.011 CEST [1171] LOG: redo starts at 0/1668238
2019-09-25 20:51:53.011 CEST [1171] LOG: invalid record length at
0/1668318: wanted 24, got 0
2019-09-25 20:51:53.011 CEST [1171] LOG: redo done at 0/16682E0
2019-09-25 20:51:53.043 CEST [1096] LOG: database system is ready to
accept connections
2019-09-25 20:51:53.569 CEST [1206] [unknown]@[unknown] LOG: incomplete
startup packet
2019-09-25 20:56:57.540 CEST [4620] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.543 CEST [4622] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.544 CEST [4623] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate

This is how I set the pg_hba.conf file in the fabmnet postgresql cluster :

(base) marco@pc:~$ sudo -su postgres
(base) postgres@pc:~$ nano /etc/postgresql/11/fabmnet/pg_hba.conf
Unable to create directory /home/marco/.local/share/nano/: Permission denied
It is required for saving/loading search history or cursor positions.

Press Enter to continue

# TYPE DATABASE USER ADDRESS METHOD

# 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 10.1.2.0/24 subnet only to fabric_ca_db for fabric_ca_user
hostssl fabmnetdb fabmnet_admin 10.1.2.0/24 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

And this is the db's configuration in (base) marco@pc:~$ nano ./fabric/fabric-ca/fabric-ca-
server-config.yaml :

db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin password=pwd dbname=fabmnetdb
sslmode=verify-full

How to correctly set up SSL connection to PostgresSQL-11 db?

Looking forward to your kind help
Marco

Re: could not accept SSL connection: sslv3 alert bad certificate

From

Adrian Klaver

Date:

25 September 2019, 23:56:50

On 9/25/19 12:34 PM, Marco Ippolito wrote:
> Following the indications here: 
> https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database 
> I'm trying to understand how to correctly set Fabric-CA with a 
> PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.
> 
> I created a postgresql-11 db to which I can connect with SSL:
> 
>      (base) marco@pc:~$ psql --cluster 11/fabmnet -h 127.0.0.1 -d 
> fabmnetdb -U fabmnet_admin
>      Password for user fabmnet_admin:
>      psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
>      SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, 
> bits: 256, compression: off)
>      Type "help" for help.
> 
>      fabmnetdb=> \l
>                                      List of databases
>         Name    |     Owner     | Encoding | Collate |  Ctype  |   
> Access privileges
>      
> -----------+---------------+----------+---------+---------+-----------------------
>       fabmnetdb | fabmnet_admin | UTF8     | C.UTF-8 | C.UTF-8 |
>       postgres  | postgres      | UTF8     | C.UTF-8 | C.UTF-8 |
>       template0 | postgres      | UTF8     | C.UTF-8 | C.UTF-8 | 
> =c/postgres          +
>                 |               |          |         |         | 
> postgres=CTc/postgres
>       template1 | postgres      | UTF8     | C.UTF-8 | C.UTF-8 | 
> =c/postgres          +
>                 |               |          |         |         | 
> postgres=CTc/postgres
>      (4 rows)
> 
>      fabmnetdb=>
> 
> 
> but when trying to start a fabric-ca-server :
> 
>      (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
>      admin:adminpw
>      2019/09/25 20:56:57 [INFO] Configuration file location: 
> /home/marco/fabric
>      /fabric-ca/fabric-ca-server-config.yaml
>      2019/09/25 20:56:57 [INFO] Starting server in home directory: 
> /home/marco
>      /fabric/fabric-ca
>      2019/09/25 20:56:57 [INFO] Server Version: 1.4.4
>      2019/09/25 20:56:57 [INFO] Server Levels: &{Identity:2 Affiliation:1
>      Certificate:1 Credential:1 RAInfo:1 Nonce:1}
>      2019/09/25 20:56:57 [INFO] The CA key and certificate already exist
>      2019/09/25 20:56:57 [INFO] The key is stored by BCCSP provider 'SW'
>      2019/09/25 20:56:57 [INFO] The certificate is at: /home/marco/fabric
>      /fabric-ca/ca-cert.pem
>      2019/09/25 20:56:57 [WARNING] Failed to connect to database 'fabmnetdb'
>      2019/09/25 20:56:57 [WARNING] Failed to connect to database 'postgres'
>      2019/09/25 20:56:57 [WARNING] Failed to connect to database 'template1'
>      2019/09/25 20:56:57 [ERROR] Error occurred initializing database: 
> Failed
>      to connect to Postgres database. Postgres requires connecting to a
>      specific database, the following databases were tried: [fabmnetdb 
> postgres
>       template1]. Please create one of these database before continuing
>      2019/09/25 20:56:57 [INFO] Home directory for default CA: /home/marco
>      /fabric/fabric-ca
>      2019/09/25 20:56:57 [INFO] Operation Server Listening on 
> 127.0.0.1:9443 <http://127.0.0.1:9443>
>      2019/09/25 20:56:57 [INFO] Listening on http://0.0.0.0:7054
> 
> This is the corresponding part in 
> /var/log/postgresql/postgresql-11-fabmnet.log :
> 
>      2019-09-25 20:51:52.655 CEST [1096] LOG:  listening on IPv6 address 
> "::1",
>      port 5433
>      2019-09-25 20:51:52.673 CEST [1096] LOG:  listening on IPv4 address
>      "127.0.0.1", port 5433
>      2019-09-25 20:51:52.701 CEST [1096] LOG:  listening on Unix socket
>      "/var/run/postgresql/.s.PGSQL.5433"
>      2019-09-25 20:51:52.912 CEST [1171] LOG:  database system was 
> interrupted;
>       last known up at 2019-09-25 09:50:30 CEST
>      2019-09-25 20:51:53.001 CEST [1171] LOG:  database system was not 
> properly
>       shut down; automatic recovery in progress
>      2019-09-25 20:51:53.011 CEST [1171] LOG:  redo starts at 0/1668238
>      2019-09-25 20:51:53.011 CEST [1171] LOG:  invalid record length at
>      0/1668318: wanted 24, got 0
>      2019-09-25 20:51:53.011 CEST [1171] LOG:  redo done at 0/16682E0
>      2019-09-25 20:51:53.043 CEST [1096] LOG:  database system is ready to
>      accept connections
>      2019-09-25 20:51:53.569 CEST [1206] [unknown]@[unknown] LOG: 
>   incomplete
>      startup packet
>      2019-09-25 20:56:57.540 CEST [4620] [unknown]@[unknown] LOG:  could 
> not
>      accept SSL connection: sslv3 alert bad certificate
>      2019-09-25 20:56:57.543 CEST [4622] [unknown]@[unknown] LOG:  could not
>      accept SSL connection: sslv3 alert bad certificate
>      2019-09-25 20:56:57.544 CEST [4623] [unknown]@[unknown] LOG:  could 
> not
>      accept SSL connection: sslv3 alert bad certificate
> 
> 
> This is how I set the pg_hba.conf file in the fabmnet postgresql cluster :
> 
>      (base) marco@pc:~$ sudo -su postgres
>      (base) postgres@pc:~$ nano /etc/postgresql/11/fabmnet/pg_hba.conf
>      Unable to create directory /home/marco/.local/share/nano/: 
> Permission denied
>      It is required for saving/loading search history or cursor positions.
> 
>      Press Enter to continue
> 
>      # TYPE  DATABASE        USER            ADDRESS                 METHOD
> 
>      # 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 10.1.2.0/24 <http://10.1.2.0/24> subnet 
> only to fabric_ca_db for fabric_ca_user
>      hostssl fabmnetdb    fabmnet_admin 10.1.2.0/24 <http://10.1.2.0/24> 
>              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
> 
>      And this is the db's configuration in (base) marco@pc:~$ nano 
> ./fabric/fabric-ca/fabric-ca-
>      server-config.yaml :
> 
>      db:
>        type: postgres
>        datasource: host=localhost port=5433 user=fabmnet_admin 
> password=pwd dbname=fabmnetdb
>      sslmode=verify-full
> 
> 
> How to correctly set up SSL connection to PostgresSQL-11 db?

I don't believe it has anything to do with SSL at this point. Looks like 
your are not connecting to the server period from:

fabric-ca-server start -b

Things I noticed that might apply:

1) For your psql connection you have:

  psql --cluster 11/fabmnet -h 127.0.0.1 -d fabmnetdb -U fabmnet_admin

There is no port provided. By default that would be 5432. In your *.yaml 
file you have port 5433.

So do you have more then once instance of Postgres running?
Or is the environment variable PGPORT set to 5433?

2) In the *.yaml file you have host=localhost.

On the chance hosts is not set correctly what happens if you change this 
to host=127.0.0.1?

> Looking forward to your kind help
> Marco


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: could not accept SSL connection: sslv3 alert bad certificate

From

Adrian Klaver

Date:

26 September 2019, 00:07:53

On 9/25/19 12:34 PM, Marco Ippolito wrote:
> Following the indications here: 
> https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database 
> I'm trying to understand how to correctly set Fabric-CA with a 
> PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.
> 

> This is the corresponding part in 
> /var/log/postgresql/postgresql-11-fabmnet.log :
> 
>      2019-09-25 20:51:52.655 CEST [1096] LOG:  listening on IPv6 address 
> "::1",
>      port 5433
>      2019-09-25 20:51:52.673 CEST [1096] LOG:  listening on IPv4 address
>      "127.0.0.1", port 5433
>      2019-09-25 20:51:52.701 CEST [1096] LOG:  listening on Unix socket
>      "/var/run/postgresql/.s.PGSQL.5433"
>      2019-09-25 20:51:52.912 CEST [1171] LOG:  database system was 
> interrupted;
>       last known up at 2019-09-25 09:50:30 CEST
>      2019-09-25 20:51:53.001 CEST [1171] LOG:  database system was not 
> properly
>       shut down; automatic recovery in progress
>      2019-09-25 20:51:53.011 CEST [1171] LOG:  redo starts at 0/1668238
>      2019-09-25 20:51:53.011 CEST [1171] LOG:  invalid record length at
>      0/1668318: wanted 24, got 0
>      2019-09-25 20:51:53.011 CEST [1171] LOG:  redo done at 0/16682E0
>      2019-09-25 20:51:53.043 CEST [1096] LOG:  database system is ready to
>      accept connections
>      2019-09-25 20:51:53.569 CEST [1206] [unknown]@[unknown] LOG: 
>   incomplete
>      startup packet
>      2019-09-25 20:56:57.540 CEST [4620] [unknown]@[unknown] LOG:  could 
> not
>      accept SSL connection: sslv3 alert bad certificate
>      2019-09-25 20:56:57.543 CEST [4622] [unknown]@[unknown] LOG:  could not
>      accept SSL connection: sslv3 alert bad certificate
>      2019-09-25 20:56:57.544 CEST [4623] [unknown]@[unknown] LOG:  could 
> not
>      accept SSL connection: sslv3 alert bad certificate
> 

Aargh, I missed the part above.

What happens if you remove the sslmode=verify-full from the *.yaml file?

> 
>      And this is the db's configuration in (base) marco@pc:~$ nano 
> ./fabric/fabric-ca/fabric-ca-
>      server-config.yaml :
> 
>      db:
>        type: postgres
>        datasource: host=localhost port=5433 user=fabmnet_admin 
> password=pwd dbname=fabmnetdb
>      sslmode=verify-full
> 
> 
> How to correctly set up SSL connection to PostgresSQL-11 db?
> 
> Looking forward to your kind help
> Marco


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: could not accept SSL connection: sslv3 alert bad certificate

From

Marco Ippolito

Date:

26 September 2019, 08:10:42

Hi Adrian,

putting in /fabric/fabric-ca/fabric-ca-server-config.yaml :

db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin password=fabmnet1971 dbname=fabmnetdb sslmode=verify-ca

this is the result:

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/26 09:44:39 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 09:44:39 [INFO] Starting server in home directory: /home/marco/fabric/fabric-ca
2019/09/26 09:44:39 [INFO] Server Version: 1.4.4
2019/09/26 09:44:39 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 09:44:39 [INFO] The CA key and certificate already exist
2019/09/26 09:44:39 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/26 09:44:39 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/26 09:44:39 [ERROR] Error occurred initializing database: Failed to create Postgres database: Failed to execute create database query: pq: permission denied to create database
2019/09/26 09:44:39 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
2019/09/26 09:44:39 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 09:44:39 [INFO] Listening on http://0.0.0.0:7054

and the corresponding log in /var/log/postgresql/postgresql-11-fabmnet.log :

2019-09-26 09:21:11.605 CEST [1132] LOG: received fast shutdown request
2019-09-26 09:21:11.613 CEST [1132] LOG: aborting any active transactions
2019-09-26 09:21:11.615 CEST [1132] LOG: background worker "logical replication launcher" (PID 1169) exited with exit code 1
2019-09-26 09:21:11.616 CEST [1161] LOG: shutting down
2019-09-26 09:21:11.643 CEST [1132] LOG: database system is shut down
2019-09-26 09:21:57.370 CEST [1077] LOG: listening on IPv6 address "::1", port 5433
2019-09-26 09:21:57.370 CEST [1077] LOG: listening on IPv4 address "127.0.0.1", port 5433
2019-09-26 09:21:57.372 CEST [1077] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2019-09-26 09:21:57.426 CEST [1124] LOG: database system was shut down at 2019-09-26 09:21:11 CEST
2019-09-26 09:21:57.446 CEST [1077] LOG: database system is ready to accept connections
2019-09-26 09:21:58.040 CEST [1147] [unknown]@[unknown] LOG: incomplete startup packet
2019-09-26 09:44:39.374 CEST [2902] fabmnet_admin@fabmnetdb ERROR: permission denied to create database
2019-09-26 09:44:39.374 CEST [2902] fabmnet_admin@fabmnetdb STATEMENT: CREATE DATABASE fabmnetdb

Putting in /home/marco/fabric/fabric-ca/fabric-ca-serve-config.yaml :

db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin password=fabmnet1971 dbname=fabmnetdb sslmode=require

which, according to https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database means: Always SSL (skip verification)

again the same error message:

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/26 10:08:27 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 10:08:27 [INFO] Starting server in home directory: /home/marco/fabric/fabric-ca
2019/09/26 10:08:27 [INFO] Server Version: 1.4.4
2019/09/26 10:08:27 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 10:08:27 [INFO] The CA key and certificate already exist
2019/09/26 10:08:27 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/26 10:08:27 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/26 10:08:27 [ERROR] Error occurred initializing database: Failed to create Postgres database: Failed to execute create database query: pq: permission denied to create database
2019/09/26 10:08:27 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
2019/09/26 10:08:27 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 10:08:27 [INFO] Listening on http://0.0.0.0:7054

and the corresponding portion of the log file:

2019-09-26 10:08:27.947 CEST [3728] fabmnet_admin@fabmnetdb ERROR: permission denied to create database
2019-09-26 10:08:27.947 CEST [3728] fabmnet_admin@fabmnetdb STATEMENT: CREATE DATABASE fabmnetdb

I do not undertand...

Marco

Il giorno gio 26 set 2019 alle ore 02:07 Adrian Klaver <adrian.klaver@aklaver.com> ha scritto:

On 9/25/19 12:34 PM, Marco Ippolito wrote:
> Following the indications here:
> https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database
> I'm trying to understand how to correctly set Fabric-CA with a
> PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.
>

> This is the corresponding part in
> /var/log/postgresql/postgresql-11-fabmnet.log :
>
> 2019-09-25 20:51:52.655 CEST [1096] LOG: listening on IPv6 address
> "::1",
> port 5433
> 2019-09-25 20:51:52.673 CEST [1096] LOG: listening on IPv4 address
> "127.0.0.1", port 5433
> 2019-09-25 20:51:52.701 CEST [1096] LOG: listening on Unix socket
> "/var/run/postgresql/.s.PGSQL.5433"
> 2019-09-25 20:51:52.912 CEST [1171] LOG: database system was
> interrupted;
> last known up at 2019-09-25 09:50:30 CEST
> 2019-09-25 20:51:53.001 CEST [1171] LOG: database system was not
> properly
> shut down; automatic recovery in progress
> 2019-09-25 20:51:53.011 CEST [1171] LOG: redo starts at 0/1668238
> 2019-09-25 20:51:53.011 CEST [1171] LOG: invalid record length at
> 0/1668318: wanted 24, got 0
> 2019-09-25 20:51:53.011 CEST [1171] LOG: redo done at 0/16682E0
> 2019-09-25 20:51:53.043 CEST [1096] LOG: database system is ready to
> accept connections
> 2019-09-25 20:51:53.569 CEST [1206] [unknown]@[unknown] LOG:
> incomplete
> startup packet
> 2019-09-25 20:56:57.540 CEST [4620] [unknown]@[unknown] LOG: could
> not
> accept SSL connection: sslv3 alert bad certificate
> 2019-09-25 20:56:57.543 CEST [4622] [unknown]@[unknown] LOG: could not
> accept SSL connection: sslv3 alert bad certificate
> 2019-09-25 20:56:57.544 CEST [4623] [unknown]@[unknown] LOG: could
> not
> accept SSL connection: sslv3 alert bad certificate
>

Aargh, I missed the part above.

What happens if you remove the sslmode=verify-full from the *.yaml file?

>
> And this is the db's configuration in (base) marco@pc:~$ nano
> ./fabric/fabric-ca/fabric-ca-
> server-config.yaml :
>
> db:
> type: postgres
> datasource: host=localhost port=5433 user=fabmnet_admin
> password=pwd dbname=fabmnetdb
> sslmode=verify-full
>
>
> How to correctly set up SSL connection to PostgresSQL-11 db?
>
> Looking forward to your kind help
> Marco

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: could not accept SSL connection: sslv3 alert bad certificate

From

Marco Ippolito

Date:

26 September 2019, 08:14:47

Thanks Martin. I need to check these important aspects as well.

What do you mean as "disable hardcoded BCCSP Provider"?

Marco

Il giorno gio 26 set 2019 alle ore 00:43 Martin Gainty <mgainty@hotmail.com> ha scritto:

Hi Marco

not necessarily with PG but with all other servers i secure when i see that error
it means the certificate and key your provider is referencing are already stored in storage (in my case "truststore")
I would clean all storage locations of certificate and key
then I would allow BCCSP provider to push your cert and key into stores (identified by BCCSP config)

if that doesnt work I would disable hardcoded BCCSP Provider then manually import your certs and keys into your truststore

YMMV
martin
From: Marco Ippolito <ippolito.marco@gmail.com>
Sent: Wednesday, September 25, 2019 3:34 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: could not accept SSL connection: sslv3 alert bad certificate

Following the indications here: https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database I'm trying to understand how to correctly set Fabric-CA with a PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.

I created a postgresql-11 db to which I can connect with SSL:

(base) marco@pc:~$ psql --cluster 11/fabmnet -h 127.0.0.1 -d fabmnetdb -U fabmnet_admin
Password for user fabmnet_admin:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

fabmnetdb=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+---------------+----------+---------+---------+-----------------------
fabmnetdb | fabmnet_admin | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

fabmnetdb=>

but when trying to start a fabric-ca-server :

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw
2019/09/25 20:56:57 [INFO] Configuration file location: /home/marco/fabric
/fabric-ca/fabric-ca-server-config.yaml
2019/09/25 20:56:57 [INFO] Starting server in home directory: /home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Server Version: 1.4.4
2019/09/25 20:56:57 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/25 20:56:57 [INFO] The CA key and certificate already exist
2019/09/25 20:56:57 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/25 20:56:57 [INFO] The certificate is at: /home/marco/fabric
/fabric-ca/ca-cert.pem
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'postgres'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'template1'
2019/09/25 20:56:57 [ERROR] Error occurred initializing database: Failed
to connect to Postgres database. Postgres requires connecting to a
specific database, the following databases were tried: [fabmnetdb postgres
template1]. Please create one of these database before continuing
2019/09/25 20:56:57 [INFO] Home directory for default CA: /home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/25 20:56:57 [INFO] Listening on http://0.0.0.0:7054

This is the corresponding part in /var/log/postgresql/postgresql-11-fabmnet.log :

2019-09-25 20:51:52.655 CEST [1096] LOG: listening on IPv6 address "::1",
port 5433
2019-09-25 20:51:52.673 CEST [1096] LOG: listening on IPv4 address
"127.0.0.1", port 5433
2019-09-25 20:51:52.701 CEST [1096] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5433"
2019-09-25 20:51:52.912 CEST [1171] LOG: database system was interrupted;
last known up at 2019-09-25 09:50:30 CEST
2019-09-25 20:51:53.001 CEST [1171] LOG: database system was not properly
shut down; automatic recovery in progress
2019-09-25 20:51:53.011 CEST [1171] LOG: redo starts at 0/1668238
2019-09-25 20:51:53.011 CEST [1171] LOG: invalid record length at
0/1668318: wanted 24, got 0
2019-09-25 20:51:53.011 CEST [1171] LOG: redo done at 0/16682E0
2019-09-25 20:51:53.043 CEST [1096] LOG: database system is ready to
accept connections
2019-09-25 20:51:53.569 CEST [1206] [unknown]@[unknown] LOG: incomplete
startup packet
2019-09-25 20:56:57.540 CEST [4620] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.543 CEST [4622] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.544 CEST [4623] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate

This is how I set the pg_hba.conf file in the fabmnet postgresql cluster :

(base) marco@pc:~$ sudo -su postgres
(base) postgres@pc:~$ nano /etc/postgresql/11/fabmnet/pg_hba.conf
Unable to create directory /home/marco/.local/share/nano/: Permission denied
It is required for saving/loading search history or cursor positions.

Press Enter to continue

# TYPE DATABASE USER ADDRESS METHOD

# 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 10.1.2.0/24 subnet only to fabric_ca_db for fabric_ca_user
hostssl fabmnetdb fabmnet_admin 10.1.2.0/24 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

And this is the db's configuration in (base) marco@pc:~$ nano ./fabric/fabric-ca/fabric-ca-
server-config.yaml :

db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin password=pwd dbname=fabmnetdb
sslmode=verify-full

How to correctly set up SSL connection to PostgresSQL-11 db?

Looking forward to your kind help
Marco

Re: could not accept SSL connection: sslv3 alert bad certificate

From

Marco Ippolito

Date:

26 September 2019, 10:05:24

Affer removing the previous cert and key files, I started again the fabric-ca server discovering that new cert and key files were created:

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/26 11:56:18 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 11:56:18 [INFO] Starting server in home directory: /home/marco/fabric/fabric-ca
2019/09/26 11:56:18 [INFO] Server Version: 1.4.4
2019/09/26 11:56:18 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 11:56:18 [WARNING] &{69 The specified CA certificate file /home/marco/fabric/fabric-ca/ca-cert.pem does not exist}
2019/09/26 11:56:18 [INFO] generating key: &{A:ecdsa S:256}
2019/09/26 11:56:18 [INFO] encoded CSR
2019/09/26 11:56:18 [INFO] signed certificate with serial number 542755587310273579559145444277178107021548224556
2019/09/26 11:56:18 [INFO] The CA key and certificate were generated for CA
2019/09/26 11:56:18 [INFO] The key was stored by BCCSP provider 'SW'
2019/09/26 11:56:18 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'postgres'
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'template1'
2019/09/26 11:56:18 [ERROR] Error occurred initializing database: Failed to connect to Postgres database. Postgres requires connecting to a specific database, the following databases were tried: [fabmnetdb postgres template1]. Please create one of these database before continuing
2019/09/26 11:56:18 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
2019/09/26 11:56:18 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 11:56:18 [INFO] Listening on http://0.0.0.0:7054

but, again, the corresponding log says "bad certificate" :

2019-09-26 11:55:04.514 CEST [4837] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:55:04.517 CEST [4839] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:55:04.518 CEST [4840] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.967 CEST [4862] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.969 CEST [4865] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.971 CEST [4866] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate

So..how could it be "bad certificate" if it's just been created brand new by the execution of fabric-ca-server start?

Marco

Il giorno gio 26 set 2019 alle ore 00:43 Martin Gainty <mgainty@hotmail.com> ha scritto:

Hi Marco

not necessarily with PG but with all other servers i secure when i see that error
it means the certificate and key your provider is referencing are already stored in storage (in my case "truststore")
I would clean all storage locations of certificate and key
then I would allow BCCSP provider to push your cert and key into stores (identified by BCCSP config)

if that doesnt work I would disable hardcoded BCCSP Provider then manually import your certs and keys into your truststore

YMMV
martin
From: Marco Ippolito <ippolito.marco@gmail.com>
Sent: Wednesday, September 25, 2019 3:34 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: could not accept SSL connection: sslv3 alert bad certificate

Following the indications here: https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database I'm trying to understand how to correctly set Fabric-CA with a PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.

I created a postgresql-11 db to which I can connect with SSL:

(base) marco@pc:~$ psql --cluster 11/fabmnet -h 127.0.0.1 -d fabmnetdb -U fabmnet_admin
Password for user fabmnet_admin:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

fabmnetdb=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+---------------+----------+---------+---------+-----------------------
fabmnetdb | fabmnet_admin | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

fabmnetdb=>

but when trying to start a fabric-ca-server :

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw
2019/09/25 20:56:57 [INFO] Configuration file location: /home/marco/fabric
/fabric-ca/fabric-ca-server-config.yaml
2019/09/25 20:56:57 [INFO] Starting server in home directory: /home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Server Version: 1.4.4
2019/09/25 20:56:57 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/25 20:56:57 [INFO] The CA key and certificate already exist
2019/09/25 20:56:57 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/25 20:56:57 [INFO] The certificate is at: /home/marco/fabric
/fabric-ca/ca-cert.pem
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'postgres'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'template1'
2019/09/25 20:56:57 [ERROR] Error occurred initializing database: Failed
to connect to Postgres database. Postgres requires connecting to a
specific database, the following databases were tried: [fabmnetdb postgres
template1]. Please create one of these database before continuing
2019/09/25 20:56:57 [INFO] Home directory for default CA: /home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/25 20:56:57 [INFO] Listening on http://0.0.0.0:7054

This is the corresponding part in /var/log/postgresql/postgresql-11-fabmnet.log :

2019-09-25 20:51:52.655 CEST [1096] LOG: listening on IPv6 address "::1",
port 5433
2019-09-25 20:51:52.673 CEST [1096] LOG: listening on IPv4 address
"127.0.0.1", port 5433
2019-09-25 20:51:52.701 CEST [1096] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5433"
2019-09-25 20:51:52.912 CEST [1171] LOG: database system was interrupted;
last known up at 2019-09-25 09:50:30 CEST
2019-09-25 20:51:53.001 CEST [1171] LOG: database system was not properly
shut down; automatic recovery in progress
2019-09-25 20:51:53.011 CEST [1171] LOG: redo starts at 0/1668238
2019-09-25 20:51:53.011 CEST [1171] LOG: invalid record length at
0/1668318: wanted 24, got 0
2019-09-25 20:51:53.011 CEST [1171] LOG: redo done at 0/16682E0
2019-09-25 20:51:53.043 CEST [1096] LOG: database system is ready to
accept connections
2019-09-25 20:51:53.569 CEST [1206] [unknown]@[unknown] LOG: incomplete
startup packet
2019-09-25 20:56:57.540 CEST [4620] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.543 CEST [4622] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.544 CEST [4623] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate

This is how I set the pg_hba.conf file in the fabmnet postgresql cluster :

(base) marco@pc:~$ sudo -su postgres
(base) postgres@pc:~$ nano /etc/postgresql/11/fabmnet/pg_hba.conf
Unable to create directory /home/marco/.local/share/nano/: Permission denied
It is required for saving/loading search history or cursor positions.

Press Enter to continue

# TYPE DATABASE USER ADDRESS METHOD

# 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 10.1.2.0/24 subnet only to fabric_ca_db for fabric_ca_user
hostssl fabmnetdb fabmnet_admin 10.1.2.0/24 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

And this is the db's configuration in (base) marco@pc:~$ nano ./fabric/fabric-ca/fabric-ca-
server-config.yaml :

db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin password=pwd dbname=fabmnetdb
sslmode=verify-full

How to correctly set up SSL connection to PostgresSQL-11 db?

Looking forward to your kind help
Marco

"Failed to connect to Postgres database"

From

Marco Ippolito

Date:

26 September 2019, 14:21:14

In order to restart from a clean situation and configuration, I removed the previous fabric-ca folder, created a new one, and then initiated the fabric-ca-server. With the default SQLite everything seem working fine. But one I try to use the PostgreSQL-11 db I created before, errors appear:

(base) marco@pc:~/fabric$ rm -rf fabric-ca
(base) marco@pc:~/fabric$ mkdir fabric-ca
(base) marco@pc:~/fabric$ cd fabric-ca/
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b  
admin:adminpw
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b 
admin:adminpw
2019/09/26 15:48:54 [INFO] Created default configuration file at 
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 15:48:54 [INFO] Starting server in home directory: 
/home/marco/fabric/fabric-ca
2019/09/26 15:48:54 [INFO] Server Version: 1.4.4
2019/09/26 15:48:54 [INFO] Server Levels: &{Identity:2 Affiliation:1 
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 15:48:54 [WARNING] &{69 The specified CA certificate file 
/home/marco/fabric/fabric-ca/ca-cert.pem does not exist}
2019/09/26 15:48:54 [INFO] generating key: &{A:ecdsa S:256}
2019/09/26 15:48:54 [INFO] encoded CSR
2019/09/26 15:48:54 [INFO] signed certificate with serial number 
162595303982096068338873480987512684820342253664
2019/09/26 15:48:54 [INFO] The CA key and certificate were generated for
CA 
2019/09/26 15:48:54 [INFO] The key was stored by BCCSP provider 'SW'
2019/09/26 15:48:54 [INFO] The certificate is at: /home/marco/fabric
/fabric-ca/ca-cert.pem
2019/09/26 15:48:54 [INFO] Initialized sqlite3 database at /home/marco
/fabric/fabric-ca/fabric-ca-server.db
2019/09/26 15:48:54 [INFO] The issuer key was successfully stored. The 
public key is at: /home/marco/fabric/fabric-ca/IssuerPublicKey, secret 
key is at: /home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
2019/09/26 15:48:54 [INFO] Idemix issuer revocation public and secret 
keys were generated for CA ''
2019/09/26 15:48:54 [INFO] The revocation key was successfully stored. 
The public key is at: /home/marco/fabric/fabric-
ca/IssuerRevocationPublicKey, private key is at: /home/marco/fabric
/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
2019/09/26 15:48:54 [INFO] Home directory for default CA: /home/marco
/fabric/fabric-ca
2019/09/26 15:48:54 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 15:48:54 [INFO] Listening on http://0.0.0.0:7054

I set the brand-new fabric-ca-server-config.yaml in this way:

#db:
#  type: sqlite3
#  datasource: fabric-ca-server.db
#  tls:
#      enabled: false
#      certfiles:
#      client:
#        certfile:
#        keyfile:

db: type: postgres datasource: host=localhost port=5433 user=fabmnet_admin    password=password dbname=fabmnetdb sslmode=verify-full

and in /etc/postgresql/11/fabmnet/postgresql.conf :

ssl = on
ssl_cert_file = '/home/marco/fabric/fabric-ca/ca-cert.pem'
ssl_key_file = '/home/marco/fabric/fabric-ca/msp/keystore
/IssuerSecretKey'

After systemctl restart postgresql, I tried to start the fabric-ca-server:

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b 
admin:adminpw
2019/09/26 15:56:50 [INFO] Configuration file location: /home/marco
/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 15:56:50 [INFO] Starting server in home directory: 
/home/marco/fabric/fabric-ca
2019/09/26 15:56:50 [INFO] Server Version: 1.4.4
2019/09/26 15:56:50 [INFO] Server Levels: &{Identity:2 Affiliation:1 
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 15:56:50 [INFO] The CA key and certificate already exist
2019/09/26 15:56:50 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/26 15:56:50 [INFO] The certificate is at: /home/marco/fabric
/fabric-ca/ca-cert.pem
2019/09/26 15:56:50 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/26 15:56:50 [WARNING] Failed to connect to database 'postgres'
2019/09/26 15:56:50 [WARNING] Failed to connect to database 'template1'
2019/09/26 15:56:50 [ERROR] Error occurred initializing database: Failed
to connect to Postgres database. Postgres requires connecting to a 
specific database, the following databases were tried: [fabmnetdb 
postgres template1]. Please create one of these database before 
continuing
2019/09/26 15:56:50 [INFO] Home directory for default CA: /home/marco
/fabric/fabric-ca
2019/09/26 15:56:50 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 15:56:50 [INFO] Listening on http://0.0.0.0:7054

Before I also removed all the previous content of /var/log/postgresql/postgresql-11-fabmnet.log to have a clean situation. But strangely now I do not get any new logging information in postgresql-11-fabmnet.log

So. I think there must be something to fix in the interface between fabric-ca-server and PostgreSQL-11 db. In fabric-ca-server-config.yaml, in postgresql.conf, in both or somewhere else.

Il giorno gio 26 set 2019 alle ore 12:05 Marco Ippolito <ippolito.marco@gmail.com> ha scritto:

Affer removing the previous cert and key files, I started again the fabric-ca server discovering that new cert and key files were created:

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/26 11:56:18 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 11:56:18 [INFO] Starting server in home directory: /home/marco/fabric/fabric-ca
2019/09/26 11:56:18 [INFO] Server Version: 1.4.4
2019/09/26 11:56:18 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 11:56:18 [WARNING] &{69 The specified CA certificate file /home/marco/fabric/fabric-ca/ca-cert.pem does not exist}
2019/09/26 11:56:18 [INFO] generating key: &{A:ecdsa S:256}
2019/09/26 11:56:18 [INFO] encoded CSR
2019/09/26 11:56:18 [INFO] signed certificate with serial number 542755587310273579559145444277178107021548224556
2019/09/26 11:56:18 [INFO] The CA key and certificate were generated for CA
2019/09/26 11:56:18 [INFO] The key was stored by BCCSP provider 'SW'
2019/09/26 11:56:18 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'postgres'
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'template1'
2019/09/26 11:56:18 [ERROR] Error occurred initializing database: Failed to connect to Postgres database. Postgres requires connecting to a specific database, the following databases were tried: [fabmnetdb postgres template1]. Please create one of these database before continuing
2019/09/26 11:56:18 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
2019/09/26 11:56:18 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 11:56:18 [INFO] Listening on http://0.0.0.0:7054

but, again, the corresponding log says "bad certificate" :

2019-09-26 11:55:04.514 CEST [4837] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:55:04.517 CEST [4839] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:55:04.518 CEST [4840] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.967 CEST [4862] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.969 CEST [4865] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.971 CEST [4866] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate

So..how could it be "bad certificate" if it's just been created brand new by the execution of fabric-ca-server start?

Marco

Il giorno gio 26 set 2019 alle ore 00:43 Martin Gainty <mgainty@hotmail.com> ha scritto:
Hi Marco

not necessarily with PG but with all other servers i secure when i see that error
it means the certificate and key your provider is referencing are already stored in storage (in my case "truststore")
I would clean all storage locations of certificate and key
then I would allow BCCSP provider to push your cert and key into stores (identified by BCCSP config)

if that doesnt work I would disable hardcoded BCCSP Provider then manually import your certs and keys into your truststore

YMMV
martin
From: Marco Ippolito <ippolito.marco@gmail.com>
Sent: Wednesday, September 25, 2019 3:34 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: could not accept SSL connection: sslv3 alert bad certificate

Following the indications here: https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database I'm trying to understand how to correctly set Fabric-CA with a PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.

I created a postgresql-11 db to which I can connect with SSL:

(base) marco@pc:~$ psql --cluster 11/fabmnet -h 127.0.0.1 -d fabmnetdb -U fabmnet_admin
Password for user fabmnet_admin:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

fabmnetdb=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+---------------+----------+---------+---------+-----------------------
fabmnetdb | fabmnet_admin | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

fabmnetdb=>

but when trying to start a fabric-ca-server :

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw
2019/09/25 20:56:57 [INFO] Configuration file location: /home/marco/fabric
/fabric-ca/fabric-ca-server-config.yaml
2019/09/25 20:56:57 [INFO] Starting server in home directory: /home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Server Version: 1.4.4
2019/09/25 20:56:57 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/25 20:56:57 [INFO] The CA key and certificate already exist
2019/09/25 20:56:57 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/25 20:56:57 [INFO] The certificate is at: /home/marco/fabric
/fabric-ca/ca-cert.pem
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'postgres'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'template1'
2019/09/25 20:56:57 [ERROR] Error occurred initializing database: Failed
to connect to Postgres database. Postgres requires connecting to a
specific database, the following databases were tried: [fabmnetdb postgres
template1]. Please create one of these database before continuing
2019/09/25 20:56:57 [INFO] Home directory for default CA: /home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/25 20:56:57 [INFO] Listening on http://0.0.0.0:7054

This is the corresponding part in /var/log/postgresql/postgresql-11-fabmnet.log :

2019-09-25 20:51:52.655 CEST [1096] LOG: listening on IPv6 address "::1",
port 5433
2019-09-25 20:51:52.673 CEST [1096] LOG: listening on IPv4 address
"127.0.0.1", port 5433
2019-09-25 20:51:52.701 CEST [1096] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5433"
2019-09-25 20:51:52.912 CEST [1171] LOG: database system was interrupted;
last known up at 2019-09-25 09:50:30 CEST
2019-09-25 20:51:53.001 CEST [1171] LOG: database system was not properly
shut down; automatic recovery in progress
2019-09-25 20:51:53.011 CEST [1171] LOG: redo starts at 0/1668238
2019-09-25 20:51:53.011 CEST [1171] LOG: invalid record length at
0/1668318: wanted 24, got 0
2019-09-25 20:51:53.011 CEST [1171] LOG: redo done at 0/16682E0
2019-09-25 20:51:53.043 CEST [1096] LOG: database system is ready to
accept connections
2019-09-25 20:51:53.569 CEST [1206] [unknown]@[unknown] LOG: incomplete
startup packet
2019-09-25 20:56:57.540 CEST [4620] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.543 CEST [4622] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.544 CEST [4623] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate

This is how I set the pg_hba.conf file in the fabmnet postgresql cluster :

(base) marco@pc:~$ sudo -su postgres
(base) postgres@pc:~$ nano /etc/postgresql/11/fabmnet/pg_hba.conf
Unable to create directory /home/marco/.local/share/nano/: Permission denied
It is required for saving/loading search history or cursor positions.

Press Enter to continue

# TYPE DATABASE USER ADDRESS METHOD

# 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 10.1.2.0/24 subnet only to fabric_ca_db for fabric_ca_user
hostssl fabmnetdb fabmnet_admin 10.1.2.0/24 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

And this is the db's configuration in (base) marco@pc:~$ nano ./fabric/fabric-ca/fabric-ca-
server-config.yaml :

db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin password=pwd dbname=fabmnetdb
sslmode=verify-full

How to correctly set up SSL connection to PostgresSQL-11 db?

Looking forward to your kind help
Marco

Re: could not accept SSL connection: sslv3 alert bad certificate

From

Adrian Klaver

Date:

26 September 2019, 14:35:28

On 9/26/19 1:10 AM, Marco Ippolito wrote:
> Hi Adrian,
> putting in /fabric/fabric-ca/fabric-ca-server-config.yaml :
> 
> db:
>    type: postgres
>    datasource: host=localhost port=5433 user=fabmnet_admin 
> password=fabmnet1971 dbname=fabmnetdb sslmode=verify-ca
> 

> and the corresponding portion of the log file:
> 
> 2019-09-26 10:08:27.947 CEST [3728] fabmnet_admin@fabmnetdb ERROR: 
>   permission denied to create database
> 2019-09-26 10:08:27.947 CEST [3728] fabmnet_admin@fabmnetdb STATEMENT: 
>   CREATE DATABASE fabmnetdb

User fabmnet_admin does not have CREATE DATABASE privileges. In a psql 
session do:

\du fabmnet_admin

If the results do not include Create DB or Superuser then you need to 
ALTER ROLE fabmnet_admin to have CREATEDB:

https://www.postgresql.org/docs/11/sql-alterrole.html

NOTE: You will need to above as ROLE that has privileges. Easiest if you 
have a SUPERUSER role you can log in as.

> 
> I do not undertand...
> 
> Marco
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Adrian Klaver

Date:

26 September 2019, 14:50:24

On 9/26/19 7:21 AM, Marco Ippolito wrote:
> In order to restart from a clean situation and configuration, I removed 
> the previous fabric-ca folder, created a new one, and then initiated the 
> fabric-ca-server. With the default SQLite everything seem working fine. 
> But one I try to use the PostgreSQL-11 db I created before, errors appear:
> 
> |(base)marco@pc:~/fabric$rm -rf fabric-ca (base)marco@pc:~/fabric$mkdir 
> fabric-ca (base)marco@pc:~/fabric$cd 
> fabric-ca/(base)marco@pc:~/fabric/fabric-ca$fabric-ca-server init -b 
> admin:adminpw (base)marco@pc:~/fabric/fabric-ca$fabric-ca-server start-b 
> admin:adminpw 2019/09/2615:48:54[INFO]Created defaultconfiguration 
> fileat /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml 
> 2019/09/2615:48:54[INFO]Starting server inhome 
> directory:/home/marco/fabric/fabric-ca 2019/09/2615:48:54[INFO]Server 
> Version:1.4.42019/09/2615:48:54[INFO]Server 
> Levels:&{Identity:2Affiliation:1Certificate:1Credential:1RAInfo:1Nonce:1}2019/09/2615:48:54[WARNING]&{69The 
> specified CA certificate file/home/marco/fabric/fabric-ca/ca-cert.pem 
> does notexist}2019/09/2615:48:54[INFO]generating key:&{A:ecdsa 
> S:256}2019/09/2615:48:54[INFO]encoded CSR 2019/09/2615:48:54[INFO]signed 
> certificate withserial number 
> 1625953039820960683388734809875126848203422536642019/09/2615:48:54[INFO]The 
> CA keyandcertificate were generated forCA 2019/09/2615:48:54[INFO]The 
> keywas stored byBCCSP provider 'SW'2019/09/2615:48:54[INFO]The 
> certificate isat:/home/marco/fabric /fabric-ca/ca-cert.pem 
> 2019/09/2615:48:54[INFO]Initialized sqlite3 databaseat /home/marco 
> /fabric/fabric-ca/fabric-ca-server.db 2019/09/2615:48:54[INFO]The issuer 
> keywas successfully stored.The 
> publickeyisat:/home/marco/fabric/fabric-ca/IssuerPublicKey,secret 
> keyisat:/home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey 
> 2019/09/2615:48:54[INFO]Idemix issuer revocation publicandsecret keys 
> were generated forCA ''2019/09/2615:48:54[INFO]The revocation keywas 
> successfully stored.The publickeyisat:/home/marco/fabric/fabric- 
> ca/IssuerRevocationPublicKey,private keyisat:/home/marco/fabric 
> /fabric-ca/msp/keystore/IssuerRevocationPrivateKey 
> 2019/09/2615:48:54[INFO]Home directory fordefaultCA:/home/marco 
> /fabric/fabric-ca 2019/09/2615:48:54[INFO]Operation Server Listening 
> on127.0.0.1:94432019/09/2615:48:54[INFO]Listening onhttp://0.0.0.0:7054|
> 
> I set the brand-new fabric-ca-server-config.yaml in this way:
> 
> |#db:#type:sqlite3 #datasource:fabric-ca-server.db #tls:#enabled:false 
> #certfiles:#client:#certfile:#keyfile:db:type:postgres 
> datasource:host=localhost port=5433user=fabmnet_admin password=password 
> dbname=fabmnetdb sslmode=verify-full|

Shouldn't the TLS info also be there for the Postgres datasource:

https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#postgresql

As to below, you will not get logs as you are never connecting to the 
database. Those errors get sent to the console.

You are fighting two issues, role permissions and SSL certs. I would 
deal with one a time. Drop the SSL requirement until you can verify a 
connection and database creation. Then deal with the SSL issues.

> 
> and in /etc/postgresql/11/fabmnet/postgresql.conf :
> 
> |ssl =onssl_cert_file 
> ='/home/marco/fabric/fabric-ca/ca-cert.pem'ssl_key_file 
> ='/home/marco/fabric/fabric-ca/msp/keystore /IssuerSecretKey'|
> 
> After systemctl restart postgresql, I tried to start the fabric-ca-server:
> 
> |(base)marco@pc:~/fabric/fabric-ca$fabric-ca-server start-b 
> admin:adminpw 2019/09/2615:56:50[INFO]Configuration 
> filelocation:/home/marco /fabric/fabric-ca/fabric-ca-server-config.yaml 
> 2019/09/2615:56:50[INFO]Starting server inhome 
> directory:/home/marco/fabric/fabric-ca 2019/09/2615:56:50[INFO]Server 
> Version:1.4.42019/09/2615:56:50[INFO]Server 
> Levels:&{Identity:2Affiliation:1Certificate:1Credential:1RAInfo:1Nonce:1}2019/09/2615:56:50[INFO]The 
> CA keyandcertificate already exist 2019/09/2615:56:50[INFO]The 
> keyisstored byBCCSP provider 'SW'2019/09/2615:56:50[INFO]The certificate 
> isat:/home/marco/fabric /fabric-ca/ca-cert.pem 
> 2019/09/2615:56:50[WARNING]Failed 
> toconnecttodatabase'fabmnetdb'2019/09/2615:56:50[WARNING]Failed 
> toconnecttodatabase'postgres'2019/09/2615:56:50[WARNING]Failed 
> toconnecttodatabase'template1'2019/09/2615:56:50[ERROR]Error occurred 
> initializing database:Failed toconnecttoPostgres database.Postgres 
> requires connecting toa specific database,the followingdatabases were 
> tried:[fabmnetdb postgres template1].Please createone ofthese 
> databasebefore continuing 2019/09/2615:56:50[INFO]Home directory 
> fordefaultCA:/home/marco /fabric/fabric-ca 
> 2019/09/2615:56:50[INFO]Operation Server Listening 
> on127.0.0.1:94432019/09/2615:56:50[INFO]Listening onhttp://0.0.0.0:7054|
> 
> Before I also removed all the previous content of 
> /var/log/postgresql/postgresql-11-fabmnet.log to have a clean situation. 
> But strangely now I do not get any new logging information in 
> postgresql-11-fabmnet.log
> 
> So. I think there must be something to fix in the interface between 
> fabric-ca-server and PostgreSQL-11 db. In fabric-ca-server-config.yaml, 
> in postgresql.conf, in both or somewhere else.
> 
> 
> 
> 
> 
> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Marco Ippolito

Date:

26 September 2019, 17:10:40

Hi Adrian,

I removed the previous fabmentdb and created a new one whose owner is postgres:

First objective: enable and make it working ssl connection for fabmnetdb :

Following the indications found here: https://vibhork.blogspot.com/2011/07/how-to-enable-ssl-in-postgresqlppas.html

I created .pem and .req files in /var/lib/postgresql/11/fabmnet/ where, according to nano /etc/postgresql/11/fabmnet/postgresql.conf, the data folder is located:

data_directory = '/var/lib/postgresql/11/fabmnet'

ssl = on

(base) postgres@pc:/var/lib/postgresql/11/fabmnet$ chmod 600 privkey.pem
(base) postgres@pc:/var/lib/postgresql/11/fabmnet$ chmod 600 cert.req

But now, testing the ssl connection :

(base) marco@pc:/usr/local/pgsql$ psql -h 127.0.0.1 -d fabmnetdb -U postgres
Password for user postgres:
psql: FATAL: database "fabmnetdb" does not exist

What am I missing?

Thanks again for your kind help.

Marco

Il giorno gio 26 set 2019 alle ore 16:50 Adrian Klaver <adrian.klaver@aklaver.com> ha scritto:

On 9/26/19 7:21 AM, Marco Ippolito wrote:
> In order to restart from a clean situation and configuration, I removed
> the previous fabric-ca folder, created a new one, and then initiated the
> fabric-ca-server. With the default SQLite everything seem working fine.
> But one I try to use the PostgreSQL-11 db I created before, errors appear:
>
> |(base)marco@pc:~/fabric$rm -rf fabric-ca (base)marco@pc:~/fabric$mkdir
> fabric-ca (base)marco@pc:~/fabric$cd
> fabric-ca/(base)marco@pc:~/fabric/fabric-ca$fabric-ca-server init -b
> admin:adminpw (base)marco@pc:~/fabric/fabric-ca$fabric-ca-server start-b
> admin:adminpw 2019/09/2615:48:54[INFO]Created defaultconfiguration
> fileat /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/2615:48:54[INFO]Starting server inhome
> directory:/home/marco/fabric/fabric-ca 2019/09/2615:48:54[INFO]Server
> Version:1.4.42019/09/2615:48:54[INFO]Server
> Levels:&{Identity:2Affiliation:1Certificate:1Credential:1RAInfo:1Nonce:1}2019/09/2615:48:54[WARNING]&{69The
> specified CA certificate file/home/marco/fabric/fabric-ca/ca-cert.pem
> does notexist}2019/09/2615:48:54[INFO]generating key:&{A:ecdsa
> S:256}2019/09/2615:48:54[INFO]encoded CSR 2019/09/2615:48:54[INFO]signed
> certificate withserial number
> 1625953039820960683388734809875126848203422536642019/09/2615:48:54[INFO]The
> CA keyandcertificate were generated forCA 2019/09/2615:48:54[INFO]The
> keywas stored byBCCSP provider 'SW'2019/09/2615:48:54[INFO]The
> certificate isat:/home/marco/fabric /fabric-ca/ca-cert.pem
> 2019/09/2615:48:54[INFO]Initialized sqlite3 databaseat /home/marco
> /fabric/fabric-ca/fabric-ca-server.db 2019/09/2615:48:54[INFO]The issuer
> keywas successfully stored.The
> publickeyisat:/home/marco/fabric/fabric-ca/IssuerPublicKey,secret
> keyisat:/home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
> 2019/09/2615:48:54[INFO]Idemix issuer revocation publicandsecret keys
> were generated forCA ''2019/09/2615:48:54[INFO]The revocation keywas
> successfully stored.The publickeyisat:/home/marco/fabric/fabric-
> ca/IssuerRevocationPublicKey,private keyisat:/home/marco/fabric
> /fabric-ca/msp/keystore/IssuerRevocationPrivateKey
> 2019/09/2615:48:54[INFO]Home directory fordefaultCA:/home/marco
> /fabric/fabric-ca 2019/09/2615:48:54[INFO]Operation Server Listening
> on127.0.0.1:94432019/09/2615:48:54[INFO]Listening onhttp://0.0.0.0:7054|
>
> I set the brand-new fabric-ca-server-config.yaml in this way:
>
> |#db:#type:sqlite3 #datasource:fabric-ca-server.db #tls:#enabled:false
> #certfiles:#client:#certfile:#keyfile:db:type:postgres
> datasource:host=localhost port=5433user=fabmnet_admin password=password
> dbname=fabmnetdb sslmode=verify-full|

Shouldn't the TLS info also be there for the Postgres datasource:

https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#postgresql

As to below, you will not get logs as you are never connecting to the
database. Those errors get sent to the console.

You are fighting two issues, role permissions and SSL certs. I would
deal with one a time. Drop the SSL requirement until you can verify a
connection and database creation. Then deal with the SSL issues.

>
> and in /etc/postgresql/11/fabmnet/postgresql.conf :
>
> |ssl =onssl_cert_file
> ='/home/marco/fabric/fabric-ca/ca-cert.pem'ssl_key_file
> ='/home/marco/fabric/fabric-ca/msp/keystore /IssuerSecretKey'|
>
> After systemctl restart postgresql, I tried to start the fabric-ca-server:
>
> |(base)marco@pc:~/fabric/fabric-ca$fabric-ca-server start-b
> admin:adminpw 2019/09/2615:56:50[INFO]Configuration
> filelocation:/home/marco /fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/2615:56:50[INFO]Starting server inhome
> directory:/home/marco/fabric/fabric-ca 2019/09/2615:56:50[INFO]Server
> Version:1.4.42019/09/2615:56:50[INFO]Server
> Levels:&{Identity:2Affiliation:1Certificate:1Credential:1RAInfo:1Nonce:1}2019/09/2615:56:50[INFO]The
> CA keyandcertificate already exist 2019/09/2615:56:50[INFO]The
> keyisstored byBCCSP provider 'SW'2019/09/2615:56:50[INFO]The certificate
> isat:/home/marco/fabric /fabric-ca/ca-cert.pem
> 2019/09/2615:56:50[WARNING]Failed
> toconnecttodatabase'fabmnetdb'2019/09/2615:56:50[WARNING]Failed
> toconnecttodatabase'postgres'2019/09/2615:56:50[WARNING]Failed
> toconnecttodatabase'template1'2019/09/2615:56:50[ERROR]Error occurred
> initializing database:Failed toconnecttoPostgres database.Postgres
> requires connecting toa specific database,the followingdatabases were
> tried:[fabmnetdb postgres template1].Please createone ofthese
> databasebefore continuing 2019/09/2615:56:50[INFO]Home directory
> fordefaultCA:/home/marco /fabric/fabric-ca
> 2019/09/2615:56:50[INFO]Operation Server Listening
> on127.0.0.1:94432019/09/2615:56:50[INFO]Listening onhttp://0.0.0.0:7054|
>
> Before I also removed all the previous content of
> /var/log/postgresql/postgresql-11-fabmnet.log to have a clean situation.
> But strangely now I do not get any new logging information in
> postgresql-11-fabmnet.log
>
> So. I think there must be something to fix in the interface between
> fabric-ca-server and PostgreSQL-11 db. In fabric-ca-server-config.yaml,
> in postgresql.conf, in both or somewhere else.
>
>
>
>
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Adrian Klaver

Date:

26 September 2019, 18:53:49

On 9/26/19 10:10 AM, Marco Ippolito wrote:
> Hi Adrian,
> 
> I removed the previous fabmentdb and created a new one whose owner is 
> postgres:
> 
> (base) postgres@pc:/usr/local/pgsql$ psql --cluster 11/fabmnet
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> Type "help" for help.
> 
> postgres=# \l
>                                List of databases
>     Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
> -----------+----------+----------+---------+---------+-----------------------
>   fabmnetdb | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
>   postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
>   template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres     
>       +
>             |          |          |         |         | 
> postgres=CTc/postgres
>   template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres     
>       +
>             |          |          |         |         | 
> postgres=CTc/postgres
> (4 rows)
> 
> postgres=#
> 
> _First objective: enable and make it working ssl connection for fabmnetd_b :
> 
> Following the indications found here: 
> https://vibhork.blogspot.com/2011/07/how-to-enable-ssl-in-postgresqlppas.html
> I created .pem and .req files in /var/lib/postgresql/11/fabmnet/  where, 
> according to nano /etc/postgresql/11/fabmnet/postgresql.conf, the data 
> folder is located:
>      data_directory = '/var/lib/postgresql/11/fabmnet'
>      ssl = on
> 
> 
> (base) postgres@pc:/var/lib/postgresql/11/fabmnet$ chmod 600 privkey.pem
> (base) postgres@pc:/var/lib/postgresql/11/fabmnet$ chmod 600 cert.req
> 
> But now, testing the ssl connection :
> 
> (base) marco@pc:/usr/local/pgsql$ psql -h 127.0.0.1 -d fabmnetdb -U postgres
> Password for user postgres:
> psql: FATAL:  database "fabmnetdb" does not exist
> 
> What am I missing?

My suspicion is that you have more then once instance of Postgres 
running. Partly because of this:

psql --cluster 11/fabmnet

and then later:

psql -h 127.0.0.1 -d fabmnetdb -U postgres

Not sure they are pointing at the same thing.



At command line what does:

ps ax | grep post

show.


> Thanks again for your kind help.
> Marco
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

rob stone

Date:

27 September 2019, 02:28:31

Hi,

On Thu, 2019-09-26 at 16:21 +0200, Marco Ippolito wrote:
> 
> db:
>   type: postgres
>   datasource: host=localhost port=5433 user=fabmnet_admin   
>   password=password dbname=fabmnetdb sslmode=verify-full
> 

> 
> (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b 
> admin:adminpw
> 2019/09/26 15:56:50 [INFO] Configuration file location: /home/marco
> /fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/26 15:56:50 [INFO] Starting server in home directory: 
> /home/marco/fabric/fabric-ca
> 2019/09/26 15:56:50 [INFO] Server Version: 1.4.4
> 2019/09/26 15:56:50 [INFO] Server Levels: &{Identity:2 Affiliation:1 
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/26 15:56:50 [INFO] The CA key and certificate already exist
> 2019/09/26 15:56:50 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/26 15:56:50 [INFO] The certificate is at: /home/marco/fabric
> /fabric-ca/ca-cert.pem
> 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> 'fabmnetdb'
> 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> 'postgres'
> 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> 'template1'
> 2019/09/26 15:56:50 [ERROR] Error occurred initializing database:
> Failed
> to connect to Postgres database. Postgres requires connecting to a 
> specific database, the following databases were tried: [fabmnetdb 
> postgres template1]. Please create one of these database before 
> continuing


Why is it trying to connect to *any* database?

In the fabric-ca docs it shows the connection string as a single line
but your configuration file has it split over two lines.
My uneducated guess is that it is ignoring the 'password=password
dbname=fabmnetdb sslmode=verify-full'
line and thus unable to connect to fabmnetdb.

Cheers,
Robert

Re: "Failed to connect to Postgres database"

From

Marco Ippolito

Date:

27 September 2019, 10:37:23

I need to understand this:

I dropped the previous fabmnet cluster and re-created a new one:

(base) postgres@pc:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
11 fabmnet 5433 online postgres /var/lib/postgresql/11/fabmnet /var/log/postgresql/postgresql-11-fabmnet.log
11 main 5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

I can connect with SSL to cluster 11/main:

(base) postgres@pc:/var/lib/postgresql/11/main$ ls -lah
total 92K
drwx------ 19 postgres postgres 4.0K Sep 27 09:38 .
drwxr-xr-x 4 postgres postgres 4.0K Sep 27 10:58 ..
-rw------- 1 postgres postgres 3 Sep 18 16:22 PG_VERSION
drwx------ 6 postgres postgres 4.0K Sep 27 11:57 base
drwx------ 2 postgres postgres 4.0K Sep 27 09:39 global
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_commit_ts
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_dynshmem
drwx------ 4 postgres postgres 4.0K Sep 27 11:57 pg_logical
drwx------ 4 postgres postgres 4.0K Sep 18 16:22 pg_multixact
drwx------ 2 postgres postgres 4.0K Sep 27 09:38 pg_notify
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_replslot
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_serial
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_snapshots
drwx------ 2 postgres postgres 4.0K Sep 27 09:38 pg_stat
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_stat_tmp
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_subtrans
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_tblspc
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_twophase
drwx------ 3 postgres postgres 4.0K Sep 18 16:22 pg_wal
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_xact
-rw------- 1 postgres postgres 88 Sep 18 16:22 postgresql.auto.conf
-rw------- 1 postgres postgres 130 Sep 27 09:38 postmaster.opts
-rw------- 1 postgres postgres 108 Sep 27 09:38 postmaster.pid

(base) marco@pc:~$ ps ax | grep post
1030 ? S 0:00 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
1107 ? Ss 0:00 postgres: 11/main: checkpointer
1108 ? Ss 0:00 postgres: 11/main: background writer
1109 ? Ss 0:00 postgres: 11/main: walwriter
1110 ? Ss 0:00 postgres: 11/main: autovacuum launcher
1111 ? Ss 0:00 postgres: 11/main: stats collector
1112 ? Ss 0:00 postgres: 11/main: logical replication launcher
5003 ? S 0:00 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/fabmnet -c config_file=/etc/postgresql/11/fabmnet/postgresql.conf
5005 ? Ss 0:00 postgres: 11/fabmnet: checkpointer
5006 ? Ss 0:00 postgres: 11/fabmnet: background writer
5007 ? Ss 0:00 postgres: 11/fabmnet: walwriter
5008 ? Ss 0:00 postgres: 11/fabmnet: autovacuum launcher
5009 ? Ss 0:00 postgres: 11/fabmnet: stats collector
5010 ? Ss 0:00 postgres: 11/fabmnet: logical replication launcher
6543 pts/1 S 0:00 sudo -su postgres
6867 pts/1 S+ 0:00 /usr/lib/postgresql/11/bin/psql -h localhost
6878 ? Ss 0:00 postgres: 11/main: postgres postgres ::1(49156) idle
6883 pts/0 S 0:00 sudo -su postgres
8167 pts/2 S+ 0:00 grep --color=auto post

Bu I cannot do the same with cluster 11/fabmnet :

(base) postgres@pc:/var/lib/postgresql/11/fabmnet$ ls -lah
total 104K
drwx------ 19 postgres postgres 4.0K Sep 27 11:28 .
drwxr-xr-x 4 postgres postgres 4.0K Sep 27 10:58 ..
-rw------- 1 postgres postgres 3 Sep 27 10:58 PG_VERSION
drwx------ 6 postgres postgres 4.0K Sep 27 11:40 base
drwx------ 2 postgres postgres 4.0K Sep 27 11:13 global
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_commit_ts
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_dynshmem
drwx------ 4 postgres postgres 4.0K Sep 27 11:45 pg_logical
drwx------ 4 postgres postgres 4.0K Sep 27 10:58 pg_multixact
drwx------ 2 postgres postgres 4.0K Sep 27 10:59 pg_notify
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_replslot
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_serial
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_snapshots
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_stat
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_stat_tmp
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_subtrans
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_tblspc
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_twophase
drwx------ 3 postgres postgres 4.0K Sep 27 10:58 pg_wal
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_xact
-rw------- 1 postgres postgres 88 Sep 27 10:58 postgresql.auto.conf
-rw------- 1 postgres postgres 136 Sep 27 10:59 postmaster.opts
-rw------- 1 postgres postgres 111 Sep 27 10:59 postmaster.pid
-rw------- 1 root root 4.4K Sep 27 11:26 server.crt
-rw------- 1 root root 1.7K Sep 27 11:26 server.key

I created the server.crt and server.key files following the indications found here: https://vibhork.blogspot.com/2011/07/how-to-enable-ssl-in-postgresqlppas.html

(base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost
Password for user postgres:
psql: FATAL: password authentication failed for user "postgres"
FATAL: password authentication failed for user "postgres"

So, before passing to the fabric-ca side , I need to solve this aspect.

Marco

Il giorno ven 27 set 2019 alle ore 04:28 rob stone <floriparob@gmail.com> ha scritto:

Hi,

On Thu, 2019-09-26 at 16:21 +0200, Marco Ippolito wrote:
>
> db:
> type: postgres
> datasource: host=localhost port=5433 user=fabmnet_admin
> password=password dbname=fabmnetdb sslmode=verify-full
>

>
> (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
> admin:adminpw
> 2019/09/26 15:56:50 [INFO] Configuration file location: /home/marco
> /fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/26 15:56:50 [INFO] Starting server in home directory:
> /home/marco/fabric/fabric-ca
> 2019/09/26 15:56:50 [INFO] Server Version: 1.4.4
> 2019/09/26 15:56:50 [INFO] Server Levels: &{Identity:2 Affiliation:1
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/26 15:56:50 [INFO] The CA key and certificate already exist
> 2019/09/26 15:56:50 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/26 15:56:50 [INFO] The certificate is at: /home/marco/fabric
> /fabric-ca/ca-cert.pem
> 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> 'fabmnetdb'
> 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> 'postgres'
> 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> 'template1'
> 2019/09/26 15:56:50 [ERROR] Error occurred initializing database:
> Failed
> to connect to Postgres database. Postgres requires connecting to a
> specific database, the following databases were tried: [fabmnetdb
> postgres template1]. Please create one of these database before
> continuing

Why is it trying to connect to *any* database?

In the fabric-ca docs it shows the connection string as a single line
but your configuration file has it split over two lines.
My uneducated guess is that it is ignoring the 'password=password
dbname=fabmnetdb sslmode=verify-full'
line and thus unable to connect to fabmnetdb.

Cheers,
Robert

Re: "Failed to connect to Postgres database"

From

"Daniel Verite"

Date:

27 September 2019, 11:34:30

    Marco Ippolito wrote:

> (base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost
> Password for user postgres:
> psql: FATAL:  password authentication failed for user "postgres"
> FATAL:  password authentication failed for user "postgres"

Did you set a password for the postgres user in that newly created
cluster?
If not, try psql --cluster 11/fabmnet (without -h localhost),
it should connect you without a password,
then set a password with the \password command in psql,
then try again with -h localhost.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: "Failed to connect to Postgres database"

From

Marco Ippolito

Date:

27 September 2019, 12:58:08

Thanks Daniel.

After adding the password, now ssh connection to the cluster fabmnet works:

And may be the fact the it's compulsory to add a password is testified also by the fact that changing the ownership of the database while adding a password, lets connect with ssh to the database:

Now I have to fix the interface between fabric-ca and postgresql-11 on both sides. And I will let you know how it is going

Marco

Il giorno ven 27 set 2019 alle ore 13:34 Daniel Verite <daniel@manitou-mail.org> ha scritto:

Marco Ippolito wrote:

> (base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost
> Password for user postgres:
> psql: FATAL: password authentication failed for user "postgres"
> FATAL: password authentication failed for user "postgres"

Did you set a password for the postgres user in that newly created
cluster?
If not, try psql --cluster 11/fabmnet (without -h localhost),
it should connect you without a password,
then set a password with the \password command in psql,
then try again with -h localhost.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: "Failed to connect to Postgres database"

From

Adrian Klaver

Date:

27 September 2019, 14:38:07

On 9/27/19 5:58 AM, Marco Ippolito wrote:
> Thanks Daniel.
> After adding the password, now ssh connection to the cluster fabmnet works:

You might want to take a look at:

https://help.ubuntu.com/lts/serverguide/postgresql.html


> And may be the fact the it's compulsory to add a password is testified 
> also by the fact that changing the ownership of the database while 
> adding a password, lets connect with ssh to the database:

First it is SSL.
Second password and SSL are two different things. This is covered in the 
auth file pg_hba.conf:

https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

What you are seeing below is dependent on whether you connect using a 
host(-h localhost) or a socket(no -h). That behavior is in turn 
determined by the settings in pg_hba.conf.

Also to help down the road when you are setting up the fabric-ca server 
you need to remember you are now running two Postgres servers:

Ver Cluster Port Status Owner    Data directory                 Log file
11  fabmnet 5433 online postgres /var/lib/postgresql/11/fabmnet 
/var/log/postgresql/postgresql-11-fabmnet.log
11  main    5432 online postgres /var/lib/postgresql/11/main 
/var/log/postgresql/postgresql-11-main.log

The most important part is that the fabric server needs to connect to 
the one using port 5433.  FYI, this also means that it is not necesssary 
to use the --cluster option to psql. Just set the appropriate port -p 
5432 for maon and -p 5433 for fabmnet.

More below.
> 
> postgres=# CREATE USER fabmnet_admin;
> CREATE ROLE
> postgres=# ALTER USER fabmnet_admin WITH PASSWORD 'A';
> ALTER ROLE
> 
> postgres=# ALTER DATABASE fabmnet_ca OWNER TO fabmnet_admin;
> ALTER DATABASE
> postgres=# \l
>                                   List of databases
>      Name    |     Owner     | Encoding | Collate |  Ctype  |   Access 
> privileges
> ------------+---------------+----------+---------+---------+-----------------------
>   fabmnet_ca | fabmnet_admin | UTF8     | C.UTF-8 | C.UTF-8 |
>   postgres   | postgres      | UTF8     | C.UTF-8 | C.UTF-8 |
>   template0  | postgres      | UTF8     | C.UTF-8 | C.UTF-8 | 
> =c/postgres          +
>              |               |          |         |         | 
> postgres=CTc/postgres
>   template1  | postgres      | UTF8     | C.UTF-8 | C.UTF-8 | 
> =c/postgres          +
>              |               |          |         |         | 
> postgres=CTc/postgres
> (4 rows)
> 
> (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet
> Password for user postgres:
> psql: FATAL:  password authentication failed for user "postgres"
> FATAL:  password authentication failed for user "postgres"

This failed because you did not specify a database or username, so by 
default psql used the system user(postgres) as the database name and the 
user name. I'm guesing you do not have a password set up for the 
postgres user yet. Pretty sure if you left off the -h localhost you 
would have connected as Ubuntu sets up trust authentication for postgres 
user on local socket.

> (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet -d 
> fabmnet_ca -U fabmnet_admin
> Password for user fabmnet_admin:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 
> 256, compression: off)
> Type "help" for help.
> fabmnet_ca=>
> 
> 
> Now I have to fix the interface between fabric-ca and postgresql-11 on 
> both sides. And I will let you know how it is going
> 
> Marco
> 
> Il giorno ven 27 set 2019 alle ore 13:34 Daniel Verite 
> <daniel@manitou-mail.org <mailto:daniel@manitou-mail.org>> ha scritto:
> 
>              Marco Ippolito wrote:
> 
>      > (base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost
>      > Password for user postgres:
>      > psql: FATAL:  password authentication failed for user "postgres"
>      > FATAL:  password authentication failed for user "postgres"
> 
>     Did you set a password for the postgres user in that newly created
>     cluster?
>     If not, try psql --cluster 11/fabmnet (without -h localhost),
>     it should connect you without a password,
>     then set a password with the \password command in psql,
>     then try again with -h localhost.
> 
> 
>     Best regards,
>     -- 
>     Daniel Vérité
>     PostgreSQL-powered mailer: http://www.manitou-mail.org
>     Twitter: @DanielVerite
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Marco Ippolito

Date:

27 September 2019, 15:10:52

Hi Adrian,

thanks to your kind explanation I discovered that I can connect to the db without explicitly calling the belonging cluster:

Now I have to understand how to "tranfer" this ssh-capability to connect with the fabmnet_ca db of cluster fabmnet without explicitly call the cluster to the fabric-ca-server :

This is what I set in fabric-ca-server-config.yaml :

#db:
# type: sqlite3
# datasource: fabric-ca-server.db
# tls:
# enabled: false
# certfiles:
# client:
# certfile:
# keyfile:

db:
type: postgres
datasource: host=localhost port=5433 user=postgres password=pwd dbname=fabmnet_ca sslmode=verify-full
tls:
enabled: false
certfiles:
client:
certfile:
keyfile:

Initializing the fabric-ca-server gives "Failed to connect to Postgres database" and in postgresql-11-fabmnet.log : sslv3 alert bad certificate

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

/var/log/postgresql/postgresql-11-fabmnet.log : 2019-09-27 17:07:27.159 CEST [6626] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate

Why it says "sslv3 alert bad certificate" if it's exactly the same certificate used when connecting to the same database with ssl in postgres environment as shown above?

Marco

Il giorno ven 27 set 2019 alle ore 16:38 Adrian Klaver <adrian.klaver@aklaver.com> ha scritto:

On 9/27/19 5:58 AM, Marco Ippolito wrote:
> Thanks Daniel.
> After adding the password, now ssh connection to the cluster fabmnet works:

You might want to take a look at:

https://help.ubuntu.com/lts/serverguide/postgresql.html

> And may be the fact the it's compulsory to add a password is testified
> also by the fact that changing the ownership of the database while
> adding a password, lets connect with ssh to the database:

First it is SSL.
Second password and SSL are two different things. This is covered in the
auth file pg_hba.conf:

https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

What you are seeing below is dependent on whether you connect using a
host(-h localhost) or a socket(no -h). That behavior is in turn
determined by the settings in pg_hba.conf.

Also to help down the road when you are setting up the fabric-ca server
you need to remember you are now running two Postgres servers:

Ver Cluster Port Status Owner Data directory Log file
11 fabmnet 5433 online postgres /var/lib/postgresql/11/fabmnet
/var/log/postgresql/postgresql-11-fabmnet.log
11 main 5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log

The most important part is that the fabric server needs to connect to
the one using port 5433. FYI, this also means that it is not necesssary
to use the --cluster option to psql. Just set the appropriate port -p
5432 for maon and -p 5433 for fabmnet.

More below.
>
> postgres=# CREATE USER fabmnet_admin;
> CREATE ROLE
> postgres=# ALTER USER fabmnet_admin WITH PASSWORD 'A';
> ALTER ROLE
>
> postgres=# ALTER DATABASE fabmnet_ca OWNER TO fabmnet_admin;
> ALTER DATABASE
> postgres=# \l
> List of databases
> Name | Owner | Encoding | Collate | Ctype | Access
> privileges
> ------------+---------------+----------+---------+---------+-----------------------
> fabmnet_ca | fabmnet_admin | UTF8 | C.UTF-8 | C.UTF-8 |
> postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> (4 rows)
>
> (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet
> Password for user postgres:
> psql: FATAL: password authentication failed for user "postgres"
> FATAL: password authentication failed for user "postgres"

This failed because you did not specify a database or username, so by
default psql used the system user(postgres) as the database name and the
user name. I'm guesing you do not have a password set up for the
postgres user yet. Pretty sure if you left off the -h localhost you
would have connected as Ubuntu sets up trust authentication for postgres
user on local socket.

> (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet -d
> fabmnet_ca -U fabmnet_admin
> Password for user fabmnet_admin:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)
> Type "help" for help.
> fabmnet_ca=>
>
>
> Now I have to fix the interface between fabric-ca and postgresql-11 on
> both sides. And I will let you know how it is going
>
> Marco
>
> Il giorno ven 27 set 2019 alle ore 13:34 Daniel Verite
> <daniel@manitou-mail.org <mailto:daniel@manitou-mail.org>> ha scritto:
>
> Marco Ippolito wrote:
>
> > (base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost
> > Password for user postgres:
> > psql: FATAL: password authentication failed for user "postgres"
> > FATAL: password authentication failed for user "postgres"
>
> Did you set a password for the postgres user in that newly created
> cluster?
> If not, try psql --cluster 11/fabmnet (without -h localhost),
> it should connect you without a password,
> then set a password with the \password command in psql,
> then try again with -h localhost.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Marco Ippolito

Date:

27 September 2019, 15:20:55

Correction of my previous email :

This is the correct ssl connection, not the one before via socket:

Anyway, I'm still struggling in understanding how to configure the ssh connection of fabric-ca-server to fabmnet_ca database:

This is what I set in fabric-ca-server-config.yaml :

Initializing the fabric-ca-server gives "Failed to connect to Postgres database" and in postgresql-11-fabmnet.log : sslv3 alert bad certificate

/var/log/postgresql/postgresql-11-fabmnet.log : 2019-09-27 17:07:27.159 CEST [6626] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate

Why it says "sslv3 alert bad certificate" if it's exactly the same certificate used when connecting to the same database with ssl in postgres environment as shown above?

Marco

Il giorno ven 27 set 2019 alle ore 17:10 Marco Ippolito <ippolito.marco@gmail.com> ha scritto:

Hi Adrian,

thanks to your kind explanation I discovered that I can connect to the db without explicitly calling the belonging cluster:

(base) postgres@pc:~$ psql -p5433 -d fabmnet_ca
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

fabmnet_ca=# \conninfo
You are connected to database "fabmnet_ca" as user "postgres" via socket in "/var/run/postgresql" at port "5433".
fabmnet_ca=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+----------+----------+---------+---------+-----------------------
fabmnet_ca | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

Now I have to understand how to "tranfer" this ssh-capability to connect with the fabmnet_ca db of cluster fabmnet without explicitly call the cluster to the fabric-ca-server :

This is what I set in fabric-ca-server-config.yaml :

#db:
# type: sqlite3
# datasource: fabric-ca-server.db
# tls:
# enabled: false
# certfiles:
# client:
# certfile:
# keyfile:

db:
type: postgres
datasource: host=localhost port=5433 user=postgres password=pwd dbname=fabmnet_ca sslmode=verify-full
tls:
enabled: false
certfiles:
client:
certfile:
keyfile:

Initializing the fabric-ca-server gives "Failed to connect to Postgres database" and in postgresql-11-fabmnet.log : sslv3 alert bad certificate

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

/var/log/postgresql/postgresql-11-fabmnet.log : 2019-09-27 17:07:27.159 CEST [6626] [unknown]@[unknown] LOG: could not accept SSL connection: sslv3 alert bad certificate

Why it says "sslv3 alert bad certificate" if it's exactly the same certificate used when connecting to the same database with ssl in postgres environment as shown above?

Marco

Il giorno ven 27 set 2019 alle ore 16:38 Adrian Klaver <adrian.klaver@aklaver.com> ha scritto:
On 9/27/19 5:58 AM, Marco Ippolito wrote:
> Thanks Daniel.
> After adding the password, now ssh connection to the cluster fabmnet works:

You might want to take a look at:

https://help.ubuntu.com/lts/serverguide/postgresql.html

> And may be the fact the it's compulsory to add a password is testified
> also by the fact that changing the ownership of the database while
> adding a password, lets connect with ssh to the database:

First it is SSL.
Second password and SSL are two different things. This is covered in the
auth file pg_hba.conf:

https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

What you are seeing below is dependent on whether you connect using a
host(-h localhost) or a socket(no -h). That behavior is in turn
determined by the settings in pg_hba.conf.

Also to help down the road when you are setting up the fabric-ca server
you need to remember you are now running two Postgres servers:

Ver Cluster Port Status Owner Data directory Log file
11 fabmnet 5433 online postgres /var/lib/postgresql/11/fabmnet
/var/log/postgresql/postgresql-11-fabmnet.log
11 main 5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log

The most important part is that the fabric server needs to connect to
the one using port 5433. FYI, this also means that it is not necesssary
to use the --cluster option to psql. Just set the appropriate port -p
5432 for maon and -p 5433 for fabmnet.

More below.
>
> postgres=# CREATE USER fabmnet_admin;
> CREATE ROLE
> postgres=# ALTER USER fabmnet_admin WITH PASSWORD 'A';
> ALTER ROLE
>
> postgres=# ALTER DATABASE fabmnet_ca OWNER TO fabmnet_admin;
> ALTER DATABASE
> postgres=# \l
> List of databases
> Name | Owner | Encoding | Collate | Ctype | Access
> privileges
> ------------+---------------+----------+---------+---------+-----------------------
> fabmnet_ca | fabmnet_admin | UTF8 | C.UTF-8 | C.UTF-8 |
> postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> (4 rows)
>
> (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet
> Password for user postgres:
> psql: FATAL: password authentication failed for user "postgres"
> FATAL: password authentication failed for user "postgres"

This failed because you did not specify a database or username, so by
default psql used the system user(postgres) as the database name and the
user name. I'm guesing you do not have a password set up for the
postgres user yet. Pretty sure if you left off the -h localhost you
would have connected as Ubuntu sets up trust authentication for postgres
user on local socket.

> (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet -d
> fabmnet_ca -U fabmnet_admin
> Password for user fabmnet_admin:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)
> Type "help" for help.
> fabmnet_ca=>
>
>
> Now I have to fix the interface between fabric-ca and postgresql-11 on
> both sides. And I will let you know how it is going
>
> Marco
>
> Il giorno ven 27 set 2019 alle ore 13:34 Daniel Verite
> <daniel@manitou-mail.org <mailto:daniel@manitou-mail.org>> ha scritto:
>
> Marco Ippolito wrote:
>
> > (base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost
> > Password for user postgres:
> > psql: FATAL: password authentication failed for user "postgres"
> > FATAL: password authentication failed for user "postgres"
>
> Did you set a password for the postgres user in that newly created
> cluster?
> If not, try psql --cluster 11/fabmnet (without -h localhost),
> it should connect you without a password,
> then set a password with the \password command in psql,
> then try again with -h localhost.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Adrian Klaver

Date:

27 September 2019, 16:37:23

On 9/27/19 8:20 AM, Marco Ippolito wrote:
> Correction of my previous email :
> 
> This is the correct ssl connection, not the one before via socket:

A tip, when troubleshooting be as explicit as possible in your command 
line usage. So for below explicitly state the -d postgres -U postgres. 
This will save you issues with default values and environment values 
that you don't know about changing the command. This is not the issue 
here, just a heads up for future use.

More below.

> 
> (base) postgres@pc:~$ psql -p5433 -h localhost
> Password for user postgres:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 
> 256, compression: off)

> fabmnet_ca=#
> 
> Anyway, I'm still struggling in understanding how to configure the ssh 
> connection of fabric-ca-server to fabmnet_ca database:
> 
> This is what I set in fabric-ca-server-config.yaml :
> 
> #db:
> #  type: sqlite3
> #  datasource: fabric-ca-server.db
> #  tls:
> #      enabled: false
> #      certfiles:
> #      client:
> #        certfile:
> #        keyfile:
> 
> 
> db:
>    type: postgres
>    datasource: host=localhost port=5433 user=postgres password=pwd 
> dbname=fabmnet_ca sslmode=verify-full

For now I would drop the sslmode or set it to require.
If I am following correctly, if you are cert authentication with fabric-ca:

https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#postgresql

Then you need to fill in the certfile(s) sections. I know you have 
tls.enabled: false. I think that the server is taking the datasource as 
priority and trying a verify-full without the necessary cert 
information. That is why I suggested backing off on the SSL requirements 
to see if you can make a connection. For what the sslmode options means 
go here:

https://www.postgresql.org/docs/11/libpq-connect.html#LIBPQ-PARAMKEYWORDS

and search in page for sslmode.

Plan B would be to fill in the certfile(s) information.

As to your question below as to why the psql connection works. You are 
not specifying an sslmode to the connection so it defaults to a sslmode of:

prefer (default)

     first try an SSL connection; if that fails, try a non-SSL connection

There is no cert authentication going on in that case, so you connect. 
The connection is done using SSL, it just does not verify the cert.



>    tls:
>        enabled: false
>        certfiles:
>        client:
>          certfile:
>          keyfile:
> 
> Initializing the fabric-ca-server gives "Failed to connect to Postgres 
> database" and in postgresql-11-fabmnet.log : sslv3 alert bad certificate
> 
> (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
> 2019/09/27 17:07:27 [INFO] Configuration file location: 
> /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/27 17:07:27 [INFO] Server Version: 1.4.4
> 2019/09/27 17:07:27 [INFO] Server Levels: &{Identity:2 Affiliation:1 
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/27 17:07:27 [INFO] The CA key and certificate already exist
> 2019/09/27 17:07:27 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/27 17:07:27 [INFO] The certificate is at: 
> /home/marco/fabric/fabric-ca/ca-cert.pem
> 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'fabmnet_ca'
> 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'postgres'
> 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'template1'
> 2019/09/27 17:07:27 [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/27 17:07:27 [INFO] Home directory for default CA: 
> /home/marco/fabric/fabric-ca
> 2019/09/27 17:07:27 [INFO] Initialization was successful
> 
> /var/log/postgresql/postgresql-11-fabmnet.log : 2019-09-27 17:07:27.159 
> CEST [6626] [unknown]@[unknown] LOG:  could not accept SSL connection: 
> sslv3 alert bad certificate
> 
> Why it says "sslv3 alert bad certificate" if it's exactly the same 
> certificate used when connecting to the same database with ssl in 
> postgres environment as shown above?
> 
> Marco

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Marco Ippolito

Date:

27 September 2019, 18:02:27

Thank you very much Adrian.

Two things:

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

(base) postgres@pc:~$ psql -p5433 -h localhost
Password for user postgres:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "5433".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

(base) postgres@pc:~$ psql -p5433 -h localhost -d fabmnet_ca -U postgres
Password for user postgres:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

fabmnet_ca=# \conninfo
You are connected to database "fabmnet_ca" as user "postgres" on host "localhost" at port "5433".
fabmnet_ca=#

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
      tls:
      enabled: false
      certfiles:
      client:
      certfile:
      keyfile:

where sslmode=allow means "first try a non-SSL connection; if that fails, try an SSL connection"

    (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
    2019/09/27 19:37:46 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
    2019/09/27 19:37:46 [INFO] Server Version: 1.4.4
    2019/09/27 19:37:46 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
    2019/09/27 19:37:46 [INFO] The CA key and certificate already exist
    2019/09/27 19:37:46 [INFO] The key is stored by BCCSP provider 'SW'
    2019/09/27 19:37:46 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
    2019/09/27 19:37:46 [WARNING] Failed to connect to database 'fabmnet_ca'
    2019/09/27 19:37:46 [WARNING] Failed to connect to database 'postgres'
    2019/09/27 19:37:46 [WARNING] Failed to connect to database 'template1'
    2019/09/27 19:37:46 [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/27 19:37:46 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
    2019/09/27 19:37:46 [INFO] Initialization was successful

/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

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:

     (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
     2019/09/27 19:55:03 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
    2019/09/27 19:55:03 [INFO] Server Version: 1.4.4
    2019/09/27 19:55:03 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
    2019/09/27 19:55:03 [INFO] The CA key and certificate already exist
    2019/09/27 19:55:03 [INFO] The key is stored by BCCSP provider 'SW'
    2019/09/27 19:55:03 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
    2019/09/27 19:55:03 [INFO] Initialized postgres database at host=localhost port=5433 user=**** password=**** dbname=fabmnet_ca sslmode=disable
    2019/09/27 19:55:03 [INFO] The Idemix issuer public and secret key files already exist
    2019/09/27 19:55:03 [INFO] secret key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
    2019/09/27 19:55:03 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerPublicKey
    2019/09/27 19:55:03 [INFO] The Idemix issuer revocation public and secret key files already exist
    2019/09/27 19:55:03 [INFO] private key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
    2019/09/27 19:55:03 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
    2019/09/27 19:55:03 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
    2019/09/27 19:55:03 [INFO] Initialization was successful

/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

    (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
    2019/09/27 19:57:58 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
    2019/09/27 19:57:58 [INFO] Starting server in home directory: /home/marco/fabric/fabric-ca
    2019/09/27 19:57:58 [INFO] Server Version: 1.4.4
    2019/09/27 19:57:58 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
    2019/09/27 19:57:58 [INFO] The CA key and certificate already exist
    2019/09/27 19:57:58 [INFO] The key is stored by BCCSP provider 'SW'
    2019/09/27 19:57:58 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
    2019/09/27 19:57:58 [INFO] Initialized postgres database at host=localhost port=5433 user=**** password=**** dbname=fabmnet_ca sslmode=disable
    2019/09/27 19:57:58 [INFO] The Idemix issuer public and secret key files already exist
    2019/09/27 19:57:58 [INFO] secret key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
    2019/09/27 19:57:58 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerPublicKey
    2019/09/27 19:57:58 [INFO] The Idemix issuer revocation public and secret key files already exist
    2019/09/27 19:57:58 [INFO] private key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
    2019/09/27 19:57:58 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
    2019/09/27 19:57:58 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
    2019/09/27 19:57:58 [INFO] Operation Server Listening on 127.0.0.1:9443
    2019/09/27 19:57:58 [INFO] Listening on http://0.0.0.0:7054

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?

Marco

Il giorno ven 27 set 2019 alle ore 18:37 Adrian Klaver <adrian.klaver@aklaver.com> ha scritto:

On 9/27/19 8:20 AM, Marco Ippolito wrote:
> Correction of my previous email :
>
> This is the correct ssl connection, not the one before via socket:

A tip, when troubleshooting be as explicit as possible in your command
line usage. So for below explicitly state the -d postgres -U postgres.
This will save you issues with default values and environment values
that you don't know about changing the command. This is not the issue
here, just a heads up for future use.

More below.

>
> (base) postgres@pc:~$ psql -p5433 -h localhost
> Password for user postgres:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)

> fabmnet_ca=#
>
> Anyway, I'm still struggling in understanding how to configure the ssh
> connection of fabric-ca-server to fabmnet_ca database:
>
> This is what I set in fabric-ca-server-config.yaml :
>
> #db:
> # type: sqlite3
> # datasource: fabric-ca-server.db
> # tls:
> # enabled: false
> # certfiles:
> # client:
> # certfile:
> # keyfile:
>
>
> db:
> type: postgres
> datasource: host=localhost port=5433 user=postgres password=pwd
> dbname=fabmnet_ca sslmode=verify-full

For now I would drop the sslmode or set it to require.
If I am following correctly, if you are cert authentication with fabric-ca:

https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#postgresql

Then you need to fill in the certfile(s) sections. I know you have
tls.enabled: false. I think that the server is taking the datasource as
priority and trying a verify-full without the necessary cert
information. That is why I suggested backing off on the SSL requirements
to see if you can make a connection. For what the sslmode options means
go here:

https://www.postgresql.org/docs/11/libpq-connect.html#LIBPQ-PARAMKEYWORDS

and search in page for sslmode.

Plan B would be to fill in the certfile(s) information.

As to your question below as to why the psql connection works. You are
not specifying an sslmode to the connection so it defaults to a sslmode of:

prefer (default)

first try an SSL connection; if that fails, try a non-SSL connection

There is no cert authentication going on in that case, so you connect.
The connection is done using SSL, it just does not verify the cert.

> tls:
> enabled: false
> certfiles:
> client:
> certfile:
> keyfile:
>
> Initializing the fabric-ca-server gives "Failed to connect to Postgres
> database" and in postgresql-11-fabmnet.log : sslv3 alert bad certificate
>
> (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
> 2019/09/27 17:07:27 [INFO] Configuration file location:
> /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/27 17:07:27 [INFO] Server Version: 1.4.4
> 2019/09/27 17:07:27 [INFO] Server Levels: &{Identity:2 Affiliation:1
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/27 17:07:27 [INFO] The CA key and certificate already exist
> 2019/09/27 17:07:27 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/27 17:07:27 [INFO] The certificate is at:
> /home/marco/fabric/fabric-ca/ca-cert.pem
> 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'fabmnet_ca'
> 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'postgres'
> 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'template1'
> 2019/09/27 17:07:27 [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/27 17:07:27 [INFO] Home directory for default CA:
> /home/marco/fabric/fabric-ca
> 2019/09/27 17:07:27 [INFO] Initialization was successful
>
> /var/log/postgresql/postgresql-11-fabmnet.log : 2019-09-27 17:07:27.159
> CEST [6626] [unknown]@[unknown] LOG: could not accept SSL connection:
> sslv3 alert bad certificate
>
> Why it says "sslv3 alert bad certificate" if it's exactly the same
> certificate used when connecting to the same database with ssl in
> postgres environment as shown above?
>
> Marco

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Marco Ippolito

Date:

27 September 2019, 18:19:58

Sorry again,

I was cheering up too quickly.

With this configuration in fabric-ca-server-config.yaml :

the output of starting fabric-ca-server at first glance seems ok:

(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/27 20:11:43 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/27 20:11:44 [INFO] Starting server in home directory: /home/marco/fabric/fabric-ca
2019/09/27 20:11:44 [INFO] Server Version: 1.4.4
2019/09/27 20:11:44 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/27 20:11:44 [INFO] The CA key and certificate already exist
2019/09/27 20:11:44 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/27 20:11:44 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/27 20:11:44 [INFO] Initialized postgres database at host=localhost port=5433 user=**** password=**** dbname=fabmnet_ca sslmode=disable
2019/09/27 20:11:44 [INFO] The Idemix issuer public and secret key files already exist
2019/09/27 20:11:44 [INFO] secret key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
2019/09/27 20:11:44 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerPublicKey
2019/09/27 20:11:44 [INFO] The Idemix issuer revocation public and secret key files already exist
2019/09/27 20:11:44 [INFO] private key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
2019/09/27 20:11:44 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
2019/09/27 20:11:44 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
2019/09/27 20:11:44 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/27 20:11:44 [INFO] Listening on http://0.0.0.0:7054

but the /var/log/postgresql/postgresql-11-fabmnet.log gives us a different, not so bright, perspective:

2019-09-27 20:11:44.012 CEST [3450] postgres@fabmnet_ca ERROR: database "fabmnet_ca" already exists
2019-09-27 20:11:44.012 CEST [3450] postgres@fabmnet_ca STATEMENT: CREATE DATABASE fabmnet_ca
2019-09-27 20:11:44.015 CEST [3451] postgres@fabmnet_ca ERROR: duplicate key value violates unique constraint "properties_pkey"
2019-09-27 20:11:44.015 CEST [3451] postgres@fabmnet_ca DETAIL: Key (property)=(identity.level) already exists.
2019-09-27 20:11:44.015 CEST [3451] postgres@fabmnet_ca STATEMENT: INSERT INTO properties (property, value) VALUES ('identity.level', '0'), ('affiliation.level', '0'), ('certificate.level', '0'), ('cred$
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca ERROR: duplicate key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca DETAIL: Key (name)=(org2) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca ERROR: duplicate key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca DETAIL: Key (name)=(org2.department1) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca ERROR: duplicate key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca DETAIL: Key (name)=(org1) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca ERROR: duplicate key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca DETAIL: Key (name)=(org1.department1) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)

What do these continuous attempts to duplicate key value mean? It doesn't look so good this fabric-ca-server connection with postgresql-11 's db ...

Marco

Il giorno ven 27 set 2019 alle ore 20:02 Marco Ippolito <ippolito.marco@gmail.com> ha scritto:

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

(base) postgres@pc:~$ psql -p5433 -h localhost
Password for user postgres:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "5433".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

(base) postgres@pc:~$ psql -p5433 -h localhost -d fabmnet_ca -U postgres
Password for user postgres:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

fabmnet_ca=# \conninfo
You are connected to database "fabmnet_ca" as user "postgres" on host "localhost" at port "5433".
fabmnet_ca=#

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
      tls:
      enabled: false
      certfiles:
      client:
      certfile:
      keyfile:

    where sslmode=allow means "first try a non-SSL connection; if that fails, try an SSL connection"

    (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
    2019/09/27 19:37:46 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
    2019/09/27 19:37:46 [INFO] Server Version: 1.4.4
    2019/09/27 19:37:46 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
    2019/09/27 19:37:46 [INFO] The CA key and certificate already exist
    2019/09/27 19:37:46 [INFO] The key is stored by BCCSP provider 'SW'
    2019/09/27 19:37:46 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
    2019/09/27 19:37:46 [WARNING] Failed to connect to database 'fabmnet_ca'
    2019/09/27 19:37:46 [WARNING] Failed to connect to database 'postgres'
    2019/09/27 19:37:46 [WARNING] Failed to connect to database 'template1'
    2019/09/27 19:37:46 [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/27 19:37:46 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
    2019/09/27 19:37:46 [INFO] Initialization was successful

    /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

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:


     (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
     2019/09/27 19:55:03 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
    2019/09/27 19:55:03 [INFO] Server Version: 1.4.4
    2019/09/27 19:55:03 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
    2019/09/27 19:55:03 [INFO] The CA key and certificate already exist
    2019/09/27 19:55:03 [INFO] The key is stored by BCCSP provider 'SW'
    2019/09/27 19:55:03 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
    2019/09/27 19:55:03 [INFO] Initialized postgres database at host=localhost port=5433 user=**** password=**** dbname=fabmnet_ca sslmode=disable
    2019/09/27 19:55:03 [INFO] The Idemix issuer public and secret key files already exist
    2019/09/27 19:55:03 [INFO] secret key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
    2019/09/27 19:55:03 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerPublicKey
    2019/09/27 19:55:03 [INFO] The Idemix issuer revocation public and secret key files already exist
    2019/09/27 19:55:03 [INFO] private key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
    2019/09/27 19:55:03 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
    2019/09/27 19:55:03 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
    2019/09/27 19:55:03 [INFO] Initialization was successful

    /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

    (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
    2019/09/27 19:57:58 [INFO] Configuration file location: /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
    2019/09/27 19:57:58 [INFO] Starting server in home directory: /home/marco/fabric/fabric-ca
    2019/09/27 19:57:58 [INFO] Server Version: 1.4.4
    2019/09/27 19:57:58 [INFO] Server Levels: &{Identity:2 Affiliation:1 Certificate:1 Credential:1 RAInfo:1 Nonce:1}
    2019/09/27 19:57:58 [INFO] The CA key and certificate already exist
    2019/09/27 19:57:58 [INFO] The key is stored by BCCSP provider 'SW'
    2019/09/27 19:57:58 [INFO] The certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem
    2019/09/27 19:57:58 [INFO] Initialized postgres database at host=localhost port=5433 user=**** password=**** dbname=fabmnet_ca sslmode=disable
    2019/09/27 19:57:58 [INFO] The Idemix issuer public and secret key files already exist
    2019/09/27 19:57:58 [INFO] secret key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
    2019/09/27 19:57:58 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerPublicKey
    2019/09/27 19:57:58 [INFO] The Idemix issuer revocation public and secret key files already exist
    2019/09/27 19:57:58 [INFO] private key file location: /home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
    2019/09/27 19:57:58 [INFO] public key file location: /home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
    2019/09/27 19:57:58 [INFO] Home directory for default CA: /home/marco/fabric/fabric-ca
    2019/09/27 19:57:58 [INFO] Operation Server Listening on 127.0.0.1:9443
    2019/09/27 19:57:58 [INFO] Listening on http://0.0.0.0:7054

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?

Marco

Il giorno ven 27 set 2019 alle ore 18:37 Adrian Klaver <adrian.klaver@aklaver.com> ha scritto:
On 9/27/19 8:20 AM, Marco Ippolito wrote:
> Correction of my previous email :
>
> This is the correct ssl connection, not the one before via socket:

A tip, when troubleshooting be as explicit as possible in your command
line usage. So for below explicitly state the -d postgres -U postgres.
This will save you issues with default values and environment values
that you don't know about changing the command. This is not the issue
here, just a heads up for future use.

More below.

>
> (base) postgres@pc:~$ psql -p5433 -h localhost
> Password for user postgres:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)

> fabmnet_ca=#
>
> Anyway, I'm still struggling in understanding how to configure the ssh
> connection of fabric-ca-server to fabmnet_ca database:
>
> This is what I set in fabric-ca-server-config.yaml :
>
> #db:
> # type: sqlite3
> # datasource: fabric-ca-server.db
> # tls:
> # enabled: false
> # certfiles:
> # client:
> # certfile:
> # keyfile:
>
>
> db:
> type: postgres
> datasource: host=localhost port=5433 user=postgres password=pwd
> dbname=fabmnet_ca sslmode=verify-full

For now I would drop the sslmode or set it to require.
If I am following correctly, if you are cert authentication with fabric-ca:

https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#postgresql

Then you need to fill in the certfile(s) sections. I know you have
tls.enabled: false. I think that the server is taking the datasource as
priority and trying a verify-full without the necessary cert
information. That is why I suggested backing off on the SSL requirements
to see if you can make a connection. For what the sslmode options means
go here:

https://www.postgresql.org/docs/11/libpq-connect.html#LIBPQ-PARAMKEYWORDS

and search in page for sslmode.

Plan B would be to fill in the certfile(s) information.

As to your question below as to why the psql connection works. You are
not specifying an sslmode to the connection so it defaults to a sslmode of:

prefer (default)

first try an SSL connection; if that fails, try a non-SSL connection

There is no cert authentication going on in that case, so you connect.
The connection is done using SSL, it just does not verify the cert.

> tls:
> enabled: false
> certfiles:
> client:
> certfile:
> keyfile:
>
> Initializing the fabric-ca-server gives "Failed to connect to Postgres
> database" and in postgresql-11-fabmnet.log : sslv3 alert bad certificate
>
> (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
> 2019/09/27 17:07:27 [INFO] Configuration file location:
> /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/27 17:07:27 [INFO] Server Version: 1.4.4
> 2019/09/27 17:07:27 [INFO] Server Levels: &{Identity:2 Affiliation:1
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/27 17:07:27 [INFO] The CA key and certificate already exist
> 2019/09/27 17:07:27 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/27 17:07:27 [INFO] The certificate is at:
> /home/marco/fabric/fabric-ca/ca-cert.pem
> 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'fabmnet_ca'
> 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'postgres'
> 2019/09/27 17:07:27 [WARNING] Failed to connect to database 'template1'
> 2019/09/27 17:07:27 [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/27 17:07:27 [INFO] Home directory for default CA:
> /home/marco/fabric/fabric-ca
> 2019/09/27 17:07:27 [INFO] Initialization was successful
>
> /var/log/postgresql/postgresql-11-fabmnet.log : 2019-09-27 17:07:27.159
> CEST [6626] [unknown]@[unknown] LOG: could not accept SSL connection:
> sslv3 alert bad certificate
>
> Why it says "sslv3 alert bad certificate" if it's exactly the same
> certificate used when connecting to the same database with ssl in
> postgres environment as shown above?
>
> Marco

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Adrian Klaver

Date:

27 September 2019, 19:39:51

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

Re: "Failed to connect to Postgres database"

From

Adrian Klaver

Date:

27 September 2019, 19:52:39

On 9/27/19 11:19 AM, Marco Ippolito wrote:
> Sorry again,
> I was cheering up too quickly.
> With this configuration in fabric-ca-server-config.yaml :
>     db:
>        type: postgres
>        datasource: host=localhost port=5433 user=postgres password=1234 
> dbname=fabmnet_ca sslmode=disable
>        tls:
>          enabled: false
>          certfiles:
>          client:
>            certfile:
>            keyfile:
> 
> the output of  starting fabric-ca-server at first glance seems ok:
> 

> 2019/09/27 20:11:44 [INFO] Operation Server Listening on 127.0.0.1:9443 
> <http://127.0.0.1:9443>
> 2019/09/27 20:11:44 [INFO] Listening on http://0.0.0.0:7054

So the server is up and running.

> 
> but the /var/log/postgresql/postgresql-11-fabmnet.log gives us a 
> different, not so bright, perspective:



> 
> What do these continuous attempts to duplicate key value mean? It 
> doesn't look so good this fabric-ca-server connection with postgresql-11 
> 's db ...

Looks to me like the fabric server is trying reinitialize the database 
again with values that already exist. I do not pretend to know what the 
fabric-ca server is up to. You might have better luck with that part of 
it here:

https://lists.hyperledger.org/g/main

> 
> Marco
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Marco Ippolito

Date:

28 September 2019, 07:07:40

Hi Adrian,

Il giorno ven 27 set 2019 alle ore 21:39 Adrian Klaver <adrian.klaver@aklaver.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@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
>

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@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@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@postgres ERROR: database "fabmnet_ca" already exists
2019-09-28 09:00:08.641 CEST [4227] postgres@postgres STATEMENT: CREATE DATABASE fabmnet_ca
2019-09-28 09:00:08.644 CEST [4228] postgres@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@postgres LOG: could not receive data from client: Connection reset by peer

Re: "Failed to connect to Postgres database"

From

Adrian Klaver

Date:

28 September 2019, 21:49:07

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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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@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@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@postgres ERROR:  database 
> "fabmnet_ca" already exists
> 2019-09-28 09:00:08.641 CEST [4227] postgres@postgres STATEMENT:  CREATE 
> DATABASE fabmnet_ca
> 2019-09-28 09:00:08.644 CEST [4228] postgres@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@postgres LOG:  could not 
> receive data from client: Connection reset by peer
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: "Failed to connect to Postgres database"

From

Marco Ippolito

Date:

30 September 2019, 16:01:17

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@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@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@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@pc:~$

PostgreSQL-Server:

(base) postgres@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@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@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@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@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@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@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@pc:~$ ls -ltr ~/.postgresql/root.crt
-rw------- 1 postgres postgres 1354 Sep 30 17:22 /home/marco/.postgresql/root.crt

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

(base) marco@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@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@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]@[unknown] LOG: incomplete startup packet
2019-09-30 17:56:22.760 CEST [10650] [unknown]@[unknown] LOG: incomplete startup packet
2019-09-30 17:56:22.760 CEST [10649] [unknown]@[unknown] LOG: incomplete startup packet

What could it mean?

Marco

Il giorno sab 28 set 2019 alle ore 23:49 Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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@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@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@postgres ERROR: database
> "fabmnet_ca" already exists
> 2019-09-28 09:00:08.641 CEST [4227] postgres@postgres STATEMENT: CREATE
> DATABASE fabmnet_ca
> 2019-09-28 09:00:08.644 CEST [4228] postgres@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@postgres LOG: could not
> receive data from client: Connection reset by peer
>

--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Marco Ippolito

Date:

30 September 2019, 16:21:56

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@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.marco@gmail.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@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@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@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@pc:~$

PostgreSQL-Server:

(base) postgres@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@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@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@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@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@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@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@pc:~$ ls -ltr ~/.postgresql/root.crt
-rw------- 1 postgres postgres 1354 Sep 30 17:22 /home/marco/.postgresql/root.crt

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

(base) marco@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@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@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]@[unknown] LOG: incomplete startup packet
2019-09-30 17:56:22.760 CEST [10650] [unknown]@[unknown] LOG: incomplete startup packet
2019-09-30 17:56:22.760 CEST [10649] [unknown]@[unknown] LOG: incomplete startup packet

What could it mean?

Marco

Il giorno sab 28 set 2019 alle ore 23:49 Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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@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@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@postgres ERROR: database
> "fabmnet_ca" already exists
> 2019-09-28 09:00:08.641 CEST [4227] postgres@postgres STATEMENT: CREATE
> DATABASE fabmnet_ca
> 2019-09-28 09:00:08.644 CEST [4228] postgres@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@postgres LOG: could not
> receive data from client: Connection reset by peer
>

--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Adrian Klaver

Date:

01 October 2019, 14:28:15

On 9/30/19 9:21 AM, Marco Ippolito wrote:
> 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@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" ?
> 

I have no idea. Per my post upstream I would test your Postgres setup 
first without bringing in the fabric server:

psql "host=localhost port=5433 dbname=fabmnet_ca user=postgres
sslmode=require"

Changing sslmode to whatever you need.



-- 
Adrian Klaver
adrian.klaver@aklaver.com