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
