Source code in greylist-collect-info.database.function.setup.mysql

To see how this is used return to this tutorial index.

-- Mysql commands to create a database, table and function to implement grey listing with exim.
-- This variant collects more information about the mail connection being tested.
-- Author: Alain Williams <addw@phcomp.co.uk> January 2010
-- SCCS: @(#)greylist-collect-info.database.function.setup.mysql	1.2 10/10/12 17:39:38

-- To use this a different GREYLIST_DEFER macro is needed within the exim config file - since is passes in the destination domain:
-- GREYLIST_DEFER = SELECT greylist_defer('${quote_mysql:$sender_address_domain}', '${quote_mysql:$domain}', '${quote_mysql:$sender_host_address}')

-- Create the database:
CREATE DATABASE exim_db;

-- Create a user that exim will use to connect to the database.
-- Change at least the password on the following line:
GRANT ALL ON exim_db.* TO exim_user@localhost IDENTIFIED BY 'code419';
FLUSH privileges;

USE exim_db;

-- This table contains one row for each machine that attempts to send mail on behalf
-- of a particular sender (or From) domain.
-- If a domain sends mail from several servers you will find several entries for that domain.
CREATE TABLE greylist
(
    sender_host_ip VARCHAR(40)  NOT NULL,   -- long enough for an IPv6 address
    to_domain      VARCHAR(256) NOT NULL,   -- max length domain name; RFC 1034 & RFC 2181
    from_domain    VARCHAR(256) NOT NULL,   -- max length domain name; RFC 1034 & RFC 2181
    first_received DATETIME     NOT NULL,   -- when first got an email
    last_received  DATETIME     NOT NULL,   -- when last got an email from the IP from the domain
    rcpt_count     INT          NOT NULL,   -- number of attempted emails
    UNIQUE (from_domain, sender_host_ip, to_domain)
);

DELIMITER !!

-- Remove any previous version:
DROP FUNCTION IF EXISTS greylist_defer !!

-- Return 'yes' if to defer, if 'no' to not defer, ie accept the mail.
CREATE FUNCTION greylist_defer(sender_domain TEXT, dest_domain TEXT, sender_ip TEXT) RETURNS TEXT
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    COMMENT "Return 'yes' if mail is to be deferred as greylisted"
BEGIN
    DECLARE first DATETIME;    -- When first received email from this source

    -- newAddr gets set to 1 if we have a new address combination
    DECLARE newAddr INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET newAddr = 1;

    -- Have we already got something ?
    SELECT first_received INTO first FROM greylist
        WHERE from_domain = sender_domain AND sender_host_ip = sender_ip AND to_domain = dest_domain;
    
    IF newAddr = 1
    THEN -- This was not in the table, insert a new row and return 1
         INSERT INTO greylist (sender_host_ip, to_domain, from_domain, first_received, last_received, rcpt_count)
             VALUES (sender_ip, dest_domain, sender_domain, NOW(), NOW(), 1);
         RETURN 'yes';    -- return Defer

    ELSE -- It was in the table, update the last_received column
         UPDATE greylist SET last_received = NOW(), rcpt_count = rcpt_count + 1
             WHERE from_domain = sender_domain AND sender_host_ip = sender_ip AND to_domain = dest_domain;

         -- If we first received email long enough ago return 'no' to say 'no defer'.
         -- Change the number '10' below for a different time after first receipt of incoming mail
         IF DATE_ADD(first, INTERVAL 10 MINUTE) < NOW()
         THEN RETURN 'no';    -- return accept
         ELSE RETURN 'yes';   -- return Defer
         END IF;
    END IF;
END
!!

-- If you wish to view the function, you can enter this to a mysql command line client:
--    show create function greylist_defer\G

Return to this tutorial index.