G. Appendix: HOW DO I ... ... add a new local address? This is outside the scope of this document; you use your OS-provided tools to add a user. You should, however, add that address to the Address table, to be able to use it as a virtual alias target, or to invoke a special smtpd restriction or restriction class for it. If you are using the optional local_recipient_maps query, you MUST add it before it can receive mail from outside. sqlite> INSERT INTO Address (localpart, domain) VALUES ('new-system-user', 0); That assigns it a random Address.id. The sample data uses (-1 * $UID) for each system user's Address.id; to do this, also add the id: sqlite> INSERT INTO Address (id, localpart, domain) VALUES (-1042, 'new-system-user', 0); ... add a new relay address? This is simple too, just add it to the Address table as such: sqlite> INSERT INTO Address (localpart, domain) VALUES ('new-relay-addr', (SELECT id FROM Domain WHERE name IS 'relay.example')); ... add a new alias address, either local or virtual? First, add the new alias to Address. For virtual: sqlite> INSERT INTO Address (localpart, domain) VALUES ('new-alias', (SELECT id FROM Domain WHERE name IS 'alias.example')); And for local: sqlite> INSERT INTO Address (localpart, domain) VALUES ('new-alias', 0); Then set up the mapping. This looks worse than it really is. For virtual: sqlite> INSERT INTO Alias (address, target) VALUES (SELECT id FROM Address JOIN Domain on (Address.domain=Domain.id) WHERE localpart IS 'new-alias' AND name IS 'alias.example'),(SELECT id FROM Address JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'your-target-user' AND name IS 'your-target-domain'); The result of this is simply, "integer, integer". The first subquery is to pull the new alias from Address, and the second is to pull the target address from Address. Repeat for multiple targets. And for local: sqlite> INSERT INTO Alias (address, target) VALUES ((SELECT id FROM Address WHERE localpart IS 'new-alias' AND domain=0), (SELECT id FROM Address JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'your-target-user' AND name IS 'your-target-domain'); If you know the Address.id values you want, you can do this with a much simpler sqlite command, for example, to map 42 to 16: sqlite> INSERT INTO Alias (address, target) VALUES (42, 16); ... add a new virtual mailbox address? First, add the new address to Address: sqlite> INSERT INTO Address (localpart, domain) VALUES ('new-vmail', (SELECT id FROM Domain WHERE name IS 'vmail.example')); And then add it to VMailbox: sqlite> INSERT INTO VMailbox (id, password) VALUES ((SELECT id FROM Address JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'new-vmail-user' AND name IS 'vmail.example'), '{PLAIN}whatAcoolPassword'); ... disable any address? General note on disabling mail addresses of all classes: this can be managed with other tools as well. Consider the Postfix relocated(5) feature, as well as access(5). Any given address can be blocked from the outside by means of a simple sqlite command: sqlite> UPDATE Address JOIN Domain ON (Address.domain=Domain.id) SET rclass=0 WHERE localpart IS 'victim' AND name IS 'domain.example'; This might not be ideal, because all a client sees when being rejected is the minimal message, "Recipient address rejected: access denied". One or more special restriction classes can be created to give a more informative message, as desired, and then the class number can be used in place of "0". See "add a restriction class" below. Another feature we have here is the "active" column in several tables. Read on to see how to apply that per address class. ... disable an existing local address? Again, use your OS tools to manage system accounts, but if the address is in your database, it must also be removed from here. This can get sticky. First, find out the Address.id value for the victim: sqlite> SELECT Address.id FROM Address WHERE localpart IS 'victim' AND domain=0; We shouldn't try to remove that yet until we check for other uses of it. Check the Alias and Alias tables, assuming the above query returned "42": sqlite> SELECT * FROM Alias WHERE address=42 OR target=42; sqlite> SELECT * FROM Alias WHERE address=42; We can remove those records: sqlite> DELETE FROM Alias WHERE address=42 OR target=42; sqlite> DELETE FROM Alias WHERE address=42; And finally, we can remove it from Address: sqlite> DELETE FROM Address WHERE id=42; Perhaps it would be better to simply set "active" to 0 for either/both alias tables, and leave it alone in Address: sqlite> UPDATE Alias SET active=0 WHERE address=42 OR target=42; sqlite> UPDATE Alias SET active=0 WHERE address=42; ... disable an existing relay address? Assuming no virtual aliasing applies, simply remove it from Address: sqlite> DELETE FROM Address JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'relay-killed' AND name IS 'relay.example'; Or, as above, use the "active" column: sqlite> UPDATE Address JOIN Domain ON (Address.domain=Domain.id) SET Address.active=0 WHERE localpart IS 'relay-killed' AND name IS 'relay.example'; Either of these will cause a client to get "Recipient address rejected: User unknown in relay recipient table" as the rejection message. See above (and "add a restriction class" below) for ways to use other, more informative, messages. ... disable an existing virtual alias address? See above on disabling an existing local user, the parts referring to Address and Alias tables. ... disable an existing virtual mailbox address? In this case you'd usually want to use the "active" column in the VMailbox table. sqlite> UPDATE VMailbox JOIN Address ON (VMailbox.id=Address.id) JOIN Domain ON (Address.domain=Domain.id) SET VMailbox.active=0 WHERE localpart IS 'vmail-killed' AND name IS 'vmail.example'; But it can be deleted if you prefer: sqlite> DELETE FROM VMailbox JOIN Address ON (VMailbox.id=Address.id) JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'vmail-killed' AND name IS 'vmail.example'; sqlite> DELETE FROM Address JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'vmail-killed' AND name IS 'vmail.example'; You may also wish to remove /home/vmail/vmail.example/vmail-killed/ and its contents and subdirectories. ... add a new local domain? sqlite> INSERT INTO Domain (name, class) VALUES ('new.local.example', 1); That's it. If you need a special restriction class, include the necessary column rclass column and value as needed. Also, add the owner column and value if it is not root. For any address in the new local domain that needs a non-default restriction class or transport, you must add that address to Address with the settings you need. For example: sqlite> INSERT INTO Address (localpart, domain, transport, rclass) VALUES ('new-user', (SELECT id FROM Domain WHERE name IS 'new.local.example'), 7, 10); This will route new-user@new.local.example via Transport.id=7 and enable "aggressive" ("/usr/sbin/postconf RC10") spam restrictions. ... add a relay domain? Generally, a new transport might be needed. That must exist, before it can be referenced in the Domain table. We'll also create a nexthop domain here. sqlite> INSERT INTO Domain(name,class) VALUES('mx.relay.example',0) sqlite> INSERT INTO Transport (nexthop) VALUES ( (SELECT id FROM Domain WHERE name IS 'mx.relay.example')); sqlite> INSERT INTO Domain (name, transport, class, rclass) VALUES ('new.relay.example', (SELECT id FROM Transport WHERE nexthop=(SELECT id FROM Domain WHERE name IS 'mx.relay.example')), 2, 20); You will want to add the postmaster and abuse aliases for new.relay.example to the Address table: sqlite> INSERT INTO Address (localpart, domain) VALUES ('postmaster', (SELECT id FROM Domain WHERE name IS 'new.relay.example'); sqlite> INSERT INTO Address (localpart, domain) VALUES ('abuse', (SELECT id FROM Domain WHERE name IS 'new.relay.example'); And continue adding other addresses as needed. As with a new local domain, add extra columns and values as needed. ... add a virtual alias domain? sqlite> INSERT INTO Domain (name, class, rclass) VALUES ('new.alias.example', 3, 20); Again, similarly, we add the postmaster and abuse aliases for new.alias.example to the Address table: sqlite> INSERT INTO Address (localpart, domain) VALUES ('postmaster',(SELECT id FROM Domain WHERE name IS 'new.alias.example'); sqlite> INSERT INTO Address (localpart, domain) VALUES ('abuse', (SELECT id FROM Domain WHERE name IS 'new.alias.example'); And continue adding other addresses as needed. Any addresses you do not already list, but need to use as alias targets, must also be added to Address. Then, set up the mappings: sqlite> INSERT INTO Alias (address, target) VALUES (SELECT id FROM Address JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'postmaster' AND name IS 'new.alias.example'),(SELECT id FROM Address JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'your-target-user' AND name IS 'your-target-domain'); Ha, that is crying for automation. :) But it is not that bad in the sqlite3 client, where you can hit the up arrow and edit the previous command in your history. (Offer void where taxed or prohibited, or if your sqlite3 lacks readline support.) ... add a virtual mailbox domain? sqlite> INSERT INTO Domain (name, class, owner, rclass) VALUES ('new.vmail.example', 842, 1007, 20); Again, similarly, we add the postmaster and abuse aliases for new.vmail.example to the Address table: sqlite> INSERT INTO Address (localpart, domain) VALUES ('postmaster', (SELECT id FROM Domain WHERE name IS 'new.vmail.example'); sqlite> INSERT INTO Address (localpart, domain) VALUES ('abuse', (SELECT id FROM Domain WHERE name IS 'new.vmail.example'); And as before, continue adding other addresses as needed. You'll probably want postmaster and abuse to be aliased, which means adding those only as virtual aliases in Alias as above. But presumably you are going to have some virtual mailboxes in this domain, so we add those to VMailbox: sqlite> INSERT INTO VMailbox (id, password) VALUES ((SELECT id FROM Address JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'new-vmail-user' AND name IS 'new.vmail.example'), '{PLAIN}whatAcoolPassword'); This creates a maildir under /home/vmail/new.vmail.example/new-vmail-user/Mail/ when mail is received for new-vmail-user@new.vmail.example. It will be owned by the system UID 842 and GID 800. Using the credentials, username: "new-vmail-user@new.vmail.example" and password: "whatAcoolPassword", an IMAP client can access this mail through Dovecot. ... deactivate any hosted domain? sqlite> UPDATE Domain set active=0 WHERE name IS 'killed.example'; ... delete a local or relay or virtual alias domain? See above about active=0. If you're going to delete it from the database, you must also delete any Address rows which refer to the row in Domain, and any Alias rows which refer (as alias or target) to the rows in Address. ... delete a virtual mailbox domain? Again, see above about active=0, and about Address and Alias. In addition, you will have the VMailbox table to check. Setting Domain.active=0 blocks receipt of new mail, but it does not disable the users' credentials in Dovecot. Here's a way to disable them all: sqlite> UPDATE VMailbox JOIN Address on (VMailbox.id=Address.id) JOIN Domain on (Address.domain=Domain.id) SET VMailbox.active=0 WHERE name IS 'vmail.example'; That's very similar to, but actually a bit simpler than, the command for disabling a single virtual mailbox address. The difference is that we don't check the localpart. Again, you might want to consider the fate of /home/vmail/vmail.example/ and all its subdirectories. ... add a restriction class? Before we begin this "simple" procedure, it is assumed that the reader knows what s/he wants to do, and why. It is beyond the scope of this document to explain smtpd restrictions and when/why/how to use them. First, add any component classes you will need to main.cf: z-yourClass1 = z-yourClass2 = [ ... ] This can include any existing class definition or any valid smtpd restriction. Next, add the composite class definition. In this example we use 35. Any unused integer, 0-99, will work; note that the access-rcpt.query has set a design limit of two-digit numbers. # RC35-yourclass I give each one a descriptive name in the comments. # Previous versions of this document had a database table mapping those # names to numbers, but that was removed. RC35 = z-yourClass1, yourClass2[, ... ] Again, this can include any existing class definition or any valid smtpd restriction. (Postfix knows no distinction between component and composite classes; that distinction was made for the purpose of this example implementation.) Then append the newly-defined classes, both component and composite, to the smtpd_restriction_classes definition: smtpd_restriction_classes = ... z-yourClass1, z-yourClass2[, ... ] RC35 Save the edited main.cf file. At this point a "postfix reload" is optional. You're not going to lose any mail if you skip this step; at worst, you will delay a few mails for a few minutes, if/when the database returns an invalid restriction name. Finally, change any domains or addresses as desired: sqlite> UPDATE Domain SET rclass=35 WHERE name IS 'changed.example'; sqlite> UPDATE Address JOIN Domain ON Address.domain=Domain.id SET rclass=35 WHERE localpart IS 'changed' AND name IS 'domain.example'; ... do a catchall address in a relay domain? Again, I certainly DO NOT recommend this, but it can be done in this schema. In a relay domain all it needs is an empty string as Address.localpart: sqlite> INSERT INTO Address (localpart, domain) VALUES ('', (SELECT id FROM Domain WHERE name IS 'relay.catchall.example')); (An empty brain and tolerance for spam attacks are not strictly required, but they will definitely help.) Read on about spam control and more dire warnings. ... do a catchall address in any other class? For classes other than relay (Domain.class=2) the only way to do a catchall is with the wildcard in virtual_alias_maps. This example assumes that your-target@your.target.example is already a valid address in your Address table, and all it needs is an empty string as Address.localpart: sqlite> INSERT INTO Address (localpart, domain) VALUES ('', (SELECT id FROM Domain WHERE name IS 'catchall.example')); -- note, the following boils down to -- "INSERT ... (integer, integer);" sqlite> INSERT INTO Alias (address, target) VALUES ((SELECT id FROM Address JOIN Domain on (Address.domain=Domain.id) WHERE localpart IS '' AND name IS 'catchall.example'), (SELECT id FROM Address JOIN Domain ON (Address.domain=Domain.id) WHERE localpart IS 'your-target' AND name IS 'your.target.example')); Really, DO NOT do this. Great pain awaits you if you disregard this warning. Especially great pain awaits the catchall recipient when a spammer uses @catchall.example addresses as senders in a spam run, or when a spammer does a dictionary attack, trying every.possible@catchall.example address. It WILL happen. Most people who think they want a catchall are better served by some combination of adding more aliases and/or using recipient_delimiter in addresses. Each address contains an almost unlimited namespace with recipient_delimiter. Bug: the access-rcpt.query does not work for addresses in a catchall domain which are not specifically listed. If using a catchall domain, append this to your smtpd_recipient_restrictions: check_recipient_access $query/access-rcptDomain.query Otherwise there is nothing done for catchall addresses ... where you need it the most! ... temporarily disable an account from logging in? Simply change Address.active for the account to -1. This applies for either local(8) or virtual(8) accounts. For virtual: sqlite> UPDATE Address JOIN Domain ON (Address.domain=Domain.id) SET Address.active=-1 WHERE localpart IS 'compromised' AND name IS 'vmbox.domain.example'; For system accounts, we use the Address.domain=0 and do not need to join the Domain table: sqlite> UPDATE Address SET active=-1 WHERE localpart IS 'compromised' AND domain=0; H. Epilogue: Shooting yourself in the foot You can indeed shoot yourself in the foot with this! The database schema includes numerous foreign key constraints which help to limit the GIGO when you put "garbage in". There are no safeguards on removing essential data, however. You can have dozens of records which depend on Domain.id=14 or on Address.id=42, and yet, sqlite3 is not going to stop you if you try to delete either row. If you do it, you will certainly get "garbage out". Don't forget, we hardcoded a recipient_delimiter setting in numerous queries, and we hardcoded a default virtual mailbox GID in the virtual_gid_maps query. A lot can go wrong if you are not careful and randomly change important settings. You're tired of reading it by now, but it still bears repeating: this is not about handing you a ready-made mail server with everything but the kitchen sink. This is about teaching you to fish, showing you what kind of fish you can catch with a sqlite database backend for a Postfix/Dovecot mail server. I hope it was interesting and useful. I. Bibliography 1. Postfix Note: all the following Postfix documents are available in your own $html_directory. Quite often it is best to refer to your own copy, as you will thereby avoid possible confusion with features which were added later. (There are also text versions in $readme_directory; use a pager like less(1), or mc(1)'s file viewer, for those. I recommend the HTML versions because of the extensive hyperlinking, however.) a. Features we covered: Address classes: local, relay, virtual alias, virtual mailbox: http://www.postfix.org/ADDRESS_CLASS_README.html Postfix SQLite Howto and manual: http://www.postfix.org/SQLITE_README.html http://www.postfix.org/sqlite_table.5.html Postfix Backscatter Howto: http://www.postfix.org/BACKSCATTER_README.html (This is a different approach to the backscatter problem.) b. Features we touched on but did not explore in detail: Basic configuration: http://www.postfix.org/BASIC_CONFIGURATION_README.html SASL Authentication: http://www.postfix.org/SASL_README.html TLS Encryption: http://www.postfix.org/TLS_README.html Virtual domain hosting: http://www.postfix.org/VIRTUAL_README.html SMTP connection triage server: http://www.postfix.org/POSTSCREEN_README.html Per-client/user/address access control: http://www.postfix.org/RESTRICTION_CLASS_README.html Postfix SMTP Access Policy Delegation: http://www.postfix.org/SMTPD_POLICY_README.html c. See also: Postfix Documentation: http://www.postfix.org/documentation.html 3. SQLite http://www.sqlite.org/ http://www.sqlite.org/docs.html http://www.sqlite.org/lang.html 4. Dovecot http://dovecot.org/ http://wiki2.dovecot.org/ http://wiki2.dovecot.org/QuickConfiguration http://wiki2.dovecot.org/AuthDatabase/SQL http://wiki2.dovecot.org/HowTo/PostfixAndDovecotSASL 5. Other resources which might be of interest PostgreSQL-based project which began as a fork of this one https://github.com/SpiceMan/Grelfiqs This is by Marcel "SpiceMan" Montes, who is a much better DBA than me. Over time I'll probably copy what he has done. SQLZoo -- SQL reference and interactive tutorials http://sqlzoo.net/ DNSBL services (author's preferred) http://www.spamhaus.org/ http://barracudacentral.org/rbl http://spameatingmonkey.com/ http://ahbl.org/ http://njabl.org/ and also: http://www.sorbs.net/ http://www.spamcop.net/ http://www.trblspam.com/ DNSWL (whitelisting) services http://dnswl.org/ http://www.spamhauswhitelist.com/ Anti-spam discussion list http://new-spam-l.com/admin/faq.html http://spammers.dontlike.us/