Re: [exim] dbm to MySQL migration

Pascal wrote:

> Hi,
>
> I run a small mail server with about 40 domains. Right now I use
> exim_dbmbuild to create the dbm files of the different domain mailer tables,
> including dbm files for the local and relay domains. I however would like to
> migrate all of this to a MySQL setup (at least I think I want that ;-)).
> Reason for this is that I now run a system wide setting for spam and virus
> scanning and that this is not really user friendly as I want to give the
> users a choice in if they want spam and virus scanning per user.

We do that (PostgreSQL), and also use per-user thresholds for protocol-violation
'scores', X-<various> header-add thresholds, divert-to-quarantine, and outright
rejection thresholds.

> And I also
> would like to switch from creating actual users on the system to switch that
> to only MySQL users.

Likewise. Even 'postmaster' is in the SQL DB here.

> Which probably also makes it more wise to switch to
> maildir format and something like Courier.
>

Maildir is better for most use, not all. But we also store per-user selection
of storage type (Maildir, mailstore, Mbox...) and storage location
(concatenating the path and folder names) in several fields in the DB.

We do not offer POP, use Dovecot vs Courier, as it is closer to Exim w/r
PostgreSQL configuration. Have used Courier-IMAP with MySQL, and Courier-MTA
(IMAP/POP) with PostgreSQL.

Dovecot is *way* easier to configure and integrate in an SQL environment.

>
> With the dbm process I can do stuff like this:
>
> mylocalalias: localuser1,localuser2,remote@???,localuser3
> *: localuser1

One can store/map and retrieve the same sort key and the same string in an SQL
DB field.

>
> And mail will be delivered to all the local and remote addresses and
> everything else will be dropped in the localuser1 mailbox.
>
> I've looked at a couple of MySQL examples but they seem to usually limit the
> localuser drop to 1 user so alias1@??? will always only be dropped
> into a localuser1 mailbox. Which is not the behaviour I want. However I
> assume that - flexible as exim is - I can also get the behaviour I have with
> a MySQL setup.

So long as the RDBMS fields are setup to permit more than just
'username@???' (as your DBM record does), then no difference.

You need to insure that the query can offer either response to Exim w/o munging
it. (i.e - do not concat a '@' between local_part_field and domain_field).

>
> As I would like to get my new setup done properly at once I hope someone can
> assist me in making the right choices. Or better yet if somebody want to
> share their setup with me would be even better as it makes no sense for me
> to engineer what already has been engineered before and has proven to work.
>

Our setup - directly conveyed - is highly specialized to a number of features
that would do you more harm than good, (*most* confusing!) but we are happy to
help 'SQL'ers' with the odd bits and pieces where we can.

The PostgreSQL features we use that MySQL lacks all have to do with our unique
needs. The rest would work equally well with MySQL syntax.

> Assuming I could create a MySQL table which can contain several local users
> and several remote users for the same alias. Would it be wise to create
> single records for each local / remote user for the same alias?

For 'convergence' (aliases) we list each 'identity' in its own record. To the
DB, there need not have to be any functional distinction between an alias and a
'real' account.

We simply load the same storage type and directory location into both records.

Thereafter, any of the identities can be removed without affecting the
functionality of the others.

The same sort of approach allows inherent 'sharing' of the messages stored, as
for a 'Help Desk' or NOC, which is one of the reasons we offer only IMAP, no POP.

> Or just put
> everything in one record? Or split the record in a localusers part and a
> remote user part and use different routers for them?
>

We separately store the local_part, domain, mail storage type, mail storage
location, UID, GID (for file privileges), login identity (in our case NOT the
email address), 'longname', plain and crypted passwords for each type of
identity, boolean status flags, heirarchical/supervisor/group oversight rights,
privileges, archiving settings, etc.

We also use separate identities and passwords for smtp submission, IMAP
recovery, and DB access for user preferences alteration.

Webmail has an entirely separate ID:PWD set, similarly arranged, which allows a
traveler to change their webmail UID:PWD after each use of an untrusted box,
(and not via said untrusted box!) to protect against key-loggers, while leaving
their normal MUA UID:PWD alone.

- Over 40 fields in all.

Note that all of this is *way* more complex than most folks need!

You shouldn't need even a fraction of that, but it does what we ask of it, and I
mention it only to illustrate that Exim/Dovecot/SA/ClamAV are comfortable with
pretty much whatever you might need to do that an RDBMS supports.

Exim has the best, cleanest, and easiest to use SQL integration of any available
MTA, and we have tried many.

You will, however, also find my name on other posts saying we *use* SQL
DB-driven Exim, but do NOT recommend it to others.

The reasoning is that the DB side is likely to be more work with a steeper
learning curve than the Exim side - unless you are already quite SQL-expert.

And - hate to say it, but *most* folks 'interested in MySQL <whatever> don't
really know SQRT-Fine Attitudes about SQL or RDBMS' in general, so will
struggle... PostgreSQL, RIM, DB2, Oracle veterans are another story...

Also - depending on an RDBMS engine means one more thing that can go wrong, so
overall the DB-driven system is more resource-intensive, and somewhat slower and
less robust than flat files or a BDB, GDB, CDB environment.

If you want to enable per-domain settings only, it may not pay.

If you want per-domain and per-user individuality, it *still* may not pay vs,
for example, simple Maildirs with .forward and .spamassassin, .<whatever>
personalization files.

OTOH, If you are also running other DB-intensive apps on the server, or on other
servers, such as to keep you involved and current with RDBMS maintenance and
SQL, then it is much less added work.

By no means should you try to migrate your one-and-only production box to
SQL-driven unless you are into SM & Bondage as well as SQL!

Extensive testing is a must, and best done on an R&D box.

You should also plan to spend a lot of time tail -f -n <big number> the logs for
several months after cutover. Some queries work fine until the unexpeected
happens, then bite yerazz - and silently reject.

BT,DT, GTTS. With bullet-holes, even ;-)

HTH,

Bill











> So if somebody who has already made a setup like I want and would like to
> share their culprits, setup of whatever they want to share that would be
> greatly appreciated.
>
> Cheers,
> Pascal