The Tridecadal Korean (astralblue) wrote,
The Tridecadal Korean
astralblue

  • Mood:

♥ 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.

Tags: mail, postgresql, spamassassin
Subscribe

  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 0 comments