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

Hi Laurenz,

Here's the connection method:
psql -h <url> -d <database>

I'm then prompted for a password and enter the password.

And the error:
psql: error: connection to server at <redacted> failed: fe_sendauth: error
sending password authentication

This same procedure worked before the upgrade to psql 14.8 on the remote
machine. This method of connection still works on my other environment that
is still using psql 9.6 on the remote machine/client with Postgres 15.3 on
the server.

SSL is enabled.

Thank you,
Kyle

On Wed, Sep 27, 2023 at 4:04 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

Kyle MacMillan <macattackftw@gmail.com> writes:

Here's the connection method:
psql -h <url> -d <database>

I'm then prompted for a password and enter the password.

And the error:
psql: error: connection to server at <redacted> failed: fe_sendauth: error
sending password authentication

What appears in the server's log when you do this?

regards, tom lane

Hi Tom,

The server was AWS's "serverless" Aurora service, I'm not sure where logs
for that would be found. I did not see any error logs generated.

I found a solution, but I still think the docs do not illustrate the
regression, at the very least. To resolve this issue I had to paste my
existing password into the master password of the server. I was able to
connect to the database after that "change".

No configuration changes, just re-inputting my existing password into the
server, then I was able to remotely connect with psql 14.

Kyle

On Wed, Sep 27, 2023 at 6:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Kyle MacMillan <macattackftw@gmail.com> writes:

The server was AWS's "serverless" Aurora service, I'm not sure where logs
for that would be found. I did not see any error logs generated.

I found a solution, but I still think the docs do not illustrate the
regression, at the very least. To resolve this issue I had to paste my
existing password into the master password of the server. I was able to
connect to the database after that "change".

That sounds like something you should discuss with AWS support.
Postgres per se doesn't even have a concept of a "master password".
I suspect you unwedged something in AWS's secret sauce, which
would not be a matter for community Postgres to document.

regards, tom lane

Ah, understood. Sorry for the confusion and thank you for your time!

Kyle

On Thu, Sep 28, 2023 at 6:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: