Goldfish, A PHP Autoresponder Script For The HowtoForge "Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail " Setups

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 06/08/2009

Goldfish is a quite simple autoresponder script (written in PHP) for Postfix. It consists of only one PHP file which can be started through a cronjob. It works with our "Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail" tutorials (it cannot be used for other setups, especially Postfix setups that don't use a MySQL database).

I do not issue any guarantee that this will work for you!

1 Preliminary Note

I'm assuming that you already have a working "Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail" setup, e.g.:

• Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Debian Lenny)
• Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (CentOS 5.3 x86_64)
• Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Fedora 10)
• Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Ubuntu 8.10)
• Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Mandriva 2009.0 i386)

2 Installing And Using Goldfish

First we connect to our MySQL mail database and create an additional table called autoresponder:

mysql -u root -p

USE mail;

CREATE TABLE `autoresponder` (
`email` varchar(255) NOT NULL default '',
`descname` varchar(255) default NULL,
`from` date NOT NULL default '0000-00-00',
`to` date NOT NULL default '0000-00-00',
`message` text NOT NULL,
`enabled` tinyint(4) NOT NULL default '0',
`subject` varchar(255) NOT NULL default '',
PRIMARY KEY (`email`),
FULLTEXT KEY `message` (`message`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

quit;

Afterwards we download the latest goldfish release to /tmp and rename the goldfish PHP script to autoresponder.php:

cd /tmp
wget http://remofritzsche.ch/goldfish/download/current.tar.gz
tar xvfz current.tar.gz
cd goldfish-002p1/
mv goldfish-002-p1.php autoresponder.php

Then we open autoresponder.php and adjust the Configuration section. Make sure you fill in the correct database details (you can use the existing mail_admin MySQL user); in the $conf['q_mailbox_path'] line, make sure that you replace the table name view_users with users:

vi autoresponder.php

[...]
    ######################################
    # Configuration                      #
    ######################################
    /* General */
    $conf['cycle'] = 5 * 60;

    /* Logging */
    $conf['log_file_path'] = "/var/log/goldfish";
    $conf['write_log'] = true;

    /* Database information */
    $conf['mysql_host'] = "localhost";
    $conf['mysql_user'] = "mail_admin";
    $conf['mysql_password'] = "mail_admin_password";
    $conf['mysql_database'] = "mail";

    /* Database Queries */

    # This query has to return the path (`path`) of the corresponding
    # maildir-Mailbox with email-address %m
    $conf['q_mailbox_path'] = "SELECT CONCAT('/home/vmail/', SUBSTRING_INDEX(email,'@',-1), '/', SUBSTRING_INDEX(email,'@',1), '/') as `path` FROM users WHERE `email` = '%m'";

    # This query has to return the following fields from the autoresponder table: `from`, `to`, `email`, `message` where `enabled` = 2
    $conf['q_forwardings'] = "SELECT * FROM `autoresponder` WHERE `enabled` = 1";

    # This query has to disable every autoresponder entry which ended in the past
    $conf['q_disable_forwarding'] = "UPDATE `autoresponder` SET `enabled` = 0 WHERE `to` < CURDATE();";

    # This query has to activate every autoresponder entry which starts today
    $conf['q_enable_forwarding'] = "UPDATE `autoresponder` SET `enabled` = 1 WHERE `from` = CURDATE();";

    # This query has to return the message of an autoresponder entry identified by email %m
    $conf['q_messages'] = "SELECT `message` FROM `autoresponder` WHERE `email` = '%m'";

    # This query has to return the subject of the autoresponder entry identified by email %m
    $conf['q_subject'] = "SELECT `subject` FROM `autoresponder` WHERE `email` = '%m'";
[...]

Afterwards we move autoresponder.php to /usr/local/bin, make it owned by the user and group vmail, and make it executable:

mv autoresponder.php /usr/local/bin

chown vmail:vmail /usr/local/bin/autoresponder.php
chmod 755 /usr/local/bin/autoresponder.php

Now we create the log file /var/log/goldfish and make it owned by the user and group vmail:

touch /var/log/goldfish
chown vmail:vmail /var/log/goldfish

I want autoresponder.php to be executed every five minutes, therefore I create a cron job for it which will be run by the user vmail:

crontab -u vmail -e

*/5 * * * * /usr/local/bin/autoresponder.php

That's it! Now you can create autoresponders in the autoresponder table, e.g. with phpMyAdmin or on the command line, e.g. as follows:

mysql -u root -p

USE mail;

INSERT INTO `autoresponder` (`email`, `descname`, `from`, `to`, `message`, `enabled`, `subject`) VALUES('sales@example.com', 'sales@example.com Autoresponder', '2009-06-08', '2009-06-12', 'I will be out the week of June 8 with very limited access to email.\r\nI will respond as soon as possible.\r\nThanks!\r\nFalko', 1, 'Out of Office');

quit;

This creates an autoresponder for the email address sales@example.com which is active between June 8, 2009, and June 12, 20009. Because the autoresponder cron job is executed only every five minutes, the autoresponder message will not be sent immediately, but within five minutes after an email is sent to the sales@example.com address.

You should see something like this in the /var/log/goldfish log file whenever an autoresponder is sent from the sales@example.com address:

2009-06-08 07:00:01 Connection to database established successfully
2009-06-08 07:00:01 Database selected successfully
2009-06-08 07:00:01 Successfully updated database (disabled entries)
2009-06-08 07:00:01 Successfully updated database (enabled entries)
2009-06-08 07:00:01 Successfully fetched maildir directories
2009-06-08 07:00:01 Successfully fetched subject of sales@example.com
2009-06-08 07:00:01 Successfully fetched message of sales@example.com
2009-06-08 07:00:02 --------- End execution ------------

3 Links

• Goldfish: http://www.remofritzsche.ch/goldfish/