SEO

permalink · #1 (**permalink**) 04-09-2008, 10:37 AM

Hi,

Is it possible to read cleartext user password from pgsql database? In
this link
http://www.postgresql.org/docs/8.1/i...w-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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

permalink · #2 (**permalink**) 04-09-2008, 10:37 AM

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/i...w-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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEt4piIB7bNG8LQkwRAk+xAKCKwygzj0ySutq1BWK4qe +LNzYnogCfYtMj
uxBEg8LKpL5D0mbq/E/DVtY=
=n1Jv
-----END PGP SIGNATURE-----

permalink · #3 (**permalink**) 04-09-2008, 10:37 AM

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/i...w-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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to read cleartext user password from pgsql database