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!
LVL 1
Caesar2kAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Caesar2kAuthor Commented:
Increasing points
0
Caesar2kAuthor Commented:
Increasing points
0
Monis MontherSystem ArchitectCommented:
use procmail to filter your mail the way you like and do what you want with it.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Caesar2kAuthor Commented:
But how to accomplish that? I don't have that much experience with Linux environment
0
Maciej SsysadminCommented:
> 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.
0
Caesar2kAuthor Commented:
hey oklit, thanks for the explanation
is it ok to use something like this:
:0:
* ^TO_bounce-([^\@]+)@domain.com
|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 > |
0
Maciej SsysadminCommented:
If you want to use some values from headers, you will probably have to use formail application to get them of the email.

^FROM_MAILER keyword, which I used in my example is special keyword - it means (from man procmailrc - procmail version 3.22): (^(((Resent-)?(From|Sender)|X-Envelope-From):|>?From)([^>]*[^(.%@a-z0-9])?(Post(ma(st(er)?|n)|office)|(send)?Mail(er)?|daemon|mmdf|n?uucp|ops|r(esponse|oot)|(bbs\.)?smtp(error)?|s(erv(ices?|er)|ystem)|A(dmin(istrator)?|MMGR))(([^).!:a-z0-9][-_a-z0-9]*)?[%@>\t ][^<)]*(\(.*\).*)?)?$([^>]|$))

I guess, that you want to use:
* ^To.*bounce-([^\@]+)@domain.com

Sample .procmailrc file with formail below. I don't know your script of course, so it won't probably recognize additional options I wrote at the end of the line, but I think you get the point.

SUBJECT and FROM variables will be set to _every_ email (but for most of them, they won't be used). Script will be fired up only for emails which met your criteria (in below example to all emails addressed to bounce-([^\@]+)@domain.com
SUBJECT=`formail -xSubject:`
FROM=`formail -rt -xTo:`
 
:0:
* ^To.*bounce-([^\@]+)@domain.com
|/path/to/ruby /var/www/model/process.rb class.method ${SUBJECT} ${FROM}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Caesar2kAuthor Commented:
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@domain.com>: Recipient address rejected; User unknown in local recipient table;"
i've added bounce to the /etc/aliases, but didn't seem to work (even executed the newaliases).
am I missing any wildcard?
0
Maciej SsysadminCommented:
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?
0
Caesar2kAuthor Commented:
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-([^\@]+)@domain.com
to
* ^To.*bounce([^\@]*)@domain.com

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

Open in new window

0
Maciej SsysadminCommented:
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?
0
Caesar2kAuthor Commented:
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 would trigger it (and not give the unknown user), bounce-999-666@domain.com would trigger it, etc
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.
0
Maciej SsysadminCommented:
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_maps

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 and deliver it to root account.
0
Caesar2kAuthor Commented:
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?
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"

Open in new window

0
Maciej SsysadminCommented:
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?
0
Caesar2kAuthor Commented:
The postconf -n added below, the /etc/postfix/bounce_maps as following:
/^bounce\-([^\@]{,17})@\.com/      root

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

Open in new window

0
Caesar2kAuthor Commented:
the bounce_maps is
/^bounce\-([^\@]{,17})@domain\.com/      root

was editing the domain name, and forgot to add "domain" there
0
Maciej SsysadminCommented:
Try pcre:/etc/postfix/bounce_maps instead of regexp:/etc/postfix/bounce_maps
Make sure, that your postfix supports pcre table type by running "postconf -m" command.
0
Caesar2kAuthor Commented:
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
0
Maciej SsysadminCommented:
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)
0
Caesar2kAuthor Commented:
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?
0
Caesar2kAuthor Commented:
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-f0-9]{8}) but I don't know how or where. One way is to use the catchall using @domain right? How can I create the user like the regex expression above?
0
Caesar2kAuthor Commented:
can I use virtual_alias_maps = regexp:/etc/postfix/virtual?
0
Caesar2kAuthor Commented:
Provided the solution with all additional info needed to understand how to enrol with it
0
Caesar2kAuthor Commented:
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-f0-9]{8})@domain.com/i bounce

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-([^\@]+)@zaqarmail.com
|/usr/bin/ruby /var/www/_process.rb ${FROM}

0
Maciej SsysadminCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux Distributions

From novice to tech pro — start learning today.