Configuring Systems To Use The Bulkmail Module

part of the ArsDigita Community System by gregh and hqm

The Big Picture

The bulkmail system provides a high performance interface to email sending services, allowing the developer to take advantage of advanced features such as VERP headers, bounce handling, and multiple mail sending servers.

The bulkmail system is not fully documented yet. This is a placeholder document. Below are some working notes about configuring and using the system.

Under the Hood

Configure qmail servers

A set of qmail servers should be configured on multiple ports and/or hosts in order to serve outgoing mail connections from an AOLserver running the bulkmail module.

[instructions here for configuring qmail to listen on N TCP ports]

[instructions should include what config files need to be set to allow mail relaying, plus how to configure test mailers which deliver all mail to a local filesystem] Configuring qmail bounce handling for use with the Bulkmail module For each mailer host, qmail configuration files need to be edited as follows:

Bounce handling for hostname.com (i.e., away.com)

In /var/qmail/alias the following files need to be created or modified: Then in your AOLServer /web/hostname/parameters/hostname.ini file, set the following params in the bulkmail section. 
[ns/server/hostname/acs/bulkmail]
; the template for the bounce (sender) address
BulkmailSenderAddress=email-return-$key_code@hostname.com

; the template for the reply-to address
BulkmailReplyAddress=email-reply-$key_code@hostname.com

Restart the qmail send process

On the mailer host(s) restart the qmail process with a HUP signal. 
as root:
ps -ef | grep qmail-send | grep -v grep | perl -n -a -e 'print "kill -HUP $F[1]\n"' | bash
man -M /var/qmail/man dot-qmail 
p: You need to cd to /service, and then issue a '/usr/local/bin/svc -d ' where instance is qmail, qmail2 or qmail3

Automatic handling of unsubscribe requests

The VERP tags used in bounce handling on outgoing mail in the return-path can also be used to do double duty in the reply-to address system, to make it easier to handle unsubscribe requests generated by users hitting "reply" to the bulkmail message they receive. The email-reply-$key_code above can be used to trigger a script which runs on any mail to the address "email-reply-XXXXXX@hostname.com". An example unsubscribe perl script is shown below. The script below should be invoked via the following qmail alias file: Create the file /var/qmail/alias/.qmail-hostname-email-reply-default containing the line 
|/web/yourserver/bin/unsubscribe-user.pl "$EXT4"
This will ensure that the script gets passed the VERP code if there is one that the message was generated with, which is the most reliable way to figure out the user_id (and email address) the mail was sent to in the first place. 

#!/usr/local/bin/perl
#
# Unsubscribe the user or users in an email message from all newsletters
#
# hqm@ai.mit.edu
#
# If bulkmail VERP keycode arg is supplied, that user is unsubscribed, otherwise
# parses out the From header and unsubscribes the user from all newsletters
# (actually, removes them from any group of type 'newsletter').
#
# Args: (optional) bulkmail encoded VERP key containing user_id
# and raw message body on stdin

# Oracle access
$ENV{'ORACLE_HOME'} = "/ora8/m01/app/oracle/product/8.1.6";
$ENV{'LD_LIBRARY_PATH'} = "/ora8/m01/app/oracle/product/8.1.6/lib:/lib:/usr/lib";
$ENV{'ORACLE_BASE'} = "/ora8/m01/app/oracle";
$ENV{'ORACLE_SID'} = "ora8";

$VERP_code        = shift;

$mailer = "/var/qmail/bin/qmail-inject";

use DBI;
use Mail::Address;
require Mail::Send;

################################################################
# Global Definitions

# For sending email error message replies back to member
$mailer = "/usr/lib/sendmail";
$return_address = "webmaster\@hostname.com";
$maintainer = "webmaster\@hostname.com";
$an_system_url = "http://www.hostname.com";


$DEBUG = 1;
$debug_logfile = "/web/yourwebserver/log/unsubscribe.log"; 



if ($DEBUG) {
    open (LOG, ">>$debug_logfile");
    debug("================================================================\n");
    debug(`/bin/date`);
}


$db_datasrc = 'dbi:Oracle:';
$db_user = 'YOURDBUSER;
$db_passwd = 'YOURDBPASSWD';

#################################################################
## Snarf down incoming msg on STDIN
#################################################################

# extract From: header
while (<>) {
    $in_header =   1  .. /^$/;
    if ($in_header) {
	if (/^From:.*([\s<]\S+\@\S+[>\s])/ || /^Subject:.*([\s<]\S+\@\S+[>\s])/) {
	    $line = $1;
	    @from = Mail::Address->parse($line);
	    $from_address = $from[0]->address;
	    last;
	}
    }
}
    
debug("VERP code = $VERP_code\n");

# open the database connection
$dbh = DBI->connect($db_datasrc, $db_user, $db_passwd)
  || die "Couldn't connect to database";
$dbh->{AutoCommit} = 1;

if ($VERP_code eq "") {
    $user_id = get_user_id_from_email($from_address);
} else {
    $user_id = decode_verp_key($VERP_code);
}

debug("user id = $user_id\n");

if ($user_id ne "") {
    $real_email = get_email_from_user_id($user_id);
    $err = unsubscribe_user($user_id);
    debug("unsubscribing user_id $user_id, email=$real_email, err=$err\n");
    if ($err eq "") {
	send_email($real_email, $return_address,
		   "Unsubscribed $real_email from hostname.com newsletters",
		   "The account with email address $real_email has been unsubscribed from all newsletters on hostname.com.\n");
    } else {
	send_email($real_email, $return_address,
		   "Error unsubscribing from hostname.com newsletters",
		   "There was an error processing your unsubscribe request. Please contact webmaster\@hostname.com. It would be helpful to forward an original copy of the newsletter you are trying to unsubscribe from.

Thank you.
\n");
    }
}

# All done

$dbh->disconnect;
if ($DEBUG) { close LOG; }

sub debug () {
    my ($msg) = @_;
    print LOG $msg;
}

# Remove user from all newsletters
# args: user_id
sub unsubscribe_user () {
  my ($id) = @_;
  $query = "delete from user_group_map 
            where user_id = $id and
            group_id in (select group_id from user_groups where group_type = 'newsletter')";
  $sth= $dbh->prepare($query) || return $dbh->errstr;
  $sth->execute || return $dbh->errstr;
  $sth->finish;
  return;
}

# take an email addr, return a user_id
sub get_user_id_from_email () {
    my ($email) = @_;
    # SQL quotify
    ($QQemail = $email) =~ s/\'/''/g;
    $h = $dbh->prepare(qq[SELECT user_id FROM users
			  WHERE lower(email) = lower('$QQemail')]);
    if (!$h->execute()) {
	die "Unable to execute query in send_error_reply:\n" . $dbh->errstr;
    }
    $id = $h->fetchrow;
    $h->finish;
    return ($id);
}


sub get_email_from_user_id () {
    my ($id) = @_;
    $h = $dbh->prepare(qq[SELECT email FROM users
			  WHERE user_id = $id]);
    if (!$h->execute()) {
	die "Unable to execute query in send_error_reply:\n" . $dbh->errstr;
    }
    $email = $h->fetchrow;
    $h->finish;
    return ($email);
}




################################################################
# decode_verp_key(key)
#
# Decode the user_id from a bulkmail VERP key
#
#
################################################################
#  regexp -nocase {([0-9]+)A([0-9]+)B([0-9]+)} $user_content match bulkmail_id user_id time

sub decode_verp_key () {
  my ($key) = @_;
  my ($bulkmail_id,$user_id,$nstime);

# key was generated by /tcl/bulkmail-utils
  ($bulkmail_id, $user_id, $nstime) =
      ($key =~ /^(\d+)A(\d+)B(\d+)C(\d+)$/i);
  
  return ($user_id);
}


################################################################
# send_email (recipient, sender, subject, body)
################################################################

sub send_email () {
    my ($recipient, $sender, $subject, $body) = @_;
    open(MAIL, "|$mailer $recipient -f$sender") || die "Cannot open: '$mailer'\n";
    print MAIL "To: $recipient\nFrom: $sender\nSubject: $subject\n\n$body\n";
    close(MAIL);
}