Support catchall email addresses

[martin ➬]

The following SQL function provides an alternative to the vmm alias map lookup which supports catchall lookups. I am just dumping this here for now without really knowing how I implemented this back in 2014 or so . I use this functionality for pobox.madduck.net so that I can just hand out e.g. [e-mail], and with an entry like %=@forward.madduck.net, mail to that address will then get forwarded to example.org=[e-mail]. But as I said, right now I don’t remember how I integrated this into vmm. It works though, and has been working for 7 years or more.

CREATE TABLE IF NOT EXISTS catchall (
    gid bigint references domain_data(gid),
    destination varchar(320 not null),
    constraint pkey_catchall primary key (gid, destination)
)

CREATE OR REPLACE FUNCTION postfix_virtual_alias_map2(localpart character varying, the_domain character varying) RETURNS SETOF recipient_destination
    LANGUAGE plpgsql STABLE STRICT
    AS $$
    DECLARE
        recipient varchar(320) := localpart || '@' || the_domain;
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
    BEGIN
        -- ALIASES
        RETURN QUERY
            SELECT recipient,
                   _interpolate_destination(destination, localpart, the_domain)::text
              FROM alias
             WHERE gid = did
               AND address = localpart;
        IF FOUND THEN RETURN; END IF;
        -- RAISE NOTICE 'No matching aliases found, looking up users/relocated: %', recipient;

        -- USERS AND RELOCATED
        RETURN QUERY
            SELECT recipient, recipient::text as destination
              FROM users
             WHERE gid = did
               AND local_part = localpart
             UNION SELECT recipient, recipient::text as destination
              FROM relocated
             WHERE gid = did
               AND address = localpart;
        IF FOUND THEN RETURN; END IF;
        -- RAISE NOTICE 'No matching users/relocated found, looking up default aliases: %', recipient;

        -- DEFAULT ALIASES
        RETURN QUERY
            SELECT recipient,
                   CASE WHEN destination = 'OWNER' THEN
                          _interpolate_destination(owner, localpart, the_domain)::text
                        ELSE
                          _interpolate_destination(destination, localpart, the_domain)::text
                   END as destination
              FROM common_alias a
              JOIN domain_data d
                ON (a.asid = d.asid)
             WHERE d.gid = did
               AND local_part = localpart;
        IF FOUND THEN RETURN; END IF;
        -- RAISE NOTICE 'No matching default alias found, looking up catchall: %', recipient;

        -- CATCHALL
        RETURN QUERY
            SELECT recipient,
                   _interpolate_destination(destination, localpart, the_domain)::text
              FROM catchall c
             WHERE c.gid = did;
        IF FOUND THEN RETURN; END IF;
        -- RAISE NOTICE 'No matching catchalls found, returning empty set: %', recipient;

        RETURN;
    END;
$$;

‌