Re: "Failed to connect to Postgres database"

Hi Adrian,

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

(base) postgres(at)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(at)pc:/var/lib/postgresql/11/fabmnet$ chmod 600 privkey.pem
(base) postgres(at)pc:/var/lib/postgresql/11/fabmnet$ chmod 600 cert.req

But now, testing the ssl connection :

(base) marco(at)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(dot)klaver(at)aklaver(dot)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(at)pc:~/fabric$rm -rf fabric-ca (base)marco(at)pc:~/fabric$mkdir
> > fabric-ca (base)marco(at)pc:~/fabric$cd
> > fabric-ca/(base)marco(at)pc:~/fabric/fabric-ca$fabric-ca-server init -b
> > admin:adminpw (base)marco(at)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(at)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(dot)klaver(at)aklaver(dot)com
>