Hi,

according to the both discussions in the last few days I've tried to find a solution for Postfix with a PostgreSQL back-end. Its working nice for regular recipient addresses like user@example.com and addresses with an extension like user+foo@example.com. Alias addresses are currently ignored. * An alias may expand into multiple destinations. If one of this destinations is over quota Postfix will expose the destination address from the alias * An alias address may point to an external destination. * Aliases, with just one internal destination maybe simple to expand (homework ;-))

Maybe someone of you would provide ideas for handling alias addresses.

The procedure is designed for global quota settings. If one of you is using quota settings per user, you have to modify the procedure.

Integrating in main.cf: smtpd_recipient_restrictions = ... reject_unlisted_recipient check_recipient_access pgsql:/etc/postfix/virtual_global_quota_check.cf ...

virtual_global_quota_check.cf: # The hosts that Postfix will try to connect to hosts = localhost # The user name and password to log into the pgsql server. user = $Your_Postfix_database_user password = $PASSWORD # The database name on the servers. dbname = mailsys # The query query = SELECT postfix_global_quota_check('%u', '%d', 10485760, 1000) # quota limit in bytes ----------------------------^
# quota limit in messages -----------------------------------^

SQL:

CREATE OR REPLACE FUNCTION postfix_global_quota_check( IN localpart varchar, IN the_domain varchar, IN q_stor bigint, IN q_msgs bigint) RETURNS TEXT AS $$ DECLARE result text := 'DUNNO'; -- remove extenstion from localpart (user+foo -> user) lpart varchar := (SELECT regexp_replace(localpart, E'\\+.*$', ''));

    -- XXX how to handle alias addresses?
    --  stop here if the destination is an external address
    --  how to handle aliases with multiple destinations???

    address varchar := lpart||'@'||the_domain;
    storage bigint := (SELECT current FROM quota
        WHERE username = address
          AND path = 'quota/storage');
    messages bigint := (SELECT current FROM quota
        WHERE username = address
          AND path = 'quota/messages');
BEGIN
    --RAISE NOTICE 'users storage: % bytes', storage;
    --RAISE NOTICE 'users message count: %', messages;
    IF storage >= q_stor THEN
        result := '552 5.2.2 Quota exceeded (mailbox for user is full)';
    ELSE
        IF messages >= q_msgs THEN
            result := '552 5.2.2 Quota exceeded (mailbox for user is full)';
        END IF;
    END IF;
    RETURN result;
END;

$$ LANGUAGE plpgsql STABLE RETURNS NULL ON NULL INPUT EXTERNAL SECURITY INVOKER;

GRANT SELECT ON quota TO $Your_Postfix_database_user;

Regards, Pascal

-- Ubuntu is an ancient African word meaning “I can’t install Debian.” -- unknown