Sitemap
Kontakt
Links
+++ erd.trabant +++

+++ erd.trabant +++

blablabla

blablabla

Exim4 - VHost/PGSQL/SA/VScan Configuration

  1. #main
  2. #sql makros
  3. #acl rcpt
  4. #acl data
  5. #router
  6. #transports
  7. #retry
  8. #rewrite
  9. #auth

exim spamlog pgsql

         continue = ${lookup pgsql{SELECT rejected('${quote_pgsql:$sender_address}',\
                     '${quote_pgsql:$local_part}@${quote_pgsql:$domain}',\
                     $spam_score, '$sender_host_address', 'spam', '$acl_c_helostatus')}}
                     
                     
                     
                     
                     
 CREATE SEQUENCE rejectreasons_id_seq;
CREATE TABLE rejectreasons (
       id                  int8         NOT NULL        DEFAULT nextval('rejectreasons_id_seq'),
       reason              varchar(64)  NOT NULL,
       banbase             bool         NOT NULL        DEFAULT false,
       PRIMARY KEY         (id),
       UNIQUE              (reason)
) WITHOUT OIDS;

CREATE SEQUENCE rejectlog_id_seq;
CREATE TABLE rejectlog (
       id              int8     NOT NULL        DEFAULT nextval('rejectlog_id_seq'),
       sender          varchar(256) NOT NULL,
       recipient       varchar(256) NOT NULL,
       clientip        inet,
       arrivedat       timestamp    NOT NULL    DEFAULT now(),
       spamscore       numeric(4,2),
       reasonid        integer  NOT NULL,
       comment         text,
       PRIMARY KEY     (id),
       FOREIGN KEY     (reasonid)       REFERENCES      rejectreasons(id)
) WITHOUT OIDS;


CREATE OR REPLACE FUNCTION rejected(_from text, _to text, _sc numeric(4,2), _ip inet, _reason text, _comme\
nt text) RETURNS void AS $$
DECLARE
        rid     int8;
BEGIN

        SELECT INTO rid id FROM rejectreasons WHERE reason=_reason;
        IF NOT FOUND THEN
           LOCK TABLE rejectreasons IN ACCESS EXCLUSIVE MODE;
           SELECT INTO rid id FROM rejectreasons WHERE reason=_reason;
           IF NOT FOUND THEN
              INSERT INTO rejectreasons (reason) VALUES (_reason) RETURNING id INTO rid;
           END IF;
        END IF;

        INSERT INTO rejectlog (sender, recipient, spamscore, reasonid, comment, clientip)
        VALUES (_from, _to, _sc, rid, _comment, _ip);

        RETURN;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

CREATE OR REPLACE FUNCTION isbanned(_ip inet) RETURNS int AS $$
DECLARE
        ctr int;
BEGIN
        SELECT INTO ctr count(r.id)
        FROM rejectlog r
             JOIN rejectreasons rs ON r.reasonid=rs.id
        WHERE rs.banbase AND r.clientip = _ip
              AND r.arrivedat > now()+'-60 minutes'::interval;
        IF NOT ctr IS NULL AND ctr > 3 THEN
           RETURN 1;
        END IF;

        RETURN 0;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

>>PopUP / Drucken / Print<<
blablabla