Odd postgres12 upgrade is changing or munging a password?

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 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