mail server with MySQL virtual users?

maxblack · #1 22nd March 2005, 12:14 PM

I keep running into brick walls on this: why is it so difficult to set up a mail server without system accounts? My latest attempt has been with Postfix (recompiled with MySQL support) and the stock Fedora Dovecot, but SMTP authentication among other things have kept me debugging for days and I'm fed up.

More than going into tedious detail about any particular config, what I (and many many others) would really like to see is a simple RPM or SRPM combination of apps that would work almost out-of-the-box with MySQL and perhaps a web-based account manager (like Postfixadmin). One should almost expect this to be included in the default distribution of Fedora...!

Scenario: A box with hosted domains and the need for managing email addresses painlessly without messing with system accounts. SMTP authentication and quota control is a must.

It must be possible?

regards,
Max

nephila · #2 22nd March 2005, 01:47 PM

I know this is possible using qmail. If you are interested check out www.qmailrocks.org

maxblack · #3 22nd March 2005, 04:04 PM

Thanks Nephila, I had a look at that. Qmail seems to need all this:

Qmail + qmail patch with qmailqueue, smtp-auth and TLS smtp encryption + EZmlm + Autoresponder + Vpopmail + Vqadmin + Maildrop + Qmailadmin + Courier-imap/imap-ssl + Squirrelmail + Clam Antivirus + SpamAssassin + qmail-scanner + qms-analog + qmailanalog + q-trap

That's 17 new and different components and patches that have to be compiled and installed. Yes, there is a tutorial walkthrough but I simply got the shivers.

Maybe keeping on trying with Postfix+Dovecot+MySQL isn't such a bad idea after all

...there are lots of tutorials out there about one or another but never with the three together, or has anybody found one?

nephila · #4 23rd March 2005, 07:28 AM

Understandable, when I first started I got more than just the shivers, but working through that tutorial I had a mail system up and running within about two hours. It looks nasty but it is really simple if you follow the tutorial. For the record I no longer use qmail, am using postfix with dovecot but using system accounts not mysql. Keep at it!

rae · #5 5th April 2005, 09:56 PM

maxbalck,

I am attempting the same thing you are but wondering if the default install of postfix is already compiled with mysql support? If not, I was wondering if you compiled it from source or a source rpm?

rae

maxblack · #6 6th April 2005, 09:03 AM

Rae, my fc3 stock postfix rpm didn't come with MySQL support. I installed the Postfix src.rpm, modified the .spec file to include MySQL support and then did a rpmbuild. There are plenty of howtos out there on Google about how to do this.

BTW still searching for the Holy Grail but don't feel like wandering down the Qmail path now that I've learnt so much about Postfix. Switched to Postfix+Courier IMAP+MySQL following the http://high5.net/postfixadmin/ howto to the letter, and everything is working except for the SMTP Auth part since my compile of SASL2 absolutely refuses logins with LOGIN encrypted MySQL passwords. Unencrypted passwords (horror) works fine though.

When I have time I'll try pam-mysql authorization through saslauthd, it seems to be a common workaround. It's really insane that one has to go through all this to set up something that should be fairly standard...

rae · #7 8th April 2005, 01:04 AM

maxblack,
Instead of changing mail servers I opted for changing the db from MySQL to PostgreSQL. typed it in google and came up with this. http://www.tummy.com/Products/vpostmaster/ very nice and sleek and works apparently. You don't really need to configure anything. Its all done for you.
rae

maxblack · #8 8th April 2005, 08:34 AM

Thanks Rae, looks nice...it even installs with yum! Looks like I have to shell out $250

, since my server is commercial.

I'll try it out first on my home box...

maxblack · #9 8th April 2005, 09:49 PM

Got the shivers again...Vpostmaster is real easy to install and should work great, but there seems to be no back door to hack it to your needs, there's ONLY the web interface...

Went back to my original config (the one I had from http://high5.net/postfixadmin/ howto) but tried the pam-mysql way of doing smtp auth instead of the described sasl2 way, and after some debugging...it worked!

# rpm -Uvh pam_mysql-0.5-0.src.rpm
# rpmbuild -ba /usr/src/redhat/SPECS/pam_mysql.spec
# rpm -Uvh /usr/src/redhat/RPMS/i386/pam_mysql-0.5-0.i386.rpm
# rpm -Uvh /usr/src/redhat/RPMS/i386/pam_mysql-debuginfo-0.5-0.i386.rpm

file /etc/pam.d/smtp:
auth sufficient pam_mysql.so user=postfix passwd=YOURPASSWORDHERE host=localhost db=postfix table=mailbox usercolumn=username passwdcolumn=password crypt=1 sqlLog=0
account required pam_mysql.so user=postfix passwd=YOURPASSWORDHERE host=localhost db=postfix table=mailbox usercolumn=username passwdcolumn=password crypt=1 sqlLog=0

file /usr/lib/sasl2/smtpd.conf (using stock FC3 cyrus-sasl):
pwcheck_method: saslauthd
mech_list: PLAIN LOGIN

file /etc/sysconfig/saslauthd:
SOCKETDIR=/var/run/saslauthd
MECH=pam
FLAGS="-r"

jafo · #10 2nd May 2005, 10:40 PM

Help me understand what you mean by a "back door". vPostMaster includes full source to the glue and management code, so that could be modified if necessary. vPostMaster is largely glue which consolidates a whole slew of existing tools such as Postfix, Squirrelmail, Dovecot, Spam Assassin. Therefore, if you want to tweek the settings of those tools, you can go in and make modifications to the setup to customize it to your exact needs.

I'm very interested in hearing more about what you wanted to do but thought you were unable to do with vPostMaster. One of the primary goals of vPostMaster is to be an easy to install and maintain mail system, which provides all the typical functionality required of today's mail servers. I plan to enhance it with extra spam prevention and other features, but an experienced user should be able to go in and make modifications to Postfix or Spam Assassin or ClamAV, for example, to further customize how it works beyond the base functionality provided. That's one of the benefits to our using existing tools instead of writing more of it from scratch.

Thanks,
Sean

jafo · #11 3rd May 2005, 02:05 AM

The short answer to why this is so difficult is just that there are a lot of little things to get right. The basic system, with just Postfix, Dovecot, and one of the databases really isn't that hard to get working. It would typically only take an hour or two for someone familair with the individual tools (but not familiar with the combination) to do. However, you probably want something a bit more full-featured than that. For example, you probably want some sort of control panel to automate creation and management of these virtual accounts. And you can't run a server on the Internet today without some level of anti-spam. Then you probably want webmail, and a user control panel so that users can manage their accounts. And SMTP AUTH relay authentication...

Pretty soon you're talking about something which can involve days of installation and setup. This is exactly why we built vPostMaster. It's a 5-minute install of exactly this consolidation of tools. We ask that people using it in a commercial environment help fund the development and maintenance of it, but we for trial and non-commercial use it's free.

So, yes, it is possible, and it's also easy, depending on the way you go.

Sean

maxblack · #12 3rd May 2005, 07:47 AM

Hi Sean,

Your email system should be great for anyone who needs exactly what you've created, and also for "real" full-time programmers who can analyze your source code or "glue" at a glance. I'm sorry if I picked on vPostmaster. What I wanted to say was that if one needs to hack source, people like me with a limited programming experience usually finds it more difficult to modify a finished control system than to roll his/her own simple one. I've had this situation while modifying web portal projects in PHP, for example: one has to first understand the underlying base systems, and then on top of that understand the control panel programmer's train of thought.

cheers,
Max

jafo · #13 3rd May 2005, 06:20 PM

Fair enough. On the other hand, of course, you did spend several days poking at the SMTP AUTH stuff, so maybe it wasn't such a big win. ;-) There's a lot you can do in vPostMaster without having to resort to hacking the vPostMaster code, because of the way it's designed. However, to be honest, there's not any documentation yet about the low-level of vPostMaster. I'll add that to the TODO list.

Thanks,
Sean