Postfix log subject with header_checks from MySQL

Most people use a seperate REGEX file to do some spamfiltering or to create log rules (such as email subject) with the header_checks file.

To do subject logging you need to enter these line in the /etc/postfix/header_checks:

/^subject:/ INFO

 

However, to enter them in MySQL it is much easier because if you have a MySQL client like for example phpMyAdmin you only need to login to this instead of login into the server everytime.

Put this line in the main.cf

header_checks = mysql:/etc/postfix/mysql-virtual_headerfilter.cf

 

Create the /etc/postfix/mysql-virtual_headerfilter.cf file with these lines:


hosts = <host>
user = <username>
password = <password>
dbname = mail

query = SELECT action FROM mail_content_filter WHERE '%s' RLIKE pattern AND type = 'header' and active = 'y'

 

Create the database table:

--
-- Table structure for table `mail_content_filter`
--

CREATE TABLE IF NOT EXISTS `mail_content_filter` (
`content_filter_id` int(11) unsigned NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `pattern` varchar(255) DEFAULT NULL,
  `data` varchar(255) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `active` varchar(255) NOT NULL DEFAULT 'y'
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `mail_content_filter`
--

INSERT INTO `mail_content_filter` (`content_filter_id`, `type`, `pattern`, `data`, `action`, `active`) VALUES
(1,'header', 'subject:', 'Log subject rules in Postfix logging', 'INFO', 'y');
(2,'header', 'some casino spam', 'Just a spam rule to reject casino spam', 'REJECT', 'y');

--
-- Indexes for table `mail_content_filter`
--
ALTER TABLE `mail_content_filter`
 ADD PRIMARY KEY (`content_filter_id`);