?

Log in

No account? Create an account
Eyes

♥ PostgreSQL, ♥ its rules, ♥ SpamAssassin

This is for future reference purposes, but just in case some of you use the same setup (SpamAssassin with PostgreSQL as its configuration backend), here's a “whitelist” updatable SQL view:

CREATE VIEW spamassassin.whitelist AS
SELECT
  x.prefid,
  substring(x.v, 1, x.si - 1) AS pattern,
  substring(x.v, x.si + 1) AS domain
FROM (
  SELECT
    userpref.prefid,
    userpref.value AS v,
    position(userpref.value, ' ') AS si
  FROM userpref
  WHERE lower(userpref.preference) = 'whitelist_from_rcvd'
) x
UNION ALL 
SELECT
  userpref.prefid,
  userpref.value AS pattern,
  NULL AS domain
FROM userpref
WHERE lower(userpref.preference) = 'whitelist_from';

CREATE RULE delete_rule
AS ON DELETE TO whitelist DO INSTEAD
DELETE FROM userpref
WHERE
  userpref.prefid = OLD.prefid AND
  userpref.preference = CASE
    WHEN old.domain ISNULL
    THEN 'whitelist_from'
    ELSE 'whitelist_from_rcvd'
  END AND
  userpref.value = CASE
    WHEN old.domain ISNULL
    THEN old.pattern
    ELSE old.pattern || ' ' || old.domain
  END;

CREATE RULE insert_rule
AS ON INSERT TO whitelist DO INSTEAD
INSERT INTO userpref (preference, value) 
VALUES (
  CASE
    WHEN new.domain ISNULL
    THEN 'whitelist_from'
    ELSE 'whitelist_from_rcvd'
  END, 
  CASE
    WHEN new.domain ISNULL
    THEN new.pattern
    ELSE new.pattern || ' ' || new.domain
  END
);

CREATE RULE update_rule
AS ON UPDATE TO whitelist DO INSTEAD
UPDATE userpref
SET
  prefid = new.prefid,
  preference = CASE
    WHEN new.domain ISNULL
    THEN 'whitelist_from'
    ELSE 'whitelist_from_rcvd'
  END,
  value = CASE
    WHEN new.domain ISNULL
    THEN new.pattern
    ELSE new.pattern || ' ' || new.domain
  END
WHERE
  userpref.prefid = old.prefid AND
  userpref.preference = CASE
    WHEN old.domain ISNULL
    THEN 'whitelist_from'
    ELSE 'whitelist_from_rcvd'
  END AND
  userpref.value = CASE
    WHEN old.domain ISNULL
    THEN old.pattern
    ELSE old.pattern || ' ' || old.domain
  END;

With this, creating/modifying/deleting whitelist entries become very straightforward:

ab=> INSERT INTO whitelist (pattern) VALUES ('*@*wamu.com');
INSERT 79800 1
ab=> SELECT * FROM userpref ORDER BY prefid;
 prefid | username |     preference      |                   value                    
--------+----------+---------------------+--------------------------------------------
      1 | ab       | report_safe         | 0
      2 | ab       | score               | BAYES_50 0 0 1.5 0.001
      3 | ab       | score               | BAYES_60 0 0 3.5 1.5
      4 | ab       | score               | BAYES_80 0 0 4.0 2.5
      5 | ab       | score               | BAYES_95 0 0 4.5 3.5
      6 | ab       | score               | BAYES_99 0 0 4.8 4.5
      7 | ab       | dns_available       | yes
      8 | ab       | score               | RCVD_IN_BL_SPAMCOP_NET 0 15 0 15
     11 | ab       | whitelist_from_rcvd | bbsmaster@sktelecom.com sktelecom.com
     12 | ab       | whitelist_from_rcvd | *@cardsite.net ecardsite.net
     13 | ab       | whitelist_from_rcvd | *@info.citibank.com bigfootinteractive.com
     14 | ab       | whitelist_from      | sendonly@cowon.com
     15 | ab       | whitelist_from_rcvd | *@dhl.com dhl.com
     16 | ab       | whitelist_from      | pgadmcust@kcp.co.kr
     17 | ab       | whitelist_from      | cyworld@cyworld.com
     18 | ab       | whitelist_from      | isky@isky.co.kr
     19 | ab       | whitelist_from_rcvd | *@sktmembership.com sktelecom.com
     20 | ab       | whitelist_from      | helpmaster@dial070.co.kr
     21 | ab       | whitelist_from      | email@surem.co.kr
     22 | ab       | whitelist_from      | g-info@g-lex.net
     23 | ab       | whitelist_from      | voicemail@g-lex.net
     25 | ab       | whitelist_from      | webmaster@wooribank.com
     26 | ab       | whitelist_from_rcvd | *@verizonwireless.com verizonwireless.com
     27 | ab       | whitelist_from_rcvd | *@isky.co.kr isky.co.kr
     40 | ab       | whitelist_from      | *@*wamu.com
(26 rows)

ab=> UPDATE whitelist SET domain = 'wamu.com' WHERE prefid = 40;
UPDATE 1
ab=> SELECT * FROM userpref ORDER BY prefid;
 prefid | username |     preference      |                   value                    
--------+----------+---------------------+--------------------------------------------
      1 | ab       | report_safe         | 0
      2 | ab       | score               | BAYES_50 0 0 1.5 0.001
      3 | ab       | score               | BAYES_60 0 0 3.5 1.5
      4 | ab       | score               | BAYES_80 0 0 4.0 2.5
      5 | ab       | score               | BAYES_95 0 0 4.5 3.5
      6 | ab       | score               | BAYES_99 0 0 4.8 4.5
      7 | ab       | dns_available       | yes
      8 | ab       | score               | RCVD_IN_BL_SPAMCOP_NET 0 15 0 15
     11 | ab       | whitelist_from_rcvd | bbsmaster@sktelecom.com sktelecom.com
     12 | ab       | whitelist_from_rcvd | *@cardsite.net ecardsite.net
     13 | ab       | whitelist_from_rcvd | *@info.citibank.com bigfootinteractive.com
     14 | ab       | whitelist_from      | sendonly@cowon.com
     15 | ab       | whitelist_from_rcvd | *@dhl.com dhl.com
     16 | ab       | whitelist_from      | pgadmcust@kcp.co.kr
     17 | ab       | whitelist_from      | cyworld@cyworld.com
     18 | ab       | whitelist_from      | isky@isky.co.kr
     19 | ab       | whitelist_from_rcvd | *@sktmembership.com sktelecom.com
     20 | ab       | whitelist_from      | helpmaster@dial070.co.kr
     21 | ab       | whitelist_from      | email@surem.co.kr
     22 | ab       | whitelist_from      | g-info@g-lex.net
     23 | ab       | whitelist_from      | voicemail@g-lex.net
     25 | ab       | whitelist_from      | webmaster@wooribank.com
     26 | ab       | whitelist_from_rcvd | *@verizonwireless.com verizonwireless.com
     27 | ab       | whitelist_from_rcvd | *@isky.co.kr isky.co.kr
     40 | ab       | whitelist_from_rcvd | *@*wamu.com wamu.com
(26 rows)

ab=> SELECT * FROM whitelist ORDER BY prefid;
 prefid |         pattern          |         domain         
--------+--------------------------+------------------------
     11 | bbsmaster@sktelecom.com  | sktelecom.com
     12 | *@cardsite.net           | ecardsite.net
     13 | *@info.citibank.com      | bigfootinteractive.com
     14 | sendonly@cowon.com       | 
     15 | *@dhl.com                | dhl.com
     16 | pgadmcust@kcp.co.kr      | 
     17 | cyworld@cyworld.com      | 
     18 | isky@isky.co.kr          | 
     19 | *@sktmembership.com      | sktelecom.com
     20 | helpmaster@dial070.co.kr | 
     21 | email@surem.co.kr        | 
     22 | g-info@g-lex.net         | 
     23 | voicemail@g-lex.net      | 
     25 | webmaster@wooribank.com  | 
     26 | *@verizonwireless.com    | verizonwireless.com
     27 | *@isky.co.kr             | isky.co.kr
     40 | *@*wamu.com              | wamu.com
(18 rows)

ab=> DELETE FROM whitelist WHERE prefid = 40;
DELETE 1
ab=> 

Note: Insertion with a specific prefid is not supported; the INSERT rule ignores the specified prefid but picks one automatically.  Shouldn't pose any real problem though, because userpref.prefid should be automatically picked anyways.

Comments