=================================== MySQL MySQL authentication section (note this doesn't use TLS or any other transport security - caveat implementor). The users login with their full email address. Passwords are held crypted. The MySQL table is used for other things apart from authentication, looks like this: mysql> describe passwd; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | address | varchar(128) | | UNI | | | | crypt | varchar(128) | | | | | | clear | varchar(128) | | | | | | name | varchar(128) | | | | | | uid | int(10) unsigned | | | 65534 | | | gid | int(10) unsigned | | | 65534 | | | home | varchar(255) | | | | | | maildir | varchar(255) | | | | | | quota | varchar(255) | | | | | | domid | int(11) | YES | | NULL | | | blockout | varchar(5) | YES | | NULL | | +----------+------------------+------+-----+---------+----------------+ begin authenticators plain: driver = plaintext public_name = PLAIN server_condition = ${if and { \ {!eq{$2}{}} \ {!eq{$3}{}} \ {crypteq{$3}{${lookup mysql{SELECT crypt FROM passwd WHERE address='$2'}{$value}fail}}} \ } {yes}{no}} server_set_id = $2 # login authenticator - here $1 is the userid and $2 is the password. otherwise all else is # the same as above login: driver = plaintext public_name = LOGIN server_prompts = "Username:: : Password::" server_condition = ${if and { \ {!eq{$1}{}} \ {!eq{$2}{}} \ {crypteq{$2}{${lookup mysql{SELECT crypt FROM passwd WHERE address='$1'}{$value}fail}}} \ } {yes}{no}} server_set_id = $1 =================================== GrayList CREATE TABLE `greylist` ( `id` bigint(20) NOT NULL auto_increment, `relay_ip` varchar(20) default NULL, `sender_type` enum('NORMAL','BOUNCE') NOT NULL default 'NORMAL', `sender` varchar(150) default NULL, `recipient` varchar(150) default NULL, `block_expires` datetime NOT NULL default '0000-00-00 00:00:00', `record_expires` datetime NOT NULL default '9999-12-31 23:59:59', `create_time` datetime NOT NULL default '0000-00-00 00:00:00', `TYPE` enum('AUTO','MANUAL') NOT NULL default 'MANUAL', `passcount` bigint(20) NOT NULL default '0', `last_pass` datetime NOT NULL default '0000-00-00 00:00:00', `blockcount` bigint(20) NOT NULL default '0', `last_block` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `relay_ip` (`relay_ip`,`sender`,`recipient`,`sender_type`) ) TYPE=MyISAM COMMENT='GrayList ' AUTO_INCREMENT=199 ; #Exim.conf file GREYLIST_ENABLED_GREY = yes GREYLIST_INITIAL_DELAY = 12 MINUTE GREYLIST_INITIAL_LIFETIME = 4 HOUR GREYLIST_WHITE_LIFETIME = 36 DAY GREYLIST_BOUNCE_LIFETIME = 7 DAY GREYLIST_RECORD_LIFETIME = 90 DAY GREYLIST_TABLE = greylist #.... .ifdef GREYLIST_ENABLED_GREY GREYLIST_TEST = SELECT CASE \ WHEN now() > block_expires THEN "accepted" \ ELSE "deferred" \ END AS result, id \ FROM GREYLIST_TABLE \ WHERE (now() < record_expires) \ AND (sender_type = ${if def:sender_address_domain{'NORMAL'}{'BOUNCE'}}) \ AND (sender = '${quote_mysql:${if def:sender_address_domain{$sender_address_domain}{${domain:$h_from:}}}}') \ AND (recipient = '${quote_mysql:${if def:domain{$domain}{${domain:$h_to:}}}}') \ AND (relay_ip = '${quote_mysql:${mask:$sender_host_address/24}}') \ ORDER BY result DESC LIMIT 1 GREYLIST_ADD = REPLACE INTO GREYLIST_TABLE \ (relay_ip, sender_type, sender, recipient, block_expires, \ record_expires, create_time, type) \ VALUES ( '${quote_mysql:${mask:$sender_host_address/24}}', \ ${if def:sender_address_domain{'NORMAL'}{'BOUNCE'}}, \ '${quote_mysql:${if def:sender_address_domain{$sender_address_domain}{${domain:$h_from:}}}}', \ '${quote_mysql:${if def:domain{$domain}{${domain:$h_to:}}}}', \ DATE_ADD(now(), INTERVAL GREYLIST_INITIAL_DELAY), \ DATE_ADD(now(), INTERVAL GREYLIST_INITIAL_LIFETIME), \ now(), \ 'AUTO' \ ) GREYLIST_DEFER_HIT = UPDATE GREYLIST_TABLE \ SET blockcount=blockcount+1, last_block=now() \ WHERE id = $acl_m9 GREYLIST_OK_COUNT = UPDATE GREYLIST_TABLE \ SET passcount=passcount+1, last_pass=now() \ WHERE id = $acl_m9 GREYLIST_OK_NEWTIME = UPDATE GREYLIST_TABLE \ SET record_expires = DATE_ADD(now(), INTERVAL GREYLIST_WHITE_LIFETIME) \ WHERE id = $acl_m9 AND type='AUTO' GREYLIST_OK_BOUNCE = UPDATE GREYLIST_TABLE \ SET record_expires = DATE_ADD(now(), INTERVAL GREYLIST_BOUNCE_LIFETIME) \ WHERE id = $acl_m9 AND type='AUTO' GREYLIST_CLEAN = DELETE FROM GREYLIST_TABLE \ WHERE (record_expires > DATE_ADD(now(), INTERVAL GREYLIST_RECORD_LIFETIME)) AND (type='AUTO') .endif #-GreyList (before rcpt and data): .ifdef GREYLIST_ENABLED_GREY greylist_acl: # clean greylist records as 09:3xBRST 10:3xBRDT (horario normal) warn condition = ${if eq {${substr{8}{3}{$tod_zulu}}} {123}{yes}{no}} set acl_m4 = ${lookup mysql{GREYLIST_CLEAN}} # For regular deliveries, check greylist. # check greylist tuple, returning "accepted", "deferred" or "unknown" # in acl_m8, and the record id in acl_m9 warn set acl_m8 = ${lookup mysql{GREYLIST_TEST}{$value}{result=unknown}} # here acl_m8 = "result=x id=y" set acl_m9 = ${extract{id}{$acl_m8}{$value}{-1}} # now acl_m9 contains the record id (or -1) set acl_m8 = ${extract{result}{$acl_m8}{$value}{unknown}} # now acl_m8 contains unknown/deferred/accepted # check if we know a certain triple, add and defer message if not accept # if above check returned unknown (no record yet) condition = ${if eq {$acl_m8} {unknown} {1}} # then also add a record condition = ${lookup mysql{GREYLIST_ADD}{yes}{no}} # check if the triple is still blocked accept # if above check returned deferred then defer condition = ${if eq{$acl_m8} {deferred} {1}} # and note it down condition = ${lookup mysql{GREYLIST_DEFER_HIT}{yes}{yes}} # use a warn verb to count records that were hit warn condition = ${lookup mysql{GREYLIST_OK_COUNT}} # use a warn verb to set a new expire time on automatic records, # but only if the mail was not a bounce, otherwise set to now(). warn !senders = : postmaster@* : Mailer-Daemon@* condition = ${lookup mysql{GREYLIST_OK_NEWTIME}} warn senders = : postmaster@* : Mailer-Daemon@* condition = ${lookup mysql{GREYLIST_OK_BOUNCE}} deny .endif #.... acl_check_rcpt: #.... .ifdef GREYLIST_ENABLED_GREY defer hosts = !+relay_from_hosts !authenticated = * !senders = : postmaster@* : Mailer-Daemon@* acl = greylist_acl message = GreyListed: please try again later .endif #.... acl_check_data: #.... .ifdef GREYLIST_ENABLED_GREY defer hosts = !+relay_from_hosts senders = : postmaster@* : Mailer-Daemon@* acl = greylist_acl message = GreyListed: please try again later .endif =================================== Email Forwards in MySQL forwarding router - which even supports multiple addresses (so an addy can forward out to more than one final recipient): virtual_user_fwd: driver = redirect verify = no check_ancestor = yes hide_child_in_errmsg = yes domains = +domain_virtual data = ${lookup mysql{SELECT fwd FROM userforward WHERE host = '${quote_mysql:$domain}' AND user = '${quote_mysql:$local_part}' AND fwd IS NOT NULL} {${sg{$value}{\\n}{, }}}} Not sure if you want hide_child_in_errmsg - I was just testing that out... It hides the final recipients if a bounceback is generated (try with it on/off to see the difference). =================================== SMTP auth, MySQL & passwords MYSQL_AUTH_CRAM = SELECT MYSQL_SMTPAUTH_PASS_FIELD from MYSQL_SMTPAUTH_TABLE where MYSQL_SMTPAUTH_USER_FIELD = '${quote _mysql:$1}' AND MYSQL_SMTPAUTH_PASS_FIELD != '' cram: driver = cram_md5 public_name = CRAM-MD5 server_secret = ${lookup mysql {MYSQL_AUTH_CRAM}} server_set_id = $1 -------------------------- mysql_cram: driver = cram_md5 public_name = CRAM-MD5 server_secret = ${lookup mysql{SELECT password FROM users \ WHERE user = '${quote_mysql:$1}'} {$value} fail} server_set_id=$1 -------------------------- domainlist local_domains = \ ${lookup mysql {SELECT domain FROM domains \ WHERE type="local" and domain="${domain}" }} # Never do _local_ delivery to these users. never_users = root # Name lookups host_lookup = * rfc1413_hosts = * rfc1413_query_timeout = 30s mysql> select * from domains ; domain type +-------------------+--------+ |mydomain.com | local | +-------------------+--------+ =================================== >Is it possible to make just a query and then instruct exim to get the >data from the yielded row ? for ACLs you could do: set acl_m0 = ${lookup mysql {select f1,f2,f3 from table where f4='x'}} and ${extract {f1}{$acl_m0}} will return the data of the first field. In routers you can use address_data to do the same thing. Check: http://www.exim.org/exim-html-4.30/doc/html/spec_11.html#IX784 and http://www.exim.org/exim-html-4.30/doc/html/spec_9.html#SECT9.17 =================================== domainlist blocked_domains = mysql;select 1 from blockeddomains where \ domain = '${quote_mysql:$sender_domain}' =================================== block by IP block list in a MySQL database. We reject at RCPT time, with a message which is mostly a URL to a web page which gives the other end a chance to request exemption (which of course some annoying mail programs will refuse to show the sender). The database has a column for "network" (meaning /24) and for host...if host is NULL the whole subnet is blocked, else just the specified IP. This rule is in the SMTP time ACL (ie, once per connection): warn hosts = +rbl_hosts : !127.0.0.0/8 : !+local_networks : !+relay_from_hosts condition = ${if ! match {$sender_host_name}{\Nmx\.aol\.com$\N}{yes}{no}} condition = ${lookup mysql{select id \ from blocklist \ where network=SUBSTRING_INDEX('$sender_host_address', '.', 3) \ and (host=SUBSTRING_INDEX('$sender_host_address', '.', -1) or \ host is NULL) \ limit 1 \ }{yes}{no}} set acl_c1 = blocklist Later at RCPT time, if an exemption mechanism hasn't changed acl_c1, we find deny condition = ${if eq {$acl_c1}{blocklist}{yes}{no}} message = rejected see http://www.olympus.net/local/$sender_host_address