--- In [email protected], Victor Duchovni <victor.ducho...@...> wrote: > > 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:majord...@...?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. >
Victor: Thank you. I know that it is not a good model for the way postfix requires the query, but from a logical standpoint, I am trying to this about it as which addresses is a user allowed to send from. This makes sense when you think about a user having e-mail service through my server, and using that to send e-mail from another unrelated valid address then he/she owns. (validating that is also important, but not in the scope of this issue). So I want to associate valid from addresses with the user, not the other way around. I understand fully the point of view that postfix takes, asking who is allowed to send from this e-mail address (more or less masquerade as this e-mail identity), which makes sense also. There may be better schema for answering the question I am asking, but it sounds from your response (I am assuming, possibly dangerously) That there is no way to do what I want to do without creating another table in the database, indexed by from address with resuting lists of users. Is that correct? Also, this seems that it won't work either. As inefficient as my query is, it successfully returned the list of valid SASL users for a given from address. But whether it found only one SASL user or a list of them, postfix rejected the mail with the same error. All of the above debate about the most effective way to achieve this is helpful, but the important question is this: Why does postfix reject the mail when the mysql query return one or several valid SASL users? And, if there are multiple SASL users permitted to send e-mail from a specific address, does postfix expect the query to return a one-row result with the one result containing a list of valid SASL users, or to return one row per SASL user, with each row containing the user name only? Any suggestions on how to make the query work? Thank you!!
