SQL valias with sieve solution for qmail. New patches and Roundcube plugin

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 no coincidence 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 Dovecot filters based on 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 the Dovecot filters 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

• defaultdelivery patch for 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

• qmailforward plugin for Roundcube

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

1. like a simple forwarder. In this case, only one record is written with the valias_line field holding the destination address. The new copy field has the value 0, while valias_type will be equal to 1.
2. as a forwarder of a copy of the message. The same as before, but in this case the copy field will be equal to 1 and valias_type equal to 1. In addition to this, another record with valias_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 be Dovecot, so that sieve rules will also be executed in cascade. Do not enter vdelivermail, which is already in .qmail-default, otherwise you will cause a vpopmail loop and a qmail-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).

The above is still fresh code and therefore should be considered as testing. As always, feel free to post any information, suggestion, and concern in the comments below.

That's all, have fun!

Remote host said: 550 5.1.1 sorry, no mailbox here by that name (chkuser)

[06-Sep-2023 22:03:07 +0300]: DB Error: [1364] Field 'id' doesn't have a default value (SQL Query: INSERT INTO valias SET alias='myuser', domain='domain.tld', valias_line='test@domain.com', copy=1 ON DUPLICATE KEY UPDATE alias='myuser', domain='domain.tld', valias_line='test@domain.com', copy=1) in /var/www/html/roundcubemail-1.6.2/program/lib/Roundcube/rcube_db.php on line 567 (POST /?_task=settings&_action=plugin.qmailforward-save)

use vpopmail;
-- DROP INDEX `PRIMARY` ON valias; -- primary index does't exist on the original table valias
ALTER TABLE valias MODIFY id INT NOT NULL;
ALTER TABLE valias DROP PRIMARY KEY;
ALTER TABLE valias DROP COLUMN id;
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`);
mysql> describe valias;
+-------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| valias_type | tinyint(1) | NO | PRI | 1 | |
| alias | char(32) | NO | PRI | NULL | |
| domain | char(96) | NO | PRI | NULL | |
| valias_line | text | NO | | NULL | |
| copy | tinyint(1) | NO | | 0 | |
+-------------+------------+------+-----+---------+-------+

#define VALIAS_TABLE_LAYOUT "alias char(32) not null, \
domain char(96) not null, \
valias_line text not null, index (alias, domain)"

#ifdef DEFAULT_DELIVERY 
#define VALIAS_TABLE_LAYOUT \ 
"valias_type tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=forwarder 0=lda', \ 
alias char(32) NOT NULL, \ 
domain char(96) NOT NULL, \ 
valias_line text NOT NULL, \ 
copy tinyint(1) NOT NULL DEFAULT 0 COMMENT '0=redirect 1=copy&redirect', \ 
PRIMARY KEY (alias,domain,valias_type), \ 
INDEX (alias, domain)" 
#else 
#define VALIAS_TABLE_LAYOUT "id int(11) PRIMARY KEY AUTO_INCREMENT, \                                                                                                                     
alias char(32) NOT NULL, \                                                                                                                                                                
domain char(96) NOT NULL, \                                                                                                                                                               
valias_line text NOT NULL, \                                                                                                                                                              
INDEX (alias, domain)"                                                                                                                                                                    
#endif