Mailing list with pure Postfix+MySQL

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’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’s all!
As I pointed out you send email “FROM”, so nobody else can do that untill you uses SMTP authorization.

The configuration ‘how to’:
1. MySQL

CREATE TABLE IF NOT EXISTS `sender_bcc` (
  `sender_email` varchar(150) NOT NULL,
  `bcc` varchar(150) NOT NULL,
  `status` tinyint(3) unsigned NOT NULL default '1',
  PRIMARY KEY  (`sender_email`)
) ENGINE=MyISAM DEFAULT CHARSET=default;

2. Postfix.
Create config file, e.g. /etc/postfix/mysql/sender_bcc.cf and put there following data:

user = MyUser # Change to yours
password = MyPasswd # Change to yours
dbname = MyDbName # Change to yours
query = SELECT bcc FROM sender_bcc  WHERE sender_email='%u@%d' AND `status` = 1
hosts = 127.0.0.1 # Change to yours

Next, edit main.cf (usually in /etc/postfix/) and put there following 2 lines:

sender_bcc = proxy:mysql:/etc/postfix/mysql/sender_bcc.cf
sender_bcc_maps = $sender_bcc

In case you use proxy, complete your proxy_read_maps with variable $sender_bcc.
Configuration is finished.

Now, you have to understand how it works and possibly prepare user interface to adapt such feature.
Please image, you’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.:

INSERT INTO `sender_bcc` (`sender_email`, `bcc`, `status`) VALUES('list@my-domain', 'virtual.unique@my-domain', 1);

Following, you insert into your alias/forward table (I assume you’ve got it on you Postfix configuration), e.g.

INSERT INTO `aliases` (`email_from`, `email_to`) VALUES('virtual.unique@my-domain', 'bulk-addressee1@domain,bulk-addressee2@domain, bulk-addressee3@domain).

It works on my production box.

    • Anonymous
    • July 26th, 2010

    How do they get away with THAT?

  1. You send an email from list@my-domain to not_important@my-domain. Posftix gets this message and sees that it has to send each message from list@my-domain also to virtual.unique@my-domain and doeas it. Next checking aliases/forwards (postfix) sees it must send message destinated to virtual.unique@my-domain to other addressees (bulk_01@receiver.com, bulk_02@receivercom, etc) – THAT is the clue.

    Hope it is clear now, possibly ask more detailed question.


    regards

  2. Thanks for the explanation of this!

  1. July 25th, 2010