After upgrade to 1.9.0 from 1.8.2 authentication is failing

Please, answer some short questions which should help us to understand your problem / question better?

• Which image of the operator are you using?
  • postgresql-operator: registry.opensource.zalan.do/acid/postgres-operator:v1.9.0
  • spilo: ghcr.io/zalando/spilo-15:2.1-p9
  • pgbouncer: registry.opensource.zalan.do/acid/pgbouncer:master-26
• Where do you run it - cloud or metal? Kubernetes or OpenShift?
  • AWS EC2, Kubernetes(1.24.x), helm chart
• Are you running Postgres Operator in production?
  • Yes
• Type of issue?
  • Authentication failure, cannot connect

Issue:

After upgrading to v1.9.0 of the operator from v1.8.2 we immediately realized the app using the DB was failing to connect. Looking at the Connection pooler, we were getting logs about ssl and failed authentication. After much digging into what is going on, have seemed to narrow the issue down to be related to the pg_hba.conf config and most specifically the fix was to change this line

from: hostssl all all all md5

to: hostssl all all all trust

We have another cluster in another kubernetes cluster that has not been upgraded we have been using as reference to see differences in configuration and from what we have been able to discern, there are not really any differences in configuration. We did not change any configuration for the initial upgrade and we are not using significant customization in configuration

pg_hba.conf is default we are seeing logs like this when attempting to log in via the poolers or directly the servers(however, logins are successful when ssh session directly on pod, regardless of pg_hba.conf):

2023-07-28 17:34:58.294 UTC,"appuser","appuser",245,"100.99.120.230:38712",64c3fc42.f5,2,"authentication",2023-07-28 17:34:58 UTC,9/211,0,FATAL,28P01,"password authentication failed for user ""appuser""","Password does not match for user ""appuser"".
Connection matched pg_hba.conf line 12: ""hostssl all             all                all                md5""",,,,,,,,"","client backend",,0

We have not found, at this point, what, if anything, changed with authentication and we would love to know what we need to do to resolve this.

Start by comparing the generated pg_hba.conf and authentication logs between the v1.8.2 and v1.9.0 clusters, focusing on the hostssl md5 entry and the pooler connection path. Reproduce the upgrade if possible, then identify the authentication regression and verify the documented or configuration-based fix against both direct and pooler connections.