Thread: Odd postgres12 upgrade is changing or munging a password?
Going from 9.5 to 12 and 2 times now, I've had a password either go missing or munged. I've had to add an alter statement at the end of the upgrade.
The DB is functioning fine, shut it down, do the upgrade and the password is munged. Seems like an odd occurrence, we have not noted any other weird issues.
Anyone else see or hear of this?
Thanks
Tory
On 5/7/20 11:55 AM, Tory M Blue wrote: > Going from 9.5 to 12 and 2 times now, I've had a password either go > missing or munged. I've had to add an alter statement at the end of the > upgrade. What are the commands you are using? Is it the same password? > > The DB is functioning fine, shut it down, do the upgrade and the > password is munged. Seems like an odd occurrence, we have not noted any > other weird issues. > > Anyone else see or hear of this? > > Thanks > Tory -- Adrian Klaver adrian.klaver@aklaver.com
Yes same password, I'm using a basic alter command to put the right password back.
I'm doing another upgrade in an hour, and will do some more checks to see if it's trying to use another password or what. I obviously can't read the password from the file , so knowing if it's munged or other, I'm not sure is possible.
Upgrade command i'm running
time /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin/ --new-bindir /usr/pgsql-12/bin/ --old-datadir /pgsql/9.5/data --new-datadir /pgsql/12/data --link
So it's very odd. and I've not experienced this in other environments, it's just this one. Now it's a bigger data set, but very odd.
I'm also not seeing any other data issues, just seems to be this one password.
Thanks,
If there are commands I can run on the data before I do an alter, to give someone more info, let me know
Tory
On Thu, May 7, 2020 at 12:08 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 5/7/20 11:55 AM, Tory M Blue wrote:
> Going from 9.5 to 12 and 2 times now, I've had a password either go
> missing or munged. I've had to add an alter statement at the end of the
> upgrade.
What are the commands you are using?
Is it the same password?
>
> The DB is functioning fine, shut it down, do the upgrade and the
> password is munged. Seems like an odd occurrence, we have not noted any
> other weird issues.
>
> Anyone else see or hear of this?
>
> Thanks
> Tory
--
Adrian Klaver
adrian.klaver@aklaver.com
On 5/7/20 12:24 PM, Tory M Blue wrote: > Yes same password, I'm using a basic alter command to put the right > password back. > > I'm doing another upgrade in an hour, and will do some more checks to > see if it's trying to use another password or what. I obviously can't > read the password from the file , so knowing if it's munged or other, > I'm not sure is possible. > > Upgrade command i'm running > > time /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin/ > --new-bindir /usr/pgsql-12/bin/ --old-datadir /pgsql/9.5/data > --new-datadir /pgsql/12/data --link > > So it's very odd. and I've not experienced this in other environments, > it's just this one. Now it's a bigger data set, but very odd. Anything different about this environment e.g. locale? What is the encoding/character set for the database? > > I'm also not seeing any other data issues, just seems to be this one > password. I'm assuming you have super user access so you could look at the password in: https://www.postgresql.org/docs/12/view-pg-shadow.html on the old server and then on the new server. > > Thanks, > > If there are commands I can run on the data before I do an alter, to > give someone more info, let me know > > Tory > > On Thu, May 7, 2020 at 12:08 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 5/7/20 11:55 AM, Tory M Blue wrote: > > Going from 9.5 to 12 and 2 times now, I've had a password either go > > missing or munged. I've had to add an alter statement at the end > of the > > upgrade. > > What are the commands you are using? > > Is it the same password? > > > > > The DB is functioning fine, shut it down, do the upgrade and the > > password is munged. Seems like an odd occurrence, we have not > noted any > > other weird issues. > > > > Anyone else see or hear of this? > > > > Thanks > > Tory > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, 2020-05-07 at 11:55 -0700, Tory M Blue wrote: > Going from 9.5 to 12 and 2 times now, I've had a password either go missing or munged. > I've had to add an alter statement at the end of the upgrade. > > The DB is functioning fine, shut it down, do the upgrade and the password is munged. > Seems like an odd occurrence, we have not noted any other weird issues. > > Anyone else see or hear of this? The only explanation I can come up with is that "password_encryption" is set to "scram-sha-256" on the v12 server. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On Thu, May 7, 2020 at 12:32 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 5/7/20 12:24 PM, Tory M Blue wrote:
> Yes same password, I'm using a basic alter command to put the right
> password back.
>
> I'm doing another upgrade in an hour, and will do some more checks to
> see if it's trying to use another password or what. I obviously can't
> read the password from the file , so knowing if it's munged or other,
> I'm not sure is possible.
>
> Upgrade command i'm running
>
> time /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin/
> --new-bindir /usr/pgsql-12/bin/ --old-datadir /pgsql/9.5/data
> --new-datadir /pgsql/12/data --link
>
> So it's very odd. and I've not experienced this in other environments,
> it's just this one. Now it's a bigger data set, but very odd.
Anything different about this environment e.g. locale?
What is the encoding/character set for the database?
>
> I'm also not seeing any other data issues, just seems to be this one
> password.
I'm assuming you have super user access so you could look at the
password in:
https://www.postgresql.org/docs/12/view-pg-shadow.html
on the old server and then on the new server.
It absolutely did change the password. Only 1 password out of 4 accounts, but it changed it. The MD5 is different so this is verified. But why, how?
Tory
On Thu, May 7, 2020 at 11:41 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2020-05-07 at 11:55 -0700, Tory M Blue wrote:
> Going from 9.5 to 12 and 2 times now, I've had a password either go missing or munged.
> I've had to add an alter statement at the end of the upgrade.
>
> The DB is functioning fine, shut it down, do the upgrade and the password is munged.
> Seems like an odd occurrence, we have not noted any other weird issues.
>
> Anyone else see or hear of this?
The only explanation I can come up with is that "password_encryption" is set to
"scram-sha-256" on the v12 server.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
If that was the case, wouldn't it change the others as well? Why a single password.
Super strange indeed :)
Tory
On 5/8/20 12:03 PM, Tory M Blue wrote: > > > On Thu, May 7, 2020 at 12:32 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 5/7/20 12:24 PM, Tory M Blue wrote: > > Yes same password, I'm using a basic alter command to put the right > > password back. > > > > I'm doing another upgrade in an hour, and will do some more > checks to > > see if it's trying to use another password or what. I obviously > can't > > read the password from the file , so knowing if it's munged or > other, > > I'm not sure is possible. > > > > Upgrade command i'm running > > > > time /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin/ > > --new-bindir /usr/pgsql-12/bin/ --old-datadir /pgsql/9.5/data > > --new-datadir /pgsql/12/data --link > > > > So it's very odd. and I've not experienced this in other > environments, > > it's just this one. Now it's a bigger data set, but very odd. > > Anything different about this environment e.g. locale? > > What is the encoding/character set for the database? > > > > > I'm also not seeing any other data issues, just seems to be this one > > password. > > I'm assuming you have super user access so you could look at the > password in: > > https://www.postgresql.org/docs/12/view-pg-shadow.html > > on the old server and then on the new server. > > > It absolutely did change the password. Only 1 password out of 4 > accounts, but it changed it. The MD5 is different so this is verified. > But why, how? To maybe answer that: 1) Can you find out what the clear text version of the password is? Not necessary to share here, just indicate anything special about it. 2) What is the encoding/character set for the database? 3) What is the OS and version? 4) Has the OS been recently updated/upgraded? > > Tory -- Adrian Klaver adrian.klaver@aklaver.com
On 5/8/20 12:11 PM, Tory M Blue wrote: > > > On Thu, May 7, 2020 at 11:41 PM Laurenz Albe <laurenz.albe@cybertec.at > <mailto:laurenz.albe@cybertec.at>> wrote: > > On Thu, 2020-05-07 at 11:55 -0700, Tory M Blue wrote: > > Going from 9.5 to 12 and 2 times now, I've had a password either > go missing or munged. > > I've had to add an alter statement at the end of the upgrade. > > > > The DB is functioning fine, shut it down, do the upgrade and the > password is munged. > > Seems like an odd occurrence, we have not noted any other weird > issues. > > > > Anyone else see or hear of this? > > The only explanation I can come up with is that > "password_encryption" is set to > "scram-sha-256" on the v12 server. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > > If that was the case, wouldn't it change the others as well? Why a > single password. I would first confirm what password_encryption in postgresql.conf is set to. By default it is set to 'md5'. > > Super strange indeed :) > > Tory -- Adrian Klaver adrian.klaver@aklaver.com
On 5/8/20 12:16 PM, Tory M Blue wrote: Please reply to list also. Ccing list. > > To maybe answer that: > > 1) Can you find out what the clear text version of the password is? Not > necessary to share here, just indicate anything special about it. > > > I can't, MD5 hashed, I don't know of a way to turn that into a > password. But I did pull the MD5 hashs from all my DB's in all > environments and none match, so this ons is a ghost. So what are you using to do the ALTER you mentioned earlier? > > > 2) What is the encoding/character set for the database? > > > UTF8 > > > 3) What is the OS and version? > > > CentOS 7.2/7.7 > > > 4) Has the OS been recently updated/upgraded? > > Not on this box, I will be doing a kernel upgrade from 7.2 to 7.7 > after. But it's not something that has happened between upgrades. 9.5 > has been running on an older 7.2 kernel for a few years. So to be clear both the 9.5 and 12 instance are running on the same OS version? > > > > > > Thanks > Tory -- Adrian Klaver adrian.klaver@aklaver.com
On 5/8/20 12:28 PM, Tory M Blue wrote: > > So what are you using to do the ALTER you mentioned earlier? > > alter user 'user' with password 'password'; I run this after, because > as I've shown the upgrade is changing this single password, the why is > the question. I can recover, by changing it obviously as seen above. > This allows the production workload to work :) So are just making up a new password. > > So to be clear both the 9.5 and 12 instance are running on the same OS > version? > > Same exact box, same hardware, same kernel same everything. I'm > literally, sshing into my 9.5 box that has been running for years and > running a postgres upgrade.. This one particular password is being > changed, and again since it's an MD5 hash, there is no way for me (or > anyone I think) to map that to a regular pass.. > I'm out of ideas at the moment. Got to go finish cutting down a tree, maybe the chainsaw will shake an another idea loose:) > > > -Tory -- Adrian Klaver adrian.klaver@aklaver.com