How to pass postgres credentials to DB pod after disabling local trust in pg_hba.conf

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

• Which image of the operator are you using?
  ghcr.io/zalando/postgres-operator:v1.11.0
• Where do you run it - cloud or metal? Kubernetes or OpenShift?
  AWS
• Are you running Postgres Operator in production? [yes | no]
  No, but soon going to GA
• Type of issue? [Bug report, question, feature request, etc.]
  Bug

In order to go GA, we need to enable some security hardening on our postgres pods so I tried updating the pg_hba.conf file by adding the following in the postgres-operator manifest (basically don't allow anyone to enter postgres by either kubectl exec or port-forwarding the DB pod, at least ask for a password) -

spec:
  dockerImage: ghcr.io/zalando/spilo-15:3.0-p1
  teamId: "acid"
  numberOfInstances: 1
    users:  # Application/Robot users
      test-user: []
      zalando:
        - superuser
        - createdb
  databases:
    test_db: test-user
  patroni:
    pg_hba:
    - local   all             all                                   md5
    - hostssl all             +zalandos    127.0.0.1/32       pam
    - host    all             all                127.0.0.1/32       md5
    - hostssl all             +zalandos    ::1/128            pam
    - host    all             all                ::1/128            md5
    - local   replication     standby                    trust
    - hostssl replication     standby all                md5
    - hostnossl all           all                all                reject
    - hostssl all             +zalandos    all                pam
    - hostssl all             all                all                md5

I basically copied the default pg_hba.conf file and then changed trust -> md5 for local but getting following errors in the database pod -

2024-05-28 16:48:45,710 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
2024-05-28 16:48:55,627 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:49:19.180 UTC [24] LOG Starting pgqd 3.5
2024-05-28 16:49:19.180 UTC [24] LOG auto-detecting dbs ...
2024-05-28 16:49:19.183 UTC [24] ERROR connection error: PQconnectPoll
2024-05-28 16:49:19.183 UTC [24] ERROR libpq: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
2024-05-28 16:49:25,628 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:49:49.209 UTC [24] LOG {ticks: 0, maint: 0, retry: 0}
2024-05-28 16:49:55,625 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:50:19.209 UTC [24] LOG {ticks: 0, maint: 0, retry: 0}
2024-05-28 16:50:19.212 UTC [24] ERROR connection error: PQconnectPoll
2024-05-28 16:50:19.212 UTC [24] ERROR libpq: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
2024-05-28 16:50:25,635 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:50:49.240 UTC [24] LOG {ticks: 0, maint: 0, retry: 0}
2024-05-28 16:50:55,624 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:51:19.239 UTC [24] ERROR connection error: PQconnectPoll
2024-05-28 16:51:19.239 UTC [24] ERROR libpq: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
2024-05-28 16:51:19.243 UTC [24] LOG {ticks: 0, maint: 0, retry: 0}

If I switch back to trust then I don't see these errors. Please suggest how to fix this?
Another thing, please add me to you slack community as we use the operator a lot and would like to keep contributing as that will help us too.

Start with the postgres-operator manifest's patroni.pg_hba settings and the database pod logs shown here. Trace how the operator and Spilo image supply credentials for local psql and pgqd connections after local trust is changed. Done means identifying the supported credential configuration that prevents the repeated authentication errors.