role passwords and md5()

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