Re: Postfix and virtual users with additional_condition
Hello, sorry for the trouble i couse but as you may nothice I'm not very experienced user . I am really grateful for your help. I DO. Victor Duchovni pisze: On Fri, Mar 20, 2009 at 11:35:09PM +0100, Sebastian Chociwski wrote: I am (almost ;) ) 100% sure that only mail could be delivered is in ONE mysql db. What in your Postfix configuration do you expect to reject mail not listed in that MySQL table? I configured postfix to use one table to deliver mails to. When I send email to non-existing account it is rejected. As well as I try to send email to user I changed in table. _email2email.cf points at DB I use to decide what mails to deliver _domains.cf is DB with domains i want to accept (one column with domain name) How did you communicate your intent (decision) to Postfix? I made additional column in table. Default integer is 1. ... And I just realized why it don't work ... virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf All changes I made in ALIAS_MAPS. Adding condition to mailbox_maps file make it work. So sorry for the time you lost on me. And THANK YOU , without your help it would take weeks before I realize why it don't work :( -- best regards, Sebastian Chociwski
Re: Postfix and virtual users with additional_condition
Sebastian Chociwski wrote: myhostname = suse11.eurimage.pl mydestination = suse11.eurimage.pl, localhost, localhost.localdomain mynetworks = 127.0.0.0/8 virtual_alias_domains = virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf You are asking Postfix to rewrite addresses in mysql:/etc/postfix/mysql-virtual_email2email.cf If not found, no harm done. virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf For domains listed in mysql:/etc/postfix/mysql-virtual_domains.cf, you should put the active check in mysql:/etc/postfix/mysql-virtual_mailboxes.cf as this will reject messages. The SQL will depend on your table structure. Brian
Re: Postfix and virtual users with additional_condition
On Mon, Mar 23, 2009 at 09:41:14AM +0100, Sebastian Chociwski wrote: On Fri, Mar 20, 2009 at 11:35:09PM +0100, Sebastian Chociwski wrote: I am (almost ;) ) 100% sure that only mail could be delivered is in ONE mysql db. What in your Postfix configuration do you expect to reject mail not listed in that MySQL table? I configured postfix to use one table to deliver mails to. WHICH CONFIGURATION SETTINGS DO YOU EXPECT DO THIS? Forget the SQL, focus on the Postfix feature that you are using, and figure out whether it will or will not in fact restrict access to unlisted addresses. And I just realized why it don't work ... virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf All changes I made in ALIAS_MAPS. Adding condition to mailbox_maps file make it work. This is exactly the point, you were too immersed in SQL tweaking to notice that for mailbox domains the valid users are listed *primarily* in the mailbox maps table. While *not* being listed in the alias table does not make an address fail reject_unlisted_recipient, being listed in the alias table makes an address pass reject_unlisted_recipient, so if you really want to reject an invalid address at SMTP time, you need to not list it in either table. Identity mappings (fixedu...@example.com - fixedu...@example.com) are not terribly useful unless you also have a catch-all address. Why do you have these at all? -- 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: Postfix and virtual users with additional_condition
Victor Duchovni pisze: On Mon, Mar 23, 2009 at 09:41:14AM +0100, Sebastian Chociwski wrote: On Fri, Mar 20, 2009 at 11:35:09PM +0100, Sebastian Chociwski wrote: WHICH CONFIGURATION SETTINGS DO YOU EXPECT DO THIS? Of course I thought it was _email2email.cf . Now I see how wrong I was. Identity mappings (fixedu...@example.com - fixedu...@example.com) are not terribly useful unless you also have a catch-all address. Why do you have these at all? Becouse I thought it is responsible for delivering mails. It's my first postfix working with any DB and after some problems with makeing it work I didn't catch what I dmade wrong. _email2email on my system is useless. I guess when I use second DB virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf, proxy:mysql:/etc/postfix/mysql-virtual_SECOND_mailboxes.cf it won't be a problem ? Does it matter which is first ? thanks again Victor. Own you a beer ;) -- best regards, Sebastian Chociwski
Re: Postfix and virtual users with additional_condition
On Mon, Mar 23, 2009 at 03:26:36PM +0100, Sebastian Chociwski wrote: Identity mappings (fixedu...@example.com - fixedu...@example.com) are not terribly useful unless you also have a catch-all address. Why do you have these at all? Becouse I thought it is responsible for delivering mails. It's my first postfix working with any DB and after some problems with makeing it work I didn't catch what I dmade wrong. _email2email on my system is useless. Get rid of this mapping. I guess when I use second DB virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf, proxy:mysql:/etc/postfix/mysql-virtual_SECOND_mailboxes.cf it won't be a problem ? Does it matter which is first ? Just get rid of the useless identity email2email mappings. -- 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: Postfix and virtual users with additional_condition
On Fri, Mar 20, 2009 at 11:35:09PM +0100, Sebastian Chociwski wrote: I am (almost ;) ) 100% sure that only mail could be delivered is in ONE mysql db. What in your Postfix configuration do you expect to reject mail not listed in that MySQL table? _email2email.cf points at DB I use to decide what mails to deliver _domains.cf is DB with domains i want to accept (one column with domain name) How did you communicate your intent (decision) to Postfix? Why postfix delivers mail that should be rejected ? Should in what sense? - You want it to be rejected? Postfix does not know what you want, it only knows what you told it to do. - You configured Postfix to reject it? I did not notice any such configuration. I tested one more thing : query = SELECT email FROM users WHERE email='%s' AND mail=1 mail is y/n so it should be rejected but while postmap -q shows empty the postfix delivers mails. It looks like it's just ignoring anything with and after 'AND... No. It looks like reject/accept decisions do not in any way depend on the contents of this table, because you have not asked Postfix to reject mail messages for addresses not listed in this table. -- 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.
Postfix and virtual users with additional_condition
Hello, I'm trying to make additional conditions work on my system : Suse 11.1 , postfix 2.5.5 and mysql 5.0.67. Forwarding and delivering mails to V_USERS works fine for me but I need to make additional condition so I make new column mailactiv with default '1'. If the mailactiv is set to '1' mail should be delivered otherwise - bounce. My email2email.cf : query = SELECT email FROM users WHERE email='%s' AND mailactiv='1' NOT WORK. query = SELECT email FROM users WHERE email='%s' additional_conditions = AND mailactiv = '1' NOT WORK, query = SELECT email FROM users WHERE email='%s' AND mailactiv='1' NOT WORK, query = SELECT email FROM users WHERE mailactiv='1' AND email='%s' SETS THE WHOLE SYSTEM OUT OF ORDER. No idea whats wrong :( -- best regards, Sebastian Chociwski
Re: Postfix and virtual users with additional_condition
On Fri, Mar 20, 2009 at 03:59:05PM +0100, Sebastian Chociwski wrote: query = SELECT email FROM users WHERE email='%s' AND mailactiv='1' Is the mailactiv column integer-valued or string-valued? NOT WORK. You really should report output from tests with postmap -q. NOT WORK is rather useless. query = SELECT email FROM users WHERE email='%s' additional_conditions = AND mailactiv = '1' Of course, additional_conditions is not used with new query = syntax, only with the the obsolete table = syntax. query = SELECT email FROM users WHERE email='%s' AND mailactiv='1' Equivalent to the first. query = SELECT email FROM users WHERE mailactiv='1' AND email='%s' Ditto, the order of constraints in SQL is not significant. SETS THE WHOLE SYSTEM OUT OF ORDER. How are we supposed to know what sets the whole system out of order means? -- 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: Postfix and virtual users with additional_condition
OH sorry for that , heres more information: Activmail is integer. I checked the varchar mail column with default 'y' and got : query = SELECT email FROM users WHERE email='%s' AND mail='y' serwersuse111:~ # postmap -q sebastian...@example.pl mysql:/etc/postfix/mysql-virtual_email2email.cf sebastian...@example.pl When I set 'mail' to 'n : serwersuse111:~ # postmap -q sebastian...@eurimage.pl mysql:/etc/postfix/mysql-virtual_email2email.cf serwersuse111:~ # So I guess it should work. But i does not and with 'n' set the mails are sill delivered to my email account. The log from delivery : Mar 20 20:08:44 serwersuse111 postfix/qmgr[17359]: 8382F40E3E0: from=x...@gazeta.pl, size=1547, nrcpt=1 (queue active) Mar 20 20:08:44 serwersuse111 postfix/smtpd[17396]: disconnect from localhost[127.0.0.1] Mar 20 20:08:44 serwersuse111 amavis[5402]: (05402-04) Passed CLEAN, [209.85.218.161] [209.85.218.161] x...@gazeta.pl - sebastian...@example.pl, Message-ID: 917ea74b0903201212l537eb7d1v6f5d38900b03f...@mail.gmail.com, mail_id: mlaPdXK+zvkO, Hits: 1.601, size: 1094, queued_as: 8382F40E3E0, 17678 ms Mar 20 20:08:44 serwersuse111 postfix/smtp[17367]: B463840E19F: to=sebastian...@example.pl, relay=127.0.0.1[127.0.0.1]:10024, delay=18, delays=0.29/0/0/18, dsn=2.0.0, status=sent (250 2.0.0 Ok, id=05402-04, from MTA([127.0.0.1]:10025): 250 2.0.0 Ok: queued as 8382F40E3E0) Mar 20 20:08:44 serwersuse111 postfix/qmgr[17359]: B463840E19F: removed Mar 20 20:08:44 serwersuse111 postfix/virtual[17397]: 8382F40E3E0: to=sebastian...@example.pl, relay=virtual, delay=0.14, delays=0.07/0.01/0/0.06, dsn=2.0.0, status=sent (delivered to maildir) Mar 20 20:08:44 serwersuse111 postfix/qmgr[17359]: 8382F40E3E0: removed Mar 20 20:08:57 serwersuse111 postfix/smtpd[17376]: disconnect from mail-bw0-f161.google.com[209.85.218.161] Thank you for your time and sorry if I missed anything. Victor Duchovni pisze: On Fri, Mar 20, 2009 at 03:59:05PM +0100, Sebastian Chociwski wrote: query = SELECT email FROM users WHERE email='%s' AND mailactiv='1' Is the mailactiv column integer-valued or string-valued? query = SELECT email FROM users WHERE mailactiv='1' AND email='%s' Ditto, the order of constraints in SQL is not significant. SETS THE WHOLE SYSTEM OUT OF ORDER. How are we supposed to know what sets the whole system out of order means? It mean the '/etc/rc.d/postfix restart' fails with the last setting.
Re: Postfix and virtual users with additional_condition
On Fri, Mar 20, 2009 at 08:19:42PM +0100, Sebastian Chociwski wrote: OH sorry for that , heres more information: Activmail is integer. Then the correct SQL query syntax is: activmail=1 NOT activmail='1' This is basic SQL, not Postfix. -- 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: Postfix and virtual users with additional_condition
I knew it must be something I am missing but the mails are still delivered. MYSQL cut : mailactivint(1) 0 mailvarchar(1) n email2email.cf : query = SELECT email FROM users WHERE email='%s' AND mailactiv= 1 (checked as well : query = SELECT email FROM users WHERE email='%s' AND mailactiv=1) And some log again : // user with mailactiv default set to 1 serwersuse111:~ # postmap -q jare...@pro-activ.pl mysql:/etc/postfix/mysql-virtual_email2email.cf jare...@pro-activ.pl // user with mailactiv changed to 0 with phpmyadmin (maybe it matters?) serwersuse111:~ # postmap -q sebastian...@eurimage.pl mysql:/etc/postfix/mysql-virtual_email2email.cf serwersuse111:~ # and maillog from delivery : Mar 20 22:06:55 serwersuse111 postfix/smtpd[18723]: connect from mail-bw0-f161.google.com[209.85.218.161] Mar 20 22:06:56 serwersuse111 postfix/smtpd[18723]: 07EA440E19F: client=mail-bw0-f161.google.com[209.85.218.161] Mar 20 22:06:56 serwersuse111 postfix/cleanup[18732]: 07EA440E19F: message-id=917ea74b0903201410v61f1925dt84a44c4bc8d7b...@mail.gmail.com Mar 20 22:06:56 serwersuse111 postfix/qmgr[18697]: 07EA440E19F: from=x...@gazeta.pl, size=4787, nrcpt=1 (queue active) Mar 20 22:06:56 serwersuse111 amavis[5402]: (05402-05) (!!)WARN: all primary virus scanners failed, considering backups Mar 20 22:07:13 serwersuse111 postfix/smtpd[18739]: warning: dict_nis_init: NIS domain name not set - NIS lookups disabled Mar 20 22:07:13 serwersuse111 postfix/smtpd[18739]: connect from localhost[127.0.0.1] Mar 20 22:07:13 serwersuse111 postfix/smtpd[18739]: 80AAA40E3E0: client=localhost[127.0.0.1] Mar 20 22:07:13 serwersuse111 postfix/cleanup[18732]: 80AAA40E3E0: message-id=917ea74b0903201410v61f1925dt84a44c4bc8d7b...@mail.gmail.com Mar 20 22:07:13 serwersuse111 postfix/smtpd[18739]: disconnect from localhost[127.0.0.1] Mar 20 22:07:13 serwersuse111 postfix/qmgr[18697]: 80AAA40E3E0: from=x...@gazeta.pl, size=5240, nrcpt=1 (queue active) Mar 20 22:07:13 serwersuse111 amavis[5402]: (05402-05) Passed CLEAN, [209.85.218.161] [209.85.218.161] x...@gazeta.pl - sebastian...@eurimage.pl, Message-ID: 917ea74b0903201410v61f1925dt84a44c4bc8d7b...@mail.gmail.com, mail_id: zN+0L8bSr-Og, Hits: 1.36, size: 4787, queued_as: 80AAA40E3E0, 17228 ms Mar 20 22:07:13 serwersuse111 postfix/smtp[18734]: 07EA440E19F: to=sebastian...@eurimage.pl, relay=127.0.0.1[127.0.0.1]:10024, delay=18, delays=0.41/0.01/0/17, dsn=2.0.0, status=sent (250 2.0.0 Ok, id=05402-05, from MTA([127.0.0.1]:10025): 250 2.0.0 Ok: queued as 80AAA40E3E0) Mar 20 22:07:13 serwersuse111 postfix/qmgr[18697]: 07EA440E19F: removed Mar 20 22:07:13 serwersuse111 postfix/virtual[18740]: 80AAA40E3E0: to=sebastian...@eurimage.pl, relay=virtual, delay=0.12, delays=0.05/0.01/0/0.06, dsn=2.0.0, status=sent (delivered to maildir) Mar 20 22:07:13 serwersuse111 postfix/qmgr[18697]: 80AAA40E3E0: removed Victor Duchovni pisze: On Fri, Mar 20, 2009 at 08:19:42PM +0100, Sebastian Chociwski wrote: OH sorry for that , heres more information: Activmail is integer. Then the correct SQL query syntax is: activmail=1 NOT activmail='1' This is basic SQL, not Postfix. Good to know. Always open to learn new things. And sorry to say it - I'm still not able to make it work. -- best regards, Sebastian Chociwski
Re: Postfix and virtual users with additional_condition
On Fri, Mar 20, 2009 at 10:17:28PM +0100, Sebastian Chociwski wrote: I knew it must be something I am missing but the mails are still delivered. MYSQL cut : mailactiv int(1) 0 mail varchar(1) n email2email.cf : query = SELECT email FROM users WHERE email='%s' AND mailactiv= 1 (checked as well : query = SELECT email FROM users WHERE email='%s' AND mailactiv=1) Is there any reason to expect that not matching the query should prevent delivery? The query is an identity mapping for valid users, what purpose does it serve? Looks neither valid nor invalid users are rewritten, so the query seems to have no point. You are looking at the problem at the wrong level. Ask a Postfix question, not an SQL question. What Postfix feature are you using to distinguish between valid and invalid users? -- 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: Postfix and virtual users with additional_condition
I am (almost ;) ) 100% sure that only mail could be delivered is in ONE mysql db. serwersuse111:~ # cat /etc/postfix/main.cf queue_directory=/var/spool/postfix command_directory=/usr/sbin daemon_directory = /usr/lib/postfix data_directory=/var/lib/postfix mail_owner=postfix myhostname = suse11.eurimage.pl mydestination = suse11.eurimage.pl, localhost, localhost.localdomain mynetworks = 127.0.0.0/8 virtual_alias_domains = virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf virtual_mailbox_base = /home/vmail virtual_uid_maps = static:5000 virtual_gid_maps = static:5000 #smtpd_sasl_auth_enable = yes #broken_sasl_auth_clients = yes smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, check_client_access hash:/etc/postfix/pop-before-smtp,reject_unauth_destination #smtpd_use_tls = yes #smtpd_tls_cert_file = /etc/postfix/smtpd.cert #smtpd_tls_key_file = /etc/postfix/smtpd.key transport_maps = proxy:mysql:/etc/postfix/mysql-virtual_transports.cf virtual_create_maildirsize = yes virtual_maildir_extended = yes #virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailbox_limit_maps.cf #virtual_mailbox_limit_override = yes #virtual_maildir_limit_message = The user you are trying to reach is over quota. #virtual_overquota_bounce = yes proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks _forvawrdings.cf points at empty DB _email2email.cf points at DB I use to decide what mails to deliver _domains.cf is DB with domains i want to accept (one column with domain name) _transports.cf empty DB If I send email to adres that does not exist in email2email DB its returned with info about Delivery to the following recipient failed permanently:. postmap -q works fine for me : return mail when mailactiv =1 and return empty when mailactiv=0 . Why postfix delivers mail that should be rejected ? I tested one more thing : query = SELECT email FROM users WHERE email='%s' AND mail=1 mail is y/n so it should be rejected but while postmap -q shows empty the postfix delivers mails. It looks like it's just ignoring anything with and after 'AND... Victor Duchovni pisze: On Fri, Mar 20, 2009 at 10:17:28PM +0100, Sebastian Chociwski wrote: I knew it must be something I am missing but the mails are still delivered. MYSQL cut : mailactiv int(1) 0 mail varchar(1) n email2email.cf : query = SELECT email FROM users WHERE email='%s' AND mailactiv= 1 (checked as well : query = SELECT email FROM users WHERE email='%s' AND mailactiv=1) Is there any reason to expect that not matching the query should prevent delivery? The query is an identity mapping for valid users, what purpose does it serve? Looks neither valid nor invalid users are rewritten, so the query seems to have no point. You are looking at the problem at the wrong level. Ask a Postfix question, not an SQL question. What Postfix feature are you using to distinguish between valid and invalid users?
