Show Source

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 caps (
  `cap_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',
  `alias_id` int(11) DEFAULT NULL,
  `mailbox_id` int(11) DEFAULT NULL,
  `note` text,
  UNIQUE (`cap`) ON CONFLICT ABORT,
  CONSTRAINT `caps_ibfk_1` FOREIGN KEY (`alias_id`) REFERENCES `aliases` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `caps_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `mailboxes` (`id`) ON UPDATE CASCADE
);

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.

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 two tables above. This is done with these two views:

CREATE VIEW `v_cap_alias` AS
  select `caps`.`cap` AS `cap`, `aliases`.`alias` AS `alias`
  from (`caps` join `aliases` on (`aliases`.`id` = `caps`.`alias_id`))
  where ((`caps`.`alias_id` is not null) and (`caps`.`enabled` = 1));

CREATE VIEW `v_cap_mailbox` AS
  select `caps`.`cap` AS `cap`, `mailboxes`.`mailbox` AS `mailbox`
  from (`caps` join `mailboxes` on (`mailboxes`.`id` = `caps`.`mailbox_id`))
  where ((`caps`.`mailbox_id` is not null) and (`caps`.`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:

  • /etc/postfix/sqlite_capmail_aliases.cf

    dbpath = etc/db/mailcapdb
    domain = cmx.ietfng.org
    query = SELECT alias FROM v_cap_alias WHERE cap = "%u"
    expansion_limit = 1
    
  • /etc/postfix/sqlite_capmail_mailboxes.cf

    dbpath = etc/db/mailcapdb
    domain = cmx.ietfng.org
    query = SELECT mailbox FROM v_cap_mailbox WHERE cap = "%u"
    expansion_limit = 1
    

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) = $sth->fetchrow_array ) {
    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"; },
            '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 caps (cap, mailbox_id, note) values (?, 1, ?);");
  $sth->bind_param(1, $CAP, SQL_VARCHAR);
  $sth->bind_param(2, $NOTE, SQL_VARCHAR);
  $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 caps 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 caps 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 FROM caps 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 FROM caps WHERE cap LIKE ?;");
  $sth->bind_param(1, $CAP, SQL_VARCHAR);
  $sth->execute();
  print_rows($sth);
  $sth->finish();
}
print "DONE\n" if $DEBUG;