Thread: MD5 password storage - should be the same everywhere?
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 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. Any pointers/ideas of what is going on?
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.klaver@aklaver.com
On 2015-05-25 17:58, Adrian Klaver wrote: > On 05/25/2015 01:41 PM, Francisco Reyes wrote: >> 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. On version 9 definitely, as documented: http://www.postgresql.org/docs/9.3/static/catalog-pg-authid.html "The MD5 hash will be of the user's password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe." Although I'm surprised it's not seeded, or even using a strong hash, but that's a different subject. -- http://yves.zioup.com gpg: 4096R/32B0F416
On 2015-05-25 17:58, Adrian Klaver wrote: > On 05/25/2015 01:41 PM, Francisco Reyes wrote: >> 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. On version 9 definitely, as documented: http://www.postgresql.org/docs/9.3/static/catalog-pg-authid.html "The MD5 hash will be of the user's password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe." Although I'm surprised it's not seeded, or even using a strong hash, but that's a different subject. -- http://yves.zioup.com gpg: 4096R/32B0F416
On 05/25/2015 08:41 PM, Yves Dorfsman wrote: > On 2015-05-25 17:58, Adrian Klaver wrote: >> On 05/25/2015 01:41 PM, Francisco Reyes wrote: >>> 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. > > On version 9 definitely, as documented: > http://www.postgresql.org/docs/9.3/static/catalog-pg-authid.html > > "The MD5 hash will be of the user's password concatenated to their user name. > For example, if user joe has password xyzzy, PostgreSQL will store the md5 > hash of xyzzyjoe." > > > Although I'm surprised it's not seeded, or even using a strong hash, but > that's a different subject. See here for more detail: http://www.postgresql.org/docs/9.4/static/protocol-flow.html AuthenticationMD5Password > -- Adrian Klaver adrian.klaver@aklaver.com
On 05/25/2015 07:58 PM, Adrian Klaver wrote: > On 05/25/2015 01:41 PM, Francisco Reyes wrote: > 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. Mistery solved.. Because I usually do "script" of most of my work for audits I was able to track down what happened. Original request was to get data from 'machine 2', but it was later changed. Found something in the output of my script that lead me to believe maybe the roles did not come from 'machine 2'. Was able to find the source machine by using select rolname, rolpassword,rolcanlogin from pg_catalog.pg_authid where rolname = 'SomeUser'; Against machines I thought could have been the source and found a matching one.