On Tue, Jan 06, 2009 at 12:16:00PM -0800, Jeff Weinberger wrote:
> Hi:
>
> I would very much appreciate any help, advice, pointers, etc. to
> resolve an issue I am encountering.
>
> I am having a challenge trying to use a mysql table for
> smtpd_sender_login_maps. Right now I have:
>
> In main.cf:
> smtpd_sender_login_maps=mysql:/path/to/map.cf
> smtpd_sender_restrictions=reject_sender_login_mismatch, ....
>
> My virtual mailbox table contains the username for authentication
> (which is also the e-mail address), the password and I am using a text
> (VARCHAR) field to hold the list of e-mail addresses that each user is
> allowed to send from.
>
> For example, my e-mail is jeff (at) jweinberger.homeip.net. I can send
> as that e-mail, but I also want to allow sending from someothername
> (at) jweinberger.homeip.net and from jeff (at) some-other-domain-I-
> own.tld. so the table row looks like:
>
> field username: jeff (at) jweinberger.homeip.net
> field password: (password)
> field valid_from: jeff (at) jweinberger.homeip.net, someothername
> (at) jweinberger.homeip.net, jeff (at) some-other-domain-I-own.tld
This violates 1st normal form. Avoid multi-value fields stored as
a concatenation. The correct schema for this uses an additional
table to correlate addresses to users:
> The select statement in the maps.cf file is "select username from
> virtual_mailbox_table where (INSTR(valid_from, "%s") > 0)" which
> successfully (tested outside postfix) looks up the sender address and
> returns the login name (field: username).
This is an inefficient table scan. You need a better schema to model this.
--
Viktor.
Disclaimer: off-list followups get on-list replies or get ignored.
Please do not ignore the "Reply-To" header.
To unsubscribe from the postfix-users list, visit
http://www.postfix.org/lists.html or click the link below:
<mailto:[email protected]?body=unsubscribe%20postfix-users>
If my response solves your problem, the best way to thank me is to not
send an "it worked, thanks" follow-up. If you must respond, please put
"It worked, thanks" in the "Subject" so I can delete these quickly.
