Thread: Authentication Question

Authentication Question

From

Jason Hihn

Date:

16 December 2003, 14:58:08

I am trying to decide between using 1 account for web DB access, and doing
the auth myself, or using the database's built in auth. It's more steps to
use the database's, but its also more secure.

1st problem with using the DB's auth: We want usernames to be email
addresses, but it chokes on the @ sign:
# create user jh@paytimepayroll.com with password 'cow';
ERROR:  syntax error at or near "@" at character 16

I can't figure out how to escape the @ sign.

2nd problem with using the DB's auth:
I need to grant all permissions on all objects in a database to the new
user. I have get to figure out how this is done easily. I had to do it
table-by-table!

Now if I don't use the built in auth, I have to do permission checks myself.
But the bigger problem is I don't want to store plain text passwords in MY
users table, so I was going to use md5. But then I checked, and the
pg_shadow entry has 'md5' prepended to what I assume is the md5 hash of my
password. But when I ask for a md5 hash of my password, I don't get the same
number.
ex:
user | passwd
-------------------------------------------
jh  | md5a8249f07eb642f6e9f4692db0519b4f7

#select md5('mypassword');
               md5
----------------------------------
 a78a900156649857f407cf67b1cd12cd

If the experts could weigh in, I'd appreciate it!

Jason Hihn
Paytime Payroll

Re: Authentication Question

From

Bruno Wolff III

Date:

16 December 2003, 15:57:55

On Tue, Dec 16, 2003 at 10:54:47 -0500,
  Jason Hihn <jhihn@paytimepayroll.com> wrote:
> I am trying to decide between using 1 account for web DB access, and doing
> the auth myself, or using the database's built in auth. It's more steps to
> use the database's, but its also more secure.
>
> 1st problem with using the DB's auth: We want usernames to be email
> addresses, but it chokes on the @ sign:
> # create user jh@paytimepayroll.com with password 'cow';
> ERROR:  syntax error at or near "@" at character 16
>
> I can't figure out how to escape the @ sign.

I believe double quotes are the correct way to allow for special charcters
in the user name.

>
> 2nd problem with using the DB's auth:
> I need to grant all permissions on all objects in a database to the new
> user. I have get to figure out how this is done easily. I had to do it
> table-by-table!

The best way to do this is to give a group access to all of the objects and
then just add or remove users from that group as needed.

> Now if I don't use the built in auth, I have to do permission checks myself.
> But the bigger problem is I don't want to store plain text passwords in MY
> users table, so I was going to use md5. But then I checked, and the
> pg_shadow entry has 'md5' prepended to what I assume is the md5 hash of my
> password. But when I ask for a md5 hash of my password, I don't get the same
> number.

I don't know for sure, but I would expect that something is being used as
a salt. This is normal as it makes using prebuilt dictionaries more
difficult and prevents you from being able to tell if two accounts
have the same password just by looking at the hash.

> ex:
> user | passwd
> -------------------------------------------
> jh  | md5a8249f07eb642f6e9f4692db0519b4f7
>
> #select md5('mypassword');
>                md5
> ----------------------------------
>  a78a900156649857f407cf67b1cd12cd
>
> If the experts could weigh in, I'd appreciate it!

Re: Authentication Question

From

Peter Eisentraut

Date:

16 December 2003, 15:59:04

Jason Hihn wrote:
> 1st problem with using the DB's auth: We want usernames to be email
> addresses, but it chokes on the @ sign:
> # create user jh@paytimepayroll.com with password 'cow';
> ERROR:  syntax error at or near "@" at character 16

create user "jh@paytimepayroll.com" ...;

> 2nd problem with using the DB's auth:
> I need to grant all permissions on all objects in a database to the
> new user. I have get to figure out how this is done easily. I had to
> do it table-by-table!

Use groups.

Re: Authentication Question

From

Michael Fuhr

Date:

16 December 2003, 16:09:48

On Tue, Dec 16, 2003 at 10:57:06AM -0600, Bruno Wolff III wrote:
> On Tue, Dec 16, 2003 at 10:54:47 -0500,
>   Jason Hihn <jhihn@paytimepayroll.com> wrote:
>
> > Now if I don't use the built in auth, I have to do permission checks myself.
> > But the bigger problem is I don't want to store plain text passwords in MY
> > users table, so I was going to use md5. But then I checked, and the
> > pg_shadow entry has 'md5' prepended to what I assume is the md5 hash of my
> > password. But when I ask for a md5 hash of my password, I don't get the same
> > number.
>
> I don't know for sure, but I would expect that something is being used as
> a salt. This is normal as it makes using prebuilt dictionaries more
> difficult and prevents you from being able to tell if two accounts
> have the same password just by looking at the hash.

The user name is the salt:

mydb=# create user johndoe with password 'opensesame';
CREATE USER
mydb=# select passwd from pg_shadow where usename = 'johndoe';
               passwd
-------------------------------------
 md5a7350a3bb54a151a858758c7266c57bd
(1 row)

mydb=# select md5('opensesame' || 'johndoe');
               md5
----------------------------------
 a7350a3bb54a151a858758c7266c57bd
(1 row)

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

Re: Authentication Question

From

Jason Hihn

Date:

16 December 2003, 16:39:55

Ah, wonderful! Now I can convert existing Postgres users to my own table and
keep their passwords, if I choose to go that way. I'm reading up on groups
now... Done (not much to read!)

Thanks for the help!

> -----Original Message-----
> From: Michael Fuhr [mailto:mfuhr+pgsql-general@fuhr.org]
> Sent: Tuesday, December 16, 2003 12:09 PM
> To: Jason Hihn; Pgsql-general
> Subject: Re: [GENERAL] Authentication Question
>
>
> On Tue, Dec 16, 2003 at 10:57:06AM -0600, Bruno Wolff III wrote:
> > On Tue, Dec 16, 2003 at 10:54:47 -0500,
> >   Jason Hihn <jhihn@paytimepayroll.com> wrote:
> >
> > > Now if I don't use the built in auth, I have to do permission
> checks myself.
> > > But the bigger problem is I don't want to store plain text
> passwords in MY
> > > users table, so I was going to use md5. But then I checked, and the
> > > pg_shadow entry has 'md5' prepended to what I assume is the
> md5 hash of my
> > > password. But when I ask for a md5 hash of my password, I
> don't get the same
> > > number.
> >
> > I don't know for sure, but I would expect that something is
> being used as
> > a salt. This is normal as it makes using prebuilt dictionaries more
> > difficult and prevents you from being able to tell if two accounts
> > have the same password just by looking at the hash.
>
> The user name is the salt:
>
> mydb=# create user johndoe with password 'opensesame';
> CREATE USER
> mydb=# select passwd from pg_shadow where usename = 'johndoe';
>                passwd
> -------------------------------------
>  md5a7350a3bb54a151a858758c7266c57bd
> (1 row)
>
> mydb=# select md5('opensesame' || 'johndoe');
>                md5
> ----------------------------------
>  a7350a3bb54a151a858758c7266c57bd
> (1 row)
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>