Configuring postfix is well-known to be the hardest, least intellectually stimulating task any IT person will ever pass off to a hapless subordinate. I, unfortunately, would seem to be that hapless subordinate. Eric recommended (and, in fact, convinced me) that I write a perl script to act as a delivery agent for postfix. This would offer grate flexibility, customizability, blah, blah, blah. Not to drag out the story, I got to a point where I realized I had a 200 line mess that didn’t work half of the time, wasn’t properly interacting with postfix, and needed another 1000 or so lines to do what was really required of it. So I dropped the project, and fell back to using mysql aliases, which looked to me like the easiest option when I need relatively dynamic mailing lists integrating with custom software that uses mysql.

My task was in two parts – write a relatively simple system that enabled users of TMS to receive mail at username@robot.mbhs.edu, as well as a limited set of aliases (like webmaster@robot.mbhs.edu – that’s me!). The second task was to create an alumni mailing list (at the same domain, on the same host), to which only a certain group of people (the ’senders’) could send mail. I thought it might be nice to use some good, old-fashioned third party software the way the school does for all its other mailing lists (or maybe google groups would be a nice idea?), but Eric wouldn’t hear of it – he wanted in-house. Irony: he and the other Teque folks spent at least a month making fun of me for my fondness for in-house software.

The first part was relatively simple, using the tutorial here. The second part was slightly harder. For starters, the alumni list was stored on binx, and I was hosting the listserv from ogodei. So some crontabs were in order. From binx, I printed the list from mysql to a file (not shown). From ogodei, I did the opposite:

root@ogodei:/var/www/ctda# cat /etc/postfix/alumni-email-manager.pl
#!/usr/bin/perl

use DBI;

$dbh=DBI->connect("dbi:mysql:web;user=web;password=********");
open FH,"/var/local/roboweb/alumni_emails";
$dbh->do("start transaction");
$dbh->do("truncate table alumni_list_temp");
while() {
    $email=$_;
    $email=~s/\n//;
    $dbh->do("insert into alumni_list_temp (destination) values ('$email')");
}
$dbh->do("commit");
close FH;
$dbh->disconnect;

Then I had to actually set up the mailing list, along with the relevant permissions to ensure that people outside of the senders list could not write to the list. (We have no moderation system, since our in-house ability does not extend to rewriting mailman.)

root@ogodei:/var/www/ctda# cat /etc/postfix/main.cf
# See /usr/share/postfix/main.cf.dist for a commented, more complete version


# Debian specific:  Specifying a file name will cause the first
# line of that file to be used as the name.  The Debian default
# is /etc/mailname.
#myorigin = /etc/mailname  

smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
biff = no

# appending .domain is the MUA's job.
append_dot_mydomain = no   

# Uncomment the next line to generate "delayed mail" warnings
#delay_warning_time = 4h

readme_directory = no

smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
smtpd_use_tls=yes
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache

smtpd_recipient_restrictions = check_recipient_access mysql:/etc/postfix/protected_users.cf, reject_unauth_destination

smtpd_restriction_classes = whitelist
whitelist = check_sender_access mysql:/etc/postfix/whitelist.cf, reject

myhostname = team.robot.mbhs.edu
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
myorigin = /etc/mailname
mydestination = team.robot.mbhs.edu, localhost.robot.mbhs.edu, localhost, robot.mbhs.edu, www.robot.mbhs.edu, ogodei.mbhs.edu, tms.robot.mbhs.edu
relayhost = 
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_command =
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all
inet_protocols = all
home_mailbox = Maildir/

alias_maps = mysql:/etc/postfix/mysql-aliases1.cf, mysql:/etc/postfix/mysql-aliases2.cf, mysql:/etc/postfix/mysql-aliases3.cf

Scattered throughout, but not shown here, are various debugging lines from me trying to figure out why postfix was completely ignoring my instructions.

root@ogodei:/var/www/ctda# cat /etc/postfix/mysql-aliases1.cf 
hosts=127.0.0.1
user = web
password = #######

dbname = web

query = SELECT destination FROM mail_aliases WHERE source='%u'
result_format = %s@robot.mbhs.edu

root@ogodei:/var/www/ctda# cat /etc/postfix/mysql-aliases2.cf 
hosts=127.0.0.1
user = web
password = ##########
dbname = web
query = SELECT email FROM users WHERE username='%u'

root@ogodei:/var/www/ctda# cat /etc/postfix/mysql-aliases3.cf 
hosts=127.0.0.1
user = web
password = ########
dbname = web
query = SELECT destination FROM alumni_list_temp WHERE '%u'='alumni'

The filenames being undescriptive, here’s what they do: the first handles the aliases, and redirects it to either another alias or a username (for example, webmaster@robot.mbhs.edu might redirect to web.dev@robot.mbhs.edu, which might redirect to scott@robot.mbhs.edu). Ruleset 2 handles actual usernames. Ruleset three handles the alumni mailing list.

Footnote: yes, as those of you who have actually been reading this blog have noticed, this is the same project I outlined in Risky Plans. The others have not made much progress either, but I have release FIRSTSearch!

Related posts: