Thread: role passwords and md5()
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
--
To verify the digital signature, you need to load the following certificate:
https://pki.pca.dfn.de/uh-ca/pub/cacert/rootcert.crt
Attachment
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
Looks like the password gets cleared when you rename a role.
Regards,
Ben
"Ben Trewern" <ben.trewern@_nospam_mowlem.com> wrote in message
news:evnt7k$14td$1@news.hub.org...
>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@_nospam_mowlem.com> wrote in message
> news:evnpgi$md3$1@news.hub.org...
>> 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@sprocks.gotdns.com> wrote in message
>> news:461E27BA.7020001@sprocks.gotdns.com...
>>> 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
>>>
>>
>>
>
>
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@sprocks.gotdns.com> wrote in message
news:461E27BA.7020001@sprocks.gotdns.com...
> 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
>
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@_nospam_mowlem.com> wrote in message
news:evnpgi$md3$1@news.hub.org...
> 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@sprocks.gotdns.com> wrote in message
> news:461E27BA.7020001@sprocks.gotdns.com...
>> 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
>>
>
>