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 $$
   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;
      RETURN 'f';
   END IF;
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.

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.


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


PGConf.EU 2016
Nov 1-4, 2016
Tallinn, Estonia
Berlin PUG
Nov 17, 2016
Berlin, Germany
PGConf.Asia 2016
Dec 2-3, 2016
Tokyo, Japan
FOSDEM + PGDay 2017
Feb 2-4, 2017
Brussels, Belgium


Stockholm PUG 2016/5
Oct 25, 2016
Stockholm, Sweden
Postgres Vision 2016
Oct 11-13, 2016
San Francisco, USA
Postgres Open
Sep 13-16, 2016
Dallas, USA
Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
More past conferences