This Apache Module is part of the "2.1 Authentication Project".

Homepage: http://mod-auth.sourceforge.net/

NOTE: The main documentation of mod_authn_dbi lives at

      http://mod-auth.sourceforge.net/docs/mod_authn_dbi/
      This file is just a quick'n'dirty ascii-conversion in
      case you don't have a browser handy!

Summary

The module mod_authn_dbi provides Authentication against an SQL database backend. It uses the application-independent abstraction layer provided by libdbi. Database drivers for libdbi are provided by the libdbi-drivers, project. At the moment, drivers are provided for MySQL, PostgreSQL, SQLite and mSQL.

mod_authn_dbi is very flexible and offers several levels of customization. This makes it easy to integrate it into existing installations and authenticate users without having to alter the structure of existing tables. It is also relatively easy to port existing authentication information from other sources, e.g. file-based authentication to a backend for use by mod_authn_dbi.

Several different formats of passwords are supported:

Plaintext

Unix-style crypt (like htpasswd)

Apache MD5 (like htpasswd)

Apache SHA1 (like htpasswd)

Apache Digest (like htdigest)

Compilation

mod_authn_dbi uses the "configure/make/make install" mechanism common to many Open Source programs. Most of the dirty work is handled by either configure or Apache's apx utility. If you have built apache modules before, there shouldn't be any surprises for you.

Before you can begin compiling mod_authn_dbi, you will need a working installation of Apache 2.1-dev (any earlier version, including Apache 2.0.x will NOT work) and libdbi with at least one driver installed. If this is not already the case, go to their homesites as given above, install them and then come back here.

The interesting options you can pass to configure are:

--with-apxs=/path/to/apache/dir/bin/apxs

This option is used to specify the location of the apxs utility that was installed as part of apache. Specify the location of the binary, not the directory it is located in.

--with-dbi=/path/to/libdbi/installation/dir or
--with-includes=/path/to/libdbi/includes --with-libs=/path/to/libdbi/lib

The installation directory of libdbi. Beneath this directory there should be directories "lib" and "include" that contain the library and its headers. Alternatively, you can specify the other two, if libraries and includes are installed into different directories. Usually, specifying the first should suffice.

Call configure with your site-specific values and then "make" and "make install" as usual. The module will be installed in the module directory of your Apache installation.

Integration into Apache

Again mod_authn_dbi behave like your average next-door Apache module. Just add

LoadModule authn_dbi_module modules/mod_authn_dbi.so

to your httpd.conf as usual and restart Apache.

Upon startup, mod_authn_dbi will read in all drivers that are in the driver directory of libdbi. This directory is a default vaule compiled into libdbi (usually <installation prefix of libdbi>/lib/dbd/). If you want mod_authn_dbi to look into a different directory, have a look at the AuthnDbiDriverDir directive.

Preparing the Database

Of course, before you can use mod_authn_dbi, you will need to decide which database to use and create it if it does not already exists. mod_authn_dbi is pretty flexible and tries to impose as few requirements on the structure of the table as possible. If you already have user records in some table, chances are good that you will be able to use these records without having to change the structure of the table. Also, migration from file-based authentication (htpasswd,htdigest) is very easy.

We will describe the "default requirements" and the first level of customization here. Most of these requirements will change, once you start to fiddle with the AuthnDbiPasswordQuery directive, most of these requirements will change or go away.

Starting from scratch...

The default structure that mod_authn_dbi expects is a database called "AuthDB", which contains a table called "Users". In this table, it expects two string (VARCHAR or similar) columns "Username" and "Password" containing the usernames and for each of them the password in plaintext.

mod_authn_dbi will, by default to access this database on through the MySQL driver on host "localhost" and login there as user "root" without a password.

Something like you would get with:

CREATE table Users (Username VARCHAR(32), Password VARCHAR(32));

In the first step of customization, you can change the values for driver, host, user, password, database, table, column names and the password format to match a table in an existing database.

So, let's continue with...

Basic configuration of mod_authn_dbi

The configuration of mod_authn_dbi consists of two main parts. First, you configure one or more configuration sets. Then, you use these configuration sets in any number of basic or digest authentication realms.

At the moment, there is only one global option (AuthnDbiDriverDir), which you will probably not need at first, so we will start with the section about mod_authn_dbi server configs. All options that are used for the creation of server configs take the name of the server config as first parameter and then some value(s) behind that. Server configs are created implicitly, each time the module encounters a configuration directive with a config name that it has not seen yet.

For example:

AuthnDbiTable Server1 MyDatabaseTable

This says that the config with the name "Server1" should use the table "MyDatabaseTable" instead of the default "AuthDB", you get the idea...

Creating Configuration sets
Most configuration directives of mod_authn_dbi are of the format

DirectiveName <name> <value>

Configuration sets are implicitely created each time you specify a <name> that has not been used before (so beware of typos in the sets name). There is no enclosing syntactic structure around the directives and directives of different sets may be mixed, but it is probably best to keep all directives of one set together like in the example below. All directives that are not specified for a given configuration set remain at their default values. So the minimum to create a usable configuration set is just one of the directives. Configuration sets contain all the information needed to connect to the database server, choose the correct database and table and extract the values needed for authenticating users. Additionally, you can specify options that for example influence the pooling of connections (see below) or the way that the data in the table is interpreted.

An example configuration set

        AuthnDbiDriver         Server1  mysql
        AuthnDbiHost           Server1  mycoolcatserver.com
        AuthnDbiUsername       Server1  MyUser
        AuthnDbiPassword       Server1  MyPass
        AuthnDbiName           Server1  MyDatabaseName
        AuthnDbiTable          Server1  MyDatabaseTable
        AuthnDbiUsernameField  Server1  theUserNameField
        AuthnDbiPasswordField  Server1  PasswordField
        AuthnDbiIsActiveField  Server1  IsActiveField
        AuthnDbiConnMin        Server1    3
        AuthnDbiConnSoftMax    Server1   12
        AuthnDbiConnHardMax    Server1   20
        AuthnDbiConnTTL        Server1  600

Configuration sets can only be defined in the main Apache configuration file or one that is included into it. Specifying configuration sets in a .htaccess files is not supported at the moment, altough it might be added in a future release. If this is a feature you would like to see, e.g. to provide hosting users with a possibility to use their own database for authentication, then tell us so on the "mod-auth-devel" mailinglist.

Defining Authentication realms
The authentication of users has changed a lot between versions 1.x/2.0 and 2.1 of Apache. An AAA framework (Authentication, Authorization, Access-Control) has been introduced that allows for more flexibility and orthogonality in the configuration. There is not much (up-to-date) documentation at the moment. There is documentation about mod_auth_basic and mod_auth_digest, the two main parts of the authentication part ("authn") of the framework. Refer to the slides of the talk at the O'Reilly Open Source conference by Justin Erenkrantz "Authentication in Apache HTTP Server 2.1" for some additional information about the new framework. More documentation will be available on this site Real Soon Now[tm]. The an updated version of the official authentication tutorial will be available in the manual section "Authentication, Authorization and Access Control", but at the moment, that page still documents the old system from 2.0 and earlier.

mod_authn_dbi registers itself as an Authentication Provider with the name "dbi" in Apache's Authentication Framework. It provides authenticating users with both Basic and Digest Authentication. Basic Authentication is by far the more common one, but you might want to consider Digest Authentication as well, since it is more secure than Basic Authentication and supported by all major browsers. To the user, both will look the same, i.e. appear as a dialog box requesting username and password. The two mechanisms just differ in the way data is exchanged in the background between browser and webserver.

To use a configuration set (in one of Apaches central configuration files), you will need the AuthnDbiServerConfig directive from mod_authn_dbi plus at least AuthType, AuthDigestProvider or AuthBasicProvider (depending on the value you pass to AuthType) and Require and a directory-level container like <Location> or <Directory> to use them in.

First, decide wheter to use Basic or Digest Authentication and set AuthType to Digest or Basic accordingly. Then, set the matching provider directive (AuthDigestProvider or AuthBasicProvider) to "dbi", this links mod_authn_dbi to this authentication section. Next, you need to tell mod_authn_dbi which configuration set to use by setting AuthnDbiServerConfig to the name of the set. Configuration sets can be used for both Basic and Digest Authentication (even at the same time) and for any number of authentication sections. Now, this authentication section is linked to a specific configuration set within mod_authn_dbi. If you specify AuthnDbiServerConfig more than once, the last value will be used. Finally, you specify who may access the section with Require to actually use this authentication section when a client request comes in. (Well, most of this is not really that different from what is is in 2.0 and earlier). Authentication realms that use mod_authn_dbi can also be used from a .htaccess file as usual. Just leave off the container directives.

Below you find two example configurations, one for Basic and one for Digest Authentication.

An example authentication realm for Digest Authentication

        <Directory  "/path/to/htdocs/to/be/area_1_protected">
          AuthType Digest
          AuthName "digest authn_dbi testing area"
          AuthDigestProvider dbi
          AuthnDbiServerConfig Server1
          Require valid-user
        </Directory>

An example authentication realm for Basic Authentication

      <Directory  "/path/to/htdocs/to/be/area_2_protected">
        AuthType Basic
        AuthName "basic authn_mysql testing area"
        AuthBasicProvider dbi
        AuthnDbiServerConfig Server1
        Require valid-user
      </Directory>

Advanced configuration of mod_authn_dbi

As mentioned earlier, mode_authn_dbi is very flexible. You can do all kinds of nifty things, with (or even without) your user database, especially with the AuthnDbiPasswordQuery.

More kung-foo will be added here soon...

If you have done something nifty with mode_authn_dbi we would appreciate it if you contributed it as an example for this section....

Pooling of database connections

To handle incoming requests in an efficient but also performant way, mod_authn_dbi organizes connections to the database server(s) into pools, with one connection pool per configuration set. It opens and closes connections depending on the load the webserver is experiencing at a given time. The pooling mechanism is built on the Resource List Routines of libapr-util (and you probably had to download separately) and is similar to the way Apache manages the number of server processes or threads.

When first started, mod_authn_dbi creates a mimimum number of connections to the database server before accepting client requests. When a client request comes in that needs to be Authenticated, a database connection is requested from the connection pool of the matching configuration set. The connection is used for authentication and then released back into the pool of idle connections. If no idle connection is available and the number of connections in the pool has not reached its hard maximum, a new connection to the database is created and used. After use, it is not destroyed but added as idle connection to the pool of connections.
Each configuration contains soft maximum value and a time-to-live (TTL) value (e.g. 600 seconds). If more than the soft maximum number of idle database connections is in a given pool, connections that have been idle for longer than the time-to-live value will be closed until a value not larger than the soft maximum is reached.

These four directives can be used to control the various limits:

AuthnDbiConnMin - The minimum (and initial) number of connections.

AuthnDbiConnSoftMax - The soft maximum value of connections.

AuthnDbiConnHardMax - The hard maximum of connections.

AuthnDbiConnTTL - The time-to-live in seconds.

AuthnDbiConnHardMax Directive

Description: The Hard Maximum Number of Database Connections Syntax: AuthnDbiConnHardMax DbiConfigName number Default: 25
Context: server config
Status: External
Module: mod_authn_dbi

This is the absolute maximum number of connections that mod_authn_dbi will open for this configuration set. See section "Pooling of database connections" for more information.

AuthnDbiConnMin Directive

Description: The Minimum Number of Database Connections Syntax: AuthnDbiConnMin DbiConfigName number Default: 1
Context: server config
Status: External
Module: mod_authn_dbi

The minimum number of connections to the database server that mod_authn_dbi will maintain for this configuration set. This is also the number of connections that mod_authn_dbi will create on start-up for the given configuration set. See section "Pooling of database connections" for more information.

AuthnDbiConnSoftMax Directive

Description: The Soft Maximum Number of Database Connections Syntax: AuthnDbiConnSoftMax DbiConfigName number Default: 5
Context: server config
Status: External
Module: mod_authn_dbi

The soft maximum number of connections to the database server that mod_authn_dbi will maintain for this configuration set. Idle connections above this limit will be closed after the time set by AuthnDbiConnTTL has expired for them. See section "Pooling of database connections" for more information.

AuthnDbiConnTTL Directive

Description: The database pool time-to-live for each connection. Syntax: AuthnDbiConnTTL DbiConfigName seconds Default: 600
Context: server config
Status: External
Module: mod_authn_dbi

This gives the time in seconds after which idle connections above the soft limit will be closed. See section "Pooling of database connections" for more information.

AuthnDbiDriver Directive

Description: Sets the Driver that DBI Uses. Syntax: AuthDbiDriver DbiConfigName mysql|pgsql|sqlite Default: mysql
Context: server config
Status: External
Module: mod_authn_dbi

The name of the driver that is passed to libdbi. The default value is "mysql". The possible values depend on what libdbi drivers you have installed. At the moment, libdbi-drivers supports:

Keyword Database
mysql MySQL
pgsql PostgreSQL
sqlite SQLite
msql mSQL
More drivers should be available soon. Check http://libdbi-drivers.sourceforge.net/ The value is treated by mod_authn_dbi as an opaque string which is just passed to libdbi.
At the moment, only the MySQL driver is really tested, but others should work, ymmv (Success/failure reports are, of course, always welcome).

AuthnDbiDriverDir Directive

Description: The directory containing the DBI drivers Syntax: AuthnDbiDriverDir PATH
Default: none
Context: server config
Status: External
Module: mod_authn_dbi

The name of a directory in which libdbi will expect its drivers. If this directive is not given, libdbi will use its default,compiled-in value. Drivers are are shared objects which end in .so, e.g. libmysql.so. Any driver found in this directory can later be used by passing its name to AuthnDbiDriver.

AuthnDbiHost Directive

Description: The host for the database connection Syntax: AuthnDbiHost DbiConfigName hostname|ip|localhost Default: localhost
Context: server config
Status: External
Module: mod_authn_dbi

This is the hostname of the database host for this configuration. Specify a hostname or an IP address. Again, this string is just passed to libdbi.

AuthnDbiIsActiveField Directive

Description: The table field that contains the username Syntax: AuthnDbiIsActiveField DbiConfigName field Default: none
Context: server config
Status: External
Module: mod_authn_dbi

Optionally, the table you use can contain an IsActive-Field of integer(!) type. If the table-column with the configured name contains a 0, the account will be treated as disabled and the user not be let in, even if the correct password was given. If the column contains a different values, the account is seen as active.

AuthnDbiName Directive

Description: The name of the database containing the tables Syntax: AuthnDbiName DbiConfigName database Default: AuthDB
Context: server config
Status: External
Module: mod_authn_dbi

The name of the database that will be used.

AuthnDbiOptions Directive

Description: A list of options for this configuration Syntax: AuthnDbiOptions DbiConfigName [AllowEmptyPasswords] Context: server config
Status: External
Module: mod_authn_dbi

This directive gives a list of options to control the behaviour of a dbi-configuration. At the moment, only one option can be given. More might follow later. Give us feedback about what you would like to see added here. Keyword Meaning AllowEmptyPasswords If this option is given, all accounts that have "::" as password value will be accepted with any password that the user supplies. With this options, you can for example implement guest-accounts. (The name of this option does not really fit its behaviour, but somehow i could not think of a better name when implementing it.)

AuthnDbiPassword Directive

Description: The password for the database connection Syntax: AuthnDbiPassword DbiConfigName password Default: login without a password
Context: server config
Status: External
Module: mod_authn_dbi

The password that is used when logging in to the database.

AuthnDbiPasswordField Directive

Description: The table field that contains the password Syntax: AuthnDbiPasswordField DbiConfigName field Default: Password
Context: server config
Status: External
Module: mod_authn_dbi

The name of the column that mod_authn_dbi will interpret as passwords.

AuthnDbiPasswordFormat Directive

Description: The format the password is saved as Syntax: AuthnDbiPasswordFormat DbiConfigName Plain|Apr|AprDigest Default: plain
Context: server config
Status: External
Module: mod_authn_dbi

This options specifies the format, value in the PasswordField is interpreted as. Possible values are:

Plain

The passwords are in plaintext format. This format is supported for Basic and Digest authentication.

Apr

Passwords are in a format that can be handled by the apr_password_validate() function of the libapr-util library, which is part of the apache project and comes bundled with the releases of the Apache server. At the moment these are SHA1 and MD5 on Unix, Windows and other platforms. And crypt on Unix only. The formats are exactly the same as are used by htdigest.

An example password in crypt format: O8D24p2LCO7PA

An example password in md5 format: $apr1$SvzPV/..$CJl3oQ/ko4Tq5eg6L2Fk..

An example password in SHA1 format: sha1:{SHA}C+7Hteo/D9vJXQ3UfzxbwnXaijM=

Just specify "Apr" as the AuthnDbiPasswordFormat, not crypt, sha1 or md5. The format is detected automatically. Entries of both formats may be mixed within the same table.

AprDigest

The Passwords are in Digest MD5-like format, like they are used by the htdigest utility of Apache.

An example for a password in Digest format: 90b9659ffec980fdfd41f14c31a07887

Refer to the documentation of htdigest for further information about this format.

AuthnDbiPasswordQuery Directive

Description: The SQL query to pick the password field from Syntax: AuthnDbiPasswordQuery DbiConfigName SQL Query Default: SELECT &{PasswordField} FROM &{Table} WHERE &{UsernameField}=&{GivenUsername} LIMIT 0,1
or
SELECT &{PasswordField} FROM &{Table} WHERE &{UsernameField}=&{GivenUsername} AND &{IsActiveField}!=0 LIMIT 0,1
Context: server config
Status: External
Module: mod_authn_dbi

This directive allows you to specify a custom SQL-query that mod_authn_dbi will use when authenticating users. It is quite flexible and can be used to shoot a lot of problems (including your foot). Changing its value may also change the behaviour of AuthnDbiUsernameField, AuthnDbiPasswordField, AuthnDbiIsActiveField, AuthnDbiTable from what is documented in here.

For each request that is received (not just at configuration time), mod_authn_dbi will go through the following steps:

Replace variables in the query string by their corresponding values The query string may contain any number of placeholders for variables of the format &{variablename}. For each request, this placeholder is replaced by the current value. The Variables supported variables come from the configuration of mod_authn_dbi or attributes of the current request. See below for a list of supported variables and their meaning.

Execute the query in the database that was configured

Select from the result the field with the name configured by PasswordField (or its default).

Interpret the value according to AuthnDbiPasswordFormat and use it for authenticating the request.

Variable Name Meaning
- AuthName The name of the authentication section, configured by the AuthName directive.
- ConfigHostname The name of the configured host the current request is serviced by.
- GivenPassword The Password value that was included in the request. This is just what the client sent, not checked in any way. - GivenUsername The Username that was included in the request. This is just what the client sent, not checked in any way. - IsActiveField The value set by the directive AuthnDbiIsActiveField. - LocalIP The local IP address of the webserver that the current request came in on. - Name The name of the mod_authn_dbi configuration that is used for the current request.
- PasswordField The value set by the directive AuthnDbiPasswordField. - PathInfo The path info value from the current request. - Realm The Digest realm, only valid for digest authentication. - RemoteIP The IP address of the client that sent the current request. - RemoteHost The hostname of the client that sent the request. Only if available, otherwise it will contain an IP address. Don't put too much (if any) trust into this. - RequestArgs Any extra arguments supplied by the client for this request. - RequestFile The name of the file this request ist for. - RequestHostname The value of the "Host:" http field from the current request. - RequestURI The path info from the current request. - Table The name of AuthnDbiTable from the current mod_authn_dbi configuration. - UsernameField The value set by the directive AuthnDbiUsernameField.

This list of variables is just what looked useful at the time of coding. Others will be added if requested (yes, that means you have to give us feedback about what you would like to be able to to with mod_authn_dbi!).

AuthnDbiServerConfig Directive

Description: This directive specifies the configuration to use for an authentication section.
Syntax: AuthnDbiServerConfig DbiConfigName Context: directory, .htaccess
Status: External
Module: mod_authn_dbi

This option invokes a configuration that was created with the other configuration directives. It is the only directive that may appear in a Directory/Location context or .htaccess file!

          <Directory  "/path/to/htdocs/to/be/area_1_protected">
            AuthType Digest
            AuthName  "digest authn_dbi testing area"
            AuthDigestProvider dbi
            AuthnDbiServerConfig Server1
            Require valid-user
          </Directory>
          
          <Directory  "/path/to/htdocs/to/be/area_2_protected">
            AuthType Basic
            AuthName  "basic authn_mysql testing area"
            AuthBasicProvider dbi
            AuthnDbiServerConfig Server1
            Require valid-user
          </Directory>