Is there any official documentation covering how to configure maildrop for use with MySQL?

I've found lots of unofficial HOWTOs that cover the configuration as a side effect of explaining how to setup Courier IMAP or integrate maildrop with MTAs, but it seems there should be a man page documenting maildropmysql.config, and more generally, maildrop's virtual user behaviors.

I was successful in getting maildrop working with MySQL, though it boiled down to a trial and error process due to the lack of documentation, and exacerbated by the lack of diagnostic output when the database couldn't be connected to due to configuration problems.

Here are some specific questions on maildropmysql.config:

What are the defaults for these and which are optional?

I first tried:

#hostname mysql.server.yourdomain.com #port 3306 #socket /var/lib/mysql/mysql.sock

Assuming that hostname would default to localhost and that the MySQL client library would take it from there (normally it would use a UNIX domain socket as specified in my.cnf when connecting to localhost, and ignore a port specification, if any).

That resulted in:

% maildrop -V 1 -d us...@example.com < /tmp/message maildrop: Invalid user specified. [...] % maildrop -V 500 -d us...@example.com < /tmp/message maildrop: Invalid user specified.

Hmmm...no diagnostics. Checking the MySQL log showed no query happened.

Perhaps skipping the obvious of setting 'hostname' to localhost, I next uncommented 'socket', which in the supplied maildropmysql.config points to a non existent file on my system. That produced similar results to the above, though an strace illuminated what was going on.

Eventually I got the expected behavior from:

hostname localhost #port 3306 #socket /var/run/mysqld/mysqld.sock

which connected to localhost via the UNIX domain socket specified in my.cnf, and results in:

% maildrop -V 500 -d us...@example.com < /tmp/message maildrop: Changing to /var/mail/example.com/user Message start at 0 bytes, envelope sender=us...@example.com maildrop: Attempting .mailfilter maildrop: Delivery complete.

So it seems 'hostname' isn't optional, but 'port' and 'socket' are.

Certainly maildrop can't be faulted for having a suggested 'socket' path that doesn't happen to work on my system, but what seems surprising is that there isn't a highly visible error message produced in both the case of the socket path being non existent and in the case of the hostname being undefined (given that it apparently doesn't default to 'localhost').

Both of these are fatal configuration errors and should never produce a misleading "Invalid user specified" error. (I'm aware there has been discussion on the list, as well as patches, to address returning a temporary failure when the database is temporarily unreachable. That is related to this situation, but different, because in the above case it is a static configuration problem, and accordingly the error message should be different.)

Quoting from maildropmysql.config:

That line should probably be removed.

What does "full path including name" mean? '/var/mail/user' rather than '/var/mail'? Or '/home/user' rather than '/home'?

I assume this needs to be an absolute path.

Is this path optionally relative to the homedirectory?

Can this point to a mailbox file instead of a maildir?

If it is a maildir, is it necessary that it have a trailing slash?

Why not leave that tucked away in CVS until it becomes actively used by the code?

These are all optional fields. Why not have them map to '' by default? (I'd expect the defaults to support the simplest configuration.)

How about some better attribute and default field names:

address_field address uidnumber_field uid gidnumber_field gid mailbox_field mailbox

What type of field? char(1)? What should it contain? Presumably this is here so administrators can include this field in their schemas now rather than adding it later when the code starts using it, but to do so it needs to be defined better. Otherwise, why not set it to '' in the code and leave it out of the configuration file for now?

For those who are familiar with SQL, it can be a big help to show the query template and organize the config directives around that. Below is an example of how a maildropmysql.config could be written to add clarity.

I've also incorporated some assumptions and suggestions I made above (that aren't necessarily accurate for the current maildrop code), as well as tips gathered from example maildropmysql.config files others have posted.

Lastly the maildropmysql.config file contains the database password as plain text, so there should be some documentation somewhere with recommended ownership and permissions for it. I ran across a thread on this here:

http://sourceforge.net/mailarchive/message.php?msg_id=8677691

I assume the answer is to set it to either be owned by the user that maildrop is SetUID to, or the user that it will be invoked as when embedded, and make the file readable only by that user.

What happens in the scenario where you aren't using a SetUID maildrop and you are running maildrop as a transport from an MTA like Postfix, that when delivering to real local users, switches to the recipient's UID before running maildrop? Is the answer that you have to use SetUID?

-Tom

# maildropmysql.config - this file defines how maildrop connects to # your MySQL database and how it maps the attributes it needs to # extract from your database to your fields.

# Host name of your MySQL server. # DEFAULT: localhost hostname localhost

# Port for TCP connections to your MySQL server. # OPTIONAL: if undefined, MySQL client library will use your my.cnf # settings # DEFAULT: 3306 #port 3306

# Path to the UNIX domain socket for your MySQL server. # OPTIONAL: if undefined, MySQL client library will use your my.cnf # settings # DEFAULT: none #socket /var/lib/mysql/mysql.sock

# MySQL database user. # DEFAULT: none (error if undefined) dbuser maildrop

# MySQL database user's password. # DEFAULT: none (error if undefined) dbpw maildroppw

# Name of the MySQL database holding the table that maildrop will # query. # DEFAULT: none (error if undefined) database sendmail

# MySQL Field definitions. # # This section allows you to specify the actual fields you # use in your MySQL table. You can map the attributes below to # any schema, but here is a schema using the defaults: # # CREATE TABLE maildrop ( # address VARCHAR(128) NOT NULL, # uid MEDIUMINT, # optional # gid MEDIUMINT, # optional # homedir VARCHAR(255) NOT NULL, # mailbox VARCHAR(255) NOT NULL, # quota VARCHAR(64) # optional # ); # # IMPORTANT: all fields mentioned below must be returned by the query, # but that doesn't mean they have to exist in your table. Some are # optional, and can be set to an empty literal string. # i.e. # quota_field '' # # Also, the field definitions don't necessarily have to be a single # field name. You can use most valid MySQL supported SQL functions. # For example, to build the 'homedirectory_field' from several # database columns, you can use a definition like: # homedirectory_field CONCAT_WS('/','/var/mail',domain,owner) # # Below is an approximate (pseudo-SQL) template of the query maildrop # will execute. The items shown in angle brackets correspond with the # configuration attributes that you can set below. # # SELECT <address_field>, # <uidnumber_field> | <default_uidnumber>, # <gidnumber_field> | <default_uidnumber>, # <homedirectory_field>, # <mailbox_field>, # <quota_field> | <default_quota> # FROM <dbtable> # WHERE <address_field> = "us...@yourdomain.com" # <where_clause>

# Name of the MySQL table that maildrop will query. # DEFAULT: maildrop dbtable maildrop

# The full email address of the user (i.e. us...@yourdomain.com). # This is the primary field searched upon by maildrop. # DEFAULT: address address_field address

# The numeric UID of the user that owns the mail files for this user. # This may be a specific UID per user, or a single (static) UID for # every user (full 'virtual' user configuration), or a combination. # # You can specify static values using string literals, for example: # uidnumber_field '5000' # # OPTIONAL: uses value defined by 'default_uidnumber' if this is # undefined. # DEFAULT: '' #uidnumber_field ''

# Default uid number to use in case no data is returned by the query # for the uidnumber_field. # DEFAULT: ?? (error if undefined) default_uidnumber 450

# The GID of the user that owns the mail files for this user. # This may be a specific GID per user, or a single (static) GID for # every user (full 'virtual' user configuration), or a combination. # # You can specify static values using string literals, for example: # gidnumber_field '5000' # # OPTIONAL: uses value defined by 'default_gidnumber' if this is # undefined. # DEFAULT: '' #gidnumber_field ''

# Default GID number to use in case no data is returned by the query # for the gidnumber_field. # DEFAULT: ?? (error if undefined) default_gidnumber 450

# Absolute path to the user's home directory. # This may be the same as the user's maildir. # # Tip: you can add a static prefix to what you have stored in your # database using a definition like: # homedirectory_field CONCAT('/var/mail/',user) # where the user field contains a user ID, resulting in a returned # value like '/var/mail/smith'. Or combine several fields as suggested # above in the introduction. # # DEFAULT: homedir homedirectory_field homedir

# The path to the user's mailbox, which is either the path to a file # in the case of an mbox format mailbox, or a path to a directory, # with a trailing slash, in the case of a Maildir format mailbox. The # path can be absolute or relative to the value returned by # 'homedirectory_field'. # # Tip: you can add a static prefix or combine several fields. See # examples above for details. # # DEFAULT: mailbox (error if undefined) mailbox_field mailbox

# The quota for this user. # (See 'default_quota' below for the format of the data that should be # returned by this field.) # # OPTIONAL: uses value defined by 'default_quota' if this is # undefined. If neither contains a value, quotas are disabled. # DEFAULT: '' #quota_field ''

# Default quota to use in case no data is returned by the query # for the quota_field. # Format: <bytes>S,<messages>C # You can specify either or both of <bytes> or <messages>. i.e.: # default_quota 10485760S,10000C # See ??? man page for the specifics of the format. # # OPTIONAL: not used unless defined # DEFAULT: '' #default_quota ''

# An an additional WHERE clause. It can be set to any SQL that would # be valid in a WHERE clause, and is appended to the existing WHERE # clause used in the query template, so it will typically need to # start with an 'AND'. i.e. # where_clause "AND active='Y'" # # OPTIONAL: not used unless defined # DEFAULT: '' #where_clause ""