Re: Rotate SSL certificates on reload (SIGHUP) without restart

Hi Bob,

Thank you for the new patch. This time it failed with different pgpool.log.
src/test/regression/log/042.ssl_reload was same.

2026-04-17 07:30:46.893: child pid 1475041: DEBUG: attempting to negotiate a secure connection
2026-04-17 07:30:46.893: child pid 1475041: DETAIL: sending client->server SSL request
2026-04-17 07:30:46.893: child pid 1475041: DEBUG: pool_flush_it: flush size: 8
2026-04-17 07:30:46.894: child pid 1475041: DEBUG: pool_read: read 1 bytes from backend 0
2026-04-17 07:30:46.894: child pid 1475041: DEBUG: attempting to negotiate a secure connection
2026-04-17 07:30:46.894: child pid 1475041: DETAIL: client->server SSL response: S
2026-04-17 07:30:46.899: child pid 1475041: LOG: pool_ssl: "SSL_connect": "certificate verify failed"
2026-04-17 07:30:46.899: child pid 1475041: DEBUG: pool_flush_it: flush size: 58
2026-04-17 07:30:46.899: child pid 1475041: DEBUG: pool_read: read 360 bytes from backend 0
2026-04-17 07:30:46.900: child pid 1475041: ERROR: backend authentication failed
2026-04-17 07:30:46.900: child pid 1475041: DETAIL: backend response with kind '' when expecting 'R'
2026-04-17 07:30:46.900: child pid 1475041: DEBUG: pool_write: to frontend: kind:E po:0
2026-04-17 07:30:46.900: child pid 1475041: DEBUG: pool_write: to frontend: length:4 po:1
2026-04-17 07:30:46.900: child pid 1475041: DEBUG: pool_write: to frontend: length:115 po:5
2026-04-17 07:30:46.900: child pid 1475041: DEBUG: pool_flush_it: flush size: 120

> 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="$CADIR/ca1.crt" on both psql invocations. The intent was
> 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=require 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="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 PM Tatsuo Ishii <ishii(at)postgresql(dot)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:
>>
>> ===== ssl_ca_cert swap (client cert auth reload) =====
>> 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 – unexpected.
>>
>> 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 client
>> 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 AM Tatsuo Ishii <ishii(at)postgresql(dot)org>
>> wrote:
>> >
>> >> Hi Bob,
>> >>
>> >> > Hi Tatsuo,
>> >> >
>> >> > Please let me know if you need any assistance with updating your test
>> >> > 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 PM Bob Ross <bob(dot)ross(dot)19821(at)gmail(dot)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 warning
>> >> >> message (per pool_ssl.c it’s “DH: could not load DH parameters”) when
>> >> >> pgpool tries to load the file.
>> >>
>> >> I think it will not work.
>> >>
>> >> ===================================================================
>> >> static bool
>> >> initialize_dh(SSL_CTX *context)
>> >> {
>> >> DH *dh = NULL;
>> >>
>> >> SSL_CTX_set_options(context, SSL_OP_SINGLE_DH_USE);
>> >>
>> >> if (pool_config->ssl_dh_params_file[0])
>> >> dh = load_dh_file(pool_config->ssl_dh_params_file);
>> >> if (!dh)
>> >> dh = load_dh_buffer(FILE_DH2048, sizeof(FILE_DH2048));
>> >> if (!dh)
>> >> {
>> >> ereport(WARNING,
>> >> (errmsg("DH: could not load DH
>> >> parameters")));
>> >> return false;
>> >> }
>> >> :
>> >> :
>> >> ===================================================================
>> >>
>> >> 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
>> >>
>>