MIME-Version: 1.0
References: 
 <CAHtZvrcnFHgLatJQrsGH0-Tupb4vY+dZ3jiBcK5uRgJ1AbGEAg@mail.gmail.com>
 <20260414.170151.1083817546153854089.ishii@postgresql.org>
 <CAHtZvrdhAbVSh2yhSwk0qeHmnL+Sr0LPvjrA+2inKF6WNh7azw@mail.gmail.com>
 <20260416.193138.2156629846536617464.ishii@postgresql.org>
In-Reply-To: <20260416.193138.2156629846536617464.ishii@postgresql.org>
From: Bob Ross <bob.ross.19821@gmail.com>
Date: Thu, 16 Apr 2026 14:25:17 +0200
Message-ID: 
 <CAHtZvreKzO3fwfWZfmSPKjc0qN4P5bsvGMLhCir6gSK8pOFnXQ@mail.gmail.com>
Subject: Re: Rotate SSL certificates on reload (SIGHUP) without restart
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: pgpool-hackers@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000a0a5bc064f92ed67"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHtZvreKzO3fwfWZfmSPKjc0qN4P5bsvGMLhCir6gSK8pOFnXQ%40mail.gmail.com>
Precedence: bulk

--000000000000a0a5bc064f92ed67
Content-Type: multipart/alternative; boundary="000000000000a0a5ba064f92ed65"

--000000000000a0a5ba064f92ed65
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Tatsuo,

Thank you for the report and the log, that was very helpful.

The bug was in the test, not in the core patch. Test 4 in 042.ssl_reload
set PGSSLROOTCERT=3D"$CADIR/ca1.crt" on both psql invocations. The intent w=
as
to use ca1.crt for server-side client-certificate verification, but
PGSSLROOTCERT actually controls client-side server certificate
verification, and ca1.crt is a freshly generated CA that never signed
pgpool's server.crt. Because libpq upgrades sslmode=3Drequire to verify-ca
behavior when a root CA file is provided, psql immediately sent unknown_ca
back to pgpool and aborted the handshake before server-side client-cert
verification ever ran.

Fix: both lines now use PGSSLROOTCERT=3D"etc/$SSL_CRT". Since server.crt is
self-signed it acts as its own root, so psql accepts pgpool's server cert,
the handshake completes, and the test exercises what it was always meant to
test: whether pgpool enforces the new ssl_ca_cert after reload.

No changes to the core patch.

Please find v3 attached. Please let me know if it fixes the test issue.

Best regards,
Bob

On Thu, Apr 16, 2026 at 12:31=E2=80=AFPM Tatsuo Ishii <ishii@postgresql.org=
> wrote:

> Hi Bob,
>
> Thank you for the patch!
>
> Unfortunately after applying the patch, the test failed. From
> src/test/regression/log/042.ssl_reload:
>
> =3D=3D=3D=3D=3D ssl_ca_cert swap (client cert auth reload) =3D=3D=3D=3D=
=3D
> waiting for server to start....1462289 2026-04-16 19:25:49.490 JST LOG:
> redirecting log output to logging collector process
> 1462289 2026-04-16 19:25:49.490 JST HINT:  Future log output will appear
> in directory "log".
>  done
> server started
> CA cert swap: CA1-signed client cert rejected before reload =E2=80=93 une=
xpected.
>
> From pgpool.log:
>
> 2026-04-16 19:26:10.150: child pid 1462331: DETAIL:  Protocol Major: 1234
> Minor: 5679 database:  user:
> 2026-04-16 19:26:10.150: child pid 1462331: DEBUG:  selecting backend
> connection
> 2026-04-16 19:26:10.150: child pid 1462331: DETAIL:  SSLRequest from clie=
nt
> 2026-04-16 19:26:10.150: child pid 1462331: DEBUG:  pool_write: to
> frontend: kind:S po:0
> 2026-04-16 19:26:10.150: child pid 1462331: DEBUG:  pool_flush_it: flush
> size: 1
> 2026-04-16 19:26:10.167: child pid 1462331: LOG:  pool_ssl: "SSL_accept":
> "tlsv1 alert unknown ca"
> 2026-04-16 19:26:10.167: child pid 1462331: DEBUG:  unable to read data
> from frontend
> 2026-04-16 19:26:10.167: child pid 1462331: DETAIL:  socket read failed
> with error "Connection reset by peer"
>
> Please let me know if you need more info.
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>
> > Hi Tatsuo,
> >
> > Please see attached v2. It adds regression coverage for SSL cert reload
> > with client certificate authentication; there are no functional code
> > changes.
> >
> > Best regards,
> > Bob
> >
> >
> > On Tue, Apr 14, 2026 at 10:02=E2=80=AFAM Tatsuo Ishii <ishii@postgresql=
.org>
> wrote:
> >
> >> Hi Bob,
> >>
> >> > Hi Tatsuo,
> >> >
> >> > Please let me know if you need any assistance with updating your tes=
t
> >> > cases. I am be happy to help.
> >> >
> >> > Thanks,
> >> > Bob
> >>
> >> Sorry for late. I was busy with personal affairs and some other
> >> projects.
> >>
> >> > On Thu, Apr 2, 2026 at 9:57=E2=80=AFPM Bob Ross <bob.ross.19821@gmai=
l.com>
> >> wrote:
> >> >
> >> >> Hi Tatsuo,
> >> >>
> >> >> Thanks for putting together the regression tests.
> >> >>
> >> >> Thoughts on your questions:
> >> >> - CA Certificates - Yes, adding a cert auth test is highly
> recommended.
> >> We
> >> >> could test this by generating two different dummy CA certificates.
> Start
> >> >> pgpool trusting CA #1, swap the config to CA #2, reload and verify =
if
> >> >> client connection correctly gets rejected.
> >>
> >> If you could extend the test file I posted so that it performs a cert
> >> auth test, that would be helpful.
> >>
> >> >> - DH parameters - perhaps we can test this by providing a
> non-existent
> >> >> file path and then use grep to check pgpool.log for specific warnin=
g
> >> >> message (per pool_ssl.c it=E2=80=99s =E2=80=9CDH: could not load DH=
 parameters=E2=80=9D) when
> >> >> pgpool tries to load the file.
> >>
> >> I think it will not work.
> >>
> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >> static bool
> >> initialize_dh(SSL_CTX *context)
> >> {
> >>         DH                 *dh =3D NULL;
> >>
> >>         SSL_CTX_set_options(context, SSL_OP_SINGLE_DH_USE);
> >>
> >>         if (pool_config->ssl_dh_params_file[0])
> >>                 dh =3D load_dh_file(pool_config->ssl_dh_params_file);
> >>         if (!dh)
> >>                 dh =3D load_dh_buffer(FILE_DH2048, sizeof(FILE_DH2048)=
);
> >>         if (!dh)
> >>         {
> >>                 ereport(WARNING,
> >>                                 (errmsg("DH: could not load DH
> >> parameters")));
> >>                 return false;
> >>         }
> >> :
> >> :
> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >>
> >> The ereport message is printed when the built-in DH parameter file is
> >> broken. But as long as the source file is fine, it would never happen.
> >>
> >> Maybe we should fix the code above so that it emits ereport when it
> >> fails to load the DH parameter file specified by ssl_dh_params_file?
> >>
> >> Regards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS K.K.
> >> English: http://www.sraoss.co.jp/index_en/
> >> Japanese:http://www.sraoss.co.jp
> >>
>

--000000000000a0a5ba064f92ed65
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi Tatsuo,<br><br>Thank you for the repor=
t and the log, that was very helpful.<br><br>The bug was in the test, not i=
n the core patch. Test 4 in 042.ssl_reload set PGSSLROOTCERT=3D&quot;$CADIR=
/ca1.crt&quot; on both psql invocations. The intent was to use ca1.crt for =
server-side client-certificate verification, but PGSSLROOTCERT actually con=
trols client-side server certificate verification, and ca1.crt is a freshly=
 generated CA that never signed pgpool&#39;s server.crt. Because libpq upgr=
ades sslmode=3Drequire to verify-ca behavior when a root CA file is provide=
d, psql immediately sent unknown_ca back to pgpool and aborted the handshak=
e before server-side client-cert verification ever ran.<br><br>Fix: both li=
nes now use PGSSLROOTCERT=3D&quot;etc/$SSL_CRT&quot;. Since server.crt is s=
elf-signed it acts as its own root, so psql accepts pgpool&#39;s server cer=
t, the handshake completes, and the test exercises what it was always meant=
 to test: whether pgpool enforces the new ssl_ca_cert after reload.<br><br>=
<div>No changes to the core patch.<br><br>Please find v3 attached. Please l=
et me know if it fixes the test issue.<br><br>Best regards,</div><div>Bob</=
div></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"=
ltr" class=3D"gmail_attr">On Thu, Apr 16, 2026 at 12:31=E2=80=AFPM Tatsuo I=
shii &lt;<a href=3D"mailto:ishii@postgresql.org">ishii@postgresql.org</a>&g=
t; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi Bob=
,<br>
<br>
Thank you for the patch!<br>
<br>
Unfortunately after applying the patch, the test failed. From<br>
src/test/regression/log/042.ssl_reload:<br>
<br>
=3D=3D=3D=3D=3D ssl_ca_cert swap (client cert auth reload) =3D=3D=3D=3D=3D<=
br>
waiting for server to start....1462289 2026-04-16 19:25:49.490 JST LOG:=C2=
=A0 redirecting log output to logging collector process<br>
1462289 2026-04-16 19:25:49.490 JST HINT:=C2=A0 Future log output will appe=
ar in directory &quot;log&quot;.<br>
=C2=A0done<br>
server started<br>
CA cert swap: CA1-signed client cert rejected before reload =E2=80=93 unexp=
ected.<br>
<br>