Mysql – Postfix & Dovecot: Mailbox Quota Configurations

Here is the solution that worked for me. I am now able to successfully track each individual message sent through our postfix serves using MailGun.org as our relay.

Please not this is a simple install of Postfix / Dovecot using iRedmail.org install script for debian /ubuntu. Your own postfix configuration may be different.

Main.cf

### MAILGUN LOGGING
smtpd_recipient_restrictions = check_recipient_access mysql:/etc/postfix/mailgun-header.cf, reject_unknown_sender_domain, reject_unknown_recipient_domain, reject_non_fqdn_sender, reject_non_fqdn_recipient$

/etc/postfix/mailgun-header.cf

#PREPEND CUSTOM HEADER FOR MESSAGES SENT THROUGH MAILGUN.NET RELAY
#MAKE SURE TO USE YOUR OWN DATABSE USER / PASSWORD

user        = vmail
password    = PutYourPasswordHere (in iRedMail tips if you used iRedmail to setup)
hosts       = 127.0.0.1
port        = 3306
dbname      = vmail
query       = Select CONCAT(prefix, '{"custom-msg-id": ','"',MD5(RAND()),'"','}') as 'check_recipient_access' from mailgun_tokens where prefixID = 1;

MySQL Requirements:

You pretty much need to create a table called mailgun_tokens in your vmail database. If your database name is different make sure to account for that. The table is simple with only two columns. One prefix (varchar 100), and one called prefixId int(1). It will only have one record. You can get more fancy in a multi domain setup and create a record for each domain or even user.

Here is the basic code to get your mysql setup to continue with the code samples above:

   -- ----------------------------
-- Table structure for `mailgun_tokens`
-- ----------------------------
DROP TABLE IF EXISTS `mailgun_tokens`;
CREATE TABLE `mailgun_tokens` (
  `prefix` varchar(100) DEFAULT NULL,
  `prefixID` int(1) unsigned zerofill NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`prefixID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of mailgun_tokens
-- ----------------------------
INSERT INTO `mailgun_tokens` VALUES ('PREPEND X-Mailgun-Variables: ', '1');

Last Step: Restart Posftfix (service postfix restart)

Once you get everything up and running, postfix will now add a custom header X-Mailgun-Variables with the appropriate JSON formatted string. If you have configured mailgun to post to your application for Message Read notifications, and link click notifications then Mailgun will post the custom-msg-id to your application also so that you can lookup the email.

Couple this with logging all messages to your DB, and you could easily create an application, crm, webmail which keeps track of messages once they have left your mailbox. Read Notifications for email.

Persistently requiring the full domain name for every address is biting you in the backside here.

It really isn't necessary since all you have is the one domain, so a simple solution would be to alter the SQL lookups to act on the localpart only.

Add the new domain to virtual_mailbox_domains and perform only a localpart lookup on virtual_mailbox_maps.

Both domains and all users in it will be accepted and delivered.

Using a complicated query for virtual_mailbox_domains isn't likely to help, either.
Just list the domain(s) in virtual_mailbox-domains:

virtual_mailbox_domains = olddomain, newdomain