Re: How to read cleartext user password from pgsql database

Martijn van Oosterhout wrote:
> On Fri, Jul 14, 2006 at 03:21:01PM +0400, Eugene Prokopiev wrote:
>>Is it possible to read cleartext user password from pgsql database? In
>>this link
>>http://www.postgresql.org/docs/8.1/interactive/view-pg-user.html
>>explained that password always reads as ********. But I need to use
>>pgsql login/password as authentication info for another service.
>
> You can't get back the cleartext password, it's hashed.
> To see the hashed password you need to bypass the view, see pg_shadow.
> The docs should say something about how the hash is calcualted.

From advice of some previous thread, I developed the following function
to help me remember the password hash:

CREATE OR REPLACE FUNCTION public.authenticate_user(name, name)
RETURNS bool AS
'
DECLARE
ls_usename ALIAS FOR $1;
ls_passwd ALIAS FOR $2;
BEGIN
RETURN EXISTS(SELECT 1 FROM pg_shadow WHERE
''md5''||encode(digest(ls_passwd||ls_usename , ''md5''), ''hex'') = passwd);
END;'
LANGUAGE 'plpgsql' VOLATILE;

So, you can see that pg_shadow.passwd stores the md5 hash of the
concatinated plaintext password and username.

Regards,
Berend Tober