Re: could not accept SSL connection: sslv3 alert bad certificate - Mailing list pgsql-general

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