Re: md5 password valid and invalid after upgrading

On Tue, 2023-09-26 at 19:14 -0400, Kyle MacMillan wrote:
> Issue:
> Unable to log into database with md5 password, only after upgrading remote system's psql version.
>
> Error:
> psql: error: connection to server at... failed: fe_sendauth: error sending password authentication
>
> Steps:
>    1. Setup Postgres 11.17 with an MD5 password
>    2. Access the DB from a remote system that only has psql 9.6 
>    3. Upgrade DB to Postgres15.x 
>    4. Log into database using psql 9.6 and the original password
>    5. Observe no issues
>    6. Upgrade psql on remote system to 14.x
>    7. Attempt to log in and see an error
> The documentation regarding Postgres 14 does not specify the old password will not work.
> It specifies the default was changed and that new passwords will be stored as SHA256.
> I am not using boolean-like values for my current password.
>
> psql14 does not appear to recognize that it needs to use md5 but psql9 doesn't know any better, so it works.

Are you sure that there is no additional error message like "out of memory"
or "could not encrypt password"? Was step 4 executed on the remote system
that was later upgraded (so that we can rule out network problems etc.)?
Is SSL enabled on the server?

Yours,
Laurenz Albe