Re: "Failed to connect to Postgres database"

Correction of my previous email :

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

(base) postgres(at)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=# \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)

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)
postgres=# \q
(base) postgres(at)pc:~$ psql -p5433 -h localhost -d fabmnet_ca
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=# \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)

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
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(at)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](at)[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(dot)marco(at)gmail(dot)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(at)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(at)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](at)[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(dot)klaver(at)aklaver(dot)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(at)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(at)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(at)manitou-mail(dot)org <mailto:daniel(at)manitou-mail(dot)org>> ha scritto:
>> >
>> > Marco Ippolito wrote:
>> >
>> > > (base) postgres(at)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(dot)klaver(at)aklaver(dot)com
>>
>