Hi,
I'm a sqlalchemy newbie and I'm evaluating it on one of my projects. I
do have a fair bit of experience with various ORM tools so I solved most
of the issues I ran into, but I got stuck on hashed passwords.
Imagine a simple table with info about application users
CREATE TABLE users (
id INT PRIMARY KEY,
login TEXT NOT NULL UNIQUE,
pwhhash TEXT NOT NULL
)
where the pwdhash is a hashed password. I'm using PostgreSQL, which
contains a 'pgcrypto' extension with various hashing functions, and a
common way to store a hashed password with a random salt is this:
INSERT INTO users
VALUES (1, 'login', crypt('password', gen_salt('bf')))
and then authenticating the user
SELECT * FROM users
WHERE login = 'login' AND pwdhash = crypt('password', pwdhash)
which simply reuses the salt from the pwdhash column. So all the hashes,
salt generation etc. happens at the database layer, not in the
application code, which has various advantages.
One possible solution is to move all this to the application level, but
I don't like that approach - it's difficult to maintain that over
multiple applications, makes some queries less efficient etc.
I can't make this work in sqlalchemy - I've been looking at the custom
data types, hybrid values etc. but I still can't make it work. I guess
this is a rather common use case, so what is the recommended solution?
A nice-to-have feature would be not fetching the password hashes at all
- it's not needed at the application layer at all, and sending the
password to the database is needed only at INSERT, or when changing it
(some of the UPDATE commands, but not all). And obviously when
authenticating the user (i.e. performing a SELECT with login/password).
In short I'm looking for a solution that
1) keeps the password hashing / validation in the DB
2) works transparently with a 'User' class, i.e. allows me to set or
change the password but does not require it unless necessary
3) does not fetch the password hash all the time
A bit more advanced approach is to revoke access to the pwdhash column
from the regular application user, and provide a simple SQL function to
handle the authentication, i.e. something like this
CREATE OR REPLACE FUNCTION authenticate(p_login TEXT, p_password TEXT)
RETURNS SETOF users AS $$
SELECT * FROM users WHERE login = p_login
AND password = crypt(p_password, password);
$$ LANGUAGE SQL;
and then doing "SELECT * FROM authenticate(....)" instead of directly
from the "users" table.
A similar problem is related to e-mail addresses - those are not hashed
but it's a good practice not to grant access to the column to the
regular application user, and providing a special getmail method so that
the user can fetch only his own e-mail.
This provides an additional protection against injection-like attacks
and application errors that often result in leaked e-mails and password
hashes, as the regular application user simply can't access the columns
directly.
regards
Tomas
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
