B. Schema

1. Transport

The first table, so positioned because it is used as a foreign key
constraint by another table, is the Transport table. This table will be
used for transport_maps in the Postfix configuration.

Oddly enough, I recommend against using transport_maps in most cases,
and have seen many online HOWTOs which overuse it. Postfix
transport_maps are used to override DNS lookups for mail routing. A
typical (and reasonable) use is for relay domains, where a Postfix
server is the public MX for a domain, but not the final mailstore for
the mail. (Since this example implements relay domains, we need to have
transport_maps.)

CREATE TABLE "Transport" (tpNum INTEGER PRIMARY KEY,
	active INTEGER DEFAULT 1, tpTransport TEXT, tpNexthop TEXT,
	tpMx INTEGER DEFAULT 1, tpPort INTEGER,
	UNIQUE (tpTransport,tpNexthop,tpMx,tpPort));

Each table has an "active" column, listed as an integer because of
sqlite limitations. But it is generally only 0 or 1, a boolean value, 0
being false. In actual practice as the queries are written, we check for
for "active!=0". Therefore any other value would equate to true.

This is the only column name in the entire schema which is duplicated.
The rest of it uses the obnoxious camelCase naming practice, where each
table has a unique prefix on all [other] column names. It was easier for
me as a SQL beginner to do this. In the future I will probably go to
plain column names and not worry about duplication.

Here in Transport, we use tpNum as a pointer in both the Domain and the
Address table, q.v. It refers to the unique combination of tpTransport,
tpNexthop, tpMx and tpPort. The tpMx value is another boolean: if 0, MX
lookup of the name given is disabled. If 1 (the default), Postfix does a
MX lookup of the name in DNS to find out how to route the mail.

"tpTransport" refers to the Postfix transport(5) which must be defined
in the master.cf file; see also master(5) for file format.

"tpPort" is an optional numeric value for a non-standard TCP port for
the target service. Mail exchange is on port 25 by default, but when we
need to override DNS, we often also need to use a non-standard port.

(Do note that transport_maps is not the only way to alter mail routing
for a given domain. An altered view of DNS can accomplish the same
thing. But for per-address routing control, transport_maps is what you
need.)

2. RClass

The second table, RClass, is also referenced as a foreign key. This
table is very small and simple, and must be kept in synch with the RC*
restriction class definitions in main.cf. These are listed under
"Composite classes" in the sample main.cf below.

CREATE TABLE "RClass" (rcNum INTEGER PRIMARY KEY,
	rcName TEXT NOT NULL, UNIQUE(rcName));

In our recipient access lookup, the query returns rcNum, a hyphen, and
the rcName, prepended by "RC". That result MUST be defined in main.cf,
and also listed among the smtpd_restriction_classes list therein.

3. Domain

The heart of the database, referred to by all elements of the server, is
the Domain table. Every domain used in the configuration is listed in
this table, regardless of the address class to which it belongs. We can
even list domains which are not under our control.

CREATE TABLE "Domain" (domNum INTEGER PRIMARY KEY,
	domName TEXT NOT NULL,
	active INTEGER DEFAULT 1, domClass INTEGER DEFAULT 0,
	domSysUser INTEGER DEFAULT 0, domTpNum INTEGER,
	domRcNum INTEGER DEFAULT 30, UNIQUE (domName),
	FOREIGN KEY(domTpNum) REFERENCES Transport(tpNum)
	FOREIGN KEY(domRcNum) REFERENCES RClass(rcNum));

The domNum integer value is used in many other tables as a foreign key
reference. Queries join the other tables on domNum to look up the
domName string.

The domClass corresponds to the Postfix address class definitions:
  1             Local domain ($mydestination, $local_transport)
  2             Relay domain ($relay_domains, $relay_transport)
  3             Virtual alias domain ($virtual_alias_domains)
 >800           Virtual mailbox domain ($virtual_mailbox_domains,
                        $virtual_transport)
  0             Internet domain ($default_transport)

For virtual mailbox domains, the domClass value is the per-domain
default UID. Each domain or domain owner has a unique UID, and all, by
default, share a common GID. (These defaults can be changed by
per-address UID/GID, but with caveats as will be covered later.)

The domSysUser column is the system UID of the person responsible for
this particular domain. If possible it would be the domain registrant,
but it might just be the person who is the contact for the hosting
arrangement. In this example, we'll see three system users, each of whom
controls two or more domains in two or more classes.

Nothing is being done with the domSysUser column at this point.
Eventually it could be used in conjunction with automated DNS and/or
whois checks to alert the domain manager to any problems. I figure it's
best to set up the database from the beginning to do what we might
eventually want or need to do.

The domTpNum column refers back to the Transport table, where a NULL
value means to use the default transport for the address class in
question (those defaults shown in the table above), and route to the
nexthop as defined in DNS MX records. For any address, that default can
be overridden in the Address table, q.v.

Finally, domRcNum: this is a per-domain SMTPD restriction class which is
called in smtpd_recipient_restrictions. This gives the domain manager
the control to set general policies for spam control for that domain.
And as with transport_maps, per-recipient-address values can be set
which override the per-domain defaults. For example, some sites might
wish to relax spam controls on the postmaster address, and/or to tighten
them on heavily-spammed role accounts like info or webmaster.

4. Address

The Address table is a master list of all email addresses to which the
server makes any kind of reference. It must be listed in here if the
server is to take any kind of action specific to that address. If it's
not in here, but is in one of our domains, it is rejected as an unknown
address. (Exception: mydestination addresses can and possibly should be
listed, but are not required, unless you choose to use the example
local_recipient_maps query.) If it's not here and NOT in one of our
domains, it's considered an external address to look up in DNS.

An advantage to this master list is that we have one central spot for
our per-address smtpd restrictions and transport mappings. Also, this
table is all we need for relay_recipient_maps.

CREATE TABLE "Address" (addNum INTEGER PRIMARY KEY,
	addLocalpart TEXT NOT NULL, addDomNum INTEGER NOT NULL,
	active INTEGER DEFAULT 1, addTpNum INTEGER, addRcNum INTEGER,
	FOREIGN KEY(addDomNum) REFERENCES Domain(domNum),
	FOREIGN KEY(addTpNum) REFERENCES Transport(tpNum),
	FOREIGN KEY(addRcNum) REFERENCES RClass(rcNum),
	UNIQUE (addLocalpart, addDomNum));

The addLocalpart is the left-hand side of the "@" sign, "user" in
"user@domain". The addDomNum is a numeric pointer to the Domains table.
Most address lookups must join the Domains table to be able to retrieve
the domain name string.

addTpNum, as above, is a pointer to the Transport table, and this value,
if present, will override any default for the domain. The same is true
of addRcNum. You could present a Web interface to your users and allow
them to control their own spam policies ... if you enjoy pain ... ;)

Address.active is not looked up in all contexts, but it will disable
the addTransNum and addRcNum, if active=0. Also, if the domain is a
relay domain, Address.active disables that address in
relay_recipient_maps. Address lookups in other contexts, such as for
virtual_alias_maps, are not affected.

Another distinction of Address.active is that it is not a boolean, it is
a tri-state setting. Address.active=-1 disables a login account in
Dovecot, for a system account or virtual mailbox address, but the
ability for that address to receive mail is unaffected.

One drawback to having this table listing all addresses: we have to
enter local(8) addresses into this table if we plan to use the features
it offers. The choice I made was to enter them with $myhostname as
domain. (This requires, of course, that $myhostname is a domClass=1, or
mydestination, domain. This assumption and requirement persists through
the entire document.)

This can be patched into any system adduser/useradd script if desired.
Local delivery will still work without extra configuration, but we can't
use those local addresses as [virtual] alias targets unless they are in
the Address table.

(Again, an option we have is to use this as our local_recipient_maps.
For my own use I decided against that, but a commented main.cf example
and the associated query file are included.)

5. Alias

The Alias table is small and will remain mostly static. It is used as
part of alias_maps in Postfix, to map unqualified local addresses to
real recipients.

CREATE TABLE "Alias" (aliasKey INTEGER PRIMARY KEY,
	aliasNum INTEGER NOT NULL,
	active INTEGER DEFAULT 1, aliasTarget TEXT NOT NULL,
	FOREIGN KEY(aliasNum) REFERENCES Address(addNum));

In our schema, the aliasNum points back to a fully-qualified address in
the Address table. The lookup from Postfix is only the localpart, but
our query compensates with a hard-coded setting of $myhostname in place
of %d, the domain.

Here I made no effort to control the target. We can deliver to commands
using alias_maps. I could have used another column to specify an address
from the Address table, with an optional text field for commands, but
given that alias_maps are less used compared to virtual_alias_maps, it
did not seem important.

One thing worthy of note here is that aliases can be a one-to-many
mapping, and the way we have done that is by allowing multiple rows with
the same lookup key. The other tables thus far all enforce unique
values. In the sample data I have included one alias with three targets,
one of which is disabled using active=0.

As with local_recipient_maps, we gain little if anything from keeping
alias_maps in SQLite, but the point here is to show how it can be done.
From that point the postmaster can decide how to proceed.

The aliasKey column was added on just because it seems best to have a
unique identifier for any given row in any table. It is not used in any
queries, but it might be useful in database management.

6. VAlias

Postfix virtual alias maps are applied recursively to all recipients in
all mail classes. Every recipient for any mail is looked up in this
table, and if found, rewritten to the target address, which likewise is
looked up as well. This recursion continues until a non-result, or until
the result is the same as the lookup. (A built-in recursion limit
exists, but should never be reached in normal cases.)

CREATE TABLE VAlias (vaKey INTEGER PRIMARY KEY,
	vaNum INTEGER NOT NULL,
	active INTEGER DEFAULT 1, vaTargetNum INTEGER NOT NULL,
	vaTargetExt TEXT,
	FOREIGN KEY(vaNum) REFERENCES Address(addNum),
	FOREIGN KEY(vaTargetNum) REFERENCES Address(addNum));

The vaNum and vaTargetAddr columns each point to the Address table. Just
like the Alias table, the vaNum column need not be unique, and addresses
going to multiple targets are listed in one row per target. Also like
Alias, the vaKey is an unused column.

vaTargetExt is an optional text field, which, if present, is appended
to the target address localpart with the recipient_delimiter. In our
sample configuration and data, we use "-" as delimiter. (This value is
hardcoded in queries, so be aware of that if you change it.)

This way, we only have to list the base addresses in the Address table,
and we can direct a virtual alias to "user-extension@domain" as needed.
Users are of course free to use delimited addresses anywhere they like.

Postfix virtual alias maps have a dual use, in that all valid addresses
in virtual alias domains must be listed therein, and those must resolve
to addresses which are NOT in virtual alias domains. The schema has no
way to enforce this, however -- that's your job as the postmaster. :)

7. VMailbox

The virtual mailbox maps is a dual-use lookup table for Postfix and
Dovecot both. For some reason I have never completely understood,
virtual mailboxes seem to be the most popular way to run a mail server.
This despite the fact that local(8) delivery has many powerful features
for shell users. (Granted, a Windows-only user would not be able to use
those features, and many howtos appear to be oriented toward that
crowd.)

Somehow it is supposed to be more "secure" to put all your email eggs
into a single basket owned by a single UID/GID. But what could happen to
your mail if that UID or GID is compromised? A process running as that
user or group could access or destroy all the mail. I still prefer
having my mail under control of my Unix user account.

This example is a bit more secure in design than the virtual mailbox
howtos I have seen. We actually use our virtual_uid_maps to maintain a
bit of UID separation in our mailbox ownership. But for ease of use, we
can keep a single global GID for our virtual_mailbox_base's first path
element. (/home is virtual_mailbox_base, and /home/vmail, where mail is
delivered, is root:vmail mode 770.)

Yes, ease of use and security are always a tradeoff. We do have the
ability in the schema and the queries to maintain a separate UID and GID
per virtual recipient, and in the sample data, I have showed this in the
form of two virtual recipients being delivered to system users.

Using the shared GID, adding a domain is simply a sqlite3(1) operation;
any user mailboxes under that domain will be created automagically when
mail is delivered. If using other UID and GID, adding a domain or
mailbox would also require creation of any necessary subdirectory
structure under $virtual_mailbox_base, with correct ownership. And note,
because of the ownership and mode of /home/vmail above, this structure
could not be inside /home/vmail.

CREATE TABLE "VMailbox" (vmNum INTEGER PRIMARY KEY,
	active INTEGER DEFAULT 1, vmPasswd TEXT, vmHome TEXT,
	vmUid INTEGER, vmGid INTEGER,
	FOREIGN KEY(vmNum) REFERENCES Address(addNum));

Unlike the previous two tables, rows in VMailbox are unique, with the
vmNum (addNum from Addresses) being the only row with that key.

The vmPasswd column is only used by Dovecot, and it is text as such:
	{encoding}string
In the sample data, all the passwords are not encoded as indicated by
"{PLAIN}". For information about encodings, see the Dovecot wiki. It is
optional; a virtual mailbox which is not accessible via POP3/IMAP would
not need a vmPasswd. Likewise, absence of vmPasswd will disable POP3 and
IMAP access to its mail.

The final three columns are only to override built-in defaults, and are
therefore also optional. "vmHome" if specified gives a path relative to
virtual_mailbox_base to use as the virtual user's $HOME. Then mail
delivery goes to that $HOME/Mail/ maildir. The query constructs the
$HOME default as "vmail/%d/%u", where %d is the recipient domain, %u is
the localpart, both folded to lowercase.

A virtual user needs a $HOME directory. The imapd uses it for state and
other meta information pertaining to the mailbox. Postfix's virtual(8)
delivery agent does not care about nor use the $HOME except to deliver
the mail to a maildir thereunder. For more about this, again, see the
Dovecot wiki.

The default value for "vmUid" is set per domain in the Domain table as
the "domClass" column. And the default for "vmGid" is hardcoded as 800,
the "vmail" group.

In general, every address in a virtual mailbox domain should be listed
in the VMailbox table. But in practice, you will probably use virtual
aliases to redirect some of those addresses. This table only needs to
list the actual mailboxes; if a virtual mailbox domain address is listed
in virtual_alias_maps, the address is valid. However, it does not hurt
if it is listed in both VMailbox and Valias tables.

8. BScat

This is a check_sender_access lookup table mostly unrelated to the rest
of the example, but it illustrates other possible uses of our single
SQLite database.

CREATE TABLE "BScat" (bsKey INTEGER PRIMARY KEY,
	bsSender TEXT NOT NULL, bsPriority INTEGER,
	bsTarget TEXT NOT NULL, UNIQUE (bsSender, bsPriority));

A great deal of spam comes from "backscatter" sites, not directly
controlled by spammers, but unwittingly (or sometimes, willfully)
acting as relays and magnifiers for spammers. This happens when a site
accepts all mail for a domain they host without verifying the validity
of the recipient. (This is still the default behavior of the qmail MTA,
but not of its successor project, netqmail.)

We have entered three partial sender addresses in this table: "<>",
which in Postfix is the default $null_sender_lookup_key and the
RFC5321-mandated bounce sender address; and "postmaster" and
"mailer-daemon", which are used in some MTAs as bounce sender.

In general, "postmaster" and "mailer-daemon" are configured as aliases,
used for inbound receipt, but not as sender addresses. When someone
writes to one of my postmaster addresses, I reply as rob0@<domain>.
Therefore, in theory at least, it should be safe to assume that mail
from such sender addresses are automated bounces.

Some DNSBLs have undertaken to list abusive backscatter hosts. The two
we consult in our example are backscatter.spameatingmonkey.net
(http://spameatingmonkey.com/lists.html) and ips.backscatterer.org
(http://www.backscatterer.org/).

It is a sad state to have to do this, but some of my hosted addresses
have been heavily hit by backscatter. Please think carefully before
following this example, because on rare occasions a backscattering host
will have a legitimate bounce for one of your users, and this lookup
will cause loss of that mail. Likewise, it is possible that some
postmasters might use "postmaster@domain" as sender in non-spam mail.

This table should never need to be changed except possibly to add a
DNSBL or to change the DNSBLs in use. (The latter could be done by
changing the contents of the restriction class.)

9. Create the database

SQLite has no native means to control access, so we will use our
filesystem permissions.

First, we need the directories. I made /etc/postfix/private, mode 750,
owned by root:postfix. This way only root and the Postfix $mail_owner
account have access here.

But Dovecot also needs access, so I also made /etc/dovecot/private,
likewise mode 750, and owned by root:dovecot. (See NOTE below, however;
root:root mode 700 works as well.)

Next, we need the database. I have no shortcut to offer to help populate
your database. You must:
   a. Set up Transport if needed
   b. Identify and classify each of your domains, build Domain table
   c. List all valid addresses in each domain
      1) first in Address
      2) then in VAlias or VMailbox if appropriate
   d. Populate Alias, if you're using that

It might help to load the sample data, below, in a text editor, and use
a search-and-replace to change domain and user names to suit.

I created the database as /etc/postfix/private/mail.sqlite and then:
	# cd /etc/dovecot/private
	# ln -vi /etc/postfix/private/mail.sqlite

NOTE: Testing (suggested from the Dovecot documentation which said the
sql.conf.ext file should be root:root mode 600) showed that Dovecot is
fine with root:root mode 700 on /etc/dovecot/private. That also means
that the second directory and hardlink does not matter; Dovecot could
just as well use the copy in /etc/postfix/private. But I like it this
way; each daemon getting its own data from its own configuration
directory. It also gives a tiny bit of protection against a stray rm(1)
command; the file being in two places means it must be deleted twice
before it is gone.

(No, no, of course not. I have never accidentally deleted the wrong
file. Never! <cough>)


C. Sample data

1. Introduction

This is a small system with three system users who control various
domains. All characters appearing in this work are fictitious. Any
resemblance to real persons, living or dead, is purely coincidental, but
the reader is welcome to find humor where it may be, whether or not the
author intended.

User "ah" is UID 1000. He controls (as registrant) a relay domain,
example.net, and a virtual mailbox domain for his family,
family.example.net. User "rw" is 1001, and he controls a relay domain,
relay.example.org, which is split between two backends. His example.org
is a local domain, and the machine's fully qualified domain name, also
local, is myhostname.example.org. His family are also system users, and
rw.example.org is a virtual alias domain for them. (All system users
have primary GID 100.)

User "rob0" is UID 1002. Okay, that's a real person (FSVO real): it's
me. I control example.com as a virtual alias domain. relay.example.com
is also mine, a relay domain, and vfd.example.com is my virtual mailbox
domain. Finally, whn.example.com is my wife's virtual mailbox domain
(nominally under my control, but you know how that goes!) and it has ...
ugh! ... a wildcard or "catchall" address!

NB: I most strongly DO NOT recommend the use of catchalls. They WILL be
abused by spammers. Probably anything you would want to accomplish using
a catchall could be done with a recipient delimiter, which gives an
endless namespace under each legitimate address. Alias any commonly
misspelled addresses to the proper recipient. And it really IS best to
reject the not-so-common misspellings.

2. The data

See separate file, "mail.sql". It is plain text with mostly reasonable
margins, and comments have been added in SQL syntax. You can and should
read it.

You can also load it into sqlite3:
	# sqlite3 -init mail.sql /etc/postfix/private/mail.sqlite
That will create your database file "mail.sqlite" containing the sample
data.