vmail-sql for Exim $Id: README,v 1.18 2002/07/03 10:44:11 chris Exp $ Chris Lightfoot http://www.ex-parrot.com/~chris/ Paul Warren http://www.ex-parrot.com/~pdw/ This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This is a set of Exim configuration sections and control scripts to allow complete configuration of a virtual-domains email system using a MySQL database; it includes a (now obsolete) patch to gnu-pop3d to allow you to use that POP3 server with the virtual-domains set up, but we recommend that you use tpop3d instead. See below. Installation and upgrade instructions can be found in the INSTALL and UPGRADE files respectively, but you should read this file for information on how the configuration works. The whole setup is not totally trivial, but here are the basics: 0. What You Need Software-- * tpop3d, from http://www.ex-parrot.com/~chris/tpop3d/ (or, but not recommended, gnu-pop3d, in a version to which the supplied patch will cleanly apply; 0.9.8 has been tested) * MySQL, or, more precisely, libmysqlclient (C client API) * the exim MTA, in a version which supports MySQL natively (or, if you wish to roll-your-own config, any MTA which can route by calling an external program) * perl 5, DBI and DBD::mysql, if you want to use the supplied scripts to configure virtual domains * Apache, or some other web server if you want to use the supplied CGI scripts for configuring domains. Hardware-- * a computer capable of running the above (with a connection to the internet, if you have ambitions beyond talking to yourself) Other-- * a clue or two; this is not a trivial setup and you will probably have to work out (at least) infelicities in its configuration. 1. The Database This has tables for domains, forwarders, and `POP3 boxes' (mail spools; the terminology is lifted from a service this was designed to replace). There is also a session table to support the web interface, but this is separate and may be omitted if you don't want to use that. The schema, in MySQL syntax, is as follows: --- file init-db.sql --- # 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 ) ; # forwarders, local and remote create table forwarder ( domain_name varchar(255) not null, # domain this refers to local_part varchar(255) not null, # local part we forward for remote_name varchar(255) not null, # whom we forward to primary key (domain_name(16), local_part(32)) ) ; # 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(16), local_part(32)) ) ; # domain aliases create table domain_alias ( domain_name varchar(255) not null, # domain to redirect to alias varchar(255) not null, # alias for 'real' domain primary key (alias) ); --- file init-session-db.sql --- create table web_session ( domain_name varchar(255) primary key, # domain name session_id char(32), # session token for this domain expires int ) ; This database is called `virtualemail' in our setup. There is a MySQL user who has access to INSERT/SELECT/UPDATE/DELETE; in our setup, this is `exim' but `vmail' might be a more logical choice. The unix_user field in domain is essentially there to allow virtual domain mailspools to be subject to quotas. It is not necessary to have a different user for each domain. 2. The Exim Configuration In sketch form: Firstly, you need to tell Exim that the domains listed in the domain table of the database are `local'. You probably have other local domains which you list explicitly, so you should do something like local_domains = <#(your existing domains separated by #s)#\ mysql;select domain_name from domain \ where domain_name = '${quote_mysql:$key}'#\ mysql;select alias from domain_alias where alias = '${quote_mysql:$key}' (Note use of the <# syntax to change the separator of the domain list. This is described in section 7.11 of spec.txt, and is required because otherwise the : in the quote_mysql operator causes the list to be broken half-way through the SQL query. Cf. http://www.exim.org/pipermail/exim-users/Week-of-Mon-20000515/018154.html which is incorrect.) Next, you need to organise things so that mail to virtual-domain users actually gets delivered. This is done using three directors, which figure out where virtual addresses go: --- from exim.conf, DIRECTORS section # The virtualemail database allows local parts to correspond to remote # addresses (forwarding), to be aliases for local maildrops, or to be local # maildrops. These are expressed in tables forwarder and popbox # respectively. # Handle forwarders and aliases (same database table) virtual_forward: driver = aliasfile search_type = mysql query = "select remote_name from forwarder left join domain_alias on domain_alias.domain_name = forwarder.domain_name where local_part = '${quote_mysql:$local_part}' and (forwarder.domain_name = '${quote_mysql:$domain}' or alias = '${quote_mysql:$domain}')" forbid_file = true forbid_pipe = true # Handle delivery; see sample config C009 for what this is based on; it only # handles things for the virtual domains, which are listed in the database. virtual_localuser: driver = aliasfile search_type = mysql query = "select mbox_name from popbox left join domain_alias on domain_alias.domain_name = popbox.domain_name where local_part = '${quote_mysql:$local_part}' and (popbox.domain_name = '${quote_mysql:$domain}' or alias = '${quote_mysql:$domain}')" transport = virtual_localdelivery # deliver any undelivered virtual email according to the _default_ forwarder virtual_defaultuser: driver = aliasfile search_type = mysql query = "select remote_name from forwarder left join domain_alias on domain_alias.domain_name = forwarder.domain_name where local_part = '_default_' and (forwarder.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}')" forbid_file = true forbid_pipe = true The virtual POP boxes are handled by a transport as follows: -- from exim.conf, TRANSPORTS section # This transport is to deliver mail going to virtual domains to user # mailspools in directories under /var/spool/mail/SERVERS/foo/bar; this is # triggered from a director below. virtual_localdelivery: driver = appendfile file = ${lookup mysql{select path from domain left join domain_alias on domain_alias.domain_name = domain.domain_name where domain.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}'}{$value}fail}/${lookup mysql{select mbox_name from popbox left join domain_alias on popbox.domain_name = domain_alias.domain_name where (popbox.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}') and local_part = '${quote_mysql:$local_part}'}{$value}fail} delivery_date_add envelope_to_add return_path_add user = ${lookup mysql{select unix_user from domain left join domain_alias on domain_alias.domain_name = domain.domain_name where domain.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}'}{$value}fail} mode = 0660 (The comment about directories is true in the setup in which we use this, but it's not a requirement. You can put the virtual mailspools anywhere.) In order to have Exim talk to the database at all, you will need a configuration line of the form hide mysql_servers = "host/name/user/pass" where host is the host running the MySQL server, name is the database name, user is the username under which to access the database, and pass is that user's password. Exim only needs to read from the database, so it is good security practice to give the specified MySQL user account the minimum privileges consistent with this requirement. The "hide" modifier prevents Exim from making this config option readable using e.g. exim -bP. Using another MTA-- we don't know of any other MTAs with native MySQL support, but we haven't been looking for any, either. If all else fails you could use program routing or its equivalent to fork a process to do database lookups; this is a performance hit, but [a truism] computers are fast and memory is cheap. 3. POP3 Daemon It is strongly recommended that you use tpop3d, rather than gnu-pop3d, which was the POP3 implementation used for the first releases of vmail-sql. You can obtain tpop3d from http://www.ex-parrot.com/~chris/tpop3d/ This supports the vmail-sql authentication scheme for virtual domains natively. Consequently, the patch for gnu-pop3d is now not actively maintained and due to a number of outstanding issues with gnu-pop3d, we recommend that you do not use it if possible. tpop3d runs on Linux and BSD, and should port relatively easily to other Unix systems. It is considerably faster and more robust than gnu-pop3d. If you use tpop3d, ignore the following section: If you do wish to use gnu-pop3d, please apply the patch in the distribution; it is a standard unified diff; cd to the gnu-pop3d directory and do `patch -p1 < gnu-pop3d-0.9.8_vmail-sql-0.2.patch' to apply it. Note that, if the patch is applied, it is extremely important that unprivileged users cannot read the binary (for example, to run strings(1) on it). The binary will contain enough information for any user to connect to MySQL, and potentially screw with your database. Also note that, in this version of the patch at least, you cannot safely run gnu-pop3d in daemon mode with virtual domains support enabled. There is a second patchfile, gnu-pop3d-0.9.8_vmail-sql-0.2_pamhack.patch, which if applied after the above patch adds an option to gnu-pop3d to allow selection of its PAM service name: `-s service'. This is intended for situations where you run a SSL-proxied gnu-pop3d for unix users, and another for virtual users; you can then use PAM config files to select different security strategies for them. This will probably get replaced/augmented with options to enable and disable PAM and virtual logins on a per-session basis. NB: Do not use gnu-pop3d. If you do use gnu-pop3d, expect bad things to happen. If you ask us for support with gnu-pop3d, we will suggest that you use tpop3d instead. 4. User Configuration Clearly this is a matter of site policy, but however you handle it you will need a set of scripts to manipulate records in the database. The scripts/ directory contains some basic command line scripts for configuring the database. These are intended to be used for administrative tasks, such as adding domains or reseting passwords. The user running these scripts will need to have permission to read the DomainAdminConfig.pm file (see below), in order to obtain the database username, dbname and password. 5. Web Configuration This distribution also includes a set of CGI scripts allowing users to configure their domain setups. The CGI scripts give a "Domain Control Panel" which allows the user to configure the email setup for their domain, and also to change the domain password. This is the "login" script in the cgi-bin directory. There is also a "passwd_popbox" script which allows a POP3 user to change their password, without knowing the domain password. Mixing CGI with database access raises a security issue on multi-user machines (and single-user machines with any concept of local security). The CGI scripts will usually get run as the same user as the webserver (usually one of apache, httpd or nobody). The scripts need to know the database password, so it must be stored in a file readable by this user. This means that any user who has access to read files owned by the webserver user can obtain your database password. Clearly this may not be desirable. If this is a problem, we suggest using Apache's suExec to have the CGI executed as another user. Your database details will be stored in the file DomainAdminConfig.pm, stored in the "lib" directory specified at install time. This is a therefore a sensitive file, so permissions should be restricted carefully. 6. Password-hash format Passwords are stored in the database in the form of a hash. From version 0.4, several different hashing methods are supported by tpop3d and the scripts in this distribution. The format of this hash is: {method}hash or hash Currently supported methods are: md5 - a straight MD5 hash of the password, represented as a hex string. For backwards compatiblity, this is the default if no method is specified. crypt - the password is hashed using the system crypt() function. What method this actually uses is system dependent. It is provided to make it possible to migrate passwords from the system password database to the vmail-sql crypt-md5 - this password hash is a salted MD5 hash of the password. This should be the same method as used by your system if it supports MD5 passwords in your /etc/shadow file. On some systems this will give the same results as the crypt method. plaintext - the password is stored unhashed. This is necessary if you wish to use tpop3d's APOP authentication method. Using APOP has the advantage that the passwords are not sent in plaintext when authenticating, but the disadvantage that they are stored in plaintext in the database. 7. More information If you use vmail_sql in anger, we'd be interested to hear. There is now a mailing list for vmail-sql, to which you can subscribe by sending a mail with the subject `subscribe' to vmail-discuss-request@lists.beasts.org. This is a low-traffic list which will also be used for announcements of new revisions of vmail-sql. An archive of posts to the lists is available at http://lists.beasts.org/pipermail/vmail-discuss/ Enjoy.