Re: [exim] Exim setup for dbmail

Pàgina inicial
Delete this message
Reply to this message
Autor: Bill Hacker
Data:  
A: exim-users
Assumpte: Re: [exim] Exim setup for dbmail
Mathieu CATTIN wrote:

> Hello, I would like to know if it is possible to use dbmail (
> http://www.dbmail.org/ ) with exim


Yes. But you might not want to do so.

Long answer, 'coz this wheel has been reinvented too many tmes already.....

> and if somebody can point me in the direction on how to configure
> exim the right way for this.


Routers, transports, and such (which I no longer have copies of), can
allow use of DBMail with PostgreSQL DB format for UID/PWD checking
and mail storage with Exim.

We have also done the UID/PWD part,
but not the storage, with Courier-MTA.

Such should work essentially the same with MySQL.

> Basically I want to use Mysql to store mails.


This comes up often. Too often.
SQL'ers (myself among them) tend to apply our 'hammer'
to every 'nail' that comes along.

Experience will show that it is seldom the best way to go.

> Dbmail is capable of redirecting the mails via a called program in a
> database,


'Capable' yes.

Any dog is capable of 'grooming' contortions most humans wouldn't
even *want* to attempt. So, too with applying the wrong tool to the job.

MTA's in general, Exim in partiucular, and the storage methods they
utilize most often are the product of several person-millenia of
'in use' experience worldwide.

> I just didn't figure out the right transport/router to do it. Then I
> will use dbmail to retrieve the mails with a MUA via pop3 or IMAP (
> dbmail accepts both with dbmail-pop3 and dbmail-imap which I already
> compiled and tested and seems to work).


'Work' is the operative word. Storing message traffic in an SQL DB
brings more problems than solutions for most 'real world' instances.

Not to be negative - but:

'been there, done that, and my tee-shirt has bullet-holes in it'

DBMail and PowerMail only have value in very specialized cases - even then,
there may be more efficient ways of getting the same result.

First, DB storage *way* more than doubles the server workload, as an RDBMS
'engine' does a lot more work to store (and prepare to 'manage') information
than the 'raw' file system, then must *also* ultimately store its tables
*IN* said file system.

Don't forget he DB engine still needs an MTA (and POP and IMAP) with
RFC-compliance, HELO/EHLO, STARTTLS, etc.
- none of which can be implemented in SQL.

Secondly, an RDBMS increases admin workload by an even greater amount.
RDBMS do not automatically clean up after message recovery/deletion.
A separate 'pack' and re-indexing must be done - at least as a chron job.

This is especially true of POP, where a message is expected to go
OFF the server when retrieved - may exist 'on box' for only minutes.
Maildir's need no further attention as this occurs, DB-storage does.
Bigtime.

DB storage is better suited to IMAP, but even then, user deletion,
not only of messages no longer of interest, but of whatever spam
(SA flagged/scored, or not) has been stored for them mandates frequent
DB packs. And/or managing 'deleted' flags where the message is NOT
going to be deleted (corporate 'big brother').

Third - storing headers, message body, and mime atachments within a
relational DB is not as straghtforward as storing them in the file system.

These are different animals as far as format, variable length, character
encoding etc. - a serious challenge even to an Object DB, and can be
poison to the regularly-structured Relational DB.

Check the history of DBMail's gradual development in this regard, and
note it has not been a 100% problem-free path, nor is it yet today.
Note, too, that 'the other' DB-based MTA, PowerMail, chose to use
the DB only for indexing, and stores the traffic in the fs.

Otherwise - attachments alone can drive one into the 'least efficient'
corner of RDBMS data types - the BLOB. And spam kills you.

Side issue, but an important one - MySQL is not inherently 'transactional',
PostgreSQL is. While not a double-entry accounting exercise, it IS important
that messages believed stored or recovered are in fact 'fully completed'
operations. While not usually a problem, MySQL does not inherently
guarantee this under all circumstances, so for safety's sake, one should
either patch on the InnoDB backend - or use PostgreSQL (Oracle, DB2 $$$)
instead.

Yet *more* work w/r configuration, maintenance, and CPU and I/O cycles.


Recommendation:

Use Exim set to create maildir's on the fly, Dovecot set to alter them
for IMAP use if so invoked, and both interfaced to the same SQL DB
(if you must) to store domain, UID, PWD, mailstorage type and location,
(you can mix them - maildir for users, mbox for a per-domain archive).

This will allow you to use any of dozens of web-based tools, with
appropriate privilege levels in the DB, to support admin and
end-user configuration, password change, etc.

That, BTW is one of the only 'DB' advantages, and only slightly better than
vpopmail with a php admin interface at that. (CAN be more secure, is not
automatically so... PHP security is not *always* broken)

Cleaning up and packing the DB after user deletions needs a 'pack' only
once in a blue moon, and the DB calls for domain, UID/PWD and such are
far 'lighter' than would be required to store and retrieve actual
message content/attachments in the DB.

Bottom line (from 2+ years of testing):

Exim and Dovecot (among others) are highly optimized for message
handling, and are two of the easiest tools to interface to an SQL DB,
to each other, and to virus and spam tools.

RDBMS's are general-purpose, and carry too much 'overhead', both in CPU
and I/O cycles and storage space inefficiency for simple message
traffic.

- SQL DB's *can* provide a flexible means of storing UID/PWD, storage
parameters, partly because there are so many available tools to allow
'safe' control from userland - but they have no patent on this, LDAP
works too.

- This is an inherently 'virtual' environment - not even one shell account
needed once set up. Again, not the only way to get there.

- conventional mbox remans best for for mailing lists, archives, other
multiple-read, seldom-deleted use.

- conventional (or enhanced) Maildirs remain best for all else - especially
POP, and any other recover-once, delete-usually use.

Either/both are 'leaner', faster, require less man and machine
overhead, and are far more 'universal' across potential future changes
or upgrades to MTA, POP, or IMAP daemons than SQL DB storage.

These are THE 'road well traveled' by essentially all MTA, POP, IMAP
tools - and with good reason.

YMMV, but 'if it ain't broke....'.

Bill Hacker


> Specifications of my server : - Linux slackware (FREESCO v0.3.4
> http://www.freesco.org) - Exim 4.50 (MTA) - Mysql 4.1.7 - DBmail
> 2.0.4 Thanks in advance, best regards, Mathieu CATTIN