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.
