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!
