Re: "Failed to connect to Postgres database" - Mailing list pgsql-general

From Marco Ippolito
Subject Re: "Failed to connect to Postgres database"
Date
Msg-id CAFegzBTe-Hbdn5o39A2Da8Sz1DN7+mGK3yvVZwc5iFch8Spkig@mail.gmail.com
Whole thread Raw
In response to Re: "Failed to connect to Postgres database"  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: "Failed to connect to Postgres database"
List pgsql-general
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 fabmnetdb :

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

pgsql-general by date:

Previous
From: Rob Sargent
Date:
Subject: Re: managing primary key conflicts while restoring data to table withexisting data
Next
From: Mark Lybarger
Date:
Subject: row_to_json white space