Re: MD5 password storage - should be the same everywhere?

On 05/25/2015 01:41 PM, Francisco Reyes wrote:
> Should the same password, stored in MD5, be the same across different DBs?
>
> If I did either:
> create user SomeUser encrypted password 'SomePassword';
> alter user SomeUser encrypted password 'SomePassword';
>
> On multiple machines, should the MD5 be the same?
> using
> select rolname, rolpassword,rolcanlogin from pg_catalog.pg_authid where
> rolname = 'SomeUser';
>
> Should the MD5 be the same?

I understood that is just a md5 hash of the password and the username
with the string md5 pre-appended, so it should be the same.

>
> I see one machine where a user has a different md5 but the password is
> the same as several other machines. In other words the MD5 is different,
> but the password is the same. Is that expected?
>
> In other words say I have machine1... machine5 and I have user with
> password 'SomePassword' in 4 of the machines the MD5 is the same, but in
> (say for example) machine 2 the MD5 is different. Yet I can login to the
> user in that machine with 'SomePassword' as password.
>
> Restoring from this "machine2" to another machine and none of the
> passwords worked.

That is not good.

>
> Any pointers/ideas of what is going on?

Not without some more information.

1) What Postgres version(s)?

2) Are all the machines the same as regards OS, architecture, and
Postgres version? Otherwise what are the specifications for the machines?

3) Has there been a crash or some other problem with 'machine 2' lately?

4) How did the data get into the various machines? Dump/restore,
pg_upgrade, replication, or some other method?

5) Are the Postgres instances standalone or are they tied together by
some replication method?

6) What are the pg_hba.conf settings on 'machine 2'?

7) Anything else you might think is relevant, in particular any log
information.

>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com