Looks like the password gets cleared when you rename a role. Regards, Ben "Ben Trewern" <ben.trewern@xxxxxxxxxxxxxxxxxx> wrote in message news:evnt7k$14td$1@xxxxxxxxxxxxxxx >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 >>> >> >> > >
