Re: role passwords and md5()

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
>