Authentication Question

[Jason Hihn]

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 [e-mail] 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Bruno Wolff III]

On Tue, Dec 16, 2003 at 10:54:47 -0500,
Jason Hihn <jh***@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 [e-mail] 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!

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Peter Eisentraut]

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 [e-mail] with password 'cow';
ERROR: syntax error at or near "@" at character 16
create user "[e-mail]" ...;
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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Michael Fuhr]

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 <jh***@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/

---------------------------(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

[Jason Hihn]

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:mf*****************@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 <jh***@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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)