FastGrayListMiniTutorialPGSQL

Nigel Metheringham edited this page Nov 25, 2012 · 1 revision

Fast Gray List Mini Tutorial (PostreSQL Edition)

Derivate from FastGrayListMiniTutorial

SQL Table Definition

--
-- Create Sql BD
--
CREATE TABLE greylist (
    id serial NOT NULL,
    relay_ip character varying(20),
    sender_type character varying(6) DEFAULT 'NORMAL'::character varying NOT NULL,
    sender character varying(150),
    recipient character varying(150),
    block_expires timestamp without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone NOT NULL,
    record_expires timestamp without time zone DEFAULT '9999-12-31 23:59:59'::timestamp without time zone NOT NULL,
    create_time timestamp without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone NOT NULL,
    "type" character varying(6) DEFAULT 'MANUAL'::character varying NOT NULL,
    passcount bigint DEFAULT 0::bigint NOT NULL,
    last_pass timestamp without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone NOT NULL,
    blockcount bigint DEFAULT 0::bigint NOT NULL,
    last_block timestamp without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone NOT NULL,
    CONSTRAINT greylist_sender_type_check CHECK ((((sender_type)::text = 'NORMAL'::text) OR ((sender_type)::text = 'BOUNCE'::text))),
    CONSTRAINT greylist_type_check CHECK (((("type")::text = 'AUTO'::text) OR (("type")::text = 'MANUAL'::text)))
);
--
-- Only for debug (optional)
--
CREATE TABLE greylist_log (
    id serial NOT NULL,
    listid bigint DEFAULT 0::bigint NOT NULL,
    "timestamp" timestamp without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone NOT NULL,
    kind character varying(8) DEFAULT 'deferred'::character varying NOT NULL,
    CONSTRAINT greylist_log_kind_check CHECK ((((kind)::text = 'deferred'::text) OR ((kind)::text = 'accepted'::text)))
);

Exim Config File Definitions

First section of the config file.

#Exim.conf file (allways enabled because PgSQL is enabled... ;-)
GREYLIST_ENABLED_GREY          = yes
GREYLIST_ENABLED_LOG           = yes
GREYLIST_INITIAL_DELAY         = 12 MINUTES
GREYLIST_INITIAL_LIFETIME      = 4 HOURS
GREYLIST_WHITE_LIFETIME        = 36 DAY
GREYLIST_BOUNCE_LIFETIME       = 7 DAY
GREYLIST_RECORD_LIFETIME       = 3 DAY
GREYLIST_CLEAR_LIFETIME        = 90 DAY
GREYLIST_TABLE                 = greylist
GREYLIST_LOG_TABLE             = greylist_log
#....
.ifdef GREYLIST_ENABLED_GREY
  GREYLIST_TEST      = SELECT CASE WHEN now() > block_expires THEN 'accepted' \
                       ELSE 'deferred' END AS result, id FROM GREYLIST_TABLE \
                       WHERE now() < record_expires \
                       AND sender_type ILIKE ${if def:sender_address_domain{'NORMAL'}{'BOUNCE'}} \
                       AND sender      ILIKE '${quote_pgsql:${if def:sender_address_domain{$sender_address_domain}{${domain:$h_from:}} }}' \
                       AND recipient   ILIKE '${quote_pgsql:${if def:domain{$domain}{${domain:$h_to:}} }}' \
                       AND relay_ip    ILIKE '${quote_pgsql:${mask:$sender_host_address/24}}' \
                       ORDER BY result DESC LIMIT 1
  GREYLIST_ADD       = DELETE FROM greylist  WHERE relay_ip = '${quote_pgsql:${mask:$sender_host_address/24}}'; \
                       INSERT INTO greylist (relay_ip, sender_type, sender, recipient, block_expires, record_expires, create_time, type) VALUES \
                       ('${quote_pgsql:${mask:$sender_host_address/24}}', ${if def:sender_address_domain{'NORMAL'}{'BOUNCE'}}, \
                                   '${quote_pgsql:${if def:sender_address_domain{$sender_address_domain}{${domain:$h_from:}} }}', \
                                   '${quote_pgsql:${if def:domain{$domain}{${domain:$h_to:}} }}', \
                                   now() + 'GREYLIST_INITIAL_DELAY'::interval, now() + 'GREYLIST_INITIAL_LIFETIME'::interval,now(), 'AUTO');
  GREYLIST_DEFER_HIT = UPDATE GREYLIST_TABLE SET blockcount=blockcount+1, last_block=now() WHERE id = $acl_m9
  GREYLIST_OK_COUNT  = UPDATE GREYLIST_TABLE SET passcount=passcount+1, last_pass=now() WHERE id = $acl_m9
  GREYLIST_OK_NEWTIME = UPDATE GREYLIST_TABLE SET record_expires = now() + 'GREYLIST_WHITE_LIFETIME'::interval WHERE id = $acl_m9 AND type='AUTO'
  GREYLIST_OK_BOUNCE = UPDATE GREYLIST_TABLE SET record_expires = now() + 'GREYLIST_BOUNCE_LIFETIME'::interval WHERE id = $acl_m9 AND type='AUTO'
  GREYLIST_CLEAN     = DELETE FROM greylist WHERE (record_expires < now() - 'GREYLIST_CLEAR_LIFETIME'::interval) AND (type='AUTO')
  GREYLIST_LOG       = INSERT INTO GREYLIST_LOG_TABLE (listid, timestamp, kind) VALUES ($acl_m9, now(), '$acl_m8')
.endif

Greylist ACL

#-GreyList (before rcpt and data):
.ifdef GREYLIST_ENABLED_GREY
greylist_acl:
  # clean expired greylist records at 00 and 30 of all day minutes
  warn  condition       = ${if or {{eq {${substr{10}{2}{$tod_zulu}} }{00}}{eq {${substr{10}{2}{$tod_zulu}} }{30}} }{yes}{no}}
        set acl_m4      = ${lookup pgsql{GREYLIST_CLEAN}}
        log_message     = clean expired greylist records

  # For regular deliveries, check greylist.

  # check greylist tuple, returning "accepted", "deferred" or "unknown"
  # in acl_m8, and the record id in acl_m9

  warn  set acl_m8       = ${lookup pgsql{GREYLIST_TEST}{$value}{result=unknown}}
        # here acl_m8 = "result=x id=y"
        set acl_m9       = ${extract{id}{$acl_m8}{$value}{-1}}
        # now acl_m9 contains the record id (or -1)
        set acl_m8       = ${extract{result}{$acl_m8}{$value}{unknown}}
        # now acl_m8 contains unknown/deferred/accepted
        log_message     = check greylist tuple, set '$acl_m8'

  # check if we know a certain triple, add and defer message if not
  accept
       # if above check returned unknown (no record yet)
       condition        = ${if eq {$acl_m8} {unknown} {yes}}
       # then also add a record
       condition        = ${lookup pgsql{GREYLIST_ADD}{yes}{no}}

  # now log, no matter what the result was
  # if the triple was unknown, we don't need a log entry
  # (and don't get one) because that is implicit through
  # the creation time above.
  .ifdef GREYLIST_ENABLED_LOG
  warn  condition        = ${lookup pgsql{GREYLIST_LOG}}
  .endif

  # check if the triple is still blocked
  accept
       # if above check returned deferred then defer
       condition        = ${if eq{$acl_m8} {deferred} {yes}}
       # and note it down
       condition        = ${lookup pgsql{GREYLIST_DEFER_HIT}{yes}{yes}}

  # use a warn verb to count records that were hit
  warn  condition        = ${lookup pgsql{GREYLIST_OK_COUNT}}

  # use a warn verb to set a new expire time on automatic records,
  # but only if the mail was not a bounce, otherwise set to now().
  warn  !senders         = : postmaster@* : Mailer-Daemon@*
        condition        = ${lookup pgsql{GREYLIST_OK_NEWTIME}}
  warn  senders          = : postmaster@* : Mailer-Daemon@*
        condition        = ${lookup pgsql{GREYLIST_OK_BOUNCE}}
  deny
.endif

You may find all additional information in the original HOWTO

CategoryHowTo

Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.