PostfixAdmin is a GUI which we use frequently to manage our users and domains in a Postfix system. Creating email aliases and “Forward only” redirections are the most regular tasks we carry out while managing it. Likewise, Roundcube is a free, convenient solution for the webmail demands and the vacation plugin is the indispensable component for enabling users to manage their own “Out Of Office” replies. Complete step-by-step setup guides can be found here for PostfixAdmin and here for Roundcube.

Although these system members work perfectly together, we realised that using Roundcube vacation plugin completely breaks the relevant user aliases entered manually under the Mailboxes section in PostfixAdmin. The matter is, simply navigating to Vacation settings of Roundcube and hitting the “Save” button once without editing the forms will be enough to remove the alias records from the database. And this means that purposely or unknowingly a user can expunge all his/her known or confidential aliases, previously defined by the system administrator.

We planned a workaround for this issue which will also take notice of the possible vacation forward addresses that shall be entered by the users. Below is the full recipe.

Primo, duplicate your Postfix “alias” table structure/data with a different name (alias_before_vacation). You can do this through the command line;

# mysql -u root -p
Enter password:

mysql> use postfix;
mysql> CREATE TABLE alias_before_vacation LIKE alias;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO alias_before_vacation SELECT * FROM alias;
Query OK, 35 rows affected (0.00 sec)
Records: 35  Duplicates: 0  Warnings: 0

mysql> exit
Bye

Alternatively, you may prefer SQLyog interface. If so, go to your postfix database, right click “alias” table, navigate to More Table Operations, select Duplicate Table Structure/Data…

Visit your vacation plugin directory, open the configuration file, remove and replace your all write data queries under “SQL driver” section with the new one below. Certainly, at LINE 18, do not please forget to change “autoreply.yourdomain.com” to your autoreply subdomain registered by your own FQDN.

# cd /usr/local/www/apache22/data/roundcube/plugins/vacation/
# vi config.inc.php

// WRITE DATA QUERIES

// purging stale vacation records
$rcmail_config['vacation_sql_write'] = array(
"DELETE FROM vacation WHERE email=%email AND domain=%email_domain;",
"DELETE FROM vacation_notification WHERE on_vacation=%email;",

// composing fresh vacation details from scratch
"INSERT INTO vacation (email,domain,subject,body,created,active) " .
"SELECT %email,%email_domain,%vacation_subject," .
"%vacation_message,NOW(),1 FROM mailbox WHERE username=%email AND domain=%email_domain AND %vacation_enable=1;",

// removing the user from main alias table (if SAVE button is not compromised)
"DELETE FROM alias WHERE address=%email AND domain=%email_domain AND %vacation_enable=1;",

// vacation_enable=1 (enabled) inserting new vacation_forwarders
"INSERT INTO alias (address,goto,domain,created,modified,active) " .
"SELECT %email,CONCAT(%email_local,'#',%email_domain,'@','autoreply.yourdomain.com,'," .

// adding previous alias records
"(SELECT goto FROM alias_before_vacation WHERE address=%email AND domain=%email_domain),',',%vacation_forwarder)," .
"%email_domain,NOW(),NOW(),1 FROM mailbox WHERE username=%email AND domain=%email_domain AND %vacation_enable=1;",

// dropping all triggers in order to prevent infinite MySQL loop, [Native code: 1442]
"DROP TRIGGER IF EXISTS ins_alias_before_vacation;",
"DROP TRIGGER IF EXISTS upd_alias_before_vacation;",
"DROP TRIGGER IF EXISTS del_alias_before_vacation;",

// vacation_enable=0 (disabled) so switching back to original alias definitions
"REPLACE alias SELECT * FROM alias_before_vacation WHERE address=%email AND domain=%email_domain AND %vacation_enable=0;",

// creating insert trigger
"CREATE TRIGGER `postfix`.`ins_alias_before_vacation` AFTER INSERT " .
"ON `postfix`.`alias` FOR EACH ROW BEGIN DECLARE found_v INT; " .
"SELECT COUNT(1) INTO found_v FROM `postfix`.`vacation` " .
"WHERE email = NEW.address; IF found_v = 0 THEN INSERT INTO alias_before_vacation " .
"(address,goto,domain,created,modified,active) VALUES " .
"(NEW.address,NEW.goto,NEW.domain,NEW.created,NOW(),NEW.active); " .
"END IF; END ",

// creating update trigger
"CREATE TRIGGER `postfix`.`upd_alias_before_vacation` AFTER UPDATE " .
"ON `postfix`.`alias` FOR EACH ROW BEGIN DECLARE found_v INT; " .
"SELECT COUNT(1) INTO found_v FROM `postfix`.`vacation` " .
"WHERE email = NEW.address; IF found_v = 0 THEN REPLACE INTO alias_before_vacation " .
"(address,goto,domain,created,modified,active) VALUES " .
"(NEW.address,NEW.goto,NEW.domain,NEW.created,NOW(),NEW.active); " .
"END IF; END ",

// creating delete trigger
"CREATE TRIGGER `postfix`.`del_alias_before_vacation` AFTER DELETE " .
"ON `postfix`.`alias` FOR EACH ROW BEGIN DECLARE found_v INT; ".
"SELECT COUNT(1) INTO found_v FROM `postfix`.`vacation` " .
"WHERE email = OLD.address; IF found_v = 0 THEN DELETE FROM alias_before_vacation " .
"WHERE address=OLD.address AND goto=OLD.goto AND domain=OLD.domain; " .
"END IF; END "
);

Save your changes, there’s no need to reload or restart any service. Just enabling the vacation once with any user will be enough to make the triggers to be created. Triggers ? Notice that we’ve used MySQL triggers to synch actual “alias” table with “alias_before_vacation” table in order to store and update all the aliases in our new database table. Thanks to triggers, we didn’t have to digg into PHP codes of Postfixadmin which would probably drag us into an agony as we’d repeatedly break another thing while fixing something.

Test the workaround by enabling/disabling vacation inside Roundcube webmail and observing the relevant alias and alias_before_vacation table movements for that user.

Please keep in mind that it will be better not to edit any user aliases if you see “VACATION IS ON” notice beside the user information inside PostfixAdmin. Wait until the user returns back to the office and disables the vacation. After that, you may interfere his/her aliases manually as before.

Related Posts

1. Roundcube setup (by also fixing Vacation and Quota Warning issues) on FreeBSD
2. PostfixAdmin Setup (with correct vacation settings) on FreeBSD
3. Postfix (Virtual Setup) Dovecot SpamAssassin ClamAV Maia Roundcube (/w MySQL db) on FreeBSD