Re: "Failed to connect to Postgres database"

Hi Adrian,

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

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