F. 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 might, however, need to 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 do add it, use your $myhostname value as the domain:

sqlite> INSERT INTO Address (addLocalpart, addDomNum) VALUES
	('new-system-user', (SELECT domNum FROM Domain WHERE domName IS
	'myhostname.example.com'));

... add a new relay address?

This is simple too, just add it to the Address table as such:

sqlite> INSERT INTO Address (addLocalpart, addDomNum) VALUES
	('new-relay-addr', (SELECT domNum FROM Domain WHERE domName
	IS 'relay.example'));

... add a new virtual alias address?

First, add the new alias to Address:

sqlite> INSERT INTO Address (addLocalpart, addDomNum) VALUES
	('new-alias', (SELECT domNum FROM Domain WHERE domName IS
	'alias.example'));

Then set up the mapping. This looks worse than it really is:

sqlite> INSERT INTO VAlias (vaNum, vaTargetNum) VALUES (SELECT addNum
	FROM Address JOIN Domain on (Address.addDomNum=Domain.domNum)
	WHERE addLocalpart IS 'new-alias' AND domName IS
	'alias.example'),(SELECT addNum FROM Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum) WHERE addLocalpart IS
	'your-target-user' AND domName 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.

If you know the Address.addNum values you want, you can do this with
a much simpler sqlite command, for example, to map 42 to 16:

sqlite> INSERT INTO VAlias (vaNum, vaTargetNum) VALUES (42, 16);

... add a new virtual mailbox address?

First, add the new address to Address:

sqlite> INSERT INTO Address (addLocalpart, addDomNum) VALUES
	('new-vmail', (SELECT domNum FROM Domain WHERE domName IS
	'vmail.example'));

And then add it to VMailbox:

sqlite> INSERT INTO VMailbox (vmNum, vmPasswd) VALUES ((SELECT
	addNum FROM Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum) WHERE addLocalpart IS
	'new-vmail-user' AND domName 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.addDomNum=Domain.domNum)
	SET addRcNum=50 WHERE addLocalpart IS 'victim' AND domName 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 "50". 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.addNum value for the victim:

sqlite> SELECT Address.addNum FROM Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum) WHERE addLocalpart IS
	'victim' and domName IS 'myhostname.example.com';

We shouldn't try to remove that yet until we check for other uses of it.
Check the Alias and VAlias tables, assuming the above query returned
"42":

sqlite> SELECT * FROM VAlias WHERE vaNum=42 OR vaTargetNum=42;
sqlite> SELECT * FROM Alias WHERE aliasNum=42;

We can remove those records:

sqlite> DELETE FROM VAlias WHERE vaNum=42 OR vaTargetNum=42;
sqlite> DELETE FROM Alias WHERE aliasNum=42;

And finally, we can remove it from Address:

sqlite> DELETE FROM Address WHERE addNum=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 VAlias SET active=0 WHERE vaNum=42 OR
	vaTargetNum=42;
sqlite> UPDATE Alias SET active=0 WHERE aliasNum=42;

... disable an existing relay address?

Assuming no virtual aliasing applies, simply remove it from Address:

sqlite> DELETE FROM Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum) WHERE addLocalpart IS
	'relay-killed' AND domName IS 'relay.example';

Or, as above, use the "active" column:

sqlite> UPDATE Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum) SET Address.active=0 WHERE
	addLocalpart IS 'relay-killed' AND domName 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 VAlias 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.vmNum=Address.addNum) JOIN Domain ON
	(Address.addDomNum=Domain.domNum) SET VMailbox.active=0
	WHERE addLocalpart IS 'vmail-killed' AND domName IS
	'vmail.example';

But it can be deleted if you prefer:

sqlite> DELETE FROM VMailbox JOIN Address ON
	(VMailbox.vmboxNum=Address.addNum) JOIN Domain ON
	(Address.addDomNum=Domain.domNum) WHERE addLocalpart IS
	'vmail-killed' AND domName IS 'vmail.example';
sqlite> DELETE FROM Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum) WHERE addLocalpart IS
	'vmail-killed' AND domName 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 (domName, domClass) VALUES
	('new.local.example', 1);

That's it. If you need a special restriction class, include the
necessary column domRcNum column and value as needed. Also, add the
domSysUser 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 (addLocalpart, addDomNum, addTpNum,
	addRcNum) VALUES ('new-user', (SELECT domNum FROM Domain WHERE
	domName IS 'new.local.example'), 7, 10);

This will route new-user@new.local.example via Transport.tpNum=7 and
enable "aggressive" (RClass.rcNum=10) spam restrictions.

Finally, if you are using the sample local_recipient_maps query, you
would need to add ALL valid addresses in the domain to the Address
table. This has the possible benefit of providing some namespace
separation for local domains, where by default, there is none. (At
least insofar as one.user@old.local.example won't get mail as
one.user@new.local.example; but if that name exists in both domains, it
could only be delivered to the same "one.user" account.)

... add a relay domain?

Generally, a new transport might be needed. That must exist, before it
can be referenced in the Domain table.

sqlite> INSERT INTO Transport (tpNexthop) VALUES ('mx.relay.example');
sqlite> INSERT INTO Domain (domName, domTpNum, domClass, domRcNum)
	VALUES ('new.relay.example', (SELECT transNum FROM Transport
	WHERE nexthop 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 (addLocalpart, addDomNum) VALUES
	('postmaster', (SELECT domNum FROM Domain WHERE domName IS
	'new.relay.example');
sqlite> INSERT INTO Address (addLocalpart, addDomNum) VALUES ('abuse',
	(SELECT domNum FROM Domain WHERE domName 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 (domName, domClass, domRcNum) VALUES
	('new.alias.example', 1, 20);

Again, similarly, we add the postmaster and abuse aliases for
new.alias.example to the Address table:

sqlite> INSERT INTO Address (addLocalpart, addDomNum) VALUES
	('postmaster',(SELECT domNum FROM Domain WHERE domName IS
	'new.alias.example');
sqlite> INSERT INTO Address (addLocalpart, addDomNum) VALUES ('abuse',
	(SELECT domNum FROM Domain WHERE domName 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 VAlias (vaNum, vaTargetNum) VALUES (SELECT addNum
	FROM Address JOIN Domain ON (Address.addDomNum=Domain.domNum)
	WHERE addLocalpart IS 'postmaster' AND domName IS
	'new.alias.example'),(SELECT addNum FROM Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum) WHERE addLocalpart IS
	'your-target-user' AND domName 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 (domName, domClass, domSysUser, domRcNum)
	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 (addLocalpart, addDomNum) VALUES
	('postmaster', (SELECT domNum FROM Domain WHERE domName IS
	'new.vmail.example');
sqlite> INSERT INTO Address (addLocalpart, addDomNum) VALUES ('abuse',
	(SELECT domNum FROM Domain WHERE domName 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 VAlias 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 (vmNum, vmPasswd) VALUES ((SELECT
	addNum FROM Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum) WHERE addLocalpart IS
	'new-vmail-user' AND domName 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.

... delete any hosted domain?

sqlite> UPDATE Domain set active=0 WHERE domName 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 VAlias rows which refer (as alias or target) to
the rows in Address.

** Don't delete your $myhostname domain; you will have to recode your
alias_maps query if you do. I'm sure you will find other nasty
consequences. You can't say you were not warned!

... delete a virtual mailbox domain?

Again, see above about active=0, and about Address and VAlias. 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.vmboxNum=Address.addNum) JOIN Domain on
	(Address.addDomNum=Domain.domNum) SET VMailbox.active=0
	WHERE domName 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 addLocalpart.

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 = <whatever it will do>
z-yourClass2 = <whatever it will do 2>
[ ... ]

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 = 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-yourclass

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.

Next, add the new class definition to the RClass table:

sqlite> INSERT INTO RClass VALUES (35, yourclass);

Finally, change any domains or addresses as desired:

sqlite> UPDATE Domain SET domRcNum=35 WHERE domName IS
	'changed.example';
sqlite> UPDATE Address JOIN Domain ON
	Address.addDomNum=Domain.domNum SET addRcNum=35
	WHERE addLocalpart IS 'changed' AND domName IS 'domain.example';

... do a catchall address?

Again, I certainly DO NOT recommend this, but it can be done in this
schema. Regardless of the address class, it should be done in Address
and VAlias. 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.addLocalpart:

sqlite> INSERT INTO Address (addLocalpart, addDomNum) VALUES ('',
	(SELECT domNum FROM Domain WHERE domName IS
	'catchall.example'));
-- note, the following boils down to
-- "INSERT ... (integer, integer);"
sqlite> INSERT INTO VAlias (vaNum, vaTargetNum) VALUES ((SELECT addNum
	FROM Address JOIN Domain on (Address.addDomNum=Domain.domNum)
	WHERE addLocalpart IS '' AND domName IS 'catchall.example'),
	(SELECT addNum FROM Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum)
	WHERE addLocalpart IS 'your-target' AND domName IS
	'your.target.example'));

Exception: for a relay domain, the catchall only needs to be in the
Address table.

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.

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

... 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.

sqlite> UPDATE Address JOIN Domain ON
	(Address.addDomNum=Domain.domNum) SET Address.active=-1 WHERE
	addLocalpart IS 'compromised' AND domName IS 'domain.example';

For system accounts, we use the value of $myhostname for the domain.



G. 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.domNum=14 or on Address.addNum=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 your $myhostname setting in the alias_maps
query, 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 useful.



H. 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/