Re: role passwords and md5()

Looks like the password gets cleared when you rename a role.

Regards,

Ben
"Ben Trewern" <ben(dot)trewern(at)_nospam_mowlem(dot)com> wrote in message
news:evnt7k$14td$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
>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(dot)trewern(at)_nospam_mowlem(dot)com> wrote in message
> news:evnpgi$md3$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
>> 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(at)sprocks(dot)gotdns(dot)com> wrote in message
>> news:461E27BA(dot)7020001(at)sprocks(dot)gotdns(dot)com(dot)(dot)(dot)
>>> 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
>>>
>>
>>
>
>