postgres user gets password rotated

[jankatins]

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.13.0
• Where do you run it - cloud or metal? Kubernetes or OpenShift? Azure k8s
• Are you running Postgres Operator in production? yes-ish
• Type of issue? Bug report

We added the postgres user to usersWithSecretRotation and it actually got the password rotated (or that's at least how I read the secret with it's dated user):

{
  "nextRotation": "2024-10-23T12:38:49Z",
  "password": "<redacted>",
  "username": "postgres240725"
}

but on the other hand I cannot find a postgres240725 user with \du when connecting on the cluster. I also was able to connect with psql -U postgres postgres, so the postgres user is also still allowed to login.

It seems credential rotation for postgres this is actually not supported, at least there is this place which explicitly excludes the superuser from password rotation:

https://github.com/zalando/postgres-operator/blob/41f5fe1dc93fb33b2bca98590a3227bde4fd6949/pkg/cluster/sync.go#L1081-L1085

The effect currently is that our sidecart cannot access the PG anymore:

Error opening connection to database" err="error querying postgresql version: pq: password authentication failed for user \"postgres\""                                           │
Error opening connection to database" dsn="postgresql://postgres:PASSWORD_REMOVED@localhost:5432/?sslmode=disable" err="pq: password authentication failed for user \"postgres\""