Thread: How to read cleartext user password from pgsql database

Hi,

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.

--
Thanks,
Eugene Prokopiev

On Fri, Jul 14, 2006 at 03:21:01PM +0400, Eugene Prokopiev wrote:
> Hi,
>
> 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.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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