I run my own mail server. I know, this makes me strange. But, one of the things I can do with this is give out email addresses that represent a “capability” to send me mail to a particular organization or person. Transfer of that address to another person will still reveal the initial holder. This is great for figuring out who sold or had stolen their database of email addresses!

For this exercise, we’ll be using postfix to handle mail and sqlite to store the database.

Database and Schema

Where

We’ll want to be making concurrent modifications to the database while the mail system has it open. It’s easiest, by far, to just run (at the sqlite prompt) pragma journal_mode=wal; to use sqlite’s very nice write-ahead-log feature, though this requires that postfix be allowed to write to the database as well as the directory containing it.

Create a sqlite3 database at /var/spool/postfix/etc/db/mailcapdb, and make it and the parent directory /var/spool/postfix/etc/db both readable and writable by postfix and whoever else needs access to the database (for me, that means chmod g+ws /var/spool/postfix/etc/db; chown mail /var/spool/postfix/etc/db).

Note

If smtpd runs chrooted, you’ll want to run these, too:

mkdir -p /var/spool/postfix/var/spool
ln -s ../.. /var/spool/postfix/var/spool/postfix

If smtpd dies with messages like “fatal: dict_sqlite_lookup: …: SQL prepare failed: disk I/O error?”, this is probably what’s wrong!

Destinations

Our system will allow delivery to both local mailboxes and to other email addresses (“aliases”):

CREATE TABLE `aliases` (
  `id` INTEGER PRIMARY KEY,
  `alias` varchar(256) NOT NULL
);
CREATE TABLE `mailboxes` (
  `id` INTEGER PRIMARY KEY,
  `mailbox` text NOT NULL,
  autoarchive TEXT default NULL);

The autoarchive field is reserved for use by a script which drives something like the cleanup-maildir script from http://svn.houseofnate.net/unix-tools/trunk/cleanup-maildir

Capabilities

Here’s the table we’ll use to store the actual mail capabilities themselves:

CREATE TABLE nouncaps (
  `id` INTEGER PRIMARY KEY,
  `cap` varchar(64) NOT NULL COLLATE NOCASE,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `note` text,
  UNIQUE (`cap`) ON CONFLICT ABORT
);

The created, and note fields are entirely for human consumption. The enabled field can be used to logically remove a capability without physically removing it from the table, for ease of log processing, for example.

Destination Routing

A capability may be associated with zero or more aliases or mailboxes. The association tables are as might be expected:

CREATE TABLE cap_aliases(
  `id` INTEGER PRIMARY KEY,
  `cap_id` int NOT NULL,
  `alias_id` int NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  CONSTRAINT `cap_aliases_1` FOREIGN KEY (`cap_id`) REFERENCES `nouncaps` (`id`),
  CONSTRAINT `cap_aliases_2` FOREIGN KEY (`alias_id`) REFERENCES `aliases` (`id`)
);
CREATE INDEX ix__cap_aliases__cap on cap_aliases(cap_id);

CREATE TABLE cap_mailboxes(
  `id` INTEGER PRIMARY KEY,
  `cap_id` int NOT NULL,
  `mailbox_id` int NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  CONSTRAINT `cap_mailboxes_1` FOREIGN KEY (`cap_id`) REFERENCES `nouncaps` (`id`),
  CONSTRAINT `cap_mailboxes_2` FOREIGN KEY (`mailbox_id`) REFERENCES `mailboxes` (`id`)
);
CREATE INDEX ix__cap_mailboxes__cap on cap_mailboxes(cap_id);

Derived Views

We want postfix to see a subset of the capabilities: namely, those that are still enabled. Posfix also expects to be able to perform a single query to get both the email address and alias or mailbox, so we have to join the tables above. This is done with these two views:

CREATE VIEW `v_cap_alias` AS
  select
    `nouncaps`.`cap` AS `cap`,
    `aliases`.`alias` AS `alias`
  from
   (`nouncaps`
     join `cap_aliases` on (`cap_aliases`.`cap_id` = `nouncaps`.`id`)
     join `aliases` on (`cap_aliases`.`alias_id` = `aliases`.`id`))
  where
   ((`nouncaps`.`enabled` = 1) and (`cap_aliases`.`enabled` = 1))

CREATE VIEW `v_cap_mailbox` AS
  select
   `nouncaps`.`cap` AS `cap`,
   `mailboxes`.`mailbox` AS `mailbox`
  from
   (`nouncaps`
     join `cap_mailboxes` on (`cap_mailboxes`.`cap_id` = `nouncaps`.`id`)
     join `mailboxes` on (`cap_mailboxes`.`mailbox_id` = `mailboxes`.`id`))
  where
   ((`nouncaps`.`enabled` = 1) and (`cap_mailboxes`.`enabled` = 1))

Postfix

With all that in place, we have to tell postfix how to use it! First, we need to create the map files that tell postfix how to access the database:

Obviously you’ll want to vary the domain = line to taste.

The last step, then, is to wire these in to the main.cf file. Note that there’s nothing stopping you from having other domains handled, too, as in my configuration:

virtual_mailbox_domains = ietfng.org cmx.ietfng.org
virtual_mailbox_base = /home/mail/mail
virtual_alias_maps = sqlite:/etc/postfix/sqlite_capmail_aliases.cf
                     hash:/etc/postfix/virtual_alias
virtual_mailbox_maps = sqlite:/etc/postfix/sqlite_capmail_mailboxes.cf
                       hash:/etc/postfix/virtual_mailboxes

Creating A New Capability

Ah, the all-important piece. I use something like this at the moment; one could do better:

#!/usr/bin/env perl
use strict;
use warnings;
use DBI;
use DBI qw(:sql_types);
use Getopt::Long;
use String::Random;

sub print_rows($) {
  my ($sth) = @_;
  while ( my ($retcap, $retnote, $retable) = $sth->fetchrow_array ) {
    if ($retable) {
      print "$retcap\@cmx.ietfng.org --- $retnote\n";
    } else {
      print "$retcap\@cmx.ietfng.org -!- $retnote\n";
    }
  }
}

my $DEBUG = 0;

my $DBFILE="/var/spool/postfix/etc/db/mailcapdb";
my $CAPLEN = 30;
my $CAP = undef;
my $MODE = "generate";
my $NOTE = undef;
my $ENABLE = undef;
my $DRYRUN = 0;
GetOptions ('db=s'   => \$DBFILE,
            'note=s' => \$NOTE,
            'cap=s'  => \$CAP,

            'generate' => sub { $MODE = "generate"; },
            'create'   => sub { $MODE = "generate"; },
	    'look'     => sub { $MODE = "look"; },
	    'show'     => sub { $MODE = "look"; },
	    'lookup'   => sub { $MODE = "look"; },
	    'search'   => sub { $MODE = "search"; },

	    # Can also change the NOTE
            'enable=i' => sub { my ($on, $ov) = @_; $ENABLE=$ov; $MODE = "able"; },

            'short'     => sub { $CAPLEN = 20; },
            'veryshort' => sub { $CAPLEN = 10; },
            'length=i'  => \$CAPLEN,

            'debug'     => sub { $DEBUG++; },
            'dryrun'    => sub { $DRYRUN++; },
           )
 or die;

my $dbh = DBI->connect("dbi:SQLite:dbname=$DBFILE","","", { RaiseError => 1 }) or die $DBI::errstr;

print STDERR "Operating in mode $MODE\n" if $DEBUG;

if ($MODE eq "generate") {
  die "Specify --note=<note>" unless defined $NOTE;
  unless (defined $CAP) { 
    my $sr = new String::Random;
    $sr->{'A'} = [ 'A'..'Z', '0'..'9' ];
    $CAP = $sr->randpattern("A" x $CAPLEN);
  }
  my $sth = $dbh->prepare("INSERT INTO nouncaps (cap, note) values (?, ?);");
  $sth->bind_param(1, $CAP, SQL_VARCHAR);
  $sth->bind_param(2, $NOTE, SQL_VARCHAR);
  $sth->execute() if not $DRYRUN;
  $sth->finish();

  $sth = $dbh->prepare("SELECT id FROM nouncaps WHERE cap = ?;");
  $sth->bind_param(1, $CAP, SQL_VARCHAR);
  $sth->execute() if not $DRYRUN;
  my ($retcapid) = $sth->fetchrow_array;
  if (not $DRYRUN) { $sth->fetchrow_array and die "Duplicate ID for cap??"; }
  $sth->finish();

  $sth = $dbh->prepare("INSERT INTO cap_mailboxes (cap_id, mailbox_id) values (?,1);");
  $sth->bind_param(1, $retcapid, SQL_INTEGER);
  $sth->execute() if not $DRYRUN;
  $sth->finish;

  print "Use $CAP\@cmx.ietfng.org\n";

} elsif ($MODE eq "able") {
  die "Specify --cap=<cap>" unless defined $CAP;
  die "Specify --enable=<value>" unless defined $ENABLE;
  my $sth = $dbh->prepare("UPDATE nouncaps SET enabled = ? WHERE cap = ?");
  $sth->bind_param(1, $ENABLE, SQL_INTEGER);
  $sth->bind_param(2, $CAP, SQL_VARCHAR);
  $sth->execute();
  $sth->finish();

  if (defined $NOTE) {
    my $sth = $dbh->prepare("UPDATE nouncaps SET note = ? WHERE cap = ?");
    $sth->bind_param(1, $NOTE, SQL_VARCHAR);
    $sth->bind_param(2, $CAP, SQL_VARCHAR);
    $sth->execute();
    $sth->finish();
  }

} elsif ($MODE eq "search") {
  die "Specify --note=<note>" unless defined $NOTE;
  print STDERR "Search for $NOTE\n" if $DEBUG;
  my $sth = $dbh->prepare("SELECT cap, note, enabled FROM nouncaps WHERE note LIKE ?;");
  $sth->bind_param(1, $NOTE, SQL_VARCHAR);
  $sth->execute();
  print_rows($sth);
  $sth->finish();
} elsif ($MODE eq "look") {
  die "Specify --cap=<cap>" unless defined $CAP;
  my $sth = $dbh->prepare("SELECT cap, note, enabled FROM nouncaps WHERE cap LIKE ?;");
  $sth->bind_param(1, $CAP, SQL_VARCHAR);
  $sth->execute();
  print_rows($sth);
  $sth->finish();
}
print "DONE\n" if $DEBUG;