Getting reject_sender_login_mismatch/smtpd_sender_login_maps and mysql to work together
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 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). I run into problems when more than one login name is permitted to send from a given address. For example, say i include one of my valid addresses as postmaster (at) jweinberger.homeip.net (so I can send from it - mail to it goes into a postmaster mailbox as required). I also want someone else to be able to send from postmaster (at) jweinberger.homeip.net. So the virtual mailbox table would contain: Row 1: 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, postmaster (at) jweinberger.homeip.net Row 2: field username: someoneelse (at) jweinberger.homeip.net field password: (password) field valid_from: someoneelse (at) jweinberger.homeip.net, yetanothername (at) jweinberger.homeip.net, postmaster (at) jweinberger.homeip.net And when I try to send mail authenticated at jeff (at) jweinberger.homeip.net from postmaster (at) jweinberger.homeip.net it fails with this log entry: postfix/smtpd[83245]: NOQUEUE: reject: RCPT from unknown[10.0.1.1]: 553 5.7.1 postmaster (at) jweinberger.homeip.net : Sender address rejected: not owned by user jeff (at) jweinberger.homeip.net from=postmaster (at) jweinberger.homeip.net to=--edited-- proto=ESMTP helo=[10.0.1.7] The query in this case would return two rows, as both users are allowed to send from that address. According to the description of smtpd_sender_login_maps in http://www.postfix.com/postconf.5.html : In all cases the result of table lookup must be either not found or a list of SASL login names separated by comma and/or whitespace I conclude that the mysql query is not returning the list in the right form. Since I don't know how mysql calls are implemented in postfix, I don't know what to do to correct this, if it's possible. So my questions: 1) Is my analysis and conclusion (that mysql is not returning the result in the required form) correct? 2) Is there anything I can do to make the result come back in the form postfix needs it? I would like to be able to do this in this way, as I think it makes sense to track which addresses are authorized for each user, rather than the other way around. If there is no direct way to do this, I can build a reverse lookup table that will return the list postfix expects. And of course, if there is any additional information that would help in diagnosing or resolving this, I'd be happy to provide it. Any help, direction, pointers, etc. are much appreciated! Thanks, --Jeff
Re: Getting reject_sender_login_mismatch/smtpd_sender_login_maps and mysql to work together
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...@postfix.org?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.
Re: Getting reject_sender_login_mismatch/smtpd_sender_login_maps and mysql to work together
On Tue Jan 6, 2009 2:42 pm Victor Duchovni wrote: I know that it is not a good model for the way postfix requires the query, More stronly, an SQL schema in which multiple data items are stored concatenated in a single table element is a poor schema regardless of the application. This violates fundamental principles of database schema design. Multi-valued elements are stored in separate tables that are joined to the main table. This is not a Postfix requirement, it is basic database design. but from a logical standpoint, I am trying to this about it as which addresses is a user allowed to send from. The correct design has a address table which links address objects to user objects, and a user table which holds attribtues of users. To discover the addresses of a user, join the two tables. Implement appropriate indices and forein keys to make the expected queries efficient. 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. In a correct SQL schema the question is moot, SQL stores relation tuples, there is no notion of which is the primary element in a relation. x R y = row (x,y) is present in table R 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. Yes, but your schema is poorly designed. A good schema will naturally support a variety of queries that select ether the x values for which x R y is true for a given y or just as easily the converse. 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? I am claiming that what you can't be done with your existing schema, perhaps it can. I am claiming that you should change your schema as soon as you can. Why does postfix reject the mail when the mysql query return one or several valid SASL users? To solve such questions, divide and conquer: - Use a simple indexed file to store the sasl_sender_login table. Make this work, resolving any issues that you find. - Once SASL sender validation is working without SQL, create an SQL query that given verbatim keys from the indexed file, returns the identical RHS values. Verify with multiple postmap -q tests. Once this works, use SQL in the Postfix configuration. The above approach allows you to identify and solve the right problem. -- Viktor. Viktor: Thank you. I agree with all your comments about the database schema, which I have not yet built. I am in testing mode and want to make sure this kind of validation will work as expected and give me the desired results. So if I may, I'd like to focus on getting this to work. I did the testing you suggested before I posted. Here are the tests: I created a hash table (text file then used postmap hash:). The text file had lines like this: sender_address1 sasl_owner sender_address2 sasl_owner2, sasl_owner3, sasl_owner4 and so on, for the several addresses I tested. I then used postmap -q to verify that the lookup returned the correct values. As speficied in the documentation for smtpd_sender_login_maps, it returned one single SASL user name when only one user is authorized to send from that from address and a string of comma-separated names when multiple users are authorized to send from that from address. I then added the line: smtpd_sender_login_maps=hash:/path/to/map to my main.cf and send a few messages. Postfix correctly allowed and rejected all of the test messaages, and the logs showed the correct reason for the rejection, and no log entries showing a successful match. I then, again just to test, added a field to my virtual users table to hold authorized addresses, and wrote the query noted above into a mysql_sender_login_maps.cf file. I used postmap -q to verify that the results of the query were correct and were exactly the same as the results received when I used postmap - q on the hash: file. They all were identical for the same lookup keys. I then changed the main.cf line to: smtpd_sender_login_maps=hash:/path/to/mysql_sender_login_maps.cf and tried the same series of test messages. All of the test messages failed. The ones where the lookup produced the same comma-separated line as with the hash: file, and the ones where the lookup produced a single result. All were rejected (as noted in the log entry I posted previously) with the reason that the from address was not authorized for that sasl
Re: Getting reject_sender_login_mismatch/smtpd_sender_login_maps and mysql to work together
On Jan 6, 2009, at 5:59 PM, Wietse Venema wrote: Jeff Weinberger: I then added the line: smtpd_sender_login_maps=hash:/path/to/map to my main.cf and send a few messages. Postfix correctly allowed and rejected all of the test messaages, and the logs showed the correct reason for the rejection, and no log entries showing a successful match. I then, again just to test, added a field to my virtual users table to hold authorized addresses, and wrote the query noted above into a mysql_sender_login_maps.cf file. I used postmap -q to verify that the results of the query were correct and were exactly the same as the results received when I used postmap - q on the hash: file. They all were identical for the same lookup keys. smtpd does the same lookups as postmap -q. If the result is different than when you run postmap -q by hand, then it is almost certain that you are using a different search key than Postfix uses. Postfix does the lookups as documented in the postconf(5) manual page: smtpd_sender_login_maps (default: empty) Optional lookup table with the SASL login names that own sender (MAIL FROM) addresses. Specify zero or more type:table lookup tables. With lookups from indexed files such as DB or DBM, or from networked tables such as NIS, LDAP or SQL, the following search operations are done with a sender address of u...@domain: 1) u...@domain This table lookup is always done and has the highest precedence. 2) user This table lookup is done only when the domain part of the sender address matches $myorigin, $mydestination, $inet_inter- faces or $proxy_interfaces. 3) @domain This table lookup is done last and has the lowest precedence. You can see what lookups Postfix does by setting the debug_peer_list parameter in main.cf. For example: # postconf -e debug_peer_list=static:all # postfix reload Wietse Wietse Thank you very much for your help. I just responded to Viktor as well, and plead stupid. I just re-ran all the same tests (by hand this time) and everything worked. All postmap-q commands gave the correct results, and all messages were accepted or rejected as expected. I checked the scripts I used earlier for logical errors and typos, but found none. so I have no idea why I was getting odd results. Never-the-less, all works now. I thank you and Victor for your generous help. --Jeff
