Email arrives at our ISP, and fetchmail(1) brings it to our home using POP over a port forwarded with ssh(1). Here at home, the MDA is procmail(1), which may (it does in my case) sort the inbound messages into folders in maildir format. We read our home email using Thunderbird/Linux or Mail.app/OS X or the IOS Mail thing. These MUAs talk IMAP to the server (dovecot(1) running on Mac Mini). All this works great until we're out of the area.
The lovely Carol will be out of town for a while, and she wants me to send email to one of her webmail accounts (gmail, yahoo, etc.)—but only email originating from an address in her Address Book.
Fly-in-the-ointment #1
She uses Mail.app on both a Macbook Air (portable) and iMac (desktop). These MUAs see the same messages (folders, etc.) because they manipulate email on the server. But that's it! Meaning that each mail client has its own address book. In addition, the Mini is running OS X 10.10.3, where the addressbook is called "Contacts" whereas the MBA runs 10.6.8, where addressbook is called "Address Book."Address list #1
Starting with the mini, we go into Contacts and say File⇒Export... and select "export archive" or something like this. This creates a new directory named "Contacts - MM-DD-YYYY.abbu" or something like this. In that directory is a file named "AddressBook-v22.abcddb". And file(1) reports that it's aContacts - 09-05-2015.abbu/AddressBook-v22.abcddb: SQLite 3.x databaseI copied it to my homedir and then had to go get sqlite3.
collin@p64:~$ sudo aptitude install sqlite3 collin@p64:~$ sudo aptitude install sqlite3-doc collin@p64:~$Then I had to learn how sqlite3 works. Fortunately I've used mysql before, and I could always rtfm...
collin@p64:~$ man sqlite3
SQLITE3(1) SQLITE3(1)
NAME
sqlite3 - A command line interface for SQLite version 3
SYNOPSIS
sqlite3 [options] [databasefile] [SQL]
SUMMARY
sqlite3 is a terminal-based front-end to the SQLite library that can
…
Yippee! Now let's see what's there…
collin@p64:/mnt/home/collin$ sqlite3 from-carol/AddressBook-v22.abcddb
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /mnt/home/collin/from-carol/AddressBook-v22.abcddb
sqlite> .tables
ZABCDALERTTONE ZABCDPOSTALADDRESS
ZABCDCALENDARURI ZABCDRECORD
ZABCDCONTACTDATE ZABCDRELATEDNAME
ZABCDCONTACTINDEX ZABCDREMOTELOCATION
ZABCDCUSTOMPROPERTY ZABCDSERVICE
ZABCDCUSTOMPROPERTYVALUE ZABCDSHARINGACCESSCONTROLENTRY
ZABCDDATECOMPONENTS ZABCDSOCIALPROFILE
ZABCDDELETEDRECORDLOG ZABCDUNKNOWNPROPERTY
ZABCDDISTRIBUTIONLISTCONFIG ZABCDURLADDRESS
ZABCDEMAILADDRESS Z_16PARENTGROUPS
…
sqlite> .schema ZABCDEMAILADDRESS
CREATE TABLE ZABCDEMAILADDRESS ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z21_OWNER INTEGER, ZADDRESS VARCHAR, ZADDRESSNORMALIZED VARCHAR, ZLABEL VARCHAR, ZUNIQUEID VARCHAR );
CREATE INDEX ZABCDEMAILADDRESS_ZADDRESSNORMALIZED_INDEX ON ZABCDEMAILADDRESS (ZADDRESSNORMALIZED);
CREATE INDEX ZABCDEMAILADDRESS_ZADDRESS_INDEX ON ZABCDEMAILADDRESS (ZADDRESS);
CREATE INDEX ZABCDEMAILADDRESS_ZOWNER_INDEX ON ZABCDEMAILADDRESS (ZOWNER);
sqlite> select ZADDRESSNORMALIZED from ZABCDEMAILADDRESS;
foo@bar.com
…
That's right: there came a whole slew of email addresses. These are "good" ones.
Combine with address list #2
List #2 is "Address Book" from the MBA running 10.6.8; we export the archive somewhere and get a directory named "Address Book - 2015-09-05.abbu", which contains a file also named "AddressBook-v22.abcddb"; I saved this to a different name, actually "MBA-AddressBook-v22.abcddb", and moved the one from the mini to "miniAddressBook-v22.abcddb", whence it was time to create a combined address list. Like this:collin@p64:~/tmp$ DBs=$HOME/from-carol/*.abcddb collin@p64:~/tmp$ for F in $DBs; do \ echo "select ZADDRESSNORMALIZED from ZABCDEMAILADDRESS;" | sqlite3 $F; \ done…all done under script(1) Then take the output from that... which may contain whitespace, and also entries like
[16-bit characters] <some@addr>To deal with that, do this:
collin@p64:~/tmp$ grep @ typescript | grep -v "<" | tr -d ' ' > a1 collin@p64:~/tmp$ grep @ typescript | grep '<' | cut '-d<' -f2 | cut '-d>' -f1 | tr -d ' ' >> a1 collin@p64:~/tmp$ sort -f a1 | uniq > a2 collin@p64:~/tmp$ less a2That yielded some stuff I don't like, such as:
collin@p64:~/tmp$forFin$DBs;doecho"selectZADDRESSNORMALIZEDfromZAB^MCDEMAILADDRE
A little tweaking covered the bad addresses...
collin@p64:~/tmp$ grep -ve p64 -e / a2 > a3 collin@p64:~/tmp$One more thing: because it was a script(1) output, we need to get rid of the '\r' characters, shown below as 0d. We can do it like so:
collin@p64:~/tmp$ head -n1 a3 | hexdump -C 00000000 31 30 31 36 36 31 2e 33 30 33 33 40 63 6f 6d 70 |101661.3033@comp| 00000010 75 73 65 72 76 65 2e 63 6f 6d 0d 0a |userve.com..| 0000001c collin@p64:~/tmp$ view a3 collin@p64:~/tmp$ tr -d '^M' a3 > a4 tr: extra operand `a3' Only one string may be given when deleting without squeezing repeats. Try `tr --help' for more information. collin@p64:~/tmp$ tr -d '^M' < a3 > a4 collin@p64:~/tmp$ head -n2 a3 | hexdump -C 00000000 31 30 31 36 36 31 2e 33 30 33 33 40 63 6f 6d 70 |101661.3033@comp| 00000010 75 73 65 72 76 65 2e 63 6f 6d 0d 0a 31 30 33 33 |userve.com..1033| 00000020 35 33 2e 32 33 31 30 40 63 6f 6d 70 75 73 65 72 |53.2310@compuser| 00000030 76 65 2e 63 6f 6d 0d 0a |ve.com..| 00000038 collin@p64:~/tmp$ head -n2 a4 | hexdump -C 00000000 31 30 31 36 36 31 2e 33 30 33 33 40 63 6f 6d 70 |101661.3033@comp| 00000010 75 73 65 72 76 65 2e 63 6f 6d 0a 31 30 33 33 35 |userve.com.10335| 00000020 33 2e 32 33 31 30 40 63 6f 6d 70 75 73 65 72 76 |3.2310@compuserv| 00000030 65 2e 63 6f 6d 0a |e.com.| 00000036 collin@p64:~/tmp$Now we have a file of known good addresses. Let's put them where Carol's procmail can find them.
collin@p64:~/tmp$ cp a4 $HOME/../carol/from-collin/known-good-addresses.txt collin@p64:~/tmp$So far so good. Then on the mini:
mini1:~ collin$ sudo su - carol mini1:~ carol$ mv from-collin/known-good-addresses.txt Maildir/ mini1:~ carol$ ls -o Maildir/kn* -rw-r--r-- 1 collin 15377 Sep 5 15:51 Maildir/known-good-addresses.txt mini1:~ carol$Whoops! Carol doesn't want a file owned by me, to refer to.
mini1:~ carol$ cp Maildir/known-good-addresses.txt Maildir/known-good-addresses.2015-09-05.txt mini1:~ carol$ ls -l Maildir/kn* -rw-r--r-- 1 carol _lpoperator 15377 Sep 5 15:54 Maildir/known-good-addresses.2015-09-05.txt -rw-r--r-- 1 collin _lpoperator 15377 Sep 5 15:51 Maildir/known-good-addresses.txt mini1:~ carol$ mv Maildir/known-good-addresses.txt ~/from-collin/ mini1:~ carol$ ^Dlogout skipping clear mini1:~ collin$
Now to tell procmail about that
I'm just gonna write this...
1 # auto-forward to ALT DEST?
2 :0
3 * AUTOFORWARD ?? yes
4 {
5 VERBOSE=yes LOGABSTRACT=yes
6
7 KNOWNGOOD=known-good-addresses.2015-09-05.txt
8 ALT_DEST=redacted@redacted.com
9
10 SENDIT=no
11
12 :0 Whc
13 | formail -zxfrom: -xsender: | grep -qif $KNOWNGOOD
14
15 :0 a
16 { SENDIT=yes }
17
18 :0 EWhc
19 | formail -rzxto: | grep -qif $KNOWNGOOD
20
21 :0 a
22 { SENDIT=yes }
23
24 :0 c ← See below for workaround
25 * SENDIT ?? yes
26 ! $ALT_DEST
27
28 VERBOSE=no
29 }
Added to Carol's .procmailrc. Here's what it does.
- Line 3 basically says not to bother with lines 4-29 unless "AUTOFORWARD=yes" appears somewhere before here.
- Lines 7-8 set some values that we'll use later. We'll refer to them as $KNOWNGOOD and… well, you get the idea
- Line 12 says that this recipe:
- W: must Wait for the pipe (line 13) to complete and check the exit code;
- h: pass only the header to the recipe
- c: continue (i.e., don't terminate) in case the pipe is successful
- Lines 15-16 say: if we ran line 13 and it was successful (that's the "a" on 15), then set variable SENDIT to "yes"
- Lines 18 says that this recipe:
- E: will run only if we did not execute line 16
- W, h, c: as line 12
- Lines 21-22 are like 15-16
- Line 24 says to continue on success (as explained for line 12);
line 25 says keep going only if SENDIT was set to "yes"
and if so, run line 26, which forwards the email to $ALT_DEST... which now that I think of it will probably fail sometimes.Fly-in-the-ointment #2
Because if the email came from, say, yahoo.com, we're now going to forward it using our ISP's mail server. So the email address at $ALT_DEST will see an email, supposedly from yahoo.com, coming from our ISP and not from any authorized sender of yahoo.com-originated email. That violates sender psomething framework (SPF) and the mail will either bounce or get spam-filed. Urp! I'll figure out a fix later... Time now to make dinner. - You can ignore lines 5 and 28; that's "For Nerds Only"
Workaround for fly-in-the-ointment #2
OK, replace lines 24-26 with the following to resolve the SPF issue. And I think… yep, it's Sender Policy Framework:24 :0
25 * SENDIT ?? yes
26 {
27
28 :0
29 * ^From: *\/.*
30 { FROM=$MATCH }
31
32 :0 fhw
33 * FROM ?? @(facebook|google|gmail|aol|yahoo).com
34 | formail "-iReply-To: $FROM" "-iFrom: redacted@redacted (See Reply-To)"
35
36 :0 c
37 ! $ALT_DEST
38 }
Here's how it works.
- 24 is the usual beginning of a procmail recipe. Initially I tried ":0c" here but that resulted in some odd messages and non-functioning. I suspect another procmail+MacOS issue but didn't want to take more time to investigate that; it might be the 4th fly in the ointment… In any case, I coded no "c" here.
- 25 says to do lines 26-38 only if we set SENDIT to "yes" (i.e., in 16 or 22)
- Lines 28-30 let us find who the sender is. Normally I would say something like
FROM=`formail -zxfrom:`
or maybeFROM=`formail -rzxto:`
but because of fly-in-the-ointment #3, aka the procmail-on-Mac problem documented in https://trac.macports.org/ticket/46623 (and referenced here) that won't work. - Line 32 says
- f: treat the recipe as a filter: that is, modify the message and pass it on to the next recipe.
- h: pass only the header to the pipe
- w: wait for the pipe to complete. This ought to be implied for "f" recipes, really, but I'm not sure if it's automatic. I seem to remember being disappointed by this assumption before, but can't say for sure and don't really want to experiment to find out.
- Line 33 says to do the recipe only if $FROM (set in line 30) matches @facebook.com or @google.com or @gmail.com, etc. The parentheses and the "|" character mean what you probably think they do. One bit of sloppiness here: I should perhaps have escaped the "."; as the recipe stands, an address like "whatever@aolxcommunity" would match. But it's close enough
- Line 34 fixes the addressing for the email message. First,
we put the original "From:" address into the "Reply-To" header.
Rather than claiming that the message comes from facebook, google, gmail, etc., we'll say that the lovely Carol is sending it from our ISP. That gets past the SPF filters. The "From:" line will remind her, when she sees one of these, to look at the Reply-To: header to find out where the mail really came from. - Finally, lines 36-37 send the mail to the alternate destination, which she'll be able to read while on the road. The "c" in line 36 like the "c" in line 12.
No comments:
Post a Comment