amavis per user settings

Modifying Workaround.org ISP Style Email to have Amavis per user settings

This article is intended for those who have already installed Workaround ISP-style Email Server, however you can probably adapt this to your installation of Amavis, especially because (for now at least) this article only covers the part where you modify stuff in Mysql and a little setting in Amavis\'s configuration. WARNING: Please don\'t use this without understanding what you are doing, if you screw up your organization\'s email server it\'s of course not my fault blah blah blah. ... You are supposed to test this on a test server, but hey, if you are responsible for anything important you probably (cough) know what you are doing.. :]

Create the Policy table

You should do this operation on the mailserver database, adapt this to your configuration. This table can vary depending on what options should be available to users, i\'ve seen different possibilities, this is what i did:

USE mailserver; CREATE TABLE IF NOT EXISTS `policy` ( `id` int(10) unsigned NOT NULL auto_increment, `policy_name` varchar(255) default NULL, `virus_lover` char(1) default \'Y\', `spam_lover` char(1) default \'Y\', `banned_files_lover` char(1) default \'Y\', `bad_header_lover` char(1) default \'Y\', `bypass_virus_checks` char(1) default \'Y\', `bypass_spam_checks` char(1) default \'Y\', `bypass_banned_checks` char(1) default \'Y\', `bypass_header_checks` char(1) default \'Y\', `spam_modifies_subj` char(1) default \'N\', `spam_quarantine_to` varchar(64) default NULL, `spam_tag_level` float default \'999\', `spam_tag2_level` float default \'999\', `spam_kill_level` float default \'999\', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This structure provides default values, you can (should) change them to suit your needs. You can add some default policies like these, it\'s even better to make your own, but if you are lazy and/or just want to test things here are 4 basic modes.

INSERT INTO `policy` (`id`, `policy_name`, `virus_lover`, `spam_lover`, `banned_files_lover`, `bad_header_lover`, `bypass_virus_checks`, `bypass_spam_checks`, `bypass_banned_checks`, `bypass_header_checks`, `discard_viruses`, `discard_spam`, `discard_banned_files`, `discard_bad_headers`, `spam_modifies_subj`, `spam_quarantine_to`, `spam_tag_level`, `spam_tag2_level`, `spam_kill_level`) VALUES (1, \'none\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', \'N\', \'N\', \'N\', \'N\', \'N\', NULL, 100, 1000, 10000), (2, \'all\', \'N\', \'N\', \'N\', \'N\', \'N\', \'N\', \'N\', \'N\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', NULL, -50, 4, 10), (3, \'nospam\', \'Y\', \'N\', \'Y\', \'N\', \'Y\', \'N\', \'Y\', \'N\', \'N\', \'Y\', \'N\', \'Y\', \'N\', NULL, 999, 999, 999), (4, \'antivirus\', \'N\', \'Y\', \'N\', \'Y\', \'N\', \'Y\', \'N\', \'Y\', \'Y\', \'N\', \'Y\', \'N\', \'N\', NULL, 999, 999, 999);

Alter virtual_users table

Now the user needs a little column where you can put the id of the policy that user should use, and even a default value (which of course you should change i guess).

ALTER TABLE `virtual_users` ADD `policy_id` INT( 11 ) NOT NULL DEFAULT \'1\' AFTER `password`

Modifying the view_users view

The idea is that Amavis gets it\'s information from the $sql_select_policy configuration line, to include the per-user policy function I made a new view_users table that includes a column with each users policy ID

So first DROP the view_user table (of course you made a backup or you are on a test server), then create the new view:

CREATE VIEW view_users AS SELECT virtual_users.id, CONCAT(virtual_users.user, \'@\', virtual_domains.name) AS email, virtual_users.password, virtual_users.policy_id FROM virtual_users LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;

Amavis Configuration

There will be 2 lines to insert/modify, and one is because i still cannot get the whitelist/blacklist function working properly (help welcome).. . . so modify /etc/amavis/conf.d/50-user and change/add the following:

$sql_select_policy = \'SELECT *,email FROM view_users,policy\'. \' WHERE (view_users.policy_id=policy.id)\'. \' AND email IN (%k)\'; $sql_select_white_black_list = undef;

Voilà, that should work... there is no interface (yet?) for modifying all this and hence allowing users to chose their own filtering policies, maybe Virtual Mail Manager will come up with something or maybe something else might happen... : ]

A special thanks to Mr. Internet for all your help