Integrating django authentication with PostgreSQL

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


Comments

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.

Posted on Jan 13, 2010 at 19:57 by Dimitri Fontaine.

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)

Posted on Jan 13, 2010 at 20:00 by Magnus.

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;

Posted on Jan 14, 2010 at 10:31 by Dimitri Fontaine.

Add comment

New comments can no longer be posted on this entry.

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

FOSDEM 2023
Feb 2-5, 2023
Brussels, Belgium
SCaLE 2023
Mar 9-12, 2023
Pasadena, CA, USA
Nordic PGDay 2023
Mar 21, 2023
Stockholm, Sweden
PGCon 2023
May 30-Jun 2, 2023
Ottawa, Canada

Past

PGConf.EU 2022
Oct 25-28, 2022
Berlin, Germany
PGConf.NYC 2022
Sep 22-23, 2022
New York, USA
Swiss PGDay 2022
Jul 1, 2022
Rapperswil, Switzerland
pgCon 2022
May 24-27, 2022
Online, Online
PGConf.DE 2022
May 13, 2022
Leipzig, Germany
More past conferences