If you've been to any of my pgcrypto talks, you know I'm a fan of exposing a login interface in the database. It's a great way to allow multiple systems to use the same authentication system, without being limited to a single framework. However, it can often be nice and easy to be able to use the account management system in said framework, because it's already there.
When I go to frameworks, django is my current favorite, and I do like the ability to use it's integrated admin system to edit users. For this reason, I wanted to add the ability to use a database function to authenticate other systems using the django user tables. Thanks to the fact that django choose a nicely readable text format for their hashes, and that PostgreSQL has the awesome pgcrypto library, this is pretty simple. Here's a function that will simply return true if authentication was successful, and false if not.
CREATE OR REPLACE FUNCTION django_login (_username text, _password text) RETURNS boolean AS $$ BEGIN IF EXISTS (SELECT * FROM auth_user WHERE username=_username) THEN RETURN encode(pgcrypto.digest(split_part(password, '$', 2) || _password, 'sha1'), 'hex') = split_part(password, '$', 3) FROM auth_user WHERE username=_username; ELSE RETURN 'f'; END IF; END; $$ LANGUAGE 'plpgsql';
This assumes you have installed pgcrypto in it's own schema, something I always recommend. If you haven't, just remove the schema specifier in the query.
Finally, if you use this type of authentication, remember to use SSL. And don't enable query logging...
I guess this should be IF EXISTS (SELECT 1 FROM ...) rather than SELECT *, and IIRC 8.4 EXPLAIN VERBOSE will show why :) But thinking some more about it I guess I'd have written it in pure SQL. Nitpicking apart, it's good to see pg people using some FLOSS stack, times are such that we should see a raise in PostgreSQL usage in Open Source products. And I think the project is ready for that.
Yeah, you can actually make it slightly faster using CTEs. But it so happens to be that the function I cut it out from actually does some more stuff, so I didn't think of that at the time :) (if you don't use a CTE for it, you'll create two scans of the table)
SELECT ok FROM (SELECT encode(pgcrypto.digest(split_part(password, '$', 2) || _password, 'sha1'), 'hex') = split_part(password, '$',3) as ok FROM auth_user WHERE username=_username UNION ALL SELECT false as ok ) LIMIT 1;