In my company each user has an separate login role. I am trying to write an PostgreSQL function to verify entered username and password against login role username and password. I create a user by using the query below:
CREATE USER foo WITH PASSWORD 'password'
When I look at the properties of user 'foo', I can see its encrypted password is 'md52ded2b65ce11cfe94e88a881982a333e'. I can get this value by
select 'md5'|| md5('password' || 'foo')
But, when I try to verify username and password by
select usename, passwd from pg_user where usename='foo' and passwd = 'md5'||md5('password' || 'foo')
no results return. Can somebody tell me what I am during wrong? Thanks....