{"id":57,"date":"2010-07-20T22:40:19","date_gmt":"2010-07-20T22:40:19","guid":{"rendered":"http:\/\/sqlornosql.10sa.com\/?p=57"},"modified":"2010-09-09T07:50:49","modified_gmt":"2010-09-09T07:50:49","slug":"mailing-list-with-pure-postfixmysql","status":"publish","type":"post","link":"http:\/\/10sa.com\/sql_stories\/?p=57","title":{"rendered":"Mailing list with pure Postfix+MySQL"},"content":{"rendered":"<p>How to create a mailing list on the base of Postfix? My main goal was to avoid installing Mailman and enable sending bulk emails. With Postfix VDA it was quite simple.<br \/>\nMy solution is in some way tricky but secure (SMTP Auth) and simple provided that user has been advised adequatly.<br \/>\nFrom user&#8217;s standpoint one has to send an email FROM account list@my-domain TO my-email@my-domain and such email is being forwarded to all addresses assigned to list@my-domain. That&#8217;s all!<br \/>\nAs I pointed out you send email &#8220;FROM&#8221;, so nobody else can do that untill you uses SMTP authorization.<\/p>\n<p>The configuration &#8216;how to&#8217;:<br \/>\n1. MySQL<\/p>\n<pre lang=\"SQL\">\r\nCREATE TABLE IF NOT EXISTS `sender_bcc` (\r\n  `sender_email` varchar(150) NOT NULL,\r\n  `bcc` varchar(150) NOT NULL,\r\n  `status` tinyint(3) unsigned NOT NULL default '1',\r\n  PRIMARY KEY  (`sender_email`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=default;\r\n<\/pre>\n<p>2. Postfix.<br \/>\nCreate config file, e.g. \/etc\/postfix\/mysql\/sender_bcc.cf and put there following data:<\/p>\n<pre lang=\"bash\">\r\nuser = MyUser # Change to yours\r\npassword = MyPasswd # Change to yours\r\ndbname = MyDbName # Change to yours\r\nquery = SELECT bcc FROM sender_bcc  WHERE sender_email='%u@%d' AND `status` = 1\r\nhosts = 127.0.0.1 # Change to yours\r\n<\/pre>\n<p>Next, edit main.cf (usually in \/etc\/postfix\/) and put there following 2 lines:<\/p>\n<pre lang=\"bash\">\r\nsender_bcc = proxy:mysql:\/etc\/postfix\/mysql\/sender_bcc.cf\r\nsender_bcc_maps = $sender_bcc\r\n<\/pre>\n<p>In case you use proxy, complete your proxy_read_maps with variable $sender_bcc.<br \/>\nConfiguration is finished.<\/p>\n<p>Now, you have to understand how it works and possibly prepare user interface to adapt such feature.<br \/>\nPlease image, you&#8217;ve created email list@my-domain. Next, you create virtual email (virtual.unique@my-domain) which exists only as bcc (receiver) and forwarding email, i.e.:<\/p>\n<pre lang=\"sql\">\r\nINSERT INTO `sender_bcc` (`sender_email`, `bcc`, `status`) VALUES('list@my-domain', 'virtual.unique@my-domain', 1);\r\n<\/pre>\n<p>Following, you insert into your alias\/forward table (I assume you&#8217;ve got it on you Postfix configuration), e.g.<\/p>\n<pre lang=\"sql\">INSERT INTO `aliases` (`email_from`, `email_to`) VALUES('virtual.unique@my-domain', 'bulk-addressee1@domain,bulk-addressee2@domain, bulk-addressee3@domain).\r\n<\/pre>\n<p>It works on my production box.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>How to create a mailing list on the base of Postfix? My main goal was to avoid installing Mailman and enable sending bulk emails. With Postfix VDA it was quite simple. My solution is in some way tricky but secure (SMTP Auth) and simple provided that user has been advised adequatly. From user&#8217;s standpoint one [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[8],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/57"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=57"}],"version-history":[{"count":36,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/57\/revisions"}],"predecessor-version":[{"id":196,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/57\/revisions\/196"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=57"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=57"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=57"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}