Virtualmin + Postfix + MySQL (and Postfixadmin)

16 posts / 0 new
Last post
#1 Thu, 09/25/2008 - 00:30
Acorp

Virtualmin + Postfix + MySQL (and Postfixadmin)

Hello all,

Background: CentOS 5.2 on a Xen Virtual Machine; Apache, Postfix, Webmin, Virtualmin, MySQL, and pretty much all of the rest of the goodies. With some modification, I essentially followed this "How-to" to get the server up and running: http://www.howtoforge.com/virtual-hosting-with-virtualmin-on-centos5.1

I'm having some trouble getting Virtualmin to work with Postfix and MySQL backend. I understand that configuration (with MySQL) is frowned upon, but I couldn't get the "easy" flat file configuration to work.

Initially users created by Virtualmin in the /etc/postfix/virtual flat file weren't seen as valid users by the system. Mail arriving for a user would bounce with a message similar to "User unknown in Virtual Alias Table," and of course that user couldn't log into the system to retrieve email either.

I fought with that for almost a month, then gave up and installed PostfixAdmin and changed /etc/postfix/main.cf to use MySQL instead of flat files. I created the requisite MySQL database and the necessary tables, and set up a virtual domain and users using PostfixAdmin. Now I can receive mail to those users. I VERY ROUGHLY followed this guide to set up PostfixAdmin and the MySQL databases and the main.cf changes: http://www.opensourcehowto.org/how-to/mysql/mysql-users-postfixadmin-pos...

But...I can't seem to get Virtualmin to work with the MySQL setup. My current relevant tables in the MySQL Postfix database are: alias domain mailbox

There use should be obvious, but alias is for aliases, domain contains all the virtual domains, and mailbox contains the username, password, full name, maildir location, and so on.

PostfixAdmin writes to these tables fine, and when virtual users and domains are created using PostfixAdmin, I can send and receive mail to/from them from external computers through the internet to this server.

When I try to create a new virtual domain with Virtualmin however, it creates a new table in the MySQL Postfix database with the name of the virtual domain, while PostfixAdmin simply writes a new record into the "domain" table in the MySQL Postfix database. These new virtual users and/or domains created by Virtualmin once again don't work ("User unknown in Virtual Alias Table" when I try to send email to them, and login attempts either from the CLI of the local server or from the internet fail.

Anyway...I don't care what happens "behind the scenes," what databases and tables are written and used, etc. What I'd really like to get is a working system with Virtualmin, NOT PostfixAdmin, whether that entails using flat files or MySQL databases, I don't really care.

So, now that I've written a novel, what information do you need from me to help me out?!

Thanks in advance.

Thu, 09/25/2008 - 13:36
Joe
Joe's picture

<div class='quote'>I'm having some trouble getting Virtualmin to work with Postfix and MySQL backend. I understand that configuration (with MySQL) is frowned upon, but I couldn't get the &quot;easy&quot; flat file configuration to work.</div>

Holy cow. Words fail. ;-)

How could you have gotten the MySQL configuration, which is incredibly complicated and fragile, to work, and a flatfile configuration (which requires literally <i>one</i> configuration directive in main.cf) gave you trouble?

So, ditch MySQL for mail users. Seriously. Life is complicated enough without introducing a <i>relational database</i> into the mix for extremely simple local key:value lookups. As I've mentioned elsewhere, it's much slower, requires tons more resources, and is a lot harder to manage. Virtualmin could probably be convinced to work with your configuration...but I'd suggest not doing so. (Obviously, I'm not a fan of using MySQL as a user database, except in cases where the users already have to be in MySQL for other reasons.)

OK, so converting to a more sane configuration...

Plan for a few minutes to an hour of downtime (it sounds like you've already got troubles, so maybe downtime isn't a big deal).

How many users do you have? If it's a lot, you'll want to write a script to pull things out of the DB and dump them into Virtualmin using the command-line tools. If it's only a few, just plan to add the users using Virtualmin once you've switched the configuration. If you need help scripting this, let me know, and I'll offer some suggestions.

OK, once you have a plan for the move, shut down Postfix (mail will be held by the sending server, or by your hold and forward server if you have a backup MX server--nothing is bounced as long as the server isn't down for more than about 24 hours), and fix the configuration.

I would probably suggest starting from scratch on your postfix configuration (though you'll want to make backups of the configuration files in /etc/postfix, just in case there's some other configuration you need, like certs or forwarding or whatever). Use RPM to remove the package:

rpm -e --nodeps postfix

Remove the bum configuration files:

rm /etc/postfix/*
rmdir /etc/postfix

Install postfix again:

yum install postfix

Use this script to configure it (or, if you have an oddball configuration for Dovecot and procmail, you might want to just pick out the postfix stuff and perform those steps manually...there are very few steps for Postfix--it's dead simple to configure for use in a Virtualmin system, as long as it isn't complicated by databases and such):

http://software.virtualmin.com/lib/mail-setup.pl

Once that's done, check things for sanity--configuration re-check in Virtualmin (you'll need to configure Virtualmin not to use MySQL for this stuff...reverting back to defaults), and try creating a user or two. Let us know the results, and include relevant maillog entries, and we'll help you work out the remaining issues (I'm sure there will be more issues...but I'm unfamiliar with the configuration you're coming from, so I don't know exactly what needs to be done to recover from it).

Another option, if you wanted to go for a &quot;default&quot; Virtualmin setup, would be to start from scratch on the whole system, and use the automated install script found on the download page. CentOS 5 is the best supported system by the install script, and should very reliably produce a nicely working system, with most features available out of the box. But this pretty much <i>requires</i> a fresh install of the OS (no Webmin, no Virtualmin, no Apache, nothing installed from alternate package sources, etc...a fresh OS install with the base and updates package repositories enabled and nothing else).

Hope this helps.

Holler if you run into any problems with the change. Things are not as bad as they seem--mail problems just look really ugly when they're happening, but in hindsight it'll all seem pretty simple, especially when you get MySQL out of the picture. I wish I could fix the Internet so it never guides people into using MySQL for this purpose. It just makes <i>no sense at all</i>...but you're not alone in ending up in this situation. People find stuff like this on the Internet and think, &quot;MySQL is awesome, so this must be an awesome way to deal with mail.&quot; They're right on the first count, but the second part just doesn't follow.

--

Check out the forum guidelines!

Sun, 06/07/2009 - 07:29 (Reply to #2)
Acorp

<b>Joe wrote:</b>
<div class='quote'>
I would probably suggest starting from scratch on your postfix configuration (though you'll want to make backups of the configuration files in /etc/postfix, just in case there's some other configuration you need, like certs or forwarding or whatever). Use RPM to remove the package:

rpm -e --nodeps postfix

Remove the bum configuration files:

rm /etc/postfix/*
rmdir /etc/postfix

Install postfix again:

yum install postfix
</div>

Done!

<div class='quote'>
Use this script to configure it...

http://software.virtualmin.com/lib/mail-setup.pl
</div>

Done!

<div class='quote'>
Once that's done, check things for sanity--configuration re-check in Virtualmin
</div>

Sane!

<div class='quote'>
and try creating a user or two.
</div>

Two virtual domains created with two users in each.

<div class='quote'>
Let us know the results, and include relevant maillog entries, and we'll help you work out the remaining issues...
</div>

OK, I am now having problems logging in with POP3 to the server using the new users/passwords. Here are the relevant maillog entries:

[code:1]
Sep 28 03:17:08 aryjan dovecot: auth(default): new auth connection: pid=31023
Sep 28 03:17:19 aryjan dovecot: auth(default): client in: AUTH 1 PLAIN service=POP3 lip=192.168.1.210 rip=192.168.1.1 resp=
Sep 28 03:17:19 aryjan dovecot: auth(default): client out: CONT 1
Sep 28 03:17:19 aryjan dovecot: auth(default): client in: CONT 1 AGluZm9AZGFrb3RhY29ycC5pbmZvAGxhdXBwcHVy
Sep 28 03:17:19 aryjan dovecot: auth(default): pam(namex@dakotax.info,192.168.1.1): lookup service=dovecot
Sep 28 03:17:19 aryjan dovecot: auth(default): new auth connection: pid=31038
Sep 28 03:17:21 aryjan dovecot: auth(default): pam(namex@dakotax.info,192.168.1.1): pam_authenticate() failed: User not known to the underlying authentication module
Sep 28 03:17:23 aryjan dovecot: auth(default): client out: FAIL 1 user=namex@dakotax.info
[/code:1]

It appears that the problem is that the user is not recognized by PAM. What that means is over my head!

I checked the /etc/postfix/virtual file, and the user is in the flat file.

I ran:
[code:1]postmap -q namex@dakotax.info /etc/postfix/virtual

and get a result of:

namex.info
[/code:1]

The &quot;passdb pam {&quot; section of dovecot.conf is uncommented; the mysql section of the same is now commented out.

I don't know what /etc/pam.d/dovecot should look like, but I haven't changed it from the default installation. Here it is:
[code:1]#%PAM-1.0
auth required pam_nologin.so
auth include system-auth
account include system-auth
session include system-auth
[/code:1]

Anything else you need to help me out?

Thanks in advance!

PS: VERY off-topic-&gt; The &quot;preview&quot; function doesn't hardly work in Google's new &quot;Chrome&quot; browser. Text color is light gray on a light gray background; can't see anything unless you highlight it. Don't know if it is Chrome or the site, but thought I'd mention it.

Sun, 06/07/2009 - 07:29 (Reply to #3)
Acorp

<b>Joe wrote:</b>
<div class='quote'>
I would probably suggest starting from scratch on your postfix configuration (though you'll want to make backups of the configuration files in /etc/postfix, just in case there's some other configuration you need, like certs or forwarding or whatever). Use RPM to remove the package:

rpm -e --nodeps postfix

Remove the bum configuration files:

rm /etc/postfix/*
rmdir /etc/postfix

Install postfix again:

yum install postfix
</div>

Done!

<div class='quote'>
Use this script to configure it...

http://software.virtualmin.com/lib/mail-setup.pl
</div>

Done!

<div class='quote'>
Once that's done, check things for sanity--configuration re-check in Virtualmin
</div>

Sane!

<div class='quote'>
and try creating a user or two.
</div>

Two virtual domains created with two users in each.

<div class='quote'>
Let us know the results, and include relevant maillog entries, and we'll help you work out the remaining issues...
</div>

OK, I am now having problems logging in with POP3 to the server using the new users/passwords. Here are the relevant maillog entries:

[code:1]
Sep 28 03:17:08 aryjan dovecot: auth(default): new auth connection: pid=31023
Sep 28 03:17:19 aryjan dovecot: auth(default): client in: AUTH 1 PLAIN service=POP3 lip=192.168.1.210 rip=192.168.1.1 resp=
Sep 28 03:17:19 aryjan dovecot: auth(default): client out: CONT 1
Sep 28 03:17:19 aryjan dovecot: auth(default): client in: CONT 1 AGluZm9AZGFrb3RhY29ycC5pbmZvAGxhdXBwcHVy
Sep 28 03:17:19 aryjan dovecot: auth(default): pam(namex@dakotax.info,192.168.1.1): lookup service=dovecot
Sep 28 03:17:19 aryjan dovecot: auth(default): new auth connection: pid=31038
Sep 28 03:17:21 aryjan dovecot: auth(default): pam(namex@dakotax.info,192.168.1.1): pam_authenticate() failed: User not known to the underlying authentication module
Sep 28 03:17:23 aryjan dovecot: auth(default): client out: FAIL 1 user=namex@dakotax.info
[/code:1]

It appears that the problem is that the user is not recognized by PAM. What that means is over my head!

I checked the /etc/postfix/virtual file, and the user is in the flat file.

I ran:
[code:1]postmap -q namex@dakotax.info /etc/postfix/virtual

and get a result of:

namex.info
[/code:1]

The &quot;passdb pam {&quot; section of dovecot.conf is uncommented; the mysql section of the same is now commented out.

I don't know what /etc/pam.d/dovecot should look like, but I haven't changed it from the default installation. Here it is:
[code:1]#%PAM-1.0
auth required pam_nologin.so
auth include system-auth
account include system-auth
session include system-auth
[/code:1]

Anything else you need to help me out?

Thanks in advance!

PS: VERY off-topic-&gt; The &quot;preview&quot; function doesn't hardly work in Google's new &quot;Chrome&quot; browser. Text color is light gray on a light gray background; can't see anything unless you highlight it. Don't know if it is Chrome or the site, but thought I'd mention it.

Sun, 09/28/2008 - 17:27 (Reply to #4)
Acorp

<div class='quote'>
Anyway, it worked so well for Postfix, let's try it with Dovecot: uninstall it and reinstall it to get a fresh configuration.

Make a backup of the Dovecot config files.

rpm -e --nodeps dovecot
yum install dovecot
</div>

Done.

<div class='quote'>
Run mail-setup.pl again. (I ordinarily don't suggest running the same thing over and over, but I think the mail-setup script is smart enough not to redo things it's already done.)
</div>

Done. I did have to do some manual configuration of Dovecot - I am using IMAPProxy, so port 143 is dedicated to IMAPProxy, so I told Dovecot to listen on 10143 (the IMAPProxy port). Regardless, this shouldn't be relevant (I think) because right now I am just trying to get POP3 logins to work.

<div class='quote'>
Check things again. See if it looks happier. If not, send us logs.
</div>

Same result - new logs with same error:

[code:1]
Sep 28 22:02:59 aryjan dovecot: auth(default): client in: AUTH 1 PLAIN service=POP3 lip=192.168.1.210 rip=192.168.1.1 resp=
Sep 28 22:02:59 aryjan dovecot: auth(default): client out: CONT 1
Sep 28 22:02:59 aryjan dovecot: auth(default): client in: CONT 1 AGluZm9AZGFrb3RhY29ycC5pbmZvAGxhdXBwcHVy
Sep 28 22:02:59 aryjan dovecot: auth(default): pam(namex@dakotax.info,192.168.1.1): lookup service=dovecot
Sep 28 22:02:59 aryjan dovecot: auth(default): new auth connection: pid=29033
Sep 28 22:03:01 aryjan dovecot: auth(default): pam(namex@dakotax.info,192.168.1.1): pam_authenticate() failed: User not known to the underlying authentic$
Sep 28 22:03:01 aryjan dovecot: auth(default): client out: FAIL 1 user=namex@dakotax.info
[/code:1]

<div class='quote'>
BTW-Are you using usernames with @ in them? If so, you need to configure saslauthd to accept that. See this FAQ for the details:
</div>

Originally I was using the Dovecot SASL authentication instead of Cyrus SASL, but that may have been overwritten now. Be aware as far as know, I am spouting gibberish; I don't really know what I am talking about.

To answer your questions, yes, I have been trying to log in with namex &quot;@&quot; dakotax.info, so I tried logging in as namex-dakotax.info instead, with the following results:

[code:1]
Sep 28 22:04:01 aryjan dovecot: auth(default): new auth connection: pid=29043
Sep 28 22:04:05 aryjan dovecot: auth(default): client in: AUTH 1 PLAIN service=POP3 lip=192.168.1.210 rip=192.168.1.1 resp=
Sep 28 22:04:05 aryjan dovecot: auth(default): client out: CONT 1
Sep 28 22:04:05 aryjan dovecot: auth(default): client in: CONT 1 AGluZm8tZGFrb3RhY29ycC5pbmZvAGxhdXBwcHVy
Sep 28 22:04:05 aryjan dovecot: auth(default): pam(namex-dakotax.info,192.168.1.1): lookup service=dovecot
Sep 28 22:04:07 aryjan dovecot: auth(default): pam(namex-dakotax.info,192.168.1.1): pam_authenticate() failed: User not known to the underlying authentic$
Sep 28 22:04:07 aryjan dovecot: auth(default): client out: FAIL 1 user=namex-dakotax.info
[/code:1]

To avoid adding additional complexity to the project with a hack, I can login in whatever way is not a hack. How would that be?

Thanks...Any other thoughts? Is it possible to see what PAM sees and find out why the user isn't &quot;known&quot; to PAM?

Thu, 09/25/2008 - 15:17
Acorp

Thanks for the help...I'll work through it.

Yeah, I was really surprised to get the MySQL setup working too, but I was desperate!

Downtime is not an issue, because this is a pre-deployment server. I'm moving over from cPanel hosted to my own server in a data center. To clarify, if it matters, the server is in my physical possession.

Ultimately I'll have about 15 domains and about 100 users, so not too many. I won't need to import/export, because at this point I'm just using a couple of &quot;disposable&quot; domains with DNS pointed to the server, so I don't even have more than a user or two set up on each domain.

I'll pick up in your instructions from the Postfix reinstall, and report back if I get it going or have any trouble. An OS reinstall isn't out of the question, but I'd need MUCH more hand-holding. I've launched head-first into this Linux/Xen/Postfix/Dovecot/Apache, etc. stuff and don't have any experience outside of DOS/Windows.

Thanks!

Thu, 09/25/2008 - 15:23 (Reply to #6)
Joe
Joe's picture

<div class='quote'>An OS reinstall isn't out of the question, but I'd need MUCH more hand-holding.</div>

You'd need dramatically less hand-holding, if you use our install script.

Seriously, if you can start from a fresh OS install without pain, I would <i>strongly</i> recommend it. Give the box a real fully qualified hostname (ns1.virtualmin.com, for example, primary.example.com, for another, virt.domain.tld, for a third...not they're all qualified out to a third level domain...I recommend that, to avoid later collisions with Postfix virtual domains). Download the install script from our download page...run it.

If <i>anything</i> goes wrong, we'll be surprised, but we'll be able to help you much more effectively, because I know the install script and the configuration it creates extremely well. And tens of thousands of systems have been installed using that exact process--and it's known to work well.

--

Check out the forum guidelines!

Thu, 09/25/2008 - 16:17
Acorp

OK, here's the newbie question:

I downloaded the script, and tried to execute it, but must be missing something or not doing something right.

I had permission problems, so I ran CHMOD and CHOWN on it, and got around the permissions problem.

But now, when I try to execute it - by using
./mail-setup.pl
I get the following:
Content-type: text/html; Charset=iso-8859-1

Followed by a bunch of html type stuff - &lt;div&gt; &lt;br&gt;.

I don't think this is &quot;normal&quot; behavior, and virtual_alias_maps never gets written to /etc/postscript/main.cf, so it appears that it isn't actually running. I may need to add at this point that I have command line interface only.

Thoughts, suggestions? I could just try to pick out the postfix stuff and put it in manually, but...

Thu, 09/25/2008 - 20:45
Acorp

Nope, I spoke too soon. Looks like it did run. But now when I start Webmin I get the following error, which prevents me from going to Virtual Servers to add a new virtual domain:

HTTP/1.0 500 Perl execution failed Server: MiniServ/0.01 Date: Fri, 26 Sep 2008 05:36:41 GMT Content-type: text/html Connection: close
Error - Perl execution failed

Undefined subroutine &amp;virtual_server::sort_indent_domains called at /usr/libexec/webmin/virtual-server-theme/left.cgi line 89.

Ideas?

Thanks!

Fri, 09/26/2008 - 00:43 (Reply to #9)
Joe
Joe's picture

My fault. I rolled out the new virtual-server-theme package before the virtual-server package...and it is missing a necessary function. It was only broken for a couple of hours...you just got lucky. ;-)

Update the Virtualmin package:

yum update wbm-virtual-server

Restart webmin:

service webmin restart

--

Check out the forum guidelines!

Sun, 09/28/2008 - 00:11
Joe
Joe's picture

You said:

<div class='quote'>It appears that the problem is that the user is not recognized by PAM. What that means is over my head! </div>

And then lots of scary stuff in your various configuration files that I have no idea about. ;-)

Let's just assume that the old configuration broke the crap out of things, and we'd like to start over. The default Dovecot configuration requires practically no changes to work fine (again, this is one of those things where I'm amazed you got it working with MySQL but can't make it work without MySQL...without it it is dead simple...with it gives me nightmares and I know what most of this stuff does and why...I've even spelunked into the Dovecot code a few times in the distant past).

Anyway, it worked so well for Postfix, let's try it with Dovecot: uninstall it and reinstall it to get a fresh configuration.

Make a backup of the Dovecot config files.

rpm -e --nodeps dovecot
yum install dovecot

Run mail-setup.pl again. (I ordinarily don't suggest running the same thing over and over, but I think the mail-setup script is smart enough not to redo things it's already done.)

It'll give an error about saslauthd not being able to start, which is fine. Ignore it.

Check things again. See if it looks happier. If not, send us logs.

BTW-Are you using usernames with @ in them? If so, you need to configure saslauthd to accept that. See this FAQ for the details:

http://www.virtualmin.com/component/option,com_openwiki/Itemid,48/id,fre...

<div class='quote'>PS: VERY off-topic-&gt; The &quot;preview&quot; function doesn't hardly work in Google's new &quot;Chrome&quot; browser. Text color is light gray on a light gray background; can't see anything unless you highlight it. Don't know if it is Chrome or the site, but thought I'd mention it.</div>

It's broken for everybody. Sorry. It'll be fixed in the website overhaul coming in a few days (I hope).

--

Check out the forum guidelines!

Sun, 09/28/2008 - 00:12 (Reply to #11)
Joe
Joe's picture

Oh, yeah, don't forget to trash the old config files for dovecot after uninstalling it--sometimes config files stick around when removing packages with RPM.

--

Check out the forum guidelines!

Sun, 09/28/2008 - 18:19
Acorp

OK, major breakthrough - but I don't know what to do with it!

You got me thinking about the &quot;@&quot; symbol in my login names. So, I thought, &quot;what does postmap -q&quot; return when I run a user through?

When I execute:

[code:1]
postmap -q namex@dakotax.info /etc/postfix/virtual
[/code:1]

I get:

[code:1]
namex.dakotax
[/code:1]

So, I thought why not try logging in as &quot;namex.dakotax&quot;

And that worked, and retrieved messages. So, now the question is, how do I let users log in as namex@dakotax.info and have that look like namex.dakotax to PAM/dovecot/etc.?

Mon, 09/29/2008 - 12:35 (Reply to #13)
Joe
Joe's picture

You don't and you can't (on the &quot;have that look like&quot; bit).

You need two users, if you want to allow user@domain.tld format usernames. Virtualmin can manage that for you...just enable it in Server Templates:Mail for Domain:&quot;Format for usernames that include domain&quot;. You users can then login as user@domain.tld and Postfix can deliver to user.domain. Everybody is happy (except Wietse, but we won't tell, if you won't...though, in reality, this hack resolves the problems Wietse was talking about, since the @ username is not part of the mail delivery path, only retrieval).

--

Check out the forum guidelines!

Mon, 09/29/2008 - 12:52 (Reply to #14)
Joe
Joe's picture

Oh, yeah, since it seems like maybe it's not obvious:

The exact username for a user is displayed in the IMAP/FTP Login field in the &quot;Edit Mail and FTP Users&quot; list. There never need be a mystery about what login name to use--it's never gonna be anything other than what's in that field.

--

Check out the forum guidelines!

Wed, 10/01/2008 - 06:39
Acorp

<div class='quote'>
...just enable it in Server Templates:Mail for Domain:&quot;Format for usernames that include domain&quot;. You users can then login as user@domain.tld...
</div>

Done...and working now, thanks!

<div class='quote'>
Oh, yeah, since it seems like maybe it's not obvious...
</div>

Oh, it's very obvious...now that you've pointed it out. There is just SO MUCH information it is hard to sift through it all.

Thanks again for everything!