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?

How to group messages into chats?

My jabber server had the feature, that it logs all messages that got sent through it.

This is pretty cool, and useful. And now, i got asked to use it to create list of conversations.

What exactly is this? Whenever I send (or receive) something there is record in database with information about which local user, communication type (send/recv), correspondent, when it happened, and what is the body of message.

And based on this, we want to list messages into chats. How?

Continue reading How to group messages into chats?

Tips N’ Tricks – using GNU Screen as shell

I'm quite often doing stuff on remote machines, and quite frequently I start some long-running job, when I remember that I didn't ran it via screen – so it will break, if my network connection will die.

Is there any sane way to start screen automatically? YES.

Continue reading Tips N' Tricks – using GNU Screen as shell

Waiting for 9.1 – concat, concat_ws, right, left, reverse

On 24th of August, Takahiro Itagaki committed patch:

Log Message:
-----------
Add string functions: concat(), concat_ws(), left(), right(), and reverse().
 
Pavel Stehule, reviewed by me.

Continue reading Waiting for 9.1 – concat, concat_ws, right, left, reverse

How to manage changes to your database?

Every now and then somebody asks how to make diff of database schemata.

Usual background is like: we have production database, and development database, and we want to see what is different on development to be able to change production in the same way.

Personally I think that such approach is inherently flawed. Why?

Continue reading How to manage changes to your database?

Waiting for 9.1 – Recognize functional dependency on primary keys.

Yesterday (August, 7th), Tom Lane committed:

Log Message:
-----------
Recognize functional dependency on primary keys.  This allows a table's
other columns to be referenced without listing them in GROUP BY, so long as
the primary key column(s) are listed in GROUP BY.
 
Eventually we should also allow functional dependency on a UNIQUE constraint
when the columns are marked NOT NULL, but that has to wait until NOT NULL
constraints are represented in pg_constraint, because we need to have
pg_constraint OIDs for all the conditions needed to ensure functional
dependency.
 
Peter Eisentraut, reviewed by Alex Hunsaker and Tom Lane

Continue reading Waiting for 9.1 – Recognize functional dependency on primary keys.

Waiting for 9.1 – Reduced lock levels for ALTER TABLE

On 28th of July, Simon Riggs committed patch which:

Log Message:
-----------
Reduce lock levels of CREATE TRIGGER and some ALTER TABLE, CREATE RULE actions.
Avoid hard-coding lockmode used for many altering DDL commands, allowing easier
future changes of lock levels. Implementation of initial analysis on DDL
sub-commands, so that many lock levels are now at ShareUpdateExclusiveLock or
ShareRowExclusiveLock, allowing certain DDL not to block reads/writes.
First of number of planned changes in this area; additional docs required
when full project complete.

Continue reading Waiting for 9.1 – Reduced lock levels for ALTER TABLE