Version: postfix-2.5.1 Linux violin 2.6.18-164.11.1.el5 #1 SMP Wed Jan 6 13:26:04 EST 2010 x86_64 x86_64 x86_64 GNU/Linux Hi All, I have a problem with postfix alias using mysql. Some emails consistently forward, others consistently fail and I don't know why as both are setup the same. I'm afraid there is a lot of info here, very grateful to anyone who can have a look. My questions are: 1) why does one of those work and the other one fail. Forwarding to any btinternet/any btconnect/any yahoo.co.uk/some gmail/some aol fail. 2) why are there so many SQL queries for each email? Should they all be there? 3) how do I stop the bounce from the failed one trying to resolve against the alias table? Regards, Rob ------------------------------------------------------------------------------------------------------ This is an example that works, sending email via telnet, the postfix logs and then the mySQL logs [roba@yeps72594 sites]$ telnet violin 25 Trying 192.168.92.29... Connected to violin.group.com. Escape character is '^]'. 220 mail.sites.co.uk ESMTP Postfix HELO gmail.com 250 mail.sites.co.uk MAIL FROM:[email protected] 250 2.1.0 Ok RCPT TO:[email protected] 250 2.1.5 Ok DATA 354 End data with <CR><LF>.<CR><LF> test . 250 2.0.0 Ok: queued as 1516B2EE66 ^] telnet> q Connection closed. postfix log Nov 18 16:39:04 violin postfix/smtpd[23963]: connect from unknown[10.51.130.35] Nov 18 16:39:32 violin postfix/smtpd[23963]: 1516B2EE66: client=unknown[10.51.130.35] Nov 18 16:39:44 violin postfix/cleanup[24257]: 1516B2EE66: message- id=<[email protected]> Nov 18 16:39:44 violin postfix/qmgr[22677]: 1516B2EE66: from=<[email protected]>, size=357, nrcpt=1 (queue active) Nov 18 16:39:44 violin postfix/smtp[24273]: 1516B2EE66: to=<[email protected]>, orig_to=<[email protected]>, relay=mail0.example.co.uk[123.456.69.136]:25, delay=23, delays=23/0.02/0.05/0.15, dsn=2.0.0, status=sent (250 2.0.0 Ok: queued as 53E5052A0) Nov 18 16:39:44 violin postfix/qmgr[22677]: 1516B2EE66: removed Nov 18 16:39:52 violin postfix/smtpd[23963]: disconnect from unknown[10.51.130.35] sql log 101118 16:39:32 666197 Connect [email protected] on postfixdb 666197 Query select domain from email_domains where domain = 'vintage.co.uk' 666198 Connect [email protected] on postfixdb 666198 Query select outgoing from email_map where incoming = 'vintage.co.uk' 666199 Connect [email protected] on postfixdb 666199 Query select domain from email_domains where domain = 'vintage.co.uk' 666199 Query select domain from email_domains where domain = 'vintage.co.uk' 666200 Connect [email protected] on postfixdb 666200 Query select outgoing from email_map where incoming = '[email protected]' 666201 Connect [email protected] on postfixdb 666201 Query select outgoing from email_map where incoming = '[email protected]' 666201 Query select outgoing from email_map where incoming = '[email protected]' 666202 Connect [email protected] on postfixdb 666202 Query select domain from email_domains where domain = 'example.co.uk' 666201 Query select outgoing from email_map where incoming = '@example.co.uk' 101118 16:39:44 666197 Query select domain from email_domains where domain = 'example.co.uk' 666198 Query select outgoing from email_map where incoming = 'example.co.uk' 666197 Query select domain from email_domains where domain = 'example.co.uk' 666197 Query select domain from email_domains where domain = 'co.uk' 666197 Query select domain from email_domains where domain = 'uk' ------------------------------------------------------------------------------------------------------------------------------- Here is an example that fails [roba@yeps72594 sites]$ telnet violin 25 Trying 192.168.92.29... Connected to violin.group.com. Escape character is '^]'. 220 mail.sites.co.uk ESMTP Postfix HELO gmail.com 250 mail.sites.co.uk MAIL FROM:[email protected] 250 2.1.0 Ok RCPT TO:[email protected] 250 2.1.5 Ok DATA 354 End data with <CR><LF>.<CR><LF> test . 250 2.0.0 Ok: queued as C69742EE66 ^] telnet> q Connection closed. postfix log Nov 18 16:37:22 violin postfix/smtpd[23963]: connect from unknown[10.51.130.35] Nov 18 16:37:35 violin postfix/smtpd[23963]: C69742EE66: client=unknown[10.51.130.35] Nov 18 16:37:44 violin postfix/cleanup[24184]: C69742EE66: message- id=<[email protected]> Nov 18 16:37:44 violin postfix/qmgr[22677]: C69742EE66: from=<[email protected]>, size=357, nrcpt=1 (queue active) Nov 18 16:37:44 violin postfix/local[24202]: C69742EE66: to=<[email protected]>, orig_to=<[email protected]>, relay=local, delay=13, delays=13/0.02/0/0.02, dsn=5.1.1, status=bounced (unknown user: "nicky") Nov 18 16:37:44 violin postfix/cleanup[24184]: AFAD42EE6B: message- id=<[email protected]> Nov 18 16:37:44 violin postfix/qmgr[22677]: AFAD42EE6B: from=<>, size=2209, nrcpt=1 (queue active) Nov 18 16:37:44 violin postfix/bounce[24203]: C69742EE66: sender non- delivery notification: AFAD42EE6B Nov 18 16:37:44 violin postfix/qmgr[22677]: C69742EE66: removed Nov 18 16:37:44 violin postfix/local[24202]: AFAD42EE6B: to=<[email protected]>, relay=local, delay=0.03, delays=0.01/0/0/0.01, dsn=5.1.1, status=bounced (unknown user: "rob") Nov 18 16:37:44 violin postfix/qmgr[22677]: AFAD42EE6B: removed Nov 18 16:37:45 violin postfix/anvil[22654]: statistics: max connection rate 13/60s for (smtp:192.168.92.11) at Nov 18 16:29:34 Nov 18 16:37:45 violin postfix/anvil[22654]: statistics: max connection count 1 for (smtp:192.168.92.11) at Nov 18 16:27:48 Nov 18 16:37:45 violin postfix/anvil[22654]: statistics: max cache size 2 at Nov 18 16:37:22 Nov 18 16:37:49 violin postfix/smtpd[23963]: disconnect from unknown[10.51.130.35] SQL log 101118 16:37:32 666191 Connect [email protected] on postfixdb 666191 Query select domain from email_domains where domain = 'gmail.com' 666192 Connect [email protected] on postfixdb 666192 Query select outgoing from email_map where incoming = 'gmail.com' 101118 16:37:35 666191 Query select domain from email_domains where domain = 'vintage.co.uk' 666192 Query select outgoing from email_map where incoming = 'vintage.co.uk' 666193 Connect [email protected] on postfixdb 666193 Query select domain from email_domains where domain = 'vintage.co.uk' 666193 Query select domain from email_domains where domain = 'vintage.co.uk' 666194 Connect [email protected] on postfixdb 666194 Query select outgoing from email_map where incoming = '[email protected]' 666195 Connect [email protected] on postfixdb 666195 Query select outgoing from email_map where incoming = '[email protected]' 666195 Query select outgoing from email_map where incoming = '[email protected]' 666196 Connect [email protected] on postfixdb 666196 Query select domain from email_domains where domain = 'btinternet.com' 666195 Query select outgoing from email_map where incoming = 'nicky' 666195 Query select outgoing from email_map where incoming = '@btinternet.com' 101118 16:37:44 666191 Query select domain from email_domains where domain = 'btinternet.com' 666192 Query select outgoing from email_map where incoming = 'btinternet.com' 666195 Query select outgoing from email_map where incoming = '[email protected]' 666196 Query select domain from email_domains where domain = 'gmail.com' 666195 Query select outgoing from email_map where incoming = 'rob' 666195 Query select outgoing from email_map where incoming = '@gmail.com' 666191 Query select domain from email_domains where domain = 'gmail.com' 666192 Query select outgoing from email_map where incoming = 'gmail.com' 101118 16:38:35 666193 Quit 666194 Quit 101118 16:38:44 666196 Quit 666195 Quit ------------------------------------------------------------------------------------------------------------------ Here is the mysql data mysql> desc email_map; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | mapid | int(11) | NO | PRI | NULL | auto_increment | | incoming | varchar(100) | YES | | NULL | | | outgoing | varchar(100) | YES | | NULL | | | status | int(11) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> select * from email_map where incoming like '%vintage.co.uk'; +-------+-----------------------------+---------------------------- +--------+ | mapid | incoming | outgoing | status | +-------+-----------------------------+---------------------------- +--------+ | 2434 | [email protected] | [email protected] | 1 | | 2770 | [email protected] | [email protected] | 1 | +-------+-----------------------------+---------------------------- +--------+ ------------------------------------------------------------------------------------------------------------------ And postfix config - main.cf queue_directory = /var/spool/postfix command_directory = /usr/sbin daemon_directory = /usr/libexec/postfix data_directory = /var/lib/postfix mail_owner = postfix myhostname = violin.sites.co.uk mydomain = sites.co.uk myorigin = $mydomain virtual_alias_maps = mysql:/home/mailadmin/virtual.cf unknown_local_recipient_reject_code = 550 mynetworks = 127.0.0.0/8 virtual_alias_domains = mysql:/home/mailadmin/domains.cf smtpd_helo_restrictions = reject_invalid_helo_hostname, reject_non_fqdn_helo_hostname, permit smtpd_sender_restrictions = reject_non_fqdn_sender, reject_unknown_sender_domain, permit smtpd_recipient_restrictions = reject_unlisted_recipient, reject_non_fqdn_recipient, permit_mynetworks, reject_unauth_destination, permit debug_peer_level = 2 debugger_command = PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin ddd $daemon_directory/$process_name $process_id & sleep 5 sendmail_path = /usr/sbin/sendmail.postfix newaliases_path = /usr/bin/newaliases.postfix mailq_path = /usr/bin/mailq.postfix setgid_group = postdrop html_directory = /usr/share/doc/postfix-2.5.1-documentation/html manpage_directory = /usr/share/man sample_directory = /etc/postfix readme_directory = /usr/share/doc/postfix-2.5.1-documentation/readme alias_database = hash:/etc/postfix/aliases alias_maps = hash:/etc/postfix/aliases ----------------------------------------------------------------------------------------------------- [mailadmin@violin ~]$ cat domains.cf hosts = violin user = postfix_r password = blah dbname = postfixdb query = select domain from email_domains where domain = '%s' ----------------------------------------------------------------------------------------------------- [mailadmin@violin ~]$ cat virtual.cf hosts = violin user = postfix_r password = blah dbname = postfixdb query = select outgoing from email_map where incoming = '%s' Domain names / email addresses have been changed to protect the innocent!
From reading postfix.org and taking config settings from other servers that do work. The biggest question is why does it work to some email addresses and not others? First thought was that the destination ISP has a whitelist which would explain why no BT emails get through at all. However the logs indicate that postfix isn't getting as far as contacting their mail servers to even be told there is a block. I think it isn't anything to do with mysql as I have dumped the email alias table to text as key/value pairs and built it as hash table, which made no difference.
Nevermind, fixed! Never underestimate your users. Some of them put the destination domains (gmail.com, btinternet.com, etc) into the alais domains table!
