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

Hi Adrian,

thanks to your kind explanation I discovered that I can connect to the db without explicitly calling the belonging cluster:

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