[Dovecot] Reject message if user is over quota. 2/3 solution for Postfix w/ PostgreSQL

[Pascal Volk]

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 at example.com
and addresses with an extension like user+foo at 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