virtual_alias_maps mysql
Hi, mail_version = 2.6.1 I using virtual_alias_maps with mysql for storage. Working fine. Does anyone have a suggestion on how to update a timestamp field in the mysql table when postfix finds a virtual_alias_maps match? I'm looking for a way to measure alias usage and cull unused aliases. Thank you, Bradley Giesbrecht
Re: virtual_alias_maps mysql
Hi, > I using virtual_alias_maps with mysql for storage. Working fine. > > Does anyone have a suggestion on how to update a timestamp field in the > mysql table when postfix finds a virtual_alias_maps match? > > I'm looking for a way to measure alias usage and cull unused aliases. Have you considered a stored procedure? HTH Regards, Serge Fonville -- http://www.sergefonville.nl Convince Google!! They need to support Adsense over SSL https://www.google.com/adsense/support/bin/answer.py?hl=en&answer=10528 http://www.google.com/support/forum/p/AdSense/thread?tid=1884bc9310d9f923&hl=en
Re: virtual_alias_maps mysql
On 1/28/2010 3:35 PM, Serge Fonville wrote: > Hi, > >> I using virtual_alias_maps with mysql for storage. Working fine. >> >> Does anyone have a suggestion on how to update a timestamp field in the >> mysql table when postfix finds a virtual_alias_maps match? >> >> I'm looking for a way to measure alias usage and cull unused aliases. >> > Have you considered a stored procedure? > Stored procedures do not work in Postfix without code changes because the |CLIENT_MULTI_RESULTS connect flag, for MySQL API, is not turned on. |
Re: virtual_alias_maps mysql
>>> I using virtual_alias_maps with mysql for storage. Working fine. >>> >>> Does anyone have a suggestion on how to update a timestamp field in the >>> mysql table when postfix finds a virtual_alias_maps match? >>> >>> I'm looking for a way to measure alias usage and cull unused aliases. >>> >> Have you considered a stored procedure? >> > > Stored procedures do not work in Postfix without code changes because > the |CLIENT_MULTI_RESULTS connect flag, for MySQL API, is not turned on. >From the manual: http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html CLIENT_MULTI_RESULTS Tell the server that the client can handle multiple result sets from multiple-statement executions or stored procedures. This flag is automatically enabled if CLIENT_MULTI_STATEMENTS is enabled. See the note following this table for more information about this flag. If your program uses CALL statements to execute stored procedures, the CLIENT_MULTI_RESULTS flag must be enabled. Not sure if I understand this right then, but to me this reads that if you use SELECT to get results from a stored procedure your fine Correct me if I'm wrong HTH Regards, Serge Fonville -- http://www.sergefonville.nl Convince Google!! They need to support Adsense over SSL https://www.google.com/adsense/support/bin/answer.py?hl=en&answer=10528 http://www.google.com/support/forum/p/AdSense/thread?tid=1884bc9310d9f923&hl=en
Re: virtual_alias_maps mysql
On 1/28/2010 4:12 PM, Serge Fonville wrote: I using virtual_alias_maps with mysql for storage. Working fine. Does anyone have a suggestion on how to update a timestamp field in the mysql table when postfix finds a virtual_alias_maps match? I'm looking for a way to measure alias usage and cull unused aliases. >>> Have you considered a stored procedure? >>> >>> >> Stored procedures do not work in Postfix without code changes because >> the |CLIENT_MULTI_RESULTS connect flag, for MySQL API, is not turned on. >> > From the manual: > http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html > CLIENT_MULTI_RESULTS > > [...] > If your program uses CALL statements to execute stored procedures, the > CLIENT_MULTI_RESULTS flag must be enabled. > Reread this ^^^.
Re: virtual_alias_maps mysql
On Thu, Jan 28, 2010 at 10:40 PM, Brian Evans - Postfix List wrote: > On 1/28/2010 4:12 PM, Serge Fonville wrote: > I using virtual_alias_maps with mysql for storage. Working fine. > > Does anyone have a suggestion on how to update a timestamp field in the > mysql table when postfix finds a virtual_alias_maps match? > > I'm looking for a way to measure alias usage and cull unused aliases. > > Have you considered a stored procedure? >>> Stored procedures do not work in Postfix without code changes because >>> the |CLIENT_MULTI_RESULTS connect flag, for MySQL API, is not turned on. >>> >> From the manual: >> http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html >> CLIENT_MULTI_RESULTS >> >> > [...] >> If your program uses CALL statements to execute stored procedures, the >> CLIENT_MULTI_RESULTS flag must be enabled. >> > Reread this ^^^. If you use a SELECT query, does it use CALL? -- http://www.sergefonville.nl Convince Google!! They need to support Adsense over SSL https://www.google.com/adsense/support/bin/answer.py?hl=en&answer=10528 http://www.google.com/support/forum/p/AdSense/thread?tid=1884bc9310d9f923&hl=en
Re: virtual_alias_maps mysql
On Jan 28, 2010, at 12:35 PM, Serge Fonville wrote: Hi, I using virtual_alias_maps with mysql for storage. Working fine. Does anyone have a suggestion on how to update a timestamp field in the mysql table when postfix finds a virtual_alias_maps match? I'm looking for a way to measure alias usage and cull unused aliases. Have you considered a stored procedure? I have but was hoping for something simpler like I do with dovecot deliver where you create a script that calls deliver after you do what you want for logging and then name your script in something like deliver_exec = script. Might be wrong with the names but thats more or less what takes place. I'd prefer to keep as much of this type of thing in the config files. It seems to be easier to quickly see what's up when there is a problem. I'll try the stored procedure if nothing more attractive turns up. Thank you, Bradley Giesbrecht
Re: virtual_alias_maps mysql
On Fri, Jan 29, 2010 at 9:19 AM, Bradley Giesbrecht wrote: > > On Jan 28, 2010, at 12:35 PM, Serge Fonville wrote: > >> Hi, >> >>> I using virtual_alias_maps with mysql for storage. Working fine. >>> >>> Does anyone have a suggestion on how to update a timestamp field in the >>> mysql table when postfix finds a virtual_alias_maps match? >>> >>> I'm looking for a way to measure alias usage and cull unused aliases. >> >> Have you considered a stored procedure? > > I have but was hoping for something simpler like I do with dovecot deliver > where you create a script that calls deliver after you do what you want for > logging and then name your script in something like deliver_exec = script. > > Might be wrong with the names but thats more or less what takes place. > > I'd prefer to keep as much of this type of thing in the config files. It > seems to be easier to quickly see what's up when there is a problem. > > I'll try the stored procedure if nothing more attractive turns up. > Well, possibly you could edit your transport to use a script and pass all the relevant variables to it, it can then also do an insert on your database. -- http://www.sergefonville.nl Convince Google!! They need to support Adsense over SSL https://www.google.com/adsense/support/bin/answer.py?hl=en&answer=10528 http://www.google.com/support/forum/p/AdSense/thread?tid=1884bc9310d9f923&hl=en
Re: virtual_alias_maps mysql
On Jan 29, 2010, at 12:29 AM, Serge Fonville wrote:
On Fri, Jan 29, 2010 at 9:19 AM, Bradley Giesbrecht
wrote:
On Jan 28, 2010, at 12:35 PM, Serge Fonville wrote:
Hi,
I using virtual_alias_maps with mysql for storage. Working fine.
Does anyone have a suggestion on how to update a timestamp field
in the
mysql table when postfix finds a virtual_alias_maps match?
I'm looking for a way to measure alias usage and cull unused
aliases.
Have you considered a stored procedure?
I have but was hoping for something simpler like I do with dovecot
deliver
where you create a script that calls deliver after you do what you
want for
logging and then name your script in something like deliver_exec =
script.
Might be wrong with the names but thats more or less what takes
place.
I'd prefer to keep as much of this type of thing in the config
files. It
seems to be easier to quickly see what's up when there is a problem.
I'll try the stored procedure if nothing more attractive turns up.
Well, possibly you could edit your transport to use a script and pass
all the relevant variables to it, it can then also do an insert on
your database.
I was kinda hoping something like this was possible.
Does anyone have an example of something like this?
Maybe add a filter to my relay in master.cf?
http://www.postfix.org/FILTER_README.html
filterunix - n n - 10 pipe
flags=Rq user=filter null_sender=
argv=/path/to/script -f ${sender} -- ${recipient}
relay unix - - n - 10 smtp
-o content_filter=filter:dummy
OR
smtp unix - - n - 10 smtp
-o content_filter=filter:dummy
As everyone probably notices other then setting up postfix with
dovecot and virtual users in mysql I don't know postfix that well.
It's been working real well for over a year.
Thanks for any help. This isn't crucial, I'd just like to be able to
view counts of message passing through aliased users.
// Brad
RE: virtual_alias_maps mysql
> > I have but was hoping for something simpler like I do with > > dovecot deliver where you create a script that calls deliver > > after you do what you want for logging and then name your > > script in something like deliver_exec = script. > > > > Might be wrong with the names but thats more or less what takes > > place. > > > > I'd prefer to keep as much of this type of thing in the config > > files. It seems to be easier to quickly see what's up when > > there is a problem. > > > > I'll try the stored procedure if nothing more attractive turns > > up. > > Well, possibly you could edit your transport to use a script and > pass all the relevant variables to it, it can then also do an > insert on your database. Or write a simple policy daemon. All necessary information is sent to a policy deamon which in turn can put data in a table. (I wrote something in PHP using pcntl because I don't know how write it in C or Perl. It writes data to a MYSQL table taken from the details sent by Postfix. Our mailflow is not as big as some here, but so far it's proven to be quite stable and it fulfills our needs.) -- Rob
Re: virtual_alias_maps mysql
On 1/29/2010 2:41 AM, Serge Fonville wrote: > On Thu, Jan 28, 2010 at 10:40 PM, Brian Evans - Postfix List > wrote: > >> On 1/28/2010 4:12 PM, Serge Fonville wrote: >> >> I using virtual_alias_maps with mysql for storage. Working fine. >> >> Does anyone have a suggestion on how to update a timestamp field in the >> mysql table when postfix finds a virtual_alias_maps match? >> >> I'm looking for a way to measure alias usage and cull unused aliases. >> >> >> > Have you considered a stored procedure? > > If you use a SELECT query, does it use CALL? > > This would be a stored function, not a procedure, to be called from a SELECT. A stored function *must* return a single result and cannot output a result set. This does not seem it would work for the OP because the query would always match from the Postfix point of view. Stored procedures in MySQL must be invoked by CALL.
Re: virtual_alias_maps mysql
On Fri, Jan 29, 2010 at 2:51 PM, Brian Evans - Postfix List wrote: > On 1/29/2010 2:41 AM, Serge Fonville wrote: >> On Thu, Jan 28, 2010 at 10:40 PM, Brian Evans - Postfix List >> wrote: >> >>> On 1/28/2010 4:12 PM, Serge Fonville wrote: >>> >>> I using virtual_alias_maps with mysql for storage. Working fine. >>> >>> Does anyone have a suggestion on how to update a timestamp field in the >>> mysql table when postfix finds a virtual_alias_maps match? >>> >>> I'm looking for a way to measure alias usage and cull unused aliases. >>> >>> >>> >> Have you considered a stored procedure? >> >> If you use a SELECT query, does it use CALL? >> >> > > This would be a stored function, not a procedure, to be called from a > SELECT. > A stored function *must* return a single result and cannot output a > result set. > This does not seem it would work for the OP because the query would > always match from the Postfix point of view. > > Stored procedures in MySQL must be invoked by CALL. > Hmmm... Makes sense. A stored function then would solve it? Regards, Serge Fonville -- http://www.sergefonville.nl Convince Google!! They need to support Adsense over SSL https://www.google.com/adsense/support/bin/answer.py?hl=en&answer=10528 http://www.google.com/support/forum/p/AdSense/thread?tid=1884bc9310d9f923&hl=en
virtual_alias_maps / mysql problem
Hello! i´m not quite sure if the problem is directly the virtual_alias_maps or something it interacts with, so to say. in main.cf i set virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf unverified_recipient_reject_code = 550 unknown_local_recipient_reject_code = 550 and in mysql-virtual.cf # the user name and password to log into the mysql server hosts = 127.0.0.1 user = mailcheck password = secretpassword dbname = mails table = virtual select_field = dest where_field = alias now, if i try to send to an address on the server that does not exist, it should refuse, right? unfortunately it, postfix just hands it over to dovecot, as if everything was fine =( I´m currently not completely sure, if the problem lies with the postfix-configuration or with the mysql-query (e.g. if it always returns "ok", even if the entry wasn´t found). But I´m currently not sure, how to test this. When i directly make the query in sql, it works fine (aka, it returns an empty result, if the mailaddress is not equal to one of the aliases). i also tried to change the mysql-virtual.cf so that it uses a query (query = SELECT dest FROM virtual WHERE alias = "%s";), but the behavior did not change. mind you, when there´s an error in this file, i can´t send any mails, so it seems to be used in some way or other. -_-; any hints would be appreciated =) best regards sil -- -- "Do you know what happens to a toad struck by lightning..? The same thing that happens to anything else..." -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
RE: virtual_alias_maps / mysql problem
First make sure that the domain you are sending to is set as a virtual mailbox domain. It sounds like you've already set the virtual transport to dovecot which is right. If you think mysql is the issue try making a virtual alias maps hash file. ***Sent via RoadSync® for Android™ -Original Message- From: lupin...@gmx.net Sent: Dec 11, 2011 1:21 PM To: postfix-users@postfix.org Subject: virtual_alias_maps / mysql problem Hello! i´m not quite sure if the problem is directly the virtual_alias_maps or something it interacts with, so to say. in main.cf i set virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf unverified_recipient_reject_code = 550 unknown_local_recipient_reject_code = 550 and in mysql-virtual.cf # the user name and password to log into the mysql server hosts = 127.0.0.1 user = mailcheck password = secretpassword dbname = mails table = virtual select_field = dest where_field = alias now, if i try to send to an address on the server that does not exist, it should refuse, right? unfortunately it, postfix just hands it over to dovecot, as if everything was fine =( I´m currently not completely sure, if the problem lies with the postfix-configuration or with the mysql-query (e.g. if it always returns "ok", even if the entry wasn´t found). But I´m currently not sure, how to test this. When i directly make the query in sql, it works fine (aka, it returns an empty result, if the mailaddress is not equal to one of the aliases). i also tried to change the mysql-virtual.cf so that it uses a query (query = SELECT dest FROM virtual WHERE alias = "%s";), but the behavior did not change. mind you, when there´s an error in this file, i can´t send any mails, so it seems to be used in some way or other. -_-; any hints would be appreciated =) best regards sil -- -- "Do you know what happens to a toad struck by lightning..? The same thing that happens to anything else..." -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
Re: virtual_alias_maps / mysql problem
Well a hash file would be the simplest thing to ensure that postfix is configured properly. I would have thought that all the information you need to see what is going on would be in the mail log and the mysql log. ***Sent via RoadSync® for Android™ -Original Message- From: lupin...@gmx.net Sent: Dec 11, 2011 2:19 PM To: postfix-users@postfix.org Subject: Re: RE: virtual_alias_maps / mysql problem thank you for you reply. virtual_mailbox_domains is set, as is virtual_transport. do you mean using a hash-file to test it or for permanent use? there are some 500 mail-users on the server, who change relatively often and who have each a number of aliases..i´d rather avoid using a hash file, especially because the mysql-query is supposed to work =) is there some handy way of testing, what postfix receives from this mysql-check? best regards sil Original-Nachricht > Datum: Sun, 11 Dec 2011 14:04:15 + > Von: James Day > An: "lupin...@gmx.net" , "postfix-users@postfix.org" > > Betreff: RE: virtual_alias_maps / mysql problem > First make sure that the domain you are sending to is set as a virtual > mailbox domain. It sounds like you've already set the virtual transport to > dovecot which is right. If you think mysql is the issue try making a virtual > alias maps hash file. > > ***Sent via RoadSync® for Android™ > > -Original Message- > From: lupin...@gmx.net > Sent: Dec 11, 2011 1:21 PM > To: postfix-users@postfix.org > Subject: virtual_alias_maps / mysql problem > > > > > Hello! > > i´m not quite sure if the problem is directly the virtual_alias_maps or > something it interacts with, so to say. > in main.cf i set > virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf > unverified_recipient_reject_code = 550 > unknown_local_recipient_reject_code = 550 > > and in mysql-virtual.cf > # the user name and password to log into the mysql server > hosts = 127.0.0.1 > user = mailcheck > password = secretpassword > dbname = mails > table = virtual > select_field = dest > where_field = alias > > now, if i try to send to an address on the server that does not exist, it > should refuse, right? unfortunately it, postfix just hands it over to > dovecot, as if everything was fine =( > > I´m currently not completely sure, if the problem lies with the > postfix-configuration or with the mysql-query (e.g. if it always returns > "ok", even > if the entry wasn´t found). > But I´m currently not sure, how to test this. When i directly make the > query in sql, it works fine (aka, it returns an empty result, if the > mailaddress is not equal to one of the aliases). > > i also tried to change the mysql-virtual.cf so that it uses a query (query > = SELECT dest FROM virtual WHERE alias = "%s";), but the behavior did not > change. mind you, when there´s an error in this file, i can´t send any > mails, so it seems to be used in some way or other. -_-; > > any hints would be appreciated =) > > best regards > sil > > > -- > -- > "Do you know what happens to a toad struck by lightning..? > The same thing that happens to anything else..." > -- > > Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir > belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de -- -- "Do you know what happens to a toad struck by lightning..? The same thing that happens to anything else..." -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
Re: virtual_alias_maps / mysql problem
On Sunday 11 December 2011 07:21:13 lupin...@gmx.net wrote: > i´m not quite sure if the problem is directly the > virtual_alias_maps or something it interacts with, so to say. in > main.cf i set > virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf 'postmap -q virtual@alias mysql:/etc/postfix/mysql-virtual.cf' lets you test how your lookups work. http://www.postfix.org/DATABASE_README.html#preparing > unverified_recipient_reject_code = 550 > unknown_local_recipient_reject_code = 550 Those do not appear to be relevant to this post nor issue. > and in mysql-virtual.cf > # the user name and password to log into the mysql server > hosts = 127.0.0.1 > user = mailcheck > password = secretpassword > dbname = mails > table = virtual > select_field = dest > where_field = alias This is deprecated query syntax. http://www.postfix.org/mysql_table.5.html http://www.postfix.org/MYSQL_README.html > now, if i try to send to an address on the server that does not > exist, it should refuse, right? unfortunately it, postfix just > hands it over to dovecot, as if everything was fine =( If you had showed us the logs of this along with the 'postconf -n' output, we could have explained why this happened. As it is, we can only guess. My guess from the problem description is that you are using a flawed testing method, perhaps sending from a client in mynetworks, thus being accepted and bounced. That's the way it is supposed to work for a client in mynetworks. > I´m currently not completely sure, if the problem lies with the > postfix-configuration or with the mysql-query (e.g. if it always > returns "ok", even if the entry wasn´t found). But I´m currently That would be wrong, see aforementioned DATABASE_README. > not sure, how to test this. When i directly make the query in sql, > it works fine (aka, it returns an empty result, if the mailaddress > is not equal to one of the aliases). This sounds right. > i also tried to change the mysql-virtual.cf so that it uses a query > (query = SELECT dest FROM virtual WHERE alias = "%s";), but the > behavior did not change. mind you, when there´s an error in this > file, i can´t send any mails, so it seems to be used in some way > or other. -_-; Possibly so! Impossible to guess, from here. > any hints would be appreciated =) http://www.postfix.org/DEBUG_README.html#mail * * * On Sunday 11 December 2011 08:04:15 James Day wrote: > First make sure that the domain you are sending to is set > as a virtual mailbox domain. This is possibly relevant, but misleading advice. > It sounds like you've already set the > virtual transport to dovecot which is right. No, the "right" thing is usually to set up virtual(8) to deliver properly before venturing out into any third-party software. > If you think mysql is the issue try making a > virtual alias maps hash file. This is good advice, essentially the same message as from the aforementioned DATABASE_README. -- Offlist mail to this address is discarded unless "/dev/rob0" or "not-spam" is in Subject: header
Re: virtual_alias_maps / mysql problem
Am 11.12.2011 15:18, schrieb lupin...@gmx.net: > thank you for you reply. > virtual_mailbox_domains is set, as is virtual_transport. > do you mean using a hash-file to test it or for permanent use? > there are some 500 mail-users on the server, who change relatively often and > who have each a number of aliases..i´d rather avoid using a hash file, > especially because the mysql-query is supposed to work =) > > is there some handy way of testing, what postfix receives from this > mysql-check? what about activate querylog in mysqld to look what really happens and c&p interesting queries into a mysql-shell to look at the results? signature.asc Description: OpenPGP digital signature
Re: virtual_alias_maps / mysql problem
thank you for the hint! i activated the query-log and the query is executed ok. i also checked it via postmap -q hutzenp...@domain.de mysql:/etc/postfix/mysql-virtual.cf (which correctly did not return anything) and postmap -q correctu...@domain.de mysql:/etc/postfix/mysql-virtual.cf which did return the correct entry, e.g. user169 so it seems mysql is not at fault. also, when i tested it with a hash-file, it sent "successfully" to an address that was not listed in said file. unfortunately, now i guess i´ll have to check any and all other config parameters that have anything to do with virtual delivery ^_^; here goes the postconf -n: broken_sasl_auth_clients = yes config_directory = /etc/postfix inet_interfaces = 192.168.12.7 127.0.0.1 mailbox_size_limit = 0 message_size_limit = 2048 mydestination = localhost mydomain = domain.de myhostname = mail.domain.de mynetworks = 192.168.12.0/24 127.0.0.0/8 myorigin = $mydomain relayhost = smtpd_recipient_restrictions = reject_unauth_pipelining, permit_mynetworks, permit_sasl_authenticated, reject_non_fqdn_recipient, reject_unknown_recipient_domain, reject_unauth_destination, permit smtpd_sasl_auth_enable = yes smtpd_sasl_local_domain = mail.domain.de smtpd_sasl_path = private/auth smtpd_sasl_security_options = noanonymous smtpd_sasl_tls_security_options = noanonymous smtpd_sasl_type = dovecot smtpd_tls_CAfile = /etc/certs/cert.pem smtpd_tls_cert_file = /etc/certs/cert.pem smtpd_tls_key_file = /etc/certs/key.pem smtpd_tls_received_header = no smtpd_use_tls = yes transport_maps = hash:/etc/postfix/transport unknown_local_recipient_reject_code = 550 unverified_recipient_reject_code = 550 virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf virtual_mailbox_domains = domain.de virtual_transport = dovecot transport_maps reads thus: domain.de : .domain.de : * smtp:192.168.12.8 (this is the external "firewall"-postfix-server) the mail.log reads thus: Dec 11 17:05:05 mehl postfix/smtpd[16897]: connect from unknown[192.168.12.1] Dec 11 17:05:05 mehl postfix/smtpd[16897]: DD60514A03F3: client=unknown[192.168.12.1], sasl_method=PLAIN, sasl_username=user169 Dec 11 17:05:05 mehl postfix/cleanup[16901]: DD60514A03F3: message-id=<4ee4d4b2.2020...@domain.de> Dec 11 17:05:06 mehl postfix/qmgr[16586]: DD60514A03F3: from=, size=858, nrcpt=1 (queue active) Dec 11 17:05:06 mehl postfix/smtpd[16897]: disconnect from unknown[192.168.12.1] Dec 11 17:05:06 mehl postfix/pipe[16902]: DD60514A03F3: to=, relay=dovecot, delay=0.32, delays=0.18/0/0/0.14, dsn=2.0.0, status=sent (delivered via dovecot service) Dec 11 17:05:06 mehl postfix/qmgr[16586]: DD60514A03F3: removed the address grmblash does not really exist ;-), when i send to an existing address, the only "difference" is that postfix/pipe has the correct target as to, e.g. user...@dmain.de thank you all for you hints, i hope this help shed some light on the problem. =) best regards sil Original-Nachricht > Datum: Sun, 11 Dec 2011 15:26:40 +0100 > Von: Reindl Harald > An: postfix-users@postfix.org > Betreff: Re: virtual_alias_maps / mysql problem > > > Am 11.12.2011 15:18, schrieb lupin...@gmx.net: > > thank you for you reply. > > virtual_mailbox_domains is set, as is virtual_transport. > > do you mean using a hash-file to test it or for permanent use? > > there are some 500 mail-users on the server, who change relatively often > and who have each a number of aliases..i´d rather avoid using a hash > file, especially because the mysql-query is supposed to work =) > > > > is there some handy way of testing, what postfix receives from this > mysql-check? > > what about activate querylog in mysqld to look what really happens and > c&p interesting queries into a mysql-shell to look at the results? > -- -- "Do you know what happens to a toad struck by lightning..? The same thing that happens to anything else..." -- NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie! Jetzt informieren: http://www.gmx.net/de/go/freephone -- -- "Do you know what happens to a toad struck by lightning..? The same thing that happens to anything else..." -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
RE: virtual_alias_maps / mysql problem
I think you need to be using virtual_mailbox_maps to create a list of valid recipients. Also I can see that dovecot has also accepted the message so you must have configured something like "allow_all_users=yes". From: owner-postfix-us...@postfix.org [owner-postfix-us...@postfix.org] On Behalf Of lupin...@gmx.net [lupin...@gmx.net] Sent: Sunday, December 11, 2011 4:31 PM To: postfix-users@postfix.org Subject: Re: virtual_alias_maps / mysql problem thank you for the hint! i activated the query-log and the query is executed ok. i also checked it via postmap -q hutzenp...@domain.de mysql:/etc/postfix/mysql-virtual.cf (which correctly did not return anything) and postmap -q correctu...@domain.de mysql:/etc/postfix/mysql-virtual.cf which did return the correct entry, e.g. user169 so it seems mysql is not at fault. also, when i tested it with a hash-file, it sent "successfully" to an address that was not listed in said file. unfortunately, now i guess i´ll have to check any and all other config parameters that have anything to do with virtual delivery ^_^; here goes the postconf -n: broken_sasl_auth_clients = yes config_directory = /etc/postfix inet_interfaces = 192.168.12.7 127.0.0.1 mailbox_size_limit = 0 message_size_limit = 2048 mydestination = localhost mydomain = domain.de myhostname = mail.domain.de mynetworks = 192.168.12.0/24 127.0.0.0/8 myorigin = $mydomain relayhost = smtpd_recipient_restrictions = reject_unauth_pipelining, permit_mynetworks, permit_sasl_authenticated, reject_non_fqdn_recipient, reject_unknown_recipient_domain, reject_unauth_destination, permit smtpd_sasl_auth_enable = yes smtpd_sasl_local_domain = mail.domain.de smtpd_sasl_path = private/auth smtpd_sasl_security_options = noanonymous smtpd_sasl_tls_security_options = noanonymous smtpd_sasl_type = dovecot smtpd_tls_CAfile = /etc/certs/cert.pem smtpd_tls_cert_file = /etc/certs/cert.pem smtpd_tls_key_file = /etc/certs/key.pem smtpd_tls_received_header = no smtpd_use_tls = yes transport_maps = hash:/etc/postfix/transport unknown_local_recipient_reject_code = 550 unverified_recipient_reject_code = 550 virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf virtual_mailbox_domains = domain.de virtual_transport = dovecot transport_maps reads thus: domain.de : .domain.de : * smtp:192.168.12.8 (this is the external "firewall"-postfix-server) the mail.log reads thus: Dec 11 17:05:05 mehl postfix/smtpd[16897]: connect from unknown[192.168.12.1] Dec 11 17:05:05 mehl postfix/smtpd[16897]: DD60514A03F3: client=unknown[192.168.12.1], sasl_method=PLAIN, sasl_username=user169 Dec 11 17:05:05 mehl postfix/cleanup[16901]: DD60514A03F3: message-id=<4ee4d4b2.2020...@domain.de> Dec 11 17:05:06 mehl postfix/qmgr[16586]: DD60514A03F3: from=, size=858, nrcpt=1 (queue active) Dec 11 17:05:06 mehl postfix/smtpd[16897]: disconnect from unknown[192.168.12.1] Dec 11 17:05:06 mehl postfix/pipe[16902]: DD60514A03F3: to=, relay=dovecot, delay=0.32, delays=0.18/0/0/0.14, dsn=2.0.0, status=sent (delivered via dovecot service) Dec 11 17:05:06 mehl postfix/qmgr[16586]: DD60514A03F3: removed the address grmblash does not really exist ;-), when i send to an existing address, the only "difference" is that postfix/pipe has the correct target as to, e.g. user...@dmain.de thank you all for you hints, i hope this help shed some light on the problem. =) best regards sil Original-Nachricht > Datum: Sun, 11 Dec 2011 15:26:40 +0100 > Von: Reindl Harald > An: postfix-users@postfix.org > Betreff: Re: virtual_alias_maps / mysql problem > > > Am 11.12.2011 15:18, schrieb lupin...@gmx.net: > > thank you for you reply. > > virtual_mailbox_domains is set, as is virtual_transport. > > do you mean using a hash-file to test it or for permanent use? > > there are some 500 mail-users on the server, who change relatively often > and who have each a number of aliases..i´d rather avoid using a hash > file, especially because the mysql-query is supposed to work =) > > > > is there some handy way of testing, what postfix receives from this > mysql-check? > > what about activate querylog in mysqld to look what really happens and > c&p interesting queries into a mysql-shell to look at the results? > -- -- "Do you know what happens to a toad struck by lightning..? The same thing that happens to anything else..." -- NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie! Jetzt informieren: http://www.gmx.net/de/go/freephone -- -- "Do you know what happens to a toad struck by lightning..? The same thing that happens to anything else..." -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
Re: RE: virtual_alias_maps / mysql problem
thank you for you reply. virtual_mailbox_domains is set, as is virtual_transport. do you mean using a hash-file to test it or for permanent use? there are some 500 mail-users on the server, who change relatively often and who have each a number of aliases..i´d rather avoid using a hash file, especially because the mysql-query is supposed to work =) is there some handy way of testing, what postfix receives from this mysql-check? best regards sil Original-Nachricht > Datum: Sun, 11 Dec 2011 14:04:15 + > Von: James Day > An: "lupin...@gmx.net" , "postfix-users@postfix.org" > > Betreff: RE: virtual_alias_maps / mysql problem > First make sure that the domain you are sending to is set as a virtual > mailbox domain. It sounds like you've already set the virtual transport to > dovecot which is right. If you think mysql is the issue try making a virtual > alias maps hash file. > > ***Sent via RoadSync® for Android™ > > -Original Message- > From: lupin...@gmx.net > Sent: Dec 11, 2011 1:21 PM > To: postfix-users@postfix.org > Subject: virtual_alias_maps / mysql problem > > > > > Hello! > > i´m not quite sure if the problem is directly the virtual_alias_maps or > something it interacts with, so to say. > in main.cf i set > virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf > unverified_recipient_reject_code = 550 > unknown_local_recipient_reject_code = 550 > > and in mysql-virtual.cf > # the user name and password to log into the mysql server > hosts = 127.0.0.1 > user = mailcheck > password = secretpassword > dbname = mails > table = virtual > select_field = dest > where_field = alias > > now, if i try to send to an address on the server that does not exist, it > should refuse, right? unfortunately it, postfix just hands it over to > dovecot, as if everything was fine =( > > I´m currently not completely sure, if the problem lies with the > postfix-configuration or with the mysql-query (e.g. if it always returns > "ok", even > if the entry wasn´t found). > But I´m currently not sure, how to test this. When i directly make the > query in sql, it works fine (aka, it returns an empty result, if the > mailaddress is not equal to one of the aliases). > > i also tried to change the mysql-virtual.cf so that it uses a query (query > = SELECT dest FROM virtual WHERE alias = "%s";), but the behavior did not > change. mind you, when there´s an error in this file, i can´t send any > mails, so it seems to be used in some way or other. -_-; > > any hints would be appreciated =) > > best regards > sil > > > -- > -- > "Do you know what happens to a toad struck by lightning..? > The same thing that happens to anything else..." > -- > > Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir > belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de -- -- "Do you know what happens to a toad struck by lightning..? The same thing that happens to anything else..." -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
Re: RE: virtual_alias_maps / mysql problem
Ah, thank you, that led me into the exact right direction! =) i changed the way dovecot checks, if the user exists, and now it works fine. ^_^; just for curiosity, what exactly would i need to feed to the virtual_maibox_maps or rather, what does it expect to get from whatever backend put there? the virtual-readme gives the example i...@example.comexample.com/info is "example.com/info" the actual directory where the mails are supposed to end up relative to some other directory? or did i read that wrong? best regards and thanks again =) sil Original-Nachricht > Datum: Sun, 11 Dec 2011 21:58:55 + > Von: James Day > An: "lupin...@gmx.net" , "postfix-users@postfix.org" > > Betreff: RE: virtual_alias_maps / mysql problem > I think you need to be using virtual_mailbox_maps to create a list of > valid recipients. > > Also I can see that dovecot has also accepted the message so you must have > configured something like "allow_all_users=yes". > > > From: owner-postfix-us...@postfix.org [owner-postfix-us...@postfix.org] On > Behalf Of lupin...@gmx.net [lupin...@gmx.net] > Sent: Sunday, December 11, 2011 4:31 PM > To: postfix-users@postfix.org > Subject: Re: virtual_alias_maps / mysql problem > > thank you for the hint! > i activated the query-log and the query is executed ok. i also checked it > via > postmap -q hutzenp...@domain.de mysql:/etc/postfix/mysql-virtual.cf > (which correctly did not return anything) > and > postmap -q correctu...@domain.de mysql:/etc/postfix/mysql-virtual.cf > which did return the correct entry, e.g. user169 > so it seems mysql is not at fault. > > also, when i tested it with a hash-file, it sent "successfully" to an > address that was not listed in said file. > > unfortunately, now i guess i´ll have to check any and all other config > parameters that have anything to do with virtual delivery ^_^; > > here goes the postconf -n: > broken_sasl_auth_clients = yes > config_directory = /etc/postfix > inet_interfaces = 192.168.12.7 127.0.0.1 > mailbox_size_limit = 0 > message_size_limit = 2048 > mydestination = localhost > mydomain = domain.de > myhostname = mail.domain.de > mynetworks = 192.168.12.0/24 127.0.0.0/8 > myorigin = $mydomain > relayhost = > smtpd_recipient_restrictions = reject_unauth_pipelining, > permit_mynetworks, permit_sasl_authenticated, reject_non_fqdn_recipient, > reject_unknown_recipient_domain, reject_unauth_destination, permit > smtpd_sasl_auth_enable = yes > smtpd_sasl_local_domain = mail.domain.de > smtpd_sasl_path = private/auth > smtpd_sasl_security_options = noanonymous > smtpd_sasl_tls_security_options = noanonymous > smtpd_sasl_type = dovecot > smtpd_tls_CAfile = /etc/certs/cert.pem > smtpd_tls_cert_file = /etc/certs/cert.pem > smtpd_tls_key_file = /etc/certs/key.pem > smtpd_tls_received_header = no > smtpd_use_tls = yes > transport_maps = hash:/etc/postfix/transport > unknown_local_recipient_reject_code = 550 > unverified_recipient_reject_code = 550 > virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf > virtual_mailbox_domains = domain.de > virtual_transport = dovecot > > transport_maps reads thus: > domain.de : > .domain.de : > * smtp:192.168.12.8 (this is the external "firewall"-postfix-server) > > the mail.log reads thus: > Dec 11 17:05:05 mehl postfix/smtpd[16897]: connect from > unknown[192.168.12.1] > Dec 11 17:05:05 mehl postfix/smtpd[16897]: DD60514A03F3: > client=unknown[192.168.12.1], sasl_method=PLAIN, sasl_username=user169 > Dec 11 17:05:05 mehl postfix/cleanup[16901]: DD60514A03F3: > message-id=<4ee4d4b2.2020...@domain.de> > Dec 11 17:05:06 mehl postfix/qmgr[16586]: DD60514A03F3: > from=, size=858, nrcpt=1 (queue active) > Dec 11 17:05:06 mehl postfix/smtpd[16897]: disconnect from > unknown[192.168.12.1] > Dec 11 17:05:06 mehl postfix/pipe[16902]: DD60514A03F3: > to=, relay=dovecot, delay=0.32, delays=0.18/0/0/0.14, > dsn=2.0.0, > status=sent (delivered via dovecot service) > Dec 11 17:05:06 mehl postfix/qmgr[16586]: DD60514A03F3: removed > > the address grmblash does not really exist ;-), when i send to an existing > address, the only "difference" is that postfix/pipe has the correct target > as to, e.g. user...@dmain.de > > thank you all for you hints, i hope this help shed some light on the > problem. =) > > best regards > sil > > Original-Nachricht > > Datum: Sun, 11 Dec 2011 15:26:40 +0100 > > Von: Reindl Harald > > An: postfix-users@postfix.org > > Betreff: Re: virtual_alias_maps / mysql problem > > > > >
