The postgresql::user define is used to create Postgres users. Initially the user is created and then a define runs an ALTER on the user to set the password. To prevent the password being set in every Puppet run, there's an onlyif check for the exec, which runs a "SELECT 1 from pg_authid WHERE rolname = $USER and rolpassword IS DISTINCT FROM $MD5PASSWORD" (with the latter being based on the md5() function from Ruby against the password obtained from Hiera).

This worked fine so far, but starting with Bookworm/PostgreSQL switched away from the known broken md5 to scram-sha-256, so we can't easily reapply the comparison scheme (we'd need to obtain the salt from Postgres and then add custom code to compute the hash, which seems brittle). Continuing to use md5 with Postgres 15 is also not an appealing option (I couldn't find an obvious way to configure it anyway).

One way forward would be to untangle the user creation and password changes:

• Modify the postgresql::user Puppet code when run on bookworm and later to only check for the presence of the user, but don't compare the hash
• Create a cookbook which gets run after a user password is changed which removes existing user definitions from pg_hba.conf and forces a puppet run to have it recreate users

I think password changes are quite rare (they are at least for Netbox and Puppetdb), but I'm adding other people involved in managing PostgreSQL-using services for additional proposals/comments/objection.