Обсуждение: Verifying a user.

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

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/

* Thomas Hallgren <thhal@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

* Steven Klassen <sklassen@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