I thought I read this be for I sent it. :-( What I meant to say was: Does the password hash change (and how?) Or is the original username kept somewhere is the system tables? Regards, Ben "Ben Trewern" <ben.trewern@xxxxxxxxxxxxxxxxxx> wrote in message news:evnpgi$md3$1@xxxxxxxxxxxxxxx > How does this work when you rename a role? Does the is the password hash > changed (and how?) or is the original username kept somewhere in the > system tables? > > Regards, > > Ben > > "Andrew Kroeger" <andrew@xxxxxxxxxxxxxxxxxx> wrote in message > news:461E27BA.7020001@xxxxxxxxxxxxxxxxxxxxx >> Lutz Broedel wrote: >>> Dear list, >>> >>> I am trying to verify the password given by a user against the system >>> catalog. Since I need the password hash later on, I can not just use the >>> authentication mechanism for verification, but need to do this in SQL >>> statements. >>> Unfortunately, even if I set passwords to use MD5 encryption in >>> pg_hba.conf, the SQL function MD5() returns a different hash. >>> >>> A (shortened) example: >>> CREATE ROLE my_user WITH ENCRYPTED PASSWORD 'my_password'; >>> >>> SELECT * FROM pg_authid >>> WHERE rolname='my_user' AND rolpassword=MD5('my_password'); >>> >>> Any ideas, what to do to make this work? >>> Best regards, >>> Lutz Broedel >> >> A quick look at the source shows that the hashed value stored in >> pg_authid uses the role name as a salt for the hashing of the password. >> Moreover, the value in pg_authid has the string "md5" prepended to the >> hash value (I imagine to allow different hash algorithms to be used, but >> I haven't personally seen anything but "md5"). >> >> Given your example above, the following statement should do what you are >> looking for: >> >> SELECT * FROM pg_authid WHERE rolname='my_user' AND rolpassword = 'md5' >> || md5('my_password' || 'my_user'); >> >> Hope this helps. >> >> Andrew >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >> > >
