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:

postgres-operator/pkg/cluster/sync.go

Lines 1081 to 1085 in 41f5fe1

	// if password rotation is enabled update password and username if rotation interval has been passed
	// rotation can be enabled globally or via the manifest (excluding the Postgres superuser)
	rotationEnabledInManifest := secretUsername != constants.SuperuserKeyName &&
	(slices.Contains(c.Spec.UsersWithSecretRotation, secretUsername) ||
	slices.Contains(c.Spec.UsersWithInPlaceSecretRotation, secretUsername))

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\""

[FxKu]

Uh, yeah so rotation for postgres user should currently be avoided because this is the role which the operator uses for most it's database actions. Ideally, it should use it's own role, but it is historically grown - and easy....

The flow with the secrets is usually that the operator changes/syncs them first then connects to the database with postgres user and alters the roles. The rotation feature was primarily designed for application users.