I'm connected to a database and I want to verify that a username and
password for some user is correct. I know I can verify a users existence
by doing:

select exists(select * from pg_user where usename = $1)

but I would like to verify the correctness of the password as well. Is
there a way to do that using SQL?

Regards,

Thomas Hallgren


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Thu, Oct 14, 2004 at 05:27:20PM +0200, Thomas Hallgren wrote:
You could look at the passwd field in pg_shadow, but you'll need
to be a database superuser to do that (but see below), and you'll
need to know what value the passwd field should have. I don't know
about earlier versions of PostgreSQL, but in 7.4.5 and 8.0.0beta3,
if the password_encryption configuration variable is set to 'on'
(the default), then the password is stored as:

'md5' || MD5(password || user)

That is, if user johndoe has the password opensesame, then the
value stored in the passwd field will be:

'md5' || MD5('opensesame' || 'johndoe')
md5a7350a3bb54a151a858758c7266c57bd

If password_encryption is 'off' then the password is stored in
plaintext.

You can avoid the need to be a database superuser with a stored
procedure that a superuser created with SECURITY DEFINER:

CREATE OR REPLACE FUNCTION valid_user(TEXT, TEXT) RETURNS BOOLEAN AS '
SELECT EXISTS(
SELECT * FROM pg_shadow
WHERE usename = $1 AND passwd = ''md5'' || MD5($2 || $1)
);
' LANGUAGE SQL SECURITY DEFINER;

You can use this function as non-superuser:

=> SELECT valid_user('johndoe', 'opensesame');
valid_user
------------
t

You might wish to revoke all privileges on this function and grant
EXECUTE only to those users who should be using it:

REVOKE ALL ON FUNCTION valid_user(TEXT, TEXT) FROM public;
GRANT EXECUTE ON FUNCTION valid_user(TEXT, TEXT) TO somebody;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

* Thomas Hallgren <thhal (AT) mailblocks (DOT) com> [2004-10-14 17:27:20 +0200]:

You can get at the md5 hashed passwords in the pg_shadow table
provided your user has the requisite permission.

There may be something out there already that does this for you, but
this is how the function/query might look if you went the Brute Force
(tm) route.

CREATE FUNCTION check_passwd(text,text) RETURNS boolean AS 'SELECT
CASE WHEN passwd = md5($2) THEN true ELSE false END FROM pg_shadow
WHERE usename = $1;' LANGUAGE sql;

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

* Steven Klassen <sklassen (AT) commandprompt (DOT) com> [2004-10-14 10:07:39 -0700]:

Strike that - go with what Michael recommended. I glanced at the
password and didn't realize it was brewed with more than just the
password string.

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org