We might be the last dinosaurs in California that use a non-web email service,
but just in case we aren't, here's the problem and solution. At least I hope
the solution works; I'm gonna document it here while developing…
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 a
Contacts - 09-05-2015.abbu/AddressBook-v22.abcddb: SQLite 3.x database
I 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 a2
That 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
and line 13 takes the header and passes it to formail(1). We then check the from: and sender: fields for a match with the $KNOWNGOOD list.
- 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
and line 19 tells formail to create a reply, then remove the "to:" field from said reply, then check (grep) the result against $KNOWNGOOD
- 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"
I did a quick test, and at least to an initial approximation "basically, it works."
I haven't tried either a "sender" or a "address in reply but not in 'from'" test,
but those are pretty rare; I guess I could claim they're there only for completeness...
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 maybe FROM=`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.
I gave this version a quick test, too, and it seems to work.
