Re: [exim] dbm to MySQL migration

Pascal wrote:

> On 8/31/06, W B Hacker <wbh@???> wrote:
>
>>
>>Maildir is better for most use, not all.

*snip*
>
> I still have to look into the advantages of the Maildir format, just like
> which POP/IMAP daemon
> to use next.
>
> Why offer only IMAP?

Primarily reason is because nearly all of our user community are
'internationalists' who travel extensively, work from home and office, have
multiple offices or assignments, use 'borrowed' or leased machines from time to
time.

Secondary reason is having several significant 'workgroup' environments where
another team member may need to pull-up and respond to messages.

Finally, many of htese foks use complex heirarchies of 'folders'.

Keepign all that synchronized between server-side and multiple local folders is
inherhent in IMAP with no special traing to speak of.

Do so with POP is deucedly difficult, even for the more expert user with
specialized clients that are not available when on a borrowed machine.

'IMAP only' increases or storage needs, but actually reduces bandwidth as
synchronization doesn't copy what it already has, and the client we use most
(Mozilla) only displays headers until a message is opened.

> Okay I run a small system but so far I've never receive
> a question to
> provide IMAP. I also did some work for a large ISP (over 1.5 million
> mailboxes) they also only
> offer POP3 and use IMAP only on their backend network for webmail use so not
> directly
> to customers. And this is mainly because they create a spam folder where
> they put all spam
> tagged mail. Which can only be viewed / removed through webmail.
>

Most, but not all Webmail services and clients are IMAP-style, so an 'all IMAP'
environment also removes the differences between what the user can access with
webmail, and what they see with POP.

>
>
>>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.
>
>
>
> So just to be sure I understand:
>
> localalias: localuser1,localuser2,remote@address
>
> would become 3 different records in the database? And you retrieve the
> destinations
> with one single SQL query?
>

No - the reverse. At least if you want to use it the same way in the MTA.

ONE field of ONE record and ONE SQL return.

That means you need to also store <username@???> as one field of one
record. ELSE you will be unable to make sense of the differences between a
string-that-becomes an Exim construct (your present DB entry), and a string
concatenated from $local_part + $domain, each stored separatly. FWIW, we do
*both*, AND store the 'longname' in its own field.

Careful planning and testing needed here, unless you have a method of ID'ing and
making two different types of calls, and doing so in advance of knowing which
type of return to expect.

That said, you may already have just that. The DB you now use may not be
SQL-driven, but it is still made of fields within records.

>
>
>>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.
>
>
>
> Cool feature. You still see a lot of mailbox / FTP account / whatever
> password sharing
> but it's quite nice to seperate accounts for different use. Although
> sometimes may be
> confusing to the customer.

As most users configure our Webmail to access multiple accounts, POP as well as
IMAP, (gmail and such, even) not all of which are on our own service, the fact
that the client itself has a UID:PWD of its own is not really a barrier.

>
>
> You will, however, also find my name on other posts saying we *use* SQL
>
>>DB-driven Exim, but do NOT recommend it to others.
>
>
>
> Well my system was setup about 6-8 years ago with Exim3. With an even
> smaller group of accounts and domains. And as was mentioned in another post
> I was not a big fan of putting user info into a database as databases may
> become unreachable and a password file always works fine (and if it doesn't
> you probably have bigger issues).
>

Not so much 'bigger' as 'different'. Corporate use, mostly, and high-net-worth
retirees, former Board Chairmen and Directors, etc.

Security-conscious and demanding folks with little tolerance for spam or
nuisances. Myself fist among them ... ;-)

> Even though the system still works fine I am looking at a redesign of the
> whole
> system. It would be nice to virtualize the users into the database. Making
> everything
> authenticate against the database.

That makes admin *much* cleaner...

Besides mail also FTP accounts

Not allowed here. scp or such if/as/when, but never legacy ftp...

Ordinarily only 3 to 5 shell accounts in total. 3 sysadmins on at least two
continents cover 24X7. Maybe a couple of R&D partners on soem boxes.

'Lusers' can buy their own damn box ;-)


> and
> probably
> more to think off. We also have a really small (and lame...) customer care
> center
> for people to change their mail aliases and create accounts. Also want to
> redevelop
> this as well and improve the stuff a customer can change regarding mail /
> pop, dns,
> ftp, websites etc.
>
> Which ofcourse it probably is easier to switch to Plesk, DirectAdmin or
> whatever.
> But so far have no intentions to do so.
>

Too many of the 'common' tools, such as phpmyadmin, are far worse security
nightmares than not.

Whether you use an SQL DB or not, make sure your UI tools are as secure as can be.


> 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.
>
>
>
> Wouldn't call myself a SQL expert. Though not inexperienced either. But as
> it
> is not my every day job to create datamodels or sql queries I struggle at
> first
> to make a good setup. Hence the question for some assistance in this ;-)
>
>
>
>>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!
>>
>>I like the pain, and ofcourse I would like to share my pain with my users
>
> as
> they are just there for my enjoyment... :-) *BOFH*
>
> I guess I still have to create and probably refine, and refine and refine a
> datamodel
> for this and just start fooling around on a playground system and see how it
> all
> works.
>

W/R DB design - do not over normalize. ONE table for all mail-related settings
may seem to violate Codd & Date, but is way easier to work with than several
'relations'.

> Thanks for the answer so far :)
>
> Cheers,
> Pascal

Good luck with it. And *test* well...

Bill