Postgres Pro
Downloads
Home   >   mailing lists

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 CAFegzBT25ZoSwQz25bgBec6y2p93Pis1p8tGtHWyaQJ2pckaHQ@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" : No usage specified forcertificate (update)
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Operator is not unique
Next
From: Marco Ippolito
Date:
Subject: Re: "Failed to connect to Postgres database" : No usage specified forcertificate (update)