Re: "Failed to connect to Postgres database"

I need to understand this:

I dropped the previous fabmnet cluster and re-created a new one:

(base) postgres(at)pc:~$ pg_lsclusters
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

I can connect with SSL to cluster 11/main:

(base) postgres(at)pc:~$ psql -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
-
----------+----------+----------+-------------+-------------+-----------------------
marco | marco | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.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 "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
bits: 256, compression: off)
postgres=#

(base) postgres(at)pc:/var/lib/postgresql/11/main$ ls -lah
total 92K
drwx------ 19 postgres postgres 4.0K Sep 27 09:38 .
drwxr-xr-x 4 postgres postgres 4.0K Sep 27 10:58 ..
-rw------- 1 postgres postgres 3 Sep 18 16:22 PG_VERSION
drwx------ 6 postgres postgres 4.0K Sep 27 11:57 base
drwx------ 2 postgres postgres 4.0K Sep 27 09:39 global
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_commit_ts
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_dynshmem
drwx------ 4 postgres postgres 4.0K Sep 27 11:57 pg_logical
drwx------ 4 postgres postgres 4.0K Sep 18 16:22 pg_multixact
drwx------ 2 postgres postgres 4.0K Sep 27 09:38 pg_notify
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_replslot
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_serial
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_snapshots
drwx------ 2 postgres postgres 4.0K Sep 27 09:38 pg_stat
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_stat_tmp
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_subtrans
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_tblspc
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_twophase
drwx------ 3 postgres postgres 4.0K Sep 18 16:22 pg_wal
drwx------ 2 postgres postgres 4.0K Sep 18 16:22 pg_xact
-rw------- 1 postgres postgres 88 Sep 18 16:22 postgresql.auto.conf
-rw------- 1 postgres postgres 130 Sep 27 09:38 postmaster.opts
-rw------- 1 postgres postgres 108 Sep 27 09:38 postmaster.pid

(base) marco(at)pc:~$ ps ax | grep post
1030 ? S 0:00 /usr/lib/postgresql/11/bin/postgres -D
/var/lib/postgresql/11/main -c
config_file=/etc/postgresql/11/main/postgresql.conf
1107 ? Ss 0:00 postgres: 11/main: checkpointer
1108 ? Ss 0:00 postgres: 11/main: background writer
1109 ? Ss 0:00 postgres: 11/main: walwriter
1110 ? Ss 0:00 postgres: 11/main: autovacuum launcher
1111 ? Ss 0:00 postgres: 11/main: stats collector
1112 ? Ss 0:00 postgres: 11/main: logical replication launcher

5003 ? S 0:00 /usr/lib/postgresql/11/bin/postgres -D
/var/lib/postgresql/11/fabmnet -c
config_file=/etc/postgresql/11/fabmnet/postgresql.conf
5005 ? Ss 0:00 postgres: 11/fabmnet: checkpointer
5006 ? Ss 0:00 postgres: 11/fabmnet: background writer
5007 ? Ss 0:00 postgres: 11/fabmnet: walwriter
5008 ? Ss 0:00 postgres: 11/fabmnet: autovacuum launcher
5009 ? Ss 0:00 postgres: 11/fabmnet: stats collector
5010 ? Ss 0:00 postgres: 11/fabmnet: logical replication
launcher
6543 pts/1 S 0:00 sudo -su postgres
6867 pts/1 S+ 0:00 /usr/lib/postgresql/11/bin/psql -h localhost
6878 ? Ss 0:00 postgres: 11/main: postgres postgres ::1(49156)
idle
6883 pts/0 S 0:00 sudo -su postgres
8167 pts/2 S+ 0:00 grep --color=auto post

Bu I cannot do the same with cluster 11/fabmnet :

(base) postgres(at)pc:/var/lib/postgresql/11/fabmnet$ ls -lah
total 104K
drwx------ 19 postgres postgres 4.0K Sep 27 11:28 .
drwxr-xr-x 4 postgres postgres 4.0K Sep 27 10:58 ..
-rw------- 1 postgres postgres 3 Sep 27 10:58 PG_VERSION
drwx------ 6 postgres postgres 4.0K Sep 27 11:40 base
drwx------ 2 postgres postgres 4.0K Sep 27 11:13 global
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_commit_ts
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_dynshmem
drwx------ 4 postgres postgres 4.0K Sep 27 11:45 pg_logical
drwx------ 4 postgres postgres 4.0K Sep 27 10:58 pg_multixact
drwx------ 2 postgres postgres 4.0K Sep 27 10:59 pg_notify
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_replslot
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_serial
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_snapshots
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_stat
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_stat_tmp
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_subtrans
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_tblspc
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_twophase
drwx------ 3 postgres postgres 4.0K Sep 27 10:58 pg_wal
drwx------ 2 postgres postgres 4.0K Sep 27 10:58 pg_xact
-rw------- 1 postgres postgres 88 Sep 27 10:58 postgresql.auto.conf
-rw------- 1 postgres postgres 136 Sep 27 10:59 postmaster.opts
-rw------- 1 postgres postgres 111 Sep 27 10:59 postmaster.pid
-rw------- 1 root root 4.4K Sep 27 11:26 server.crt
-rw------- 1 root root 1.7K Sep 27 11:26 server.key

I created the server.crt and server.key files following the indications
found here:
https://vibhork.blogspot.com/2011/07/how-to-enable-ssl-in-postgresqlppas.html

(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"

So, before passing to the fabric-ca side , I need to solve this aspect.

Marco

Il giorno ven 27 set 2019 alle ore 04:28 rob stone <floriparob(at)gmail(dot)com>
ha scritto:

> Hi,
>
> On Thu, 2019-09-26 at 16:21 +0200, Marco Ippolito wrote:
> >
> > db:
> > type: postgres
> > datasource: host=localhost port=5433 user=fabmnet_admin
> > password=password dbname=fabmnetdb sslmode=verify-full
> >
>
> >
> > (base) marco(at)pc:~/fabric/fabric-ca$ fabric-ca-server start -b
> > admin:adminpw
> > 2019/09/26 15:56:50 [INFO] Configuration file location: /home/marco
> > /fabric/fabric-ca/fabric-ca-server-config.yaml
> > 2019/09/26 15:56:50 [INFO] Starting server in home directory:
> > /home/marco/fabric/fabric-ca
> > 2019/09/26 15:56:50 [INFO] Server Version: 1.4.4
> > 2019/09/26 15:56:50 [INFO] Server Levels: &{Identity:2 Affiliation:1
> > Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> > 2019/09/26 15:56:50 [INFO] The CA key and certificate already exist
> > 2019/09/26 15:56:50 [INFO] The key is stored by BCCSP provider 'SW'
> > 2019/09/26 15:56:50 [INFO] The certificate is at: /home/marco/fabric
> > /fabric-ca/ca-cert.pem
> > 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> > 'fabmnetdb'
> > 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> > 'postgres'
> > 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> > 'template1'
> > 2019/09/26 15:56:50 [ERROR] Error occurred initializing database:
> > Failed
> > to connect to Postgres database. Postgres requires connecting to a
> > specific database, the following databases were tried: [fabmnetdb
> > postgres template1]. Please create one of these database before
> > continuing
>
>
> Why is it trying to connect to *any* database?
>
> In the fabric-ca docs it shows the connection string as a single line
> but your configuration file has it split over two lines.
> My uneducated guess is that it is ignoring the 'password=password
> dbname=fabmnetdb sslmode=verify-full'
> line and thus unable to connect to fabmnetdb.
>
> Cheers,
> Robert
>
>
>