Most of the visitors to www.postgresql.org probably never noticed that a couple of weeks back, the entire site was replaced with a new one. In fact, we didn't just change the website - just days before, we made large changes to our ftp network as well (more about that in another post, from me or others). So in fact, we hope that most people didn't notice. The changes were mainly a technical refresh, and there hasn't been much change to the contents at all yet. We did sneak in a few content changes as well, that have been requested for a while, so I'm going to start with listing those:
The rest of the changes are under the hood, and it's mostly done for two reasons: The technology powering the site was simply very old The frameworks used were quite obscure, which severely limited the number of people who could (or wanted to) work with them
Hopefully these two changes will make it easier to contribute to the website, so if you're potentially interested in doing that, please read on!
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...