Обсуждение: md5 password valid and invalid after upgrading
Hello,
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
psql: error: connection to server at... failed: fe_sendauth: error sending password authentication
Steps:
- Setup Postgres 11.17 with an MD5 password
- Access the DB from a remote system that only has psql 9.6
- Upgrade DB to Postgres15.x
- Log into database using psql 9.6 and the original password
- Observe no issues
- Upgrade psql on remote system to 14.x
- 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.
psql14 does not appear to recognize that it needs to use md5 but psql9 doesn't know any better, so it works.
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>
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
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:
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
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.
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:
> 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
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:
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