Wrong dbmail-mysql-postfix setup in wiki!

[Rene Bartsch-2]

Hi,

having a lot of trouble with getting Postfix, MySQL and DBMail running I had a little chit-chat
with Brian Evans and Wietse Venema on the Postfix-ML.

"mailbox_transport" and "local_recipient_maps" belong to the local domain class which is only for
delivering to local UNIX accounts! Using them for external MDAs can have unpredictable
side-effects!

Instead, use the virtual mailbox domain class!

example:

## Virtual mailbox domain class: DBmail MDA
virtual_transport       = dbmail-lmtp:<hostname/ip>:<port>
virtual_mailbox_domains = mysql:/etc/postfix/sql-virtual_mailbox_domains.cf
virtual_mailbox_maps    = mysql:/etc/postfix/sql-virtual_mailbox_maps.cf


Additionally, the SQL-queries MUST NOT return any value from the database. When
"mysql:/etc/postfix/sql-virtual_mailbox_domains.cf" returns one domain or the list of domains,
Postfix cannot relay mail to the internet anymore! Instead return "1" if a value exists in the
database or a empty set if it doesn't exists!


examples:

/etc/postfix/sql-virtual_mailbox_maps.cf:

user     = <MySQL-username>
password = <MySQL-password>
hosts    = <MySQL-host>
dbname   = <MySQL-database>
query    = SELECT 1 FROM dbmail_aliases WHERE alias='%s';


sql-virtual_mailbox_domains.cf:

user     = <MySQL-username>
password = <MySQL-password>
hosts    = <MySQL-host>
dbname   = <MySQL-database>
query    = SELECT DISTINCT 1 FROM dbmail_aliases WHERE SUBSTRING_INDEX(alias, '@', -1) = '%s';

(If you use aliases for the email-addresses and simple usernames you do not need to query
dbmail_users. In case you use email-addresses as usernames you need to add dbmail_users to the
query.)

Best regards,

Renne


_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Aaron Stone]

Welcome! Please update anything in the wiki that appeared to be
inaccurate, and add any helpful information you learned along the way. It
is greatly appreciated!

On Fri, 9 Oct 2009 21:15:10 +0200 (CEST), Rene Bartsch <ML@...>
wrote:
> Hi,
>
> having a lot of trouble with getting Postfix, MySQL and DBMail running I
> had a little chit-chat
> with Brian Evans and Wietse Venema on the Postfix-ML.

What kind of trouble?

> "mailbox_transport" and "local_recipient_maps" belong to the local
domain
> class which is only for
> delivering to local UNIX accounts! Using them for external MDAs can have
> unpredictable
> side-effects!

What kind of side effects?

> Instead, use the virtual mailbox domain class!
>
> example:
>
> ## Virtual mailbox domain class: DBmail MDA
> virtual_transport       = dbmail-lmtp:<hostname/ip>:<port>
> virtual_mailbox_domains =
mysql:/etc/postfix/sql-virtual_mailbox_domains.cf
> virtual_mailbox_maps    = mysql:/etc/postfix/sql-virtual_mailbox_maps.cf

Huh, ok. I'm also using the local variation. I'll try these virtual
options on my system.

> Additionally, the SQL-queries MUST NOT return any value from the
database.
> When
> "mysql:/etc/postfix/sql-virtual_mailbox_domains.cf" returns one domain
or
> the list of domains,
> Postfix cannot relay mail to the internet anymore! Instead return "1" if
a
> value exists in the
> database or a empty set if it doesn't exists!

Where does it say this in the postfix docs?

> examples:
>
> /etc/postfix/sql-virtual_mailbox_maps.cf:
>
> user     = <MySQL-username>
> password = <MySQL-password>
> hosts    = <MySQL-host>
> dbname   = <MySQL-database>
> query    = SELECT 1 FROM dbmail_aliases WHERE alias='%s';
>
>
> sql-virtual_mailbox_domains.cf:
>
> user     = <MySQL-username>
> password = <MySQL-password>
> hosts    = <MySQL-host>
> dbname   = <MySQL-database>
> query    = SELECT DISTINCT 1 FROM dbmail_aliases WHERE
> SUBSTRING_INDEX(alias, '@', -1) = '%s';
>
> (If you use aliases for the email-addresses and simple usernames you do
> not need to query
> dbmail_users. In case you use email-addresses as usernames you need to

add
> dbmail_users to the
> query.)

Adding more recipes to the wiki is welcomed.

Aaron
_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Rene Bartsch-2]

> Welcome! Please update anything in the wiki that appeared to be
> inaccurate, and add any helpful information you learned along the way. It
> is greatly appreciated!
>
> On Fri, 9 Oct 2009 21:15:10 +0200 (CEST), Rene Bartsch <ML@...>
> wrote:
>> Hi,
>>
>> having a lot of trouble with getting Postfix, MySQL and DBMail running I
>> had a little chit-chat
>> with Brian Evans and Wietse Venema on the Postfix-ML.
>
> What kind of trouble?

Postfix didn't relay mails TO the internet anymore.
("550 5.1.1 $recipient: Recipient address rejected: User unknown in virtual mailbox table")

>> "mailbox_transport" and "local_recipient_maps" belong to the local
> domain
>> class which is only for
>> delivering to local UNIX accounts! Using them for external MDAs can have
>> unpredictable
>> side-effects!
>
> What kind of side effects?

It seems to use other queues. So mail can be stuck, rejected or maybe loop?
Just ask Wietse Venema about the depths of Postfix. ;)

>> Instead, use the virtual mailbox domain class!
>>
>> example:
>>
>> ## Virtual mailbox domain class: DBmail MDA
>> virtual_transport       = dbmail-lmtp:<hostname/ip>:<port>
>> virtual_mailbox_domains =
> mysql:/etc/postfix/sql-virtual_mailbox_domains.cf
>> virtual_mailbox_maps    = mysql:/etc/postfix/sql-virtual_mailbox_maps.cf
>
> Huh, ok. I'm also using the local variation. I'll try these virtual
> options on my system.

I used it for years, too. But after changing the machine/distro it didn't work any more ...

>> Additionally, the SQL-queries MUST NOT return any value from the
> database.
>> When
>> "mysql:/etc/postfix/sql-virtual_mailbox_domains.cf" returns one domain
> or
>> the list of domains,
>> Postfix cannot relay mail to the internet anymore! Instead return "1" if
> a
>> value exists in the
>> database or a empty set if it doesn't exists!
>
> Where does it say this in the postfix docs?

http://www.postfix.org/DATABASE_README.html#lists

and

http://www.postfix.org/mysql_table.5.html (see "List Membership")

>> examples:
>>
>> /etc/postfix/sql-virtual_mailbox_maps.cf:
>>
>> user     = <MySQL-username>
>> password = <MySQL-password>
>> hosts    = <MySQL-host>
>> dbname   = <MySQL-database>
>> query    = SELECT 1 FROM dbmail_aliases WHERE alias='%s';
>>
>>
>> sql-virtual_mailbox_domains.cf:
>>
>> user     = <MySQL-username>
>> password = <MySQL-password>
>> hosts    = <MySQL-host>
>> dbname   = <MySQL-database>
>> query    = SELECT DISTINCT 1 FROM dbmail_aliases WHERE
>> SUBSTRING_INDEX(alias, '@', -1) = '%s';
>>
>> (If you use aliases for the email-addresses and simple usernames you do
>> not need to query
>> dbmail_users. In case you use email-addresses as usernames you need to
> add
>> dbmail_users to the
>> query.)
>
> Adding more recipes to the wiki is welcomed.

I'll do! ;)

Renne


_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Rene Bartsch-2]

>> Welcome! Please update anything in the wiki that appeared to be
>> inaccurate, and add any helpful information you learned along the way. It
>> is greatly appreciated!
>>
>> On Fri, 9 Oct 2009 21:15:10 +0200 (CEST), Rene Bartsch <ML@...>
>> wrote:
>>> Hi,
>>>
>>> having a lot of trouble with getting Postfix, MySQL and DBMail running I
>>> had a little chit-chat
>>> with Brian Evans and Wietse Venema on the Postfix-ML.
>>
>> What kind of trouble?
>
> Postfix didn't relay mails TO the internet anymore.
> ("550 5.1.1 $recipient: Recipient address rejected: User unknown in virtual mailbox table")
>
>>> "mailbox_transport" and "local_recipient_maps" belong to the local
>> domain
>>> class which is only for
>>> delivering to local UNIX accounts! Using them for external MDAs can have
>>> unpredictable
>>> side-effects!
>>
>> What kind of side effects?
>
> It seems to use other queues. So mail can be stuck, rejected or maybe loop?
> Just ask Wietse Venema about the depths of Postfix. ;)
>
>>> Instead, use the virtual mailbox domain class!
>>>
>>> example:
>>>
>>> ## Virtual mailbox domain class: DBmail MDA
>>> virtual_transport       = dbmail-lmtp:<hostname/ip>:<port>
>>> virtual_mailbox_domains =
>> mysql:/etc/postfix/sql-virtual_mailbox_domains.cf
>>> virtual_mailbox_maps    = mysql:/etc/postfix/sql-virtual_mailbox_maps.cf
>>
>> Huh, ok. I'm also using the local variation. I'll try these virtual
>> options on my system.
>
> I used it for years, too. But after changing the machine/distro it didn't work any more ...
>
>>> Additionally, the SQL-queries MUST NOT return any value from the
>> database.
>>> When
>>> "mysql:/etc/postfix/sql-virtual_mailbox_domains.cf" returns one domain
>> or
>>> the list of domains,
>>> Postfix cannot relay mail to the internet anymore! Instead return "1" if
>> a
>>> value exists in the
>>> database or a empty set if it doesn't exists!
>>
>> Where does it say this in the postfix docs?
>
> http://www.postfix.org/DATABASE_README.html#lists
>
> and
>
> http://www.postfix.org/mysql_table.5.html (see "List Membership")
>
>>> examples:
>>>
>>> /etc/postfix/sql-virtual_mailbox_maps.cf:
>>>
>>> user     = <MySQL-username>
>>> password = <MySQL-password>
>>> hosts    = <MySQL-host>
>>> dbname   = <MySQL-database>
>>> query    = SELECT 1 FROM dbmail_aliases WHERE alias='%s';
>>>
>>>
>>> sql-virtual_mailbox_domains.cf:
>>>
>>> user     = <MySQL-username>
>>> password = <MySQL-password>
>>> hosts    = <MySQL-host>
>>> dbname   = <MySQL-database>
>>> query    = SELECT DISTINCT 1 FROM dbmail_aliases WHERE
>>> SUBSTRING_INDEX(alias, '@', -1) = '%s';
>>>
>>> (If you use aliases for the email-addresses and simple usernames you do
>>> not need to query
>>> dbmail_users. In case you use email-addresses as usernames you need to
>> add
>>> dbmail_users to the
>>> query.)
>>
>> Adding more recipes to the wiki is welcomed.
>
> I'll do! ;)

And I did. Please review my changes and my english. ;)

Renne


_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail