Set postfix to log undelivered mail to MySQL
Hello experts,
I'm currently finishing a email marketing platform in Ruby on Rails, and, at the end of the development, I've stumbled in a problem. I'm using Debian Etch, Postfix and MYSQL 5.0, on a VPS.
The system works fine when sending the emails, but I have no idea how to "catch" the undelivered email returned to sender, and log to my database, to display to the client the bounce %. Is there any configuration in Postfix that will allow me to do so? I was thinking about parsing the postfix maillog for errors, but that's way too hacky and unreliable.
Also, using "sendmail" will always return true, even if it's a bogus email? ( ie@example.com )
If is there a way to return the current code from the sent email?
Also, any other good info I should know? Like BSMTP better for email marketing than smtp in a loop?
Thanks!
I'm currently finishing a email marketing platform in Ruby on Rails, and, at the end of the development, I've stumbled in a problem. I'm using Debian Etch, Postfix and MYSQL 5.0, on a VPS.
The system works fine when sending the emails, but I have no idea how to "catch" the undelivered email returned to sender, and log to my database, to display to the client the bounce %. Is there any configuration in Postfix that will allow me to do so? I was thinking about parsing the postfix maillog for errors, but that's way too hacky and unreliable.
Also, using "sendmail" will always return true, even if it's a bogus email? ( ie@example.com )
If is there a way to return the current code from the sent email?
Also, any other good info I should know? Like BSMTP better for email marketing than smtp in a loop?
Thanks!
ASKER
Increasing points
use procmail to filter your mail the way you like and do what you want with it.
ASKER
But how to accomplish that? I don't have that much experience with Linux environment
> Is there any configuration in Postfix that will allow me to do so?
Unfortunately, no.
> I was thinking about parsing the postfix maillog for errors, but that's way too hacky and unreliable.
Why do you think it is unreliable? In my opinion it's the best way.
Other way to handle such undeliverable emails, is monitoring your sender's mailbox (and catch delivery status notification emails - for exmaple with procmail - as small_student already wrote).
Such rule could be for example:
:0:
* ^FROM_MAILER
* ^Subject: Undelivered Mail Returned to Sender
|/path/to/some/script
Where /path/to/some/script is some script you can write to add some informations to your mysql database (for example failed destination address, subject, or whatever you want - probably you will have to use formail to get such informations). If you are going to use above example, make sure, that subject I wrote is accurate in your situation.
Unfortunately, no.
> I was thinking about parsing the postfix maillog for errors, but that's way too hacky and unreliable.
Why do you think it is unreliable? In my opinion it's the best way.
Other way to handle such undeliverable emails, is monitoring your sender's mailbox (and catch delivery status notification emails - for exmaple with procmail - as small_student already wrote).
Such rule could be for example:
:0:
* ^FROM_MAILER
* ^Subject: Undelivered Mail Returned to Sender
|/path/to/some/script
Where /path/to/some/script is some script you can write to add some informations to your mysql database (for example failed destination address, subject, or whatever you want - probably you will have to use formail to get such informations). If you are going to use above example, make sure, that subject I wrote is accurate in your situation.
ASKER
hey oklit, thanks for the explanation
is it ok to use something like this:
:0:
* ^TO_bounce-([^\@]+)@domain
|ruby /var/www/model/process.rb class.method
? What I haven't figure out yet how to pass in the email (like match the address between () and lend it to the ruby script)
You said formail, so doing
|formail -x To: |ruby ruby /var/www/model/process.rb class.method
is correct? I have no experience in this, I use regex extensively, but I just don't seem to understand how the "args" passing happens when using procmail or the pipe > |
is it ok to use something like this:
:0:
* ^TO_bounce-([^\@]+)@domain
|ruby /var/www/model/process.rb class.method
? What I haven't figure out yet how to pass in the email (like match the address between () and lend it to the ruby script)
You said formail, so doing
|formail -x To: |ruby ruby /var/www/model/process.rb class.method
is correct? I have no experience in this, I use regex extensively, but I just don't seem to understand how the "args" passing happens when using procmail or the pipe > |
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
alright, great, that clears things for me now. but now I'm having another issue, related with this problem.
the
mailbox_command = /usr/bin/procmail -a "$EXTENSION" DEFAULT=/var/mail/ MAILDIR=/var/mail
isn't being called before the delivery
"550 5.1.1 <bounce-123132-1232131@dom
i've added bounce to the /etc/aliases, but didn't seem to work (even executed the newaliases).
am I missing any wildcard?
the
mailbox_command = /usr/bin/procmail -a "$EXTENSION" DEFAULT=/var/mail/ MAILDIR=/var/mail
isn't being called before the delivery
"550 5.1.1 <bounce-123132-1232131@dom
i've added bounce to the /etc/aliases, but didn't seem to work (even executed the newaliases).
am I missing any wildcard?
Rejecting unknown user is quite normal, but what is strange for me is that you wrote, that this recipient is rejected even after adding proper alias.
Was this alias exact rejected address? Is domain.com your local (in postfix meaning) domain? Post "postconf -n" output here.
What was exact line which you added to /etc/aliases?
Was this alias exact rejected address? Is domain.com your local (in postfix meaning) domain? Post "postconf -n" output here.
What was exact line which you added to /etc/aliases?
ASKER
ive added
bounce: root
to /etc/aliases
Yes, postfix is my local service. The content of postconf -n attached. Changed my domain name to "domain.com"
If I email from the outside (like Gmail) to the account bounce@domain.com, then it works, and the mailbox_commands get called along with the script, changing the
* ^To.*bounce-([^\@]+)@domai
to
* ^To.*bounce([^\@]*)@domain
But that's not the intended syntax I want, of course, just did to see if it was working.
bounce: root
to /etc/aliases
Yes, postfix is my local service. The content of postconf -n attached. Changed my domain name to "domain.com"
If I email from the outside (like Gmail) to the account bounce@domain.com, then it works, and the mailbox_commands get called along with the script, changing the
* ^To.*bounce-([^\@]+)@domai
to
* ^To.*bounce([^\@]*)@domain
But that's not the intended syntax I want, of course, just did to see if it was working.
alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases
append_dot_mydomain = no
biff = no
config_directory = /etc/postfix
inet_interfaces = "MY EXTERNAL IP"
mailbox_command = /usr/bin/procmail -a "$EXTENSION" DEFAULT=/var/mail/ MAILDIR=/var/mail
mailbox_size_limit = 0
masquerade_domains = domain.com
mydestination = /etc/postfix/virtual/domains, localhost
myhostname = domain.com
mynetworks = "MY EXTERNAL IP" 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
myorigin = /etc/mailname
readme_directory = no
recipient_delimiter = +
relayhost =
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
smtpd_banner = $myhostname ESMTP
smtpd_helo_required = yes
smtpd_recipient_restrictions = reject_non_fqdn_sender,reject_unknown_sender_domain,permit_sasl_authenticated,permit_mynetworks,reject_unauth_destination,check_policy_service unix:private/policy
smtpd_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
smtpd_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtpd_use_tls = yes
strict_rfc821_envelopes = yes
Hm.. postfix behaves properly - it doesn't accept emails for unknown user - bounce-whatever here. You alias is "bounce", not "bounce-whatever".
What exactly you want to achieve? Do you want to accept all emails addressed to bounce-whatever, without need to create aliases for all possible bounce.* addresses?
What exactly you want to achieve? Do you want to accept all emails addressed to bounce-whatever, without need to create aliases for all possible bounce.* addresses?
ASKER
yup, that's exactly what I need. I need the mailbox_command to be triggered to any incoming addresses that start with bounce-
so bounce-123-2321@domain.com
I'm not sure if I can use regex or wildcards in /etc/aliases, didn't try to, because my other hosted sites uses the same postfix for sending alerts, so I can't be fooling around with the settings while the other is functioning.
so bounce-123-2321@domain.com
I'm not sure if I can use regex or wildcards in /etc/aliases, didn't try to, because my other hosted sites uses the same postfix for sending alerts, so I can't be fooling around with the settings while the other is functioning.
Oh.. ok, apparently I didn't understand your first post :)
You cannot do it with aliases, but you can do it without it :)
As you wrote about aliases, I assume, that your want to do it for your local domain, not virtual ones.
Add to your main.cf:
local_recipient_maps = proxy:unix:passwd.byname $alias_maps regexp:/path/to/bounce_map
Then create /path/to/bounce_maps file with below line:
/^bounce-.*@domain\.com/ root
Then run "postfix reload". Now your postfix should accept all emails addressed to bounce-whatever@domain.com
You cannot do it with aliases, but you can do it without it :)
As you wrote about aliases, I assume, that your want to do it for your local domain, not virtual ones.
Add to your main.cf:
local_recipient_maps = proxy:unix:passwd.byname $alias_maps regexp:/path/to/bounce_map
Then create /path/to/bounce_maps file with below line:
/^bounce-.*@domain\.com/ root
Then run "postfix reload". Now your postfix should accept all emails addressed to bounce-whatever@domain.com
ASKER
hey oklit, thanks for your patient for going through this all. no wonder you are a guru ;)
below is the last log after the modification using the local maps. I've sent an email from the outside, and it didn't delivered or called mailbox_command =/
then it sent the undelivered email to my outside email. I don't want a catch all because it will certainly flood with spam from the outside, that's why I'm trying to setup a filter only for bounce-(.*)@domain.com
you mentioned virtual, would that fit my needs? how does a catch all account works? maybe it's the same mechanism?
below is the last log after the modification using the local maps. I've sent an email from the outside, and it didn't delivered or called mailbox_command =/
then it sent the undelivered email to my outside email. I don't want a catch all because it will certainly flood with spam from the outside, that's why I'm trying to setup a filter only for bounce-(.*)@domain.com
you mentioned virtual, would that fit my needs? how does a catch all account works? maybe it's the same mechanism?
Jun 16 04:54:09 domain postfix/local[14034]: 5B36F1605E: to=<bounce-324234-234523@domain.com>, relay=local, delay=0.42, delays=0.36/0.01/0/0.05, dsn=5.1.1, status=bounced (unknown user: "bounce-324234-234523")
The returned email is as
Final-Recipient: rfc822; bounce-324234-234523@domain.com
Original-Recipient: rfc822;bounce-324234-234523@domain.com
Action: failed
Status: 5.1.1
Diagnostic-Code: X-Postfix; unknown user: "bounce-324234-234523"
Hm... it is really strange - I checked my solution before posting and it worked as expected. Can you please post here output of "postconf -n" again, as well as your new maps file?
ASKER
The postconf -n added below, the /etc/postfix/bounce_maps as following:
/^bounce\-([^\@]{,17})@\.c
if there would be any way to call mailbox_command before the delivery, would be perfect, since postfix is currently calling it after checking for the users. after mailbox_command is called, is there any way to dispose the email? or is it done automatically?
/^bounce\-([^\@]{,17})@\.c
if there would be any way to call mailbox_command before the delivery, would be perfect, since postfix is currently calling it after checking for the users. after mailbox_command is called, is there any way to dispose the email? or is it done automatically?
alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases
append_dot_mydomain = no
biff = no
config_directory = /etc/postfix
inet_interfaces = 0.0.0.0
local_recipient_maps = proxy:unix:passwd.byname $alias_maps regexp:/etc/postfix/bounce_maps
mailbox_command = /usr/bin/procmail -a "$EXTENSION" DEFAULT=/var/mail/ MAILDIR=/var/mail
mailbox_size_limit = 0
masquerade_domains = domain.com
mydestination = domain.com, localhost
myhostname = domain.com
mynetworks = 0.0.0.0 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
myorigin = /etc/mailname
readme_directory = no
recipient_delimiter = +
relayhost =
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
smtpd_banner = $myhostname ESMTP
smtpd_helo_required = yes
smtpd_recipient_restrictions = reject_non_fqdn_sender,reject_unknown_sender_domain,permit_sasl_authenticated,permit_mynetworks,reject_unauth_destination,check_policy_service unix:private/policy
smtpd_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
smtpd_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtpd_use_tls = yes
strict_rfc821_envelopes = yes
ASKER
the bounce_maps is
/^bounce\-([^\@]{,17})@dom
was editing the domain name, and forgot to add "domain" there
/^bounce\-([^\@]{,17})@dom
was editing the domain name, and forgot to add "domain" there
Try pcre:/etc/postfix/bounce_m
Make sure, that your postfix supports pcre table type by running "postconf -m" command.
Make sure, that your postfix supports pcre table type by running "postconf -m" command.
ASKER
My postfix didnt had a pcre extension, so I apt-get'd postfix-pcre. installed, and postconf -m is showing it as supported.
changed from regexp: to pcre: same error... I even tried a really simple regex
/@domain\.com$/ root
didn't work =O
since it's not starting with ^, it should match anything (like a catch all) addresses to domain.com... I'm kinda lost now ;P
changed from regexp: to pcre: same error... I even tried a really simple regex
/@domain\.com$/ root
didn't work =O
since it's not starting with ^, it should match anything (like a catch all) addresses to domain.com... I'm kinda lost now ;P
I have to admit, that I'm a little bit confused also :) Please give me some time to test it - I will try to reproduce your errors. One more question - which postfix version are you using? (postconf mail_version)
ASKER
hey oklit, thanks for your patience. I wish I could use more points ;)
mail_version = 2.5.5
seems to be the lastest on etch, even though being released on Sep 2008 ;P
should I manually upgrade to something else?
mail_version = 2.5.5
seems to be the lastest on etch, even though being released on Sep 2008 ;P
should I manually upgrade to something else?
ASKER
Ok, the procmail thing is fixed, but I'm not able to use a wildcard for the user account.
I need to create the user bounce- that accepts bounce-([a-f0-9]{8})-([a-f
I need to create the user bounce- that accepts bounce-([a-f0-9]{8})-([a-f
ASKER
can I use virtual_alias_maps = regexp:/etc/postfix/virtua
ASKER
Provided the solution with all additional info needed to understand how to enrol with it
ASKER
I've made it putting to main.cf as
virtual_alias_maps = pcre:/etc/postfix/virtual
and inside /etc/postfix/virtual as
/bounce-([a-f0-9]{8})-([a-
and mailbox_command as
/usr/bin/procmail -a "$EXTENSION" DEFAULT=/var/mail/ MAILDIR=/var/mail
and inside the procmailrc as
FROM=`formail -rt -xTo:`
:0:
* ^To.*bounce-([^\@]+)@zaqar
|/usr/bin/ruby /var/www/_process.rb ${FROM}
virtual_alias_maps = pcre:/etc/postfix/virtual
and inside /etc/postfix/virtual as
/bounce-([a-f0-9]{8})-([a-
and mailbox_command as
/usr/bin/procmail -a "$EXTENSION" DEFAULT=/var/mail/ MAILDIR=/var/mail
and inside the procmailrc as
FROM=`formail -rt -xTo:`
:0:
* ^To.*bounce-([^\@]+)@zaqar
|/usr/bin/ruby /var/www/_process.rb ${FROM}
Sorry for leaving you alone for such long time :( I got new job, and I'm quite busy last days.
Anyway - glad you have this solved finally.
Anyway - glad you have this solved finally.
ASKER