##############################
Single-Purpose Email Addresses
##############################

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"):

.. code-block:: sql

  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:

.. code-block:: sql

  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:

.. code-block:: sql

  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:

.. code-block:: sql

  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:

* ``/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:

.. literalinclude:: capmail.pl
   :language: perl