README for MySQL authentication in tpop3d
$Id: README.auth_mysql,v 1.7 2001/11/07 00:50:41 chris Exp $

The auth-mysql authentication driver was developed for use with the vmail-sql
database schema for virtual domains, though it is easy to adjust for a
different one by specifying SQL query templates in the tpop3d configuration
file. If you don't intend to use vmail-sql (or are impatient to find out how
it works), these notes should help:

tpop3d communicates with the database using the standard libmysqlclient
interface. The database name, username and password are set in the
configuration file -- see tpop3d.conf(5) for more information. In the given
database, there are two tables relevant to POP3 operations:

# this is the table which holds information about this domain
create table domain (
        domain_name varchar(255) primary key, # domain name
        unix_user varchar(255),               # which Unix user owns files etc.
        password_hash varchar(255),           # admin password for this domain
        path varchar(255),                    # base path for this domain
        max_popbox int                        # maximum number of popboxes in
) ;                                           # this domain

# local POP boxes (virtual)
create table popbox (
        domain_name varchar(255) not null,    # domain this refers to
        local_part varchar(255) not null,     # username for this POP box
        password_hash varchar(255),           # hash of this user's password
        mbox_name varchar(255),               # appended to domain.path
        primary key (domain_name(8), local_part(8))
) ;

(Only the fields domain_name, unix_user and path in the domain table are used
directly by tpop3d.)

The model of use here is that a virtual email domain is owned by a single Unix
user. This is so that you can apply, for instance, file quotas to the domain
using the standard mechanisms. Within the domain, there exist a number of
`popboxes', i.e., mail spools; these are described by rows in the popbox table.

Each domain has a path associated with it. In our configuration, this is
something like /var/spool/mail/SERVERS/$(domain). Under this path, mailspools
reside in names given by mbox_name in the popbox table. This is probably more
general than is necessary, since there is no reason to have mbox_name not
equal to the local_part. 

To authenticate a user and set her mailspool, auth_mysql performs the
following steps (for USER/PASS authentication -- APOP is conceptually
similar):

1. Check that the connection to the database is live.

2. Perform a SELECT statement of the form

   SELECT CONCAT(domain.path, '/', popbox.mbox_name),
          popbox.password_hash, domain.unix_user, 'bsd'
      FROM popbox, domain
      WHERE popbox.local_part = $(local_part)
      AND popbox.domain_name = $(domain)
      AND popbox.domain_name = domain.domain_name

   where $(local_part) and $(domain) represent the local-part and domain name
   which the user has given. (Note that if the configuration option
   append-domain is switched on, the domain name may be inferred from the IP
   address to which the connection was made, rather than one explicitly
   specified in the form USER fred@example.com. See tpop3d.conf(5) for more
   information on this.) The trailing field, `bsd' is the mailbox type to use.
   Alternative schemes may have mailbox types specified in the database.

3. If the statement returned no rows, then the user does not exist; if it
   returned more than one row, then there is a database inconsistency (this is
   a `can't happen' condition). Otherwise, we have a row with which to
   authenticate the user.

4. Obtain the password hash, which must be the second column returned by the
   above query. Look at the first part of it, and use this to determine the
   format of the password hash:

      {crypt}...          crypt(3) hash
      {crypt_md5}...      crypt_md5 hash
      {plaintext}...      plaintext password
      {mysql}...          password hashed using MySQL's PASSWORD()
      {md5} or no prefix  old-style simple MD5 password

   {crypt} is the format of the system crypt(3) function-- two salt characters
   and a password hash produced using DES. {crypt_md5} is the new-style BSD
   MD5 password (also used on Linux) which looks like $1$<salt>$<password
   hash>$; on BSD/Linux systems, the libc will automatically detect this case
   in crypt(3), but for portability it is included as a separate case.
   {plaintext} is a password saved in plain text; this format is useful if you
   want to support authentication by the APOP shared-secret mechanism. {mysql}
   is the format produced by the PASSWORD() function in MySQL.  {md5} is a
   simple MD5 hash of the password, without salt, saved as a 32-character hex
   string. This is the historical format for vmail-sql, and so is the default
   if no prefix is used. This may go away at some point.

   Now, we compare the user's password against the stored hash or password,
   and decide whether to authenticate the user or not. If this does not
   succeed, log a message.

5. If authentication has succeeded, we need to figure out which user and group
   to use. The Unix user is domain.unix_user, and the group is either that
   user's primary group (i.e., the one specified in /etc/passwd or
   equivalent), or, if specified, the value of the auth-mysql-mail-group
   configuration directive. These results are stored in an authcontext
   structure, which is returned to the main program.

Most people who want to modify auth_mysql seem to want to change the names of
database columns. From version 1.3.5, it is possible to do this at run-time
using the auth-mysql-pass-query and auth-mysql-apop-query configuration
directives as discussed in the tpop3d.conf man page.

If you want to do something more complex and which cannot be achieved by
modifying the query templates in the config file, you might want to consider
using auth-other or auth-perl; these make it easier to prototype new designs,
and don't require you to mess around with writing database code in C, which
isn't much fun all told. In particular, I strongly recommend that if you want
to use a database other than MySQL, you use the perl DBI/DBD support to
implement an authenticator, rather than writing a new driver. This will be far
quicker to develop and much easier to debug; in addition, it avoids pointless
duplication of effort. perl and DBI/DBD present an excellent interface for
communicating with relational databases, and there is no purpose in duplicating
it.

You may well find it useful to browse the archives of the tpop3d-discuss
mailing list (see the README) and discuss your ideas on the list.