Password authentication fails for initially created users

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

• Which image of the operator are you using? registry.opensource.zalan.do/acid/postgres-operator:v1.10.1
• Where do you run it - cloud or metal? Kubernetes or OpenShift? K3S (Kubernetes)
• Are you running Postgres Operator in production? no
• Type of issue? question

I am encountering a really annoying problem which is related to authenticating the users. It seems like the users are created but can't be authenticated and it fails with the password authentication failed for user. The one thing I don't get is, when I get into the pod's shell, su to postgres, I log into the psql and change the alter the any user password and set even EXACTLY the same password as stays in secret, then I can authenticate. But for some reason I don't understand I can't authenticate any users which are initially created. This is also refered to the user "postgres" which causes some problems by creating the cluster itself.

The most things seems to be okay besides the user creation in the cluster but not this thing mentioned above. My cluster configuration looks like this:

kind: postgresql
apiVersion: acid.zalan.do/v1

metadata:
  name: postgres-cluster
  namespace: postgres-db
  labels:
    team: acid

spec:
  teamId: acid
  postgresql:
    version: "15"
  numberOfInstances: 1
  volume:
    size: 10Gi
    storageClass: postgres-sc
  users:
    foo: []
  databases:
    bar: foo
  allowedSourceRanges:
    # IP ranges to access your cluster go here
  
  resources:
    requests:
      cpu: 100m
      memory: 100Mi
    limits:
      cpu: 500m
      memory: 500Mi

After deploying this cluster (no matter if via kubectl or the postgres-operator-ui) the postgres-operator logs tells me this:

time="2023-11-24T13:30:27Z" level=debug msg="syncing pod disruption budgets" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:30:27Z" level=debug msg="syncing roles" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:30:28Z" level=warning msg="could not connect to Postgres database: pq: password authentication failed for user \"postgres\"" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:30:43Z" level=warning msg="could not connect to Postgres database: pq: password authentication failed for user \"postgres\"" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:30:58Z" level=warning msg="could not connect to Postgres database: pq: password authentication failed for user \"postgres\"" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:31:13Z" level=warning msg="could not connect to Postgres database: pq: password authentication failed for user \"postgres\"" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:31:28Z" level=warning msg="could not connect to Postgres database: pq: password authentication failed for user \"postgres\"" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:31:43Z" level=warning msg="could not connect to Postgres database: pq: password authentication failed for user \"postgres\"" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:31:58Z" level=warning msg="could not connect to Postgres database: pq: password authentication failed for user \"postgres\"" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:32:13Z" level=warning msg="could not connect to Postgres database: pq: password authentication failed for user \"postgres\"" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0
time="2023-11-24T13:32:13Z" level=error msg="could not sync roles: could not init db connection: could not init db connection: still failing after 8 retries" cluster-name=postgres-db/postgres-cluster pkg=cluster worker=0

After some time the cluster says it's running:

➜  ~kubectl -n postgres-operator get all                                                       
NAME                                        READY   STATUS    RESTARTS   AGE
pod/postgres-operator-5558f76c8f-n6zx8      1/1     Running   0          28h
pod/postgres-operator-ui-6b6bc94868-jccms   1/1     Running   0          28h

NAME                           TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)    AGE
service/postgres-operator      ClusterIP   10.43.95.41    <none>        8080/TCP   28h
service/postgres-operator-ui   ClusterIP   10.43.79.117   <none>        80/TCP     28h

NAME                                   READY   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/postgres-operator      1/1     1            1           28h
deployment.apps/postgres-operator-ui   1/1     1            1           28h

NAME                                              DESIRED   CURRENT   READY   AGE
replicaset.apps/postgres-operator-5558f76c8f      1         1         1       28h
replicaset.apps/postgres-operator-ui-6b6bc94868   1         1         1       28h

NAME                                                    IMAGE                             CLUSTER-LABEL      SERVICE-ACCOUNT   MIN-INSTANCES   AGE
operatorconfiguration.acid.zalan.do/postgres-operator   ghcr.io/zalando/spilo-15:3.0-p1   postgres-cluster   postgres-pod      -1              28h

➜  ~ kubectl -n postgres-db get all
NAME                     READY   STATUS    RESTARTS   AGE
pod/postgres-cluster-0   1/1     Running   0          3h1m

NAME                              TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE
service/postgres-cluster          ClusterIP   10.43.50.25     <none>        5432/TCP   3h1m
service/postgres-cluster-repl     ClusterIP   10.43.145.239   <none>        5432/TCP   3h1m
service/postgres-cluster-config   ClusterIP   None            <none>        <none>     3h1m

NAME                                READY   AGE
statefulset.apps/postgres-cluster   1/1     3h1m

NAME                                        TEAM   VERSION   PODS   VOLUME   CPU-REQUEST   MEMORY-REQUEST   AGE    STATUS
postgresql.acid.zalan.do/postgres-cluster   acid   15        1      10Gi     100m          100Mi            3h1m   Running

@EDIT

Somehow I have observed, that this issue doesn't appear when I don't use storage-class. I am creating my SC and PV like this:

apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  namespace: postgres-db
  name: postgres-sc
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer
---
apiVersion: v1
kind: PersistentVolume
metadata:
  namespace: postgres-db
  name: postgres-pv
  labels:
    type: local
spec:
  storageClassName: postgres-sc
  persistentVolumeReclaimPolicy: Retain
  capacity:
    storage: 10Gi
  volumeMode: Filesystem
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /k8s/postgres

Start with the cluster specification, especially the users, PostgreSQL version, storageClass, and the operator warnings showing authentication failures for postgres. Compare behavior with and without the custom StorageClass/PersistentVolume, then use the observed password mismatch and role-sync retry failure as the completion criteria.