A Different Set of Stories

I am getting ready to replace the production email server at work with a new one using a different set of components. Right now, I handle virtual domains and virtual users in a hard-to-maintain fashion using courier-auth and courier-imapd and some hocus-pocus involving flat files.

I want to put as much as possible in postgresql. To that end I'm using DBmail with a few of my own database extensions.

I have a test server running. And while troubleshooting a delivery problem I turned up verbosity on all bits of postfix. And a warning appeared in the logs. A warning on a piece I thought was settled long ago. It seems postfix thought the localhost domain was in mydestinations and virtual_mailbox_domains. Well, I knew for a fact (and double checked) that localhost was not in the virtual tables anywhere.

When postfix is looking to see if we accept mail for a given domain, it checks a bunch of places. One of the last is the virtual_mailbox_domains list. Postfix is not asking for a yes/no response from the database; rather, it is counting the NUMBER OF ROWS returned by the database query which will be 0 or 1. I'm sure there must be a good reason for this. I suspect it allows the same code to be used to support a lot of ways of storing this information and some can't return boolean.

I wrote a series of functions in postgresql so that the postfix queries call my functions. And that is the rub. If you have a function foo(domain text) that looks like this:

CREATE FUNCTION foo (domain text) RETURNS text AS $$ SELECT domain FROM allowed_domains WHERE domain = $1 $$ LANGUAGE sql

And you call this function like this:

SELECT foo('example.com');

You will always get one row returned and it doesn't matter if example.com is in the database or not. That row will either be "example.com" or "NULL". foo() returns no rows or 1 row, but the select that calls it returns the result of foo or null. Postfix counts rows and it doesn't care that the value of the row is unassigned.

It has also been suggested that returning arbitrary text might be an issue for postfix. I have seen no evidence of that, but returning a "1" when there is a match seems safer. That means that I could do something like this:

SELECT 1 FROM (select domain from foo('%s') where domain is not null) x

Postfix substitutes the necessary key value for the '%s'. This call to my function returns one row with a value of 1 if the domain is found and returns no rows if the domain is not found.

But this is a bit clunky looking. The answer is a view.

CREATE VIEW allowed_domain AS
SELECT domain FROM allowed_domains
WHERE domain <> 'specialname' AND
domain <> 'otherspecialname';

Then the postfix query can look like this:

SELECT 1 FROM allowed_domain WHERE domain = '%s';

Better. Much, much better. It is an error to return more than one row to postfix. The table allowed_domains is not allowed to have duplicate entries so we will "never" return more than 1 row and now we can return 0 rows.