Changelog
- Jan 10, 2024
- fixed a bug in vpopmail which was preventing the correct auto creation of the valias table in MySQL - Nov 5, 2023
-bug fix: vpopmail defaultdelivery patch: it won't create the .qmail file in case control/defaultdelivery already has vdelivermail, in order to prevent a vpopmail loop
-qmailforward RC plugin: it won't create the copy record if $config['qmailforward_defaultdelivery'] contains 'vdelivermail'
Among the various problems that I set out to fix sooner or later, I think I've finally solved one: the impossibility of having the vpopmail
aliases saved in the database (--enable-valias
) and the sieve
rules at the same time.
But before presenting the solutions, let's clarify the problem to be solved, which has also been discussed in several threads of this blog. It is not a case that the solution I identified was partly inspired by the (re)reading of some of the visitors' posts.
The problem
Usually vpopmail
copies its delivery agent (vdelivermail
) into the .qmail-default file of newly created domains. This makes it impossible to use the Dovecot
filters based on the sieve
rules, as this would require Dovecot-LDA
.
On the other hand, if we set the Dovecot
delivery agent to .qmail-default in order to have the sieve
rules, when the user uses a Dovecot
filter to generate a forward (s)he will unintentionally cause the violation of the SPF
, because qmail
is out of the question and srsfilter
cannot be launched to rewrite the sender's address.
The best thing would be for the user to manage the aliases via dot-qmail
, but this can only be done by the domain administrator, while the aliases stored on SQL
database are not an option, as Dovecot
has nothing to do with them.
These are problems that we know well due to the protest emails of users who see their forwarding messages bounced back because of the SPF/DKIM/DMARC
check failure. But now we're going to fix it once and for all :-)
The solution
As you know, it is possible to leave vdelivermail
in the .qmail-default of domains, and run Dovecot-LDA
against the .qmail files written to the user's home directory. For example:
domains/ | | --- sagredo.eu/ | | --- .qmail-default (| ~vpopmail/bin/vdelivermail '' delete) | | --- roberto/ | | --- .qmail (| /var/qmail/bin/preline -f /usr/local/dovecot/libexec/dovecot/deliver -d $EXT@$USER)
In this way the management of the forwards and srsfilter
, both in dot-qmail
mode and in the SQL/valias
mode, is done in advance by qmail/vpopmail
, while the final saving of the messages and the execution of the sieve
rules (filters) is performed later by Dovecot
, when opening the user's .qmail file.
The new defaultdelivery patch for vpopmail
I prepared will handle the creation of new domains/users and the corresponding dot-qmail
files according to the above logic, when configuring vpopmail
with the --enable-defaultdelivery
option.
The vpopmail
update brings with it my vmakedotqmail
program that can help in the migration, to automatically create or rewrite the dot-qmail
files, as we'll see later.
The forwards on SQL valias
From now on it will also be possible to save the forwards on MySQL
(vpopmail
compiled with –enable-valias)
and have sieve
working at the same time. I will put this setting as default in this guide and remember now why it is important.
In the current state of affairs, the management of these resubmissions (with possible copy) on user's mailboxes can only be done via the qmailadmin
control panel by the domain administrator, who obviously does not have the time to satisfy certain requests which, in a well-designed system, should instead be owned by the user himself.
Having user's forwards stored on SQL
database opens the possibility of building web programs for their management, such as a plugin for Roundcube,
which in fact you can find in the package of goods that I am releasing here.
Updating your own server
vpopmail
The new defaultdelivery feature for vpopmail
works differently than before, i.e. according to the logic illustrated above. You need to reinstall vpopmail
in the usual way.
If you want, as I believe, to use valias
and with them my qmailforward plugin for Roundcube,
you need to compile vpopmail
with –enable-valias
and then update the schema of the valias.vpopmail
table, unless this table doesn't exist yet and in this case vpopmail
itself will create it for you. You could also decide to drop that table and let vpopmail
recreate it, if it's empty or contains only a few records that you can quickly add again later.
So here is what to do about vpopmail
:
./configure \ --other-options-here \ --enable-defaultdelivery \ --enable-valias make make install-strip
Now recompile and reinstall qmail
make qmailctl stop make setup check qmailctl start
If needed, this is the SQL
query to run in order to modify the schema of the vpopmail.valias
table.
USE vpopmail; ALTER TABLE valias DROP INDEX IF EXISTS `PRIMARY`; ALTER TABLE `valias` ADD `valias_type` TINYINT(1) NOT NULL DEFAULT '1' COMMENT '1=forwarder 0=lda' FIRST; ALTER TABLE `valias` ADD `copy` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0=redirect 1=copy&redirect' AFTER `valias_line`; ALTER TABLE `valias` ADD PRIMARY KEY (`valias_type`, `alias`, `domain`);
Note how this query replaces any existing primary keys, which are not in the default installation anyway. Be aware that the line ALTER TABLE valias DROP INDEX IF EXISTS `PRIMARY`
may throw errors if you already have PRIMARY KEYS
with AUTO_INCREMENT
active. In that case manually drop the column with the PRIMARY KEY
and proceed with the last three lines.
As already mentioned, I wrote a little program vmakedotqmail
that can help you to rewrite your dot-qmail
files. As said, it installs the LDA
stored in control/defaultdelivery in the user's .qmail and the vpopmail
's vdelivermail
program in the domain's .qmail-default. Here is how it works:
# vmakedotqmail -h Usage: vmakedotqmail [option] [argument] options: -u <username@domain> install .qmail for the user <username@domain> -d <domain> install .qmail for all users of domain <domain> -A install .qmail for all users of all domains -o (overwrite) do not skip existing .qmail files. Use with -A|-d|-u -r (reverse) remove the existing .qmail files. Use with -A|-d|-u -q [default|argument] reinstall the .qmail-default in domain -d <domain> or in ALL domains (-A). -t (testing mode) do not really open or write the .qmail -h this help Existing .qmail files won't be overwritten unless you pass -o Examples: Install control/defaultdelivery to .qmail of all mailboxes of all domain (overwrite -o active) vmakedotqmail -o -A Install control/defaultdelivery to .qmail of user username@domain (skip if existing) vmakedotqmail -u username@domain Install .qmail-default with vdelivermail (delete option) for domain 'domain.tld' vmakedotqmail -d domain.tld -q default Install .qmail-default with your favourite LDA for all domains vmakedotqmail -A -q "My LDA instruction as quoted argument here" Remove all existing .qmail from all mailboxes of domain <domain> vmakedotqmail -r -d <domain>
qmailadmin
Re-download the program, which has a couple of fixes concerning the aliases, recompile and install in the usual way. Recompiling qmailadmin
is always necessary after a vpopmail
source or configuration change.
qmailforward Roundcube
plugin
The basic idea behind this plugin is from Michael Dick, who I would like to thank.
qmailforward takes the php
code used to generate the form from the managesieve-forward sub-plugin, which already makes forwarding available, but at the cost of using the sieve
rules, as already mentioned. If you have enabled it, disable the forward button in that plugin so that you don't confuse it with this one:
$config['managesieve_forward'] = 0;
qmailforward allows any user to manage their own forwarding (with possible copy) through the Roundcube
settings panel.
To install it you have to download it from github, unpack it in the plugin folder, where a qmailforward folder will appear, and enable it from the Roundcube
configuration file:
$config[‘plugins’] = […… ,‘qmailforward’];
As an alternative, you can install it via composer
.
The default settings are in the config.inc.dist.php file. Read this file and copy the settings you want to override into config.inc.php. It is necessary to enter at least the database access credentials, which are those of vpopmail
already stored in the ~vpopmail/etc/vpopmail.mysql file.
The plugin looks like this and needs no explanation on how to use it:
For those curious about what's going on in the vpopmail.valias
table, qmailforward can behave in two ways
- like a simple forwarder. In this case, only one record is written with the
valias_line
field holding the destination address. The newcopy
field has the value0
, whilevalias_type
will be equal to1
. - as a forwarder of a copy of the message. The same as before, but in this case the
copy
field will be equal to1
andvalias_type
equal to1.
In addition to this, another record withvalias_type=0
will be inserted with the delivery agent responsible for saving the copy in the mailbox. You can configure which delivery agent to save by editing the configuration file. By default it will beDovecot
, so thatsieve
rules will also be executed in cascade. Do not entervdelivermail
, which is already in .qmail-default, otherwise you will cause avpopmail
loop and aqmail-queue
error.
The primary key we added to the table is needed to modify the database, so don't change it.
The plugin has only a few translations. Please collaborate in the translation into other languages by sending them to me via mail (localization folder).
That's all, have fun!
Comments
SQL valias with sieve solution
Mike November 10, 2023 14:21 CET
Hello Roberto,
first of all thx for your great tutorial.
I'm playing with fresh installation of qmail/vpopmail software. Everything went smoothly until I start testing SQL aliases.
I can add to SQL table new email alias with no errors but when try to sent test email alwas get error from CHKUSER.
From otherside .qmail-alias delivery emails to mailbox perfectly.
Pls show me direction where to dig.
Reply | Permalink
SQL valias with sieve solution
Roberto Puzzanghera Mike November 10, 2023 14:36 CET
Hi, it seems like chkuser is not aware of valiases... Did you recompile qmail on top of the patched vpopmail?
Reply | Permalink
SQL valias with sieve solution
Mike Roberto Puzzanghera November 13, 2023 14:51 CET
Roberto,
thx for your help.
After recompiling SQL ALIAS start working.
Reply | Permalink
SQL valias with sieve solution
Roberto Puzzanghera Mike November 13, 2023 15:12 CET
Great! Remember to recompile qmailadmin as well
Reply | Permalink
SQL valias with sieve solution
Bai Borko September 7, 2023 09:07 CET
Hi Roberto,
Just small update for SQL valias with sieve solution guide.
When modify primary key on table valias to be multi-column (`valias_type`, `alias`, `domain`) you must drop column `id`, qmailforward plugin failed with the following error .
The column `id` in the original table valias is primary key and it is auto increment . The insert query qmailforward plugin doesn't set value for column `id` because it is not mandatory.
If don't drop column `id` insert query will fail like this:
Here is the commands for mysql 8.0 to modify valias table:
After the modification vpopmail.valias table SQL valias with sieve is working great.
Thank you !
Reply | Permalink
SQL valias with sieve solution
Roberto Puzzanghera Bai Borko September 7, 2023 11:43 CET
The default valias table does not have any id field and no PRIMARY KEY. This is the original vpopmail:
That's the reason why I cannot mention
id
in my instructions, so your error is explained. That said, I figured out that someone could have defined a PRIMARY KEY and that's why I am trying to drop that PRIMARY KEY, with no luck because of the AUTO_INCREMENT flag. But once the PRIMARY KEY has been deleted, the zombie id field can continue to exist as an ordinary field, and it's not important to delete it, while it's important to drop any existing PRIMARY KEY.But I agree that it's convenient to define a PRIMARY KEY anyway, just to satisfy phpmyadmin which doesn't show the modify button otherwise.
In my patch I have this:
So, if one has no interest in my defaultdelivery feature the setup is identical to yours, while if one has defined DEFAULT_DELIVERY the table goes as explained with a multi-column PRIMARY KEY.
Of course if the table already exists the program doesn't ALTER it because it's already populated and this is left to the administrator.
Reply | Permalink