Postfix check_recipient_access with MySQL

Having a list of rejecting email addresses in a DB table makes adding them easy (add via command line insert, add via script, add via email trigger, add via desktop SQL app like Sequel Pro, add via web app), removing the need for root access to edit configuration files and restart the postfix process that may happen when hashed files are used.

This is what I did, without a list of why I did it, or how I ended up with this particular solution:

In my /etc/postfix/main.cf file, I have this entry in my list of smtpd_recipient_restrictions values:

  check_recipient_access mysql:/etc/postfix/mysql-virtual-recipient-access.cf

This says, check the recipient address by executing the SQL query specified in /etc/postfix/mysql-virtual-recipient-access.cf using the mysql credentials in the same file.

My whole smtpd_recipient_restrictions section looks like, this, but your mileage will vary:

smtpd_recipient_restrictions =
  permit_sasl_authenticated,
  permit_mynetworks,
  reject_unauth_destination,
  reject_unknown_sender_domain
  reject_invalid_hostname,
  reject_non_fqdn_hostname,
  reject_unknown_recipient_domain,
  reject_unknown_helo_hostname,
  check_recipient_access mysql:/etc/postfix/mysql-virtual-recipient-access.cf

For the mysql query to work, I needed my table. I created my mysql table with this command:

CREATE TABLE `virtual_sender_access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source` varchar(256) NOT NULL DEFAULT '',
  `access` varchar(128) NOT NULL DEFAULT '',
  `created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Where the fields are id to track unique entries, source is the email address (using the conventions from other tables that are defined in a whole bunch of tutorials for the virtual domains, aliases, and users), access is what I'm going to do with the email address: deliver it, reject it, drop it, etc. as allowed in the access(5) values on http://www.postfix.org/access.5.html.

I nearly always use created_on and last_modified in my tables, as a way to track changes. You can totally leave those out if you don't care about them or have a different logging mechanism.

I have the table, I have the configuration, now the query.

In my mysql-virtual-recipient-access.cf, I have this:

user = lookupuser
password = *********************
hosts = 127.0.0.1
dbname = postfix
query = SELECT access FROM virtual_sender_access WHERE source='%s'

Now, all of this you likely could have gotten from the other tutorials around the web. The problem I had when setting this thing up with my mail server was *what do I put IN the database table*?

Right.

Put in this to reject an address:

INSERT INTO virtual_sender_access (source, access, created_on) VALUES ('reject@example.io', 'REJECT', NOW());

Put in this to accept an address and deliver it:

INSERT INTO virtual_sender_access (source, access, created_on) VALUES ('accept@example.io', 'OK', NOW());

Follow along with the other tutorials for the rest of the configuration, restart your postfix server with something like "service postfix restart" and check your queries and tables are all setup correctly. I tested by sending email to an address I wanted rejected, as listed in my virtual_sender_access with a REJECT code, and watching it be rejected. I then sent an email to an address NOT listed in the table, and it was delivered. I considered this a success. I would not recommend that as the correct way to test if a system is working.