I. Schema A. Introduction A well-thought schema is the most important thing here, and it should be designed with a solid understanding of the RDBMS or data platform (not sure if SQLite qualifies as a true RDBMS, and definitely LDAP is not, but both can be used as a data backend for a mail server running Postfix and Dovecot.) But more important is a solid understanding of the software for which data is being provided. As mentioned elsewhere, this is approximately the sixth major rewrite of my schema. Each time I have built on lessons from the previous rewrite. This time I hope it's mature. The schema and queries can easily be adapted to any other SQL, which in the Postfix and Dovecot worlds means either MySQL or PostgreSQL. There too, you would need to know your RDBMS. In some cases, change the data type for a column and set appropriate limits for fields. SQLite doesn't enforce limits, although you can set them. SQLite doesn't even enforce data types; these column definitions set affinity, but they'll accept anything you might wish to shove down its throat. (It would be an interesting subproject to see what kinds of "garbage out" we can get for various types and values of "garbage in". Maybe eventually I will try that. Now the focus is on making it work and writing about it.) And while the schema itself is not portable to LDAP, perhaps the logic in it can help an LDAP implementor. Postfix and Dovecot need the same data out, regardless of origin. Another thing of interest to other SQL porters is at the end of each table's discussion: I have mentioned which daemon consults that table. You might use that information for proper access control. Do note, however, that the majority of the database is going to be read by both Postfix and Dovecot. Note, the following is written more as a narrative than as a reference, but it would probably function as a reference too. B. The Tables 1. Transport The first table is so positioned because it is used as a foreign key constraints in two other tables which follow, Domain and Address. -- CREATE TABLE "Transport" (id INTEGER PRIMARY KEY, active INTEGER DEFAULT 1, transport TEXT, nexthop INTEGER, mx INTEGER DEFAULT 1, port INTEGER, UNIQUE (transport,nexthop,mx,port)); -- Each table has an integer primary key column named "id". The Transport.id is used in two other tables as a foreign key for a "Table.transport" column. This is the standard for the schema: "Table.othertable" columns refer back to "Othertable.id". Most tables also have a "Table.active" column, an integer, which in most (but not all) cases we use as a boolean value. Here it is a boolean, where 0 means disabled or 1 means enabled. (In actual practice any nonzero integer means the same thing: the queries check for "Transport.active!=0". But I stick with 1 for consistency.) Note: this is a SQLite limitation -- there is no boolean data type. If you plan to port this schema to another RDBMS, you might choose to make this a boolean. Transport.transport corresponds to the "transport" side of the Postfix concept of "transport:nexthop" from the transport(5) manual. These refer to services as defined in the master(5) configuration file, master.cf. This column can be null, in which case Postfix will use whatever transport is the default for the nexthop or destination domain. Transport.nexthop of course corresponds to the "nexthop" side. It will be a pointer to Domain.id, but without a foreign key constraint, because the Domain table has yet to be created at this point. Note, this is an exception to the standard stated above, but the choice of "nexthop" makes sense as it is the exact Postfix term. So I might more precisely say that the standard only applies when using foreign key constraints. Also, nexthop could conceivably be something other than a DNS hostname, depending on the transport in question, but in practice it usually is a hostname. "Domain" is not the right term to use, but it is the best name I can think of for the Domain table, and that is definitely the best place for it in the database. By default, Postfix plans to do a DNS MX lookup on the nexthop value. The Transport.mx column is another integer used as boolean which controls whether or not to look up MX. 0 means no, 1 means yes. (Again, any nonzero integer would work, but I just use 1.) The purpose of transport_maps in Postfix is to override whatever the default transport and/or nexthop would be for any given mail. Often when changing this, a nonstandard TCP service port might be required as well. All Internet mail exchange takes place on the receiver's port 25. If a value is set for Transport.port, it will override whatever the service default port is for that particular transport:nexthop combination. (Note: not all services Postfix might use default to port 25. LMTP, for example, uses port 24 by default.) The Transport table enforces uniqueness for any combination of transport, nexthop, mx, and port. There was no particular structural reason why we need that constraint, but it's probably a good idea to limit accumulation of junk in the database. Transport is only consulted by Postfix. 2. Domain The Domain table is the heart of the database. Almost every other table refers to (or is referred to by) the Domain table. This table holds a list of all domains (DNS hostnames, more precisely, although as per above in the Transport discussion, these names might not all be DNS hostnames) to which the server makes any kind of reference. This includes all hosted mail domains of all classes, as well as external hostnames used in transport(5) mapping. (I have no specific example of a nexthop value which is not a DNS hostname.) -- CREATE TABLE "Domain" (id INTEGER PRIMARY KEY, name TEXT, active INTEGER DEFAULT 1, class INTEGER DEFAULT 0, owner INTEGER DEFAULT 0, transport INTEGER, rclass INTEGER DEFAULT 30, UNIQUE (name), FOREIGN KEY(transport) REFERENCES Transport(id); Again we have an "id". This integer column is a foreign key constraint in the Address table below. Additionally, the value in Transport.nexthop must refer to a Domain.id value. Domain.name is the text string, a DNS hostname. Queries of most other tables join on Domain.id to look up the Domain.name value. This revision removed the NOT NULL constraint from Domain.name; I inserted a special record Domain.id=0 with Domain.name=NULL which I thought would thus maintain a defacto NOT NULL constraint for other rows. Not so: SQLite considers NULL values unique, so that any number of NULL rows can coexist in a column with a UNIQUE constraint. -- INSERT INTO "Domain" VALUES(0,NULL,NULL,NULL,NULL,NULL,NULL); -- Domain.active is as most other "active" columns, an integer used as a boolean. The Domain.class integer value 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 Domain.class value is used as 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 values but with caveats as will be covered later.) Domain.owner refers to the system UID of the person who owns or controls the domain. For most external names, that would be 0, root. At this point nothing is done with the owner value, but there are many ways in which it could be useful in the future. Domain.transport is an optional pointer to Transport.id, which would override the transport(5) default for that domain, as shown in the Domain.class table above. Similarly, Domain.rclass sets a default restriction class for recipient addresses in that Domain.name. I did not use a NOT NULL constraint on Domain.rclass, because any bad data here is just as wrong as a NULL. It would mean no additional spam protection is provided after postscreen(8), if that is enabled (as it is in the sample main.cf.) (Previous versions of this schema had a RClass table with foreign key constraints, but since only main.cf can define the actual restriction classes, this table did not matter.) The Domain.rclass default is 30, which is "conservative" in the sample main.cf. This class emphasizes safety above all; probably an ideal default. Domain is consulted directly by Postfix, for domain lists and defaults, and it is consulted by both Postfix and Dovecot in a join on Address.domain = Domain.id, to be able to look up the Domain.name strings in email addresses. 3. Address The Address table is a master list of all addresses to which the server makes any kind of reference. It must include all valid addresses in all relay and virtual alias/mailbox domains. It must include all local addresses if the optional local_recipient_maps lookup is used, and even if not, any local addresses which are aliases, or would be used as alias targets, must be listed. Inside this master list, addresses can be have routing or spam control policies changed. -- CREATE TABLE "Address" (id INTEGER PRIMARY KEY, localpart TEXT NOT NULL, domain INTEGER NOT NULL, active INTEGER DEFAULT 1, transport INTEGER, rclass INTEGER, FOREIGN KEY(domain) REFERENCES Domain(id), FOREIGN KEY(transport) REFERENCES Transport(id), UNIQUE (localpart, domain)); -- We will insert a special record Address.id=0 with Address.domain=0 -- to differentiate aliases(5) commands, paths or includes from -- address targets. The localpart is a NUL character, ASCII 0x00, -- guaranteed not to be a valid email localpart. INSERT INTO "Address" VALUES(0,X'00',0,NULL,NULL,NULL); -- Address.id is used in the next two tables as a foreign key constraint. It constitutes the numeric representation of any listed email address. Address.localpart is the text string which is the "user" part in a "user@domain" email address. Address.domain is a pointer to Domain.id, and thus is the numeric numeric representation of any listed email domain. Taken together, both domain and localpart must be unique in the database, so there cannot be any more than one row for any address. Address.active is not just a boolean, it is a tri-state field. In addition to 1 (fully enabled) and 0 (fully disabled), it has a third state: -1, delivery enabled but IMAP login (which includes SMTP AUTH) disabled. This third state is only relevant for system user addresses and virtual mailbox addresses. Address.transport and Address.rclass work exactly as they did above in the Domain table, but change the routing or spam restrictions for only that address. Both are optional, and transport has a foreign key constraint to Transport.id. (Previous versions of this schema had a RClass table with foreign key constraints, but since only main.cf can define the actual restriction classes, this table did not matter.) Local ($mydestination) addresses must be entered into the database with Address.domain=0, using the special NULL Domain record. One might consider a patch to the OS useradd/adduser script to automate this. Or, conversely, consider it a feature that adding an OS user does not add an email address. Address is queried directly by both Postfix and Dovecot in numerous contexts. 4. Alias The Alias table does double duty for Postfix as alias_maps and virtual_alias_maps. -- CREATE TABLE "Alias" (id INTEGER PRIMARY KEY, address INTEGER NOT NULL, active INTEGER DEFAULT 1, target INTEGER NOT NULL, extension TEXT, FOREIGN KEY(address) REFERENCES Address(id) FOREIGN KEY(target) REFERENCES Address(id) UNIQUE(address, target, extension)); -- Alias.id is not used anywhere; it is merely a unique integer identifier for any given address/target/extension combination. It seems like a good idea to have such a column. Alias.address is a pointer to Address.id, which indicates the address to be aliased. Alias.active is another boolean integer column. As in other tables, active=0 disables the mapping. Alias.target is another pointer to Address.id, which indicates [one of] the address[es] to which the Alias.address is to be redirected. There can be any number of targets per Alias.address, listed in one row per Alias.target. (This is another exception to the general rule about foreign-key-constrained columns' naming, but it was obviously necessary here, as we already have an Alias.address column.) Alias.extension is a text field for the address extension, where (as in our sample configuration) a recipient_delimiter is in use. But if Alias.target=0, that tells us that the Alias.extension field contains a /file/name, or a |command, or an :include:/file/name. This is only valid for an alias_maps entry. As with traditional hash: alias_maps, the first character distinguishes the actual function to be used: append to /file/name, pipe to |command, or :include the contents of /file/name as additional alias targets. Note: if the command has options or other arguments, they must all be enclosed in double quotes: |"command options arguments" See the Postfix aliases(5) manual for complete documentation of the allowable syntax and what it means. The differentiation between local(8) aliases(5) and virtual(5) lies in the Address table. Joining Address on Alias.address = Address.id, if Address.domain=0, that indicates alias_maps (for local domains), and any other value means it is a virtual_alias_maps entry. This method is nice and simple, but it costs us the ability to do a virtual alias lookup of an unqualified localpart address. We do have that ability in alias_maps, so I see no need to implement it for virtual aliases. It would be at the expense of a more complicated means of distinguishing aliases from virtual aliases, and gain nothing. Alias is only consulted by Postfix. 5. VMailbox The VMailbox table is the listing of virtual mailboxes (not system accounts.) It stores information about their mapping to system UIDs and GIDs, their home directories, and their passwords. -- CREATE TABLE "VMailbox" (id INTEGER PRIMARY KEY, active INTEGER DEFAULT 1, uid INTEGER, gid INTEGER, home TEXT, password TEXT, FOREIGN KEY(id) REFERENCES Address(id)); -- VMailbox.id is yet another pointer to Address.id. Unlike in the Alias table, here it is also the primary key, so there can only be one mailbox entry per email address. (And to receive mail from the outside, that address must be in a virtual mailbox domain.) VMailbox.active is yet another boolean integer, where 0 disables the address and 1 enables it, for purposes of both Postfix and Dovecot. VMailbox.home is an optional text field, which, if supplied, is the path of the home directory relative to Postfix's virtual_mailbox_base, which in this example is /home. If not supplied, a default is used: vmail/domain/user for "user@domain". VMailbox.uid and VMailbox.gid are optional integer fields for the system user and group IDs respectively for the ownership of the mailbox, and likewise for the UID/GID to use when reading it. If not provided, the default for VMailbox.uid is the Domain.class value, and the default for VMailbox.gid is 800, the "vmail" group on my system. VMailbox.password is the actual password string or hash for Dovecot's use only. The encodings are done as such: password='{encoding}string' The default password scheme encoding method in our configuration is {MD5}, which means that "password='string'" is the same as "password='{MD5}string'". But the sample data only has "{PLAIN}", unencrypted actual passwords. VMailbox is consulted by Postfix to know which addresses to accept and where to deliver them; and also by Dovecot, both to check virtual users' login credentials and to find where to read their mail. 6. BScat The BScat table is distinct from the rest of the example, but is included as evidence that SQLite can replace most lookups that a mail server might need to do. -- CREATE TABLE "BScat" (id INTEGER PRIMARY KEY, sender TEXT NOT NULL, priority INTEGER NOT NULL, target TEXT NOT NULL, UNIQUE (sender, priority)); -- BScat.id is another unused integer primary key, just like Alias.id above, and it is here for the same reason, "just because". BScat.sender is a sender address or address localpart pattern commonly found in backscatter. BScat.target is one of two "component restriction classes" as documented in the sample main.cf. BScat.priority is an integer to order these restriction classes. Other restrictions and other sender patterns could be added, but this illustrates what I use to limit backscatter. Note: this comes at the cost of a small risk of lost mail. It is always possible that a non- backscatter bounce might come from a known backscatter host, or that the listed sender patterns might be used in legitimate, non-backscatter, mail. BScat is only consulted by Postfix. C. Sample Data See mail.sql, SQL with comments. D. Set up the database SQLite has no native means of access control. Only Unix filesystem attributes control access. If your user can read a SQLite database file, you can query it; if you can write it, you can INSERT, UPDATE, DELETE, or CREATE. We have a situation here in which two distinct network-facing daemons running as different users with no group in common both need access to data that to some extent needs to be kept secure. (Strictly speaking, only the VMailbox table contains sensitive information, that being the passwords of virtual users. But those require the username to be useful, thus expanding sensitivity to both the Address and Domain tables. Also, the paranoid among us might well consider the Address and Domain tables as sensitive data in their own right. Those would give an attacker a complete list of all domains served and all valid addresses therein.) My solution was to create the database as world-readable, mode 644, but to store it in two secured directories. The database is created in one of them, and hardlinked into the other. The two directories are /etc/X/private, owned root:X, mode 750: drwxr-x--- 2 root dovecot 37 2012-01-13 16:31 /etc/dovecot/private drwxr-x--- 2 root postfix 37 2012-01-13 16:32 /etc/postfix/private /etc/dovecot/private: total 28 -rw-r--r-- 4 root root 526 2012-01-13 00:04 README -rw-r--r-- 2 root root 21504 2012-02-14 09:01 mail.sqlite /etc/postfix/private: total 28 -rw-r--r-- 4 root root 526 2012-01-13 00:04 README -rw-r--r-- 2 root root 21504 2012-02-14 09:01 mail.sqlite The README file contains this: This file is hardlinked at each of these locations: /etc/dovecot/private/README /etc/dovecot/README.private /etc/postfix/private/README /etc/postfix/README.private The "private" subdirectory under each of /etc/dovecot and /etc/postfix has been secured to protect access to the SQLite mail database. Each private directory is mode 750, and owned by root:X, where X is dovecot and postfix respectively. The database itself is world readable, but non-root users other than dovecot and postfix will be unable to get to it. (end of README) I'm not much into tossing a fish, more into teaching how to catch a fish (metaphorically speaking, I am not good at catching actual fish!) But here I'll make an exception and show the commands to create, populate, and place the database. I used GNU coreutils in bash. There are other tools and methods to accomplish the same thing. root@chestnut:~# for X in dovecot postfix > do mkdir -pm750 /etc/$X/private ; chgrp $X $_ ; done root@chestnut:~# sqlite3 -init mail.sql /etc/postfix/private/mail.sqlite -- Loading resources from mail.sql SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .q root@chestnut:~# cd /etc/dovecot/private/ root@chestnut:/etc/dovecot/private# ln -vi ../../postfix/private/mail.sqlite `./mail.sqlite' => `../../postfix/private/mail.sqlite' The README/README.private file is optional, but a good idea. Note: in my own non-PAM setup, Dovecot reads the database as root, so the hardlinking in two directories would not matter. Regardless, it is best to do it this way anyway, and it's essential if the "service auth" in dovecot/conf.d/10-master.conf is using $default_internal_user.