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.