Re: pg_stat_ssl additions

At Mon, 28 Jan 2019 14:53:43 +0100, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote in <24783370-5acd-e0f3-8eb7-7f42ff2a026d(at)2ndquadrant(dot)com>
> On 28/01/2019 09:14, Kyotaro HORIGUCHI wrote:
> > 0002:
> >
> > The test 54-56 of 001_ssltest.pl failed, which succeeded before
> > applying 0002. Seems to need to use another user.
> >
> > # Failed test 'pg_stat_ssl view without client certificate: no stderr'
> > # at t/001_ssltests.pl line 313.
> > # got: 'psql: SSL error: certificate verify failed
> > # FATAL: no pg_hba.conf entry for host "127.0.0.1", user "ssltestuser", database "trustdb", SSL off
> > # '
> >
> > If this is not specific to my environment, the connevcion string
> > at line 313 of 001_ssltests.pl needs sslrootcert setting (, which
> > is feeded to test_connect_ok/fails() via $connstr, not via
> > $common_connstr).
>
> This is strange. The tests work for me, and also on the cfbot. The

Agreed. It seemed so also to me.

> pg_hba.conf method is "trust", and there is nothing that should make it
> do certificate verification for this test. Do you have have any PGSSL*
> environment variables set perhaps? An interesting OpenSSL version or
> configuration perhaps?

Some further investigation told me that the file
~/.postgresql/root.cert was the culprit.

When initializing SSL context, it picks up the root certificate
from my home directory, not in test installation and I had one
there. It is not based on $HOME but pwent so it is unchangeable
(and it is the right design for the purpose).

sslcert, sslkey, sslrootcert and sslcrl are in the same
characteristic so they should be set to invalid value (namely
"invalid") if not used.

The attached diff file on top of 0002 adds a new variable
$def_connstr for the properties above and some other variables,
then uses it as the first part of $common_connstr.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center