The application im developing works integrated with postgresql(my application's user is the postgre user). The application's user only access postgre data trough stored functions, so I want to create the following function to allow the user changing his password :

CREATE FUNCTION work_management.proc_change_pass(IN p_username character varying, IN p_old_password character varying, IN p_new_password character varying) RETURNS void AS...

As you can see, I want the user to type his old password before allowing the change. My question is :" How do I check the user password against the md5 encrypted password in 'pg_shadow' view?" I tried to compare against the value in this view using the function "md5(string)" to encrypt the old password, but this function returns the md5 hash as hexadecimal in a variable of type "text" so the compare doesnt work directly , maybe if theres some other function to convert the "hex text" to "ascii text it will work, but I didnt find any such function(im still a newbie at manipulating data in postgre, thats why i need a function to do such thing)...

Any clues are appreciated.

Can't you cast the result as text?

i.e.

md5(password_str)::text

Will there be security implication in passing unencrypted passwords between your front end application and the stored function?

Yes, that might work. I didnt know you could cast the datatypes like that, thank you very much