Would you like to use an existing database for Apache authentication, e.g. instead of the htpasswd file in your small WebDAV server?

A few lines of httpd configuration will help. This example is for PostgreSQL, but similar mechanisms would work for MySQL or any other database working with Apache’s DBD module.

In your virtual host configuration, add the following. This will access the database named mail.

DBDriver pgsql
DBDParams "dbname=mail"
DBDMin 1
DBDKeep 8
DBDMax 20
DBDExptime 300

Replace the AuthUserFileName line in a standard configuration with the following block:

AuthBasicProvider dbd
AuthDBDUserPWQuery "SELECT encrypted FROM users_encpw WHERE username = %s AND active = 1"

(Don’t worry about SQL injection attacks, the “%s” above is actually magic for most “real” databases, which includes PostgreSQL.)

users_encpw referenced above is actually a view in PostgreSQL, with the following format:

username | encrypted    | fullname | active
---------+--------------+----------+-------
user1    | {SHA}Wat…z8= | User 1   | 1
user2    | {SHA1}r6…jU= | User 2   | 1

The format used for the password is the one generated by htpasswd -s.

But why did I choose PostgreSQL over MySQL? I have come to enjoy the convenience of the passwordless login of PostgreSQL. My Apache runs as user www-data. So I created a user www-data in PostgreSQL and gave it read rights on that view. To enable the Unix user www-data to act as database user www-data, the following line needs to be added to /etc/postgresql/<version>/main/pg_hba.conf :

# TYPE  DATABASE USER  ADDRESS  METHOD
local   www-data       all      peer
# "local" is for Unix domain socket connections only

Using that “peer” authentication mechanism, you do not need to store plaintext passwords in configuration files, as long as the database server is on the same machine as the web server.

If you want all local Unix users to access their database accounts of the same name without having to enter a password, you can change pg_hba.conf as follows:

# TYPE  DATABASE USER  ADDRESS  METHOD
local   all            all      peer
# "local" is for Unix domain socket connections only

When logged in as www-data, you can then interactively access the database as follows, no (additional) password required:

www-data% psql -d mail

Or, if logged in as root:

# su www-data -c 'psql -d mail'