amavis-user October 2010 archive
Main Archive Page > Month Archives  > amavis-user archives
amavis-user: Re: [AMaViS-user] Feat req. policy inheritance

Re: [AMaViS-user] Feat req. policy inheritance

From: Mark Martinec <Mark.Martinec+amavis_at_nospam>
Date: Fri Oct 29 2010 - 19:00:07 GMT
To: amavis-user@lists.sourceforge.net

> That is very nice to hear, it meant that you had a very little work
> to do (just fixing the docs).

Btw, I made the example in README.sql make more sense
and illustrate better the concept of multiple matches:

INSERT INTO users VALUES ( 1, 9, 5, 'u1+foo@y.example.com','Name1 Surname1');
INSERT INTO users VALUES ( 2, 8, 5, 'u1@y.example.com', 'Name1 Surname1');
INSERT INTO users VALUES ( 3, 8, 2, 'u2@y.example.com', 'Name2 Surname2');
INSERT INTO users VALUES ( 4, 8, 7, 'u3@z.example.com', 'Name3 Surname3');
INSERT INTO users VALUES ( 5, 8, 7, 'u4@example.com', 'Name4 Surname4');
INSERT INTO users VALUES ( 6, 8, 1, 'u5@example.com', 'Name5 Surname5');
INSERT INTO users VALUES ( 7, 7, 9, 'userB+bar', 'NameB SurnameB');
INSERT INTO users VALUES ( 8, 6, 10, 'userC', 'NameC SurnameC');
INSERT INTO users VALUES ( 9, 6, 11, 'userD', 'NameD SurnameD');
INSERT INTO users VALUES (10, 5, 61, '@.a.b.example.com', NULL);
INSERT INTO users VALUES (11, 4, 62, '@.sub2.example.com', NULL);
INSERT INTO users VALUES (12, 3, 70, '@example.com', NULL);
INSERT INTO users VALUES (12, 2, 70, '@.example.com', NULL);
INSERT INTO users VALUES (13, 1, 80, '@.com', NULL);
INSERT INTO users VALUES (13, 1, 80, '@.edu', NULL);
INSERT INTO users VALUES (14, 0, 99, '@.', NULL); -- catchall

INSERT INTO users VALUES (15, 5, 0, '@sub1.example.net', NULL);
INSERT INTO users VALUES (16, 5, 7, '@sub2.example.net', NULL);
INSERT INTO users VALUES (17, 3, 5, '@example.net', NULL);
INSERT INTO users VALUES (18, 8, 5, 'u1@example.org', 'u1');
INSERT INTO users VALUES (19, 8, 6, 'u2@example.org', 'u2');
INSERT INTO users VALUES (20, 8, 3, 'u3@example.org', 'u3');

INSERT INTO policy (id, policy_name,
  virus_lover, spam_lover, bypass_virus_checks, bypass_spam_checks,
  spam_modifies_subj, spam_tag2_level, spam_kill_level) VALUES
  (0, 'none', NULL,NULL, NULL,NULL, NULL, NULL, NULL),
  (1, 'Non-paying', 'N','N', 'Y','Y', 'N', NULL, NULL),
  (2, 'Uncensored', 'Y','Y', 'N','N', 'N', NULL, NULL),
  (3, 'Wants all spam','N','Y', 'N','N', 'Y', NULL, NULL),
  (4, 'Wants viruses', 'Y','N', 'N','N', 'Y', NULL, NULL),
  (5, 'Normal', 'N','N', 'N','N', NULL, NULL, NULL),
  (6, 'Trigger happy', NULL,NULL, NULL,NULL, NULL, 4.9, 4.9),
  (7, 'Permissive', NULL,NULL, NULL,NULL, NULL, 9, 20),
  (8, '6.5/7.8', NULL,NULL, NULL,NULL, NULL, 6.5, 7.8),
  (9, 'userB', NULL,NULL, NULL,NULL, 'N', 6.3, 6.3),
  (10,'userC', NULL,NULL, NULL,NULL, 'Y', 6.0, 6.0),
  (11,'userD', NULL,NULL, NULL,NULL, NULL, 7, 7),
  (61,'our-sub-a', NULL,'Y', NULL,NULL, NULL, NULL, NULL),
  (62,'our-sub-2', NULL,'Y', NULL,NULL, NULL, NULL, NULL),
  (70,'our domain', NULL,NULL, NULL,NULL, NULL, NULL, NULL),
  (80,'our com & edu', NULL,NULL, NULL,NULL, NULL, NULL, 6.6),
  (99,'catchall', NULL,NULL, NULL,NULL, NULL, 5.6, 6.7);

Now some sample queries:

1) first one for a recipient 'u4@xxx.com' :

SELECT users.email, policy.policy_name, policy.spam_lover,
policy.spam_tag2_level, policy.spam_kill_level, users.id
FROM users LEFT JOIN policy ON users.policy_id=policy.id
WHERE users.email IN ('u4@xxx.com','@xxx.com','@.xxx.com','@.com','@.')
ORDER BY users.priority DESC;

 email | policy_name | spam_lover | spam_tag2_level | spam_kill_level | id
-------+-------------+------------+-----------------+-----------------+----
 @.com | our com&edu | | | 6.6 | 13
 @. | catchall | | 5.6 | 6.7 | 14

Now, searhing from the top on each field, the result would be:
  spam_lover is NULL/undef => fall back to statioc defaults;
  spam_tag2_level = 5.6;
  spam_kill_level = 6.6;

2) recipient 'u2@example.com':

    email | policy_name |spam_lover|spam_tag2_level|spam_kill_level| id
--------------+-------------+----------+---------------+---------------+----
 @example.com | our domain | | | | 12
 @.com | our com&edu | | | 6.6 | 13
 @. | catchall | | 5.6 | 6.7 | 14

  spam_lover is NULL/undef => fall back to statioc defaults;
  spam_tag2_level = 5.6;
  spam_kill_level = 6.6;

3) recipient 'u4@example.com':

     email | policy_name |spam_lover|spam_tag2_level|spam_kill_level| id
----------------+-------------+----------+---------------+---------------+----
 u4@example.com | Permissive | | 9 | 20 | 5
 @example.com | our domain | | | | 12
 @.com | our com&edu | | | 6.6 | 13
 @. | catchall | | 5.6 | 6.7 | 14

  spam_lover is NULL/undef => fall back to statioc defaults;
  spam_tag2_level = 9;
  spam_kill_level = 20;

4) 'u5@example.com':

     email | policy_name |spam_lover|spam_tag2_level|spam_kill_level| id
----------------+-------------+----------+---------------+---------------+----
 u5@example.com | Non-paying | N | | | 6
 @example.com | our domain | | | | 12
 @.com | our com&edu | | | 6.6 | 13
 @. | catchall | | 5.6 | 6.7 | 14

  spam_lover is 'N', i.e. false;
  spam_tag2_level = 5.6;
  spam_kill_level = 6.6;

5) 'dd@example.de':

 email | policy_name | spam_lover | spam_tag2_level | spam_kill_level | id
-------+-------------+------------+-----------------+-----------------+----
 @. | catchall | | 5.6 | 6.7 | 14

  spam_lover is NULL/undef => fall back to statioc defaults;
  spam_tag2_level = 5.6;
  spam_kill_level = 6.7;

Mark

------------------------------------------------------------------------------
Nokia and AT&T present the 2010 Calling All Innovators-North America contest
Create new apps & games for the Nokia N8 for consumers in U.S. and Canada
$10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing
Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
http://p.sf.net/sfu/nokia-dev2dev
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
 Please visit http://www.ijs.si/software/amavisd/ regularly
 For administrativa requests please send email to rainer at openantivirus dot org