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