Re: Verifying a user.

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/