vpopmail aliases: virtualmin's "additional email addresses for user" broken with mysql backend

see below

Please make the changes...

--- feature-mail.pl.orig 2012-08-04 02:16:12.387122113 +0000
+++ feature-mail.pl 2012-08-04 02:20:35.202868090 +0000
@@ -1838,7 +1838,10 @@
# Create with valias command
local $qfrom = quotemeta("$box\@$dom");
foreach $t (@{$_[0]->{'to'}}) {
- local $qto = quotemeta(&vpopmail_to_qmail($t, $dom));
+ # see https://www.virtualmin.com/node/22970
+ # vpopmail doesn't support dot-qmail parsing in ldap/mysql valiases
+ # local $qto = quotemeta(&vpopmail_to_qmail($t, $dom));
+ local $qto = quotemeta("$t\@$dom");
local $cmd = "$vpopbin/valias -i $qto $qfrom";
local $out = &backquote_logged("$cmd 2>&1");

Let's test it with a newly created user.


@40000000501c87c3216cb594 info msg 6115475: bytes 3751 from qp 5400 uid 201
@40000000501c87c3216cb97c starting delivery 48: msg 6115475 to local hihi.biz-russianmonkey@hihi.biz
@40000000501c87c3216cc14c status: local 2/25 remote 0/50
@40000000501c87c323234ed4 delivery 48: success: vdelivermail:_valiases_processed/did_0+0+1/
@40000000501c87c323270024 status: local 1/25 remote 0/50
@40000000501c87c323270bdc end msg 6115475
@40000000501c87c3232c91ec new msg 6129733
@40000000501c87c3232c99bc info msg 6129733: bytes 3860 from qp 5490 uid 89
@40000000501c87c3232ca18c starting delivery 49: msg 6129733 to local hihi.biz-shurik@hihi.biz
@40000000501c87c3232ca574 status: local 2/25 remote 0/50
@40000000501c87c32516a6dc new msg 6115475
@40000000501c87c32516b294 info msg 6115475: bytes 3959 from qp 5502 uid 89
@40000000501c87c32517337c starting delivery 50: msg 6115475 to remote shurik@gimap.hihi.biz
@40000000501c87c325173764 status: local 2/25 remote 1/50
@40000000501c87c325437784 delivery 49: success: did_0+0+1/


Yep, the Russian monkey got his mail twice, once in his mail box and once in his gmail. I can now delegate sign-ups and management of user email to the staff.

When you are not using vpopmail and virtualmin I would recommend putting a & in front of a forward in a .qmail file. Alternatively do some regex ^[a-z][0-9] if that matches on the string you can leave out the ampersand in .qmail files. I don't think virtualmin adheres to this - see dot-qmail man page. The developers on the mailing list say & is supported in aliases (with valias i mysql) though I could be wrong.

vpopmail is designed to be web administered, so all additions to the database in theory can be done through SQL, and there is no need to call all the vpopmail binaries unless you want to. Of course if you want to delete mailboxes you need to get their hands dirty and unlik the files manually.

----- Original Message -----
From: "Ken Jones"
To:
Sent: Thursday, September 09, 2004 3:48 AM
Subject: Re: [vchkpw] vpopmail + billing server integration
> On Wednesday 08 September 2004 12:25 pm, Chris Ess wrote:
> > On Wed, 8 Sep 2004, Ken Jones wrote:
> > > Hi,
> > >
> > > Here is something we built into vpopmail for sites like yours.
> > >
> > > Use mysql on the email server. Have the billing system
> > > insert an entry in the vpopmail table, leaving the directory
> > > field blank. vpopmail will automatically create the users
> > > directory and update the database when any program
> > > tries to deliver mail to the user, or authenticate as the user.
> >
> > That's really neat! I didn't realize you could do this. (Now someone
> > will tell me that it's in the documentation that I seem to've not read
> > recently.)
>
> It is probably in the mailing list archives. I'm not sure if anyone has
> updated the documentation.
>
> >
> > (I know this is getting offtopic...) So I could use an INSERT statement in
> > SQL instead of vadduser? Or am I not understanding this correctly?
>
> That is the idea. A while back some folks wanted to hook up their billing
> systems to vpopmail. Basicly they would insert into the vpopmail sql table.
> The only thing they couldn't do easily was create the hashed directory path.
> So we put in vpopmail code to check if the path is blank and automatically
> create the new path and update the database.
>
> You will also need to set the encrypted password using mysql's CRYPT function.
> Mysql's standard encryption functions are not compatible with unix/linux.
>
> Ken
>

Look at qmailadmin. Let's make a new class of mailserver virtualmin supports: qmail-vpopmail-mysql.

Luke

I've added a request for more information to the vpopmail mailing list about this breakage in mysql backed versions of vpopmail having vdelivermail pipe a email to itself.
Hopefully this clarifies what is going on.

Subject: valias line that pipes into vdelivermail again breaks delivery /
valias documentation lax
From: Luke vpopmail
To: vchkpw@inter7.com

Hi Guys,

I've been a Qmail users since Peter Samuel at SLUG.org.au did his talk a
long long time ago.

Now down to business. I have an issue with vpopmail failing with
virtualmin, a webmin spin off..
http://www.virtualmin.com/node/22970

Basically what happening is this, virtualmin is configuring
|/var/vpopmail/bin/vdelivermail user@domain for aliases and it's breaking.
Why it's breaking I don't exactly know why, because according to the valias
code pipes are supported.

I seek confirmation on the mysql implementation of vmail aliases.

* multiple aliases for the same domain are supported (true)
* if qmail extensions are used (user-ext@domain) in aliases they will be
used INSTEAD of the user's alias (?)
* qmail extensions are supported on aliases if there is not a better match
(see above)
? how do qmail extensions on aliases flow through to the user accounts ?
* pipes are supported (true) (see caveat)
* &forwarding is supported - in order to support non-standard email naming
conventions i.e. dot qmail compliancy (true?)
* mbox delivery is not supported (true)
* if no hostname is provided on alias it will use
/var/qmail/control/defaultdelivery (true? seems to be using
/var/qmail/bin/qmail-inject at some point noenvhost is ignored)
* aliases are processed before users (true)
* if the database is down, a soft delivery delay occurs (true)
* mail loops for valiases only are detected (true) (not for user accounts
bouncing back to a valias??)
* delivery to maildir folders are supported if the full path is specified
(true)
* delivery to maildir folders are supported for relative paths in relation
to vpopmail's home directory defined in /etc/passwd (true?) i.e. ./domains/
hiled.biz/user/.maildir/ is prepended by vpopmail's home directory
* a maildir folder needs a ./ or a / at the beginning of the line to be
processed (true/false?)
* anything else that should go into the readme file thats not there....

I do hope some people can look at that virtualmin ticket to theorize why
vdelivermail doesn't like calling itself to do another delivery.
The issue doesn't affect me, it's just that some other setup's and indeed
virtualmin's non-sql-backend setups are all doing it this way.
It did take day to hunt down, but finally my pain is over (i never thought
of checking the valias database)

I'm also petitioning virtualmin to be fixed fixed to work with gentoo's
defacto .maildir defaultdelivery standard for qmail. They had hardcoded
./Maildir in their source.

Speaking of the maildrop patch for virtualmin, I don't like it in it's
current form. It's messy.
I think that vpopmail should support this setup instead.

1) If a domain is not owned by vpopmail:vckpwd/vpopmail still configure
/var/qmail/users/assign to use uid/gid 89 (vpopmail) - see below, this
should be configured by a #ifdef MAILDROP
2) Add maildrop to your system as SUID with it set up that vpopmail is a
trusted user. --configure-trusted-users=x,y,z,vpopmail in maildrop
configure script.
[ Due to the amount of personal information vpopmail system could release,
it should be pretty locked down anyway if any system administrator is worth
his salt.]
3) I patched vdelivermail to spawn vpopmail in LDA mode.
--- vdelivermail.c.orig 2012-08-03 06:51:43.397294158 +0000
+++ vdelivermail.c 2012-08-03 07:56:38.802799846 +0000
@@ -409,7 +409,7 @@
#ifdef MAILDROP
if ( limits.disable_maildrop==0 && vpw!=NULL &&
!(vpw->pw_gid & NO_MAILDROP) ) {
- sprintf(maildrop_command, "| preline %s", MAILDROP_PROG);
+ sprintf(maildrop_command, "| preline %s -a -d %s@%s", MAILDROP_PROG,
TheUser, TheDomain);
run_command(maildrop_command);
DeleteMail = 1;
return(0);
This way maildrop sets up the home directory, the maildir directory and the
appropriate user ID tself. You can still have site-wide configuration by
/etc/maildroprc
4) To make point #3 working you need courier-authlib working. They have
just recently the removed vpopmail authentication backend. However SQL /
LDAP or whatever vpopmail uses can be queried. Here is an an example for
mysql.

/etc/courier/authlib/authmysqlrc:
#address of mysql server
MYSQL_SERVER localhost

#login for mysql
MYSQL_USERNAME vpopmail

#password for mysql
MYSQL_PASSWORD secret
# of course i don't use the default password ^%%
#path to mysql socket
MYSQL_SOCKET /var/run/mysqld/mysqld.sock

#mysql port
#MYSQL_PORT 3306

#mysql options (leave alone)
MYSQL_OPT 0

#name of mysql database
MYSQL_DATABASE vpopmail
MYSQL_USER_TABLE vpopmail
#mysql charset to use
MYSQL_CHARACTER_SET utf8

#default domain to use, when no domain is supplied by user
DEFAULT_DOMAIN hiled.biz

#MYSQL_CRYPT_PWFIELD pw_passwd
#MYSQL_CLEAR_PWFIELD pw_clear_passwsd
#MYSQL_LOGIN_FIELD CONCAT(pw_name, '@', pw_domain)
#MYSQL_NAME_FIELD pw_gecos
#MYSQL_HOME_FIELD pw_dir
#MYSQL_QUOTA_FIELD pw_shell
#MYSQL_UID_FIELD
#ISNUMERIC(pw_shell) AS quota,

MYSQL_SELECT_CLAUSE SELECT CONCAT(pw_name, '@', pw_domain) AS username,
\
pw_passwd AS cryptpw, \
pw_clear_passwd AS clearpw, \
if (pw_uid = '0','89',pw_uid) AS uid, \
if (pw_uid = '0','89',pw_gid) AS gid, \
pw_dir AS home, \
CONCAT(pw_dir, '/.maildir/') AS maildir, \
if (pw_shell = 'NOQUOTA', '', pw_shell) AS quota, \
pw_gecos AS fullname, \
'disablewebmail=0,disablepop3=0,disableimap=0' AS
options \
FROM vpopmail WHERE \
pw_name = '$(local_part)' AND pw_domain =
'$(domain)';

MYSQL_ENUMERATE_CLAUSE SELECT CONCAT(pw_name, '@', pw_domain) AS
username, \
pw_passwd AS cryptpw, \
pw_clear_passwd AS clearpw, \
if (pw_uid = '0','89',pw_uid) AS uid, \
if (pw_uid = '0','89',pw_gid) AS gid, \
pw_dir AS home, \
CONCAT(pw_dir, '/.maildir/') AS maildir, \
if (pw_shell = 'NOQUOTA', '', pw_shell) AS quota, \
pw_gecos AS fullname, \
'disablewebmail=0,disablepop3=0,disableimap=0' AS
options \
FROM vpopmail;

MYSQL_CHPASS_CLAUSE UPDATE vpopmail \
SET pw_clear_passwd='$(newpass)', \
pw_passwd='$(newpass_crypt)' \
WHERE pw_name='$(local_part)' \
AND pw_domain = '$(domain)' ;

I think pw_gid should be hardcoded as 89, though it shouldn't matter as all
mail is umasked 007 anyway.

As you can see it works.
crm authlib # authtest user@hiled.biz
Authentication succeeded.

Authenticated: user@hiled.biz (uid 89, gid 89)
Home Directory: /var/vpopmail/domains/hiled.biz/user
Maildir: /var/vpopmail/domains/hiled.biz/user/.maildir/
Quota: 536870912S

Should I have a / on the end of the .maildir in maildrop? I've seen
examples of it not there.
Now I can have some scripting (per site and per user) before the real LDA -
from dovecot gets called. No messy configuration in vpopmail either.

What do you think? Can the maildrop patch be updated to be less of a hack
and more of a real LDA, to have it called the same way it is from courier /
postfix?

Cheers,

Luke

Bug confirmed. Jamie: Apply suggested fix to virtualmin.

On Mon, Aug 6, 2012 at 3:28 AM, Tom Collins tom@tomlogic.com wrote:

Haven't read your whole email, so I apologize if I'm off base.

vdelivermail should only go in .qmail-default. It should not go in .qmail-alias files. If you're using it there, you will have problems.

-Tom
(Sent from my phone; forgive my brevity)

http://www.mail-archive.com/vchkpw@inter7.com/msg28167.html

Follow the thread here.

See comment #2 it's a one-liner patch you are looking for.

Be careful where vpopmail_qmail() is called. It must not be used in conjuction with valias. In actual dot-qmail files OK, but it will break if injected into valias -i

valias without mysql backend does that, use .qmail files. Vpopmail mailing list people reconmend that vdelivermail only be called once per virtual domain, i.e. in the .qmail-default file - not per user.

Audit your code to ensure that the offending funciton is only used to create the .qmail-default file, or edit such a file. Any aliases to be created with valias -i

Check the source and my mail above on the limitations of valias, but I suggest just putting email addresses only. If email addresses first character doesn't match [a-z][A-Z][0-9] you are required to add a & first. See man dot-qmail.