将postfix收发邮件记录到mysql

原来的方法修改地方太多，比较麻烦，最近看到一篇文章 http://blog.csdn.net/qidizi/article/details/8779769改动很少，将from to 合到一条日志里面，

原来的方法修改地方太多，比较麻烦，最近看到一篇文章 http://blog.csdn.net/qidizi/article/details/8779769改动很少，将from to 合到一条日志里面，甚是方便，先给记录下来，以便后期查看

修改方法跟原博主一样global/recipient_list.h 在 typedef struct RECIPIENT 里面加,我是加到24行的

typedef struct RECIPIENT {    char * letterInfo;      /* new add */    long    offset;			/* REC_TYPE_RCPT byte */    const char *dsn_orcpt;		/* DSN original recipient */    int     dsn_notify;			/* DSN notify flags */    const char *orig_addr;		/* null or original recipient */    const char *address;		/* complete address */    union {				/* Application specific. */	int     status;			/* SMTP client */	struct QMGR_QUEUE *queue;	/* Queue manager */	const char *addr_type;		/* DSN */    }       u;} RECIPIENT;

global/deliver_request.c postfix版本不同，代码有部分更新，我这变成了 318行，

recipient_list_add(&request->rcpt_list, rcpt_buf->offset, vstring_str(rcpt_buf->dsn_orcpt),    rcpt_buf->dsn_notify,  vstring_str(rcpt_buf->orig_addr), vstring_str(rcpt_buf->address));    request->rcpt_list.info[request->rcpt_list.len - 1].letterInfo = mystrdup(request->sender); /* add */    }

global/recipient_list.c 加到151行

void    recipient_list_add(RECIPIENT_LIST *list, long offset,			           const char *dsn_orcpt, int dsn_notify,			           const char *orig_rcpt, const char *rcpt){    int     new_avail;    if (list->len >= list->avail) {	new_avail = list->avail * 2;	list->info = (RECIPIENT *)	    myrealloc((void *) list->info, new_avail * sizeof(RECIPIENT));	list->avail = new_avail;    }    list->info[list->len].letterInfo = mystrdup("");    /* add */    list->info[list->len].orig_addr = mystrdup(orig_rcpt);    list->info[list->len].address = mystrdup(rcpt);    list->info[list->len].offset = offset;    list->info[list->len].dsn_orcpt = mystrdup(dsn_orcpt);    list->info[list->len].dsn_notify = dsn_notify;    if (list->variant == RCPT_LIST_INIT_STATUS)	list->info[list->len].u.status = 0;    else if (list->variant == RCPT_LIST_INIT_QUEUE)	list->info[list->len].u.queue = 0;    else if (list->variant == RCPT_LIST_INIT_ADDR)	list->info[list->len].u.addr_type = 0;    list->len++;}

global/log_adhoc.c 107行 加上要增加的from和recipient->letterInfo

vstring_sprintf(buf, "%s: from=<%s>, to=<%s>", id, recipient->letterInfo, recipient->address);    if (recipient->orig_addr && *recipient->orig_addr	&& strcasecmp_utf8(recipient->address, recipient->orig_addr) != 0)

到这就可以将from和to记录到一条里面，结果如下

2017-03-03 20:58:04 postfix/smtpd[1405]: disconnect from unknown[118.44.22.82] ehlo=1 mail=1 rcpt=1 data=1 quit=1 commands=52017-03-03 20:58:06 postfix/smtp[1409]: CF4D6322472D: from=<[email protected]>, to=<[email protected]>, relay=mx3.qq.com[184.105.206.85]:25, delay=2.5, delays=0.17/0.02/0.93/1.4, dsn=2.0.0, status=sent (250 Ok: queued as )

最终是要记录到数据库里面的，那么再新建一个putmysql.h

#include <mysql.h>#include <stdio.h> void put_mysql(const char *qid, const char *sender, const char *recipient, const char *relay, const char *status, const char *result){    MYSQL mysql;    MYSQL_RES *res;    int flagone;    int flagtwo;    char rid[12] = {0};    char buf[512]={0};    char bufs[512] = {0};    mysql_init(&mysql);    /*  postfix队列id是12位，如果收件MTA也是postfix        则会在result返回 250 2.0.0 Ok: queued as 987EA61049B1          这里面取最后12位保存为rid */    if (strlen(result)>12){        sprintf(rid,strncpy(rid,result+(strlen(result)-12),12));           }else{        sprintf(rid,result);           }        msg_info("%s",rid);    if(!mysql_real_connect(&mysql, "127.0.0.1", "root", "westhost", "test", 0, NULL, 0))    {        msg_info("Failed to connect to Mysql: %s", mysql_error(&mysql));    }    /*  如果qid已经存在，则是中继，更新记录即可 */    sprintf(buf,"select qid from test where (rid=/"%s/" and recipient=/"%s/") OR (qid=/"%s/" and recipient=/"%s/")",qid,recipient,qid,recipient);        flagone = mysql_query(&mysql,buf);    if(flagone)    {        msg_info("SELECT error: %s", mysql_error(&mysql));    } else {        res = mysql_store_result(&mysql);        if((unsigned long)mysql_num_rows(res)>0){            sprintf(bufs,"update test set rid=/"%s/", relay=/"%s/", stat=/"%s/", result=/"%s/" where (qid=/"%s/" and recipient=/"%s/") OR (rid=/"%s/" and recipient=/"%s/")",rid,relay,status,result,qid,recipient,qid,recipient);            flagtwo = mysql_query(&mysql,bufs);            if(flagtwo){                msg_info("UPDATE error: %s", mysql_error(&mysql));            }else {                msg_info("Retrieved %lu rows update sucessed", (unsigned long)mysql_num_rows(res));            }        } else{            /*  不存在qid的则表示是第一次发送，新增记录 */            sprintf(bufs,"insert into test (qid,sender,recipient,stime,relay,stat,result,rid) values (/"%s/",/"%s/",/"%s/",NOW(),/"%s/",/"%s/",replace(/"%s/",/"///"/",/"'/"),/"%s/")",qid,sender,recipient,relay,status,result,rid);            flagtwo = mysql_query(&mysql,bufs);            if(flagtwo){                msg_info("INSERT error: %s", mysql_error(&mysql));            }else{                msg_info("insert sucessed");            }                   }    }    mysql_close(&mysql);}

最后在global/log_adhoc.c #include <putmysql.h>最后在加上putmysql调用

msg_info("%s", vstring_str(buf));put_mysql(id, recipient->letterInfo, recipient->address, relay, status, dsn->reason);     /* add */

编译安装就可以了，最后大功告成，日志如下，数据库里面也有相应的记录了。

2017-03-03 21:23:58 postfix/qmgr[7797]: 08CE520449CD: from=<[email protected]>, size=1695, nrcpt=1 (queue active)2017-03-03 21:24:00 postfix/smtp[7804]: 08CE520449CD: from=<[email protected]>, to=<[email protected]>, relay=mx3.qq.com[184.105.206.85]:25, delay=2.8, delays=0.19/0.01/1.2/1.4, dsn=2.0.0, status=sent (250 Ok: queued as )2017-03-03 21:24:00 postfix/smtp[7804]: insert sucessed.2017-03-03 21:24:00 postfix/qmgr[7797]: 08CE520449CD: removed

这里面dsn返回多个属性，具体如下

DF1DA20449CC: from=<[email protected]>, to=<[email protected]>, relay=mx3.qq.com[184.105.206.82]:25, delay=18, delays=6.5/0.01/2.9/8.4, dsn=2.0.0, status=sent (250 Ok: queued as )dsn->status        2.0.0  (sent.c里面定义的，发送成功为2.0.0)dsn->action        relayeddsn->reason        250 Ok: queued asdsn->dtype        smtpdsn->dtext        250 Ok: queued as dsn->mtype        dnsdsn->mname        mx3.qq.com 

如果发送状态一直是 deferred，达到queue限制时间后会直接退信，这种状态不是bounced

2017-03-10 09:07:35 postfix/cleanup[11581]: E2E14207B661: message-id=<[email protected]>2017-03-10 09:07:35 postfix/bounce[11579]: 889F5207B65B: sender non-delivery notification: E2E14207B661

这种也可以直接更新到数据库里面，算作是退信，那么要修改

src/bounce/bounce_notify_service.c

#include "../global/putmysqlb.h"if (bounce_status == 0)		    msg_info("%s: notify_service sender non-delivery notification: %s",			     queue_id, STR(new_id));            put_mysqlb(queue_id, STR(new_id));   /*  new add */	    } else {		/* No applicable recipients found - cancel this notice. */		(void) vstream_fclose(bounce);		if (count == 0)		    bounce_status = 0;	    }

putmysqlb.h

#include <mysql.h>#include <stdio.h> void put_mysqlb(const char *qid, const char *rid){    msg_info("qid: %s rid: %s",qid,rid);    char bufs[100] = {0};    char buf[200]= {0};    int flag;    int flagone;    MYSQL mysql;    MYSQL_RES *res;    mysql_init(&mysql);        if(!mysql_real_connect(&mysql, "127.0.0.1", "root", "westhost", "test", 0, NULL, 0))    {        msg_info("Failed to connect to Mysql: %s", mysql_error(&mysql));    }    sprintf(buf,"select qid from test where qid=/"%s/"",qid);    msg_info("buf: %s/r/n",buf);    flagone = mysql_query(&mysql,buf);    if(flagone)    {        msg_info("SELECT error: %s", mysql_error(&mysql));    } else {        res = mysql_store_result(&mysql);        sprintf(bufs,"update test set rid=/"%s/", stat=/"%s/" where qid=/"%s/"",rid,"bounced",qid);        flag = mysql_query(&mysql,bufs);        if(flag){            msg_info("UPDATE error: %s %s/r/n", mysql_error(&mysql),bufs);        }else {            msg_info("Retrieved %lu rows update sucessed", (unsigned long)mysql_num_rows(res));        }    }      mysql_close(&mysql);}

下面两个文件也有 sender non-delivery notification 还不清楚具体哪类退信会调用，暂不修改

src/bounce/bounce_notify_verp.c

src/bounce/bounce_one_service.c

这几个文件了

如果编译时提示 警告： 隐式声明与内建函数 ‘sprintf’ 不兼容，添加下面两个头文件

#include <stdio.h> #include <stdlib.h> 

如果提示 xxx 没有原型

因为自定义的函数都是在msg_info后面执行的，所以可以在/src/util/msg.h里面添加声明

extern void put_mysql(const char *, const char *, const char *, const char *, const char *, const char *);extern void put_bounced(const char *, const char *);