Configuring goldfish autoresponder for Postfix

goldfish is a quite simple autoresponder for Postfix in conjunction with MySQL, written in PHP. It consists of only one PHP file which can be started through a cronjob.

In this tutorial, it’s assumed that you have already installed Postfix with MySql backend using this tutorial: Installing Postfix with MySql backend and SASL for SMTP authentication

1. Install PHP5-CLI (Command Line Interpreter) if it’s not already installed

apt-get install php5-cli


2. Create a MySql table for goldfish and a user that has only read access to the users table and read/update access to the autoresponder table (mysql -u root -p)

USE mail;
 
CREATE TABLE autoresponder (
email varchar(255) NOT NULL,
descname varchar(255) default NULL,
`from` date NOT NULL,
`to` date NOT NULL,
message text NOT NULL,
enabled tinyint(4) NOT NULL default '0',
subject varchar(255) NOT NULL,
PRIMARY KEY (email),
FULLTEXT KEY message (message)
) TYPE=MyISAM;
 
-- Insert a sample out of office message for the
-- e-mail address [email protected]
-- Goldfish automatically enables and disables the out of office
-- message according to the from and to dates
-- (in this case: 7th of august to 14th of august 2010)
INSERT INTO autoresponder ('[email protected]', 'Your name', '2010-08-07', '2010-08-14', 'Your message', '1', 'Your subject');
 
GRANT SELECT,UPDATE ON mail.autoresponder TO '{username}'@'localhost' IDENTIFIED BY '{password}';
GRANT SELECT ON mail.users TO '{username}'@'localhost' IDENTIFIED BY '{password}';
FLUSH PRIVILEGES;
 
exit;


Replace {username} and {password} with selected username and password that will be used by goldfish

We’ll use the mail database that was created in the Postfix installation tutorial.

3. Download goldfish and put it to any location on the server. In this example I’ll place it in /usr/local/goldfish

mkdir /usr/local/goldfish
wget http://www.remofritzsche.com/projects/goldfish/download/goldfish-1.1-STABLE.tar.gz
tar zxvf goldfish-1.1-STABLE.tar.gz
mv goldfish-1.1-STABLE/* /usr/local/goldfish/
rm goldfish-1.1-STABLE* -rf # Clean up


1.1 was the latest stable version when this tutorial was written. Check this location for updated version: http://www.remofritzsche.com/projects/goldfish/download/

4. Configure database information in goldfish (pico /usr/local/goldfish/autoresponder.php)

/* Database information */
$conf['mysql_host'] = "localhost";
$conf['mysql_user'] = "{username}";
$conf['mysql_password'] = "{password}";
$conf['mysql_database'] = "mail";


Input your MySql server information and the login created in step 2. mysql_host should be localhost in most cases and the mysql_database should be mail (if you didn’t choose another name for the database when Postfix was installed with MySql backend)

5. Configure database queries in goldfish (pico /usr/local/goldfish/autoresponder.php)

/* 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'";


The database queries begin on line 56 in version 1.1. Replace the q_mailbox_path query with the one shown above. The only change is that the table name is changed from view_users to users.

6. Make the vmail user the owner of the goldfish directory and make the php file executable. The vmail user was created when you installed Postfix.

chown vmail /usr/local/goldfish -R
chmod 700 /usr/local/goldfish/autoresponder.php


7. Create a cronjob to run goldfish every 5 minutes as the vmail user. It must be running as a user that can read the maildir mailboxes currently located in /home/vmail. (crontab -e)

*/5 * * * * vmail /usr/local/goldfish/autoresponder.php