How can I send mail or HTTP request from database?

This question happens every now and then in one of PostgreSQL support places.

Whenever it happens, I just suggest to not try, as interacting with outside world from database can be problematic, and instead use LISTEN/NOTIFY.

But it occurred to me, that while I know how to do it, I don't think I actually did it. It being: handle listen/notify in real life code. So let's try. I will not be writing actual email sending or http requesting, but will make sure that my program will get, from database, information when NOTIFY happens. How hard could it be?

Continue reading How can I send mail or HTTP request from database?

How to send mail from database?

Similar question has been asked many times on mailing lists and on IRC. Sometimes it's not mail sending, but file/directory creation, or something else that generally requires some interaction with “world outside of database".

Can it be done? Sure. How, then?

Continue reading How to send mail from database?

Faking “From ” header with procmail/formail

I have an unusual mail setup.

My company mails are handled by gmail, and on my account there, I setup forwarding to my own mail account on my own server. Where I read the mails, and respond.

So, when someone (let's say “president@whitehouse.gov") sends me an email to my company email (let's say “depesz@company.example.com"), it arrives to gmail, where it gets forwarded to my real account ( let's say “real@depesz.com" ).

So far so good. The problem is that gmail, when forwarding mail modifies return-path, and thus my local SMTP server changes “From " pseudo-header from normal “From president@whitehouse.gov some date" to “From depesz+some_bullshit_gmail_info@company.example.com some date".

This is bad, because it destroys procmail logs, which use value from “From " to log information about who sent the email, and instead of nice and readable:

From president@whitehouse.gov Wed Sep 22 00:10:36 2010
 Subject: test Wed Sep 22 00:10:30 CEST 2010
   Folder: /somewhere/not/important/new/23423     4870

I get utterly useless:

From depesz+some_bullshit_gmail_info@company.example.com Wed Sep 22 00:10:36 2010
 Subject: test Wed Sep 22 00:10:30 CEST 2010
   Folder: /somewhere/not/important/new/23423     4870

I tried to fix the problem using formail, but apparently, when you do something like this:

:0 fhw
| formail -I "From $REAL_EMAIL"

in procmail.rc – it all works fine, but the “From " line is generated at the end of headers, which is pretty dumb, as it should be leading header.

Tried various stuff to solve the problem, but finally found one that really works. Here it goes in case someone in future will need it:

ENVELOPE_FROM=`formail "-xFrom "`
 
:0
* ENVELOPE_FROM ?? ^depesz\+.*@company.example.com
{
    MAIL_FROM=`formail -xFrom: | perl -pe 's/.*?(\\S+@\\S+).*/\$1/;s/^<//;s/>\$//'`
    RESTORED_FROM=`echo "From $MAIL_FROM $( echo "$ENVELOPE_FROM" | sed 's/^[^[:space:]]* //' )"`
 
    :0 fhw
    | ( echo "$RESTORED_FROM"; formail -R "From " "X-Old_From_:" )
}

Important – this is at the beginning of procmailrc!

What it does?

First line: ENVELOPE_FROM=`formail "-xFrom "` gets current value of “From " header, and puts it to ENVELOPE_FROM variable.

Now, with:

:0
* ENVELOPE_FROM ?? ^depesz\+.*@company.example.com
{
    ...
}

I check if the ENVELOPE_FROM is the one that is broken (after all, someone else might mail me directly), and if yes – I run what's in side of the { block }.

Inside, I get value of From: (with colon) header, and extract from it email.

After running this line:

MAIL_FROM=`formail -xFrom: | perl -pe 's/.*?(\\S+@\\S+).*/\$1/;s/^<//;s/>\$//'`

Assuming mail had line like From: “Some important guy" <president@whitehouse.gov>, MAIL_FROM will contain “president@whitehouse.gov".

Next line:

RESTORED_FROM=`echo "From $MAIL_FROM $( echo "$ENVELOPE_FROM" | sed 's/^[^[:space:]]* //' )"`

builds new value of “From " header, using extracted email, and timestamp (after first space) from original “From “.

So, to wrap with example. Assuming we have email with headers like:

From depesz+some_bullshit_gmail_info@company.example.com Wed Sep 22 00:10:36 2010
Subject: test Wed Sep 22 00:10:30 CEST 2010
From: "Some important guy" <president@whitehouse.gov>

After the RESTORED_FROM line, we will have following values in variables:

ENVELOPE_FROM="depesz+some_bullshit_gmail_info@company.example.com Wed Sep 22 00:10:36 2010"
MAIL_FROM="president@whitehouse.gov"
RESTORED_FROM="From president@whitehouse.gov Wed Sep 22 00:10:36 2010"

Then goes last part:

:0 fhw
| ( echo "$RESTORED_FROM"; formail -R "From " "X-Old_From_:" )

Which, passed mail headers to command, and treats it as filter.

The command does:

  1. prints new “From " header
  2. passes mail headers through formail, which renames “From " header into “X-Old_From_:" header

The trick is that the print happens before formail even will get the headers, so the new, fixed “From " will be returned to procmail before rest of headers, as returned by formail.

Effect: everything works, and logged From is now much more sensible.

It would be even better if gmail would include original envelope from in the headers, but it doesn't, so I have to take the address from “From:" (which not always is good idea, but at the very least – it's much better than getting all mails with the same “From “.

Anyone knows better/easier approach?