[pgpool-general: 8056] Re: SSL transport between Pgpool and Postgres

[Mike]

Mar 31, 2022, 08:04 by ishii at sraoss.co.jp:

>>>>    * I am 100% CA certificates from Google are OK. However pgpool complains: ""SSL_connect": "certificate verify failed""
>>>>    * CloudSQL complains: db=[unknown],user=[unknown] LOG:  could not accept SSL connection: BAD_PACKET_LENGTH"
>>>>    * I had tried pgpool 4.2.6 and 4.3 with Alpine Edge
>>>>    * CloudSQL version of Postgres is 13
>>>>    * Pointing pgpool at /etc/ssl/certs (which included Google's certificates) made no difference
>>>>
>>>
>>> Are you trying to set up cert authentication between pgpool and
>>> CloudSQL? It's not supported in pgpool.
>>>
> [snip]
>
>>    I am trying to encrypt communication between pgpool and Cloud SQL. That's all. I do not require SSL authentication.
>>
>
> Ok. Can you enable log_min_messages to debug1 and take pgpool log?
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
Hi Tatsuo,

   below is one "loop" of the stderr from pgpool. Currently, in pgpool.conf:

failover_on_backend_error = off
ssl = on
ssl_ca_cert_dir = '/etc/ssl/certs'

2022-03-31 07:17:24: pid 58: LOG:  pool_ssl: "SSL_connect": "certificate verify failed"
2022-03-31 07:17:24: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:24: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:24: pid 58: LOG:  health check failed on node 0 but failover is disallowed for the node
2022-03-31 07:17:24: pid 76: DEBUG:  I am 76 accept fd 6
2022-03-31 07:17:24: pid 70: DEBUG:  I am 70 accept fd 6
2022-03-31 07:17:29: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:29: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:29: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:29: pid 58: LOG:  pool_ssl: "SSL_connect": "certificate verify failed"
2022-03-31 07:17:29: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:29: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:29: pid 58: LOG:  health check retrying on DB node: 0 (round:1)
2022-03-31 07:17:29: pid 64: DEBUG:  I am 64 accept fd 6
2022-03-31 07:17:29: pid 63: DEBUG:  I am 63 accept fd 6
2022-03-31 07:17:30: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:30: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:30: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:30: pid 58: LOG:  pool_ssl: "SSL_connect": "certificate verify failed"
2022-03-31 07:17:30: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:30: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:30: pid 58: LOG:  health check retrying on DB node: 0 (round:2)
2022-03-31 07:17:31: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:31: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:31: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:31: pid 58: LOG:  pool_ssl: "SSL_connect": "certificate verify failed"
2022-03-31 07:17:31: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:31: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:31: pid 58: LOG:  health check failed on node 0 but failover is disallowed for the node
2022-03-31 07:17:31: pid 62: ERROR:  Failed to check replication time lag
2022-03-31 07:17:31: pid 62: DEBUG:  pool_acquire_follow_primary_lock: lock was not held by anyone
2022-03-31 07:17:31: pid 62: DEBUG:  pool_acquire_follow_primary_lock: succeeded in acquiring lock
2022-03-31 07:17:31: pid 62: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:31: pid 62: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:31: pid 62: LOG:  pool_ssl: "SSL_connect": "certificate verify failed"
2022-03-31 07:17:31: pid 62: DEBUG:  pool_release_follow_primary_lock called
2022-03-31 07:17:34: pid 91: DEBUG:  I am 91 accept fd 6
2022-03-31 07:17:34: pid 70: DEBUG:  I am 70 accept fd 6
2022-03-31 07:17:36: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:36: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:36: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:36: pid 58: LOG:  pool_ssl: "SSL_connect": "certificate verify failed"
2022-03-31 07:17:36: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:36: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:36: pid 58: LOG:  health check retrying on DB node: 0 (round:1)
2022-03-31 07:17:37: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:37: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:37: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:37: pid 58: LOG:  pool_ssl: "SSL_connect": "certificate verify failed"
2022-03-31 07:17:37: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:37: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:37: pid 58: LOG:  health check retrying on DB node: 0 (round:2)
2022-03-31 07:17:38: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:38: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:38: pid 58: DEBUG:  attempting to negotiate a secure connection
2022-03-31 07:17:38: pid 58: LOG:  pool_ssl: "SSL_connect": "certificate verify failed"
2022-03-31 07:17:38: pid 58: DEBUG:  health check: clearing alarm
2022-03-31 07:17:38: pid 58: DEBUG:  health check: clearing alarm

Kind Regards

Mike