By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

Verifying a user.

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Thu, Oct 14, 2004 at 05:27:20PM +0200, Thomas Hallgren wrote:
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?


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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
* Thomas Hallgren <th***@mailblocks.com> [2004-10-14 17:27:20 +0200]:
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)


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

Nov 23 '05 #3

P: n/a
* Steven Klassen <sk******@commandprompt.com> [2004-10-14 10:07:39 -0700]:
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;


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

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.