+
−-- --- Information:
+
−-- This file contains some data types and functions these should speed up some
+
−-- operations. Read the comment on each data type/functions for more details.
+
−-- ---
+
−
+
−-- ---
+
−-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
+
−-- ---
+
−CREATE TYPE sender_login AS (
+
−    sender  varchar(320),
+
−    login   text
+
−);
+
−
+
−-- ---
+
−-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
+
−--      varchar localpart
+
−--      varchar the_domain
+
−-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
+
−--      set of sender_login records.
+
−--
+
−-- Required access privileges for your postfix database user:
+
−--      GRANT SELECT ON domain_name, users, alias TO postfix;
+
−--
+
−-- For more details see postconf(5) section smtpd_sender_login_maps
+
−-- ---
+
−CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
+
−    IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
+
−AS $$
+
−    DECLARE
+
−        rec sender_login;
+
−        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+
−        sender varchar(320) := localpart || '@' || the_domain;
+
−    BEGIN
+
−        -- Get all addresses for 'localpart' in the primary and aliased domains
+
−        FOR rec IN
+
−            SELECT sender, local_part || '@' || domainname
+
−              FROM domain_name, users
+
−             WHERE domain_name.gid = did
+
−               AND users.gid = did
+
−               AND users.local_part = localpart
+
−            LOOP
+
−                RETURN NEXT rec;
+
−            END LOOP;
+
−        IF NOT FOUND THEN
+
−            -- Loop over the alias addresses for localpart@the_domain
+
−            FOR rec IN
+
−                SELECT DISTINCT sender, destination
+
−                  FROM alias
+
−                 WHERE alias.gid = did
+
−                   AND alias.address = localpart
+
−                LOOP
+
−                    RETURN NEXT rec;
+
−                END LOOP;
+
−        END IF;
+
−        RETURN;
+
−    END;
+
−$$ LANGUAGE plpgsql STABLE
+
−RETURNS NULL ON NULL INPUT
+
−EXTERNAL SECURITY INVOKER;
+
−
+
−-- ########################################################################## --
+
−
+
−-- ---
+
−-- Data type for function postfix_virtual_mailbox(varchar, varchar)
+
−-- ---
+
−CREATE TYPE address_maildir AS (
+
−    address varchar(320),
+
−    maildir text
+
−);
+
−
+
−-- ---
+
−-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+
−--      varchar localpart
+
−--      varchar the_domain
+
−-- Returns: address_maildir records
+
−--
+
−-- Required access privileges for your postfix database user:
+
−--      GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
+
−--
+
−-- For more details see postconf(5) section virtual_mailbox_maps
+
−-- ---
+
−CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
+
−   IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
+
−AS $$
+
−    DECLARE
+
−        rec address_maildir;
+
−        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+
−        address varchar(320) := localpart || '@' || the_domain;
+
−    BEGIN
+
−        FOR rec IN
+
−            SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/'
+
−              FROM domain_data, users, maillocation
+
−             WHERE domain_data.gid = did
+
−               AND users.gid = did
+
−               AND users.local_part = localpart
+
−               AND maillocation.mid = users.mid
+
−            LOOP
+
−                RETURN NEXT rec;
+
−            END LOOP;
+
−        RETURN;
+
−    END;
+
−$$ LANGUAGE plpgsql STABLE
+
−RETURNS NULL ON NULL INPUT
+
−EXTERNAL SECURITY INVOKER;
+
−
+
−-- ########################################################################## --
+
−
+
−-- ---
+
−-- Data type for functions: postfix_relocated_map(varchar, varchar)
+
−--                          postfix_virtual_alias_map(varchar, varchar)
+
−--                          
+
−-- ---
+
−CREATE TYPE recipient_destination AS (
+
−    recipient   varchar(320),
+
−    destination text
+
−);
+
−
+
−-- ---
+
−-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+
−--      varchar localpart
+
−--      varchar the_domain
+
−-- Returns: recipient_destination records
+
−--
+
−-- Required access privileges for your postfix database user:
+
−--      GRANT SELECT ON alias, domain_name TO postfix;
+
−--
+
−-- For more details see postconf(5) section virtual_alias_maps and virtual(5)
+
−-- ---
+
−CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
+
−    IN localpart varchar, IN the_domain varchar)
+
−    RETURNS SETOF recipient_destination
+
−AS $$
+
−    DECLARE
+
−        record recipient_destination;
+
−        recipient varchar(320) := localpart || '@' || the_domain;
+
−        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+
−    BEGIN
+
−        FOR record IN
+
−            SELECT recipient, destination
+
−              FROM alias
+
−             WHERE gid = did
+
−               AND address = localpart
+
−            LOOP
+
−                RETURN NEXT record;
+
−            END LOOP;
+
−        RETURN;
+
−    END;
+
−$$ LANGUAGE plpgsql STABLE
+
−RETURNS NULL ON NULL INPUT
+
−EXTERNAL SECURITY INVOKER;
+
−
+
−-- ########################################################################## --
+
−
+
−-- ---
+
−-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+
−--      varchar localpart
+
−--      varchar the_domain
+
−-- Returns: recipient_destination records
+
−--
+
−-- Required access privileges for your postfix database user:
+
−--      GRANT SELECT ON domain_name, relocated TO postfix;
+
−--
+
−-- For more details see postconf(5) section relocated_maps and relocated(5)
+
−-- ---
+
−CREATE OR REPLACE FUNCTION postfix_relocated_map(
+
−    IN localpart varchar, IN the_domain varchar)
+
−    RETURNS SETOF recipient_destination
+
−AS $$
+
−    DECLARE
+
−        record recipient_destination;
+
−        recipient varchar(320) := localpart || '@' || the_domain;
+
−        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+
−    BEGIN
+
−        FOR record IN
+
−            SELECT recipient, destination
+
−              FROM relocated
+
−             WHERE gid = did
+
−               AND address = localpart
+
−            LOOP
+
−                RETURN NEXT record;
+
−            END LOOP;
+
−        RETURN;
+
−    END;
+
−$$ LANGUAGE plpgsql STABLE
+
−RETURNS NULL ON NULL INPUT
+
−EXTERNAL SECURITY INVOKER;
+
−
+
−-- ########################################################################## --
+
−
+
−-- ---
+
−-- Data type for function postfix_transport_map(varchar, varchar)
+
−-- ---
+
−CREATE TYPE recipient_transport AS (
+
−    recipient   varchar(320),
+
−    transport   text
+
−);
+
−
+
−-- ---
+
−-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+
−--      varchar localpart
+
−--      varchar the_domain
+
−-- Returns: recipient_transport records
+
−--
+
−-- Required access privileges for your postfix database user:
+
−--      GRANT SELECT ON users, transport, domain_name TO postfix;
+
−--
+
−-- For more details see postconf(5) section transport_maps and transport(5)
+
−-- ---
+
−CREATE OR REPLACE FUNCTION postfix_transport_map(
+
−    IN localpart varchar, IN the_domain varchar)
+
−    RETURNS SETOF recipient_transport
+
−AS $$
+
−    DECLARE
+
−        record recipient_transport;
+
−        recipient varchar(320) := localpart || '@' || the_domain;
+
−    BEGIN
+
−        FOR record IN
+
−            SELECT recipient, transport
+
−              FROM transport
+
−             WHERE tid = (SELECT tid
+
−                            FROM users
+
−                           WHERE gid = (SELECT gid
+
−                                          FROM domain_name
+
−                                         WHERE domainname = the_domain)
+
−                             AND local_part = localpart)
+
−            LOOP
+
−                RETURN NEXT record;
+
−            END LOOP;
+
−        RETURN;
+
−    END;
+
−$$ LANGUAGE plpgsql STABLE
+
−RETURNS NULL ON NULL INPUT
+
−EXTERNAL SECURITY INVOKER;
+
−
+
−-- ########################################################################## --
+
−
+
−-- ---
+
−-- Data type for function postfix_virtual_uid_map(varchar, varchar)
+
−-- ---
+
−CREATE TYPE recipient_uid AS (
+
−    recipient   varchar(320),
+
−    uid         bigint
+
−);
+
−
+
−-- ---
+
−-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+
−--      varchar localpart
+
−--      varchar the_domain
+
−-- Returns: recipient_uid records
+
−--
+
−-- Required access privileges for your postfix database user:
+
−--      GRANT SELECT ON users, domain_name TO postfix;
+
−--
+
−-- For more details see postconf(5) section virtual_uid_maps
+
−-- ---
+
−CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
+
−    IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
+
−AS $$
+
−    DECLARE
+
−        record recipient_uid;
+
−        recipient varchar(320) := localpart || '@' || the_domain;
+
−    BEGIN
+
−        FOR record IN
+
−            SELECT recipient, uid
+
−              FROM users
+
−             WHERE gid = (SELECT gid
+
−                            FROM domain_name
+
−                           WHERE domainname = the_domain)
+
−               AND local_part = localpart
+
−            LOOP
+
−                RETURN NEXT record;
+
−            END LOOP;
+
−        RETURN;
+
−    END;
+
−$$ LANGUAGE plpgsql STABLE
+
−RETURNS NULL ON NULL INPUT
+
−EXTERNAL SECURITY INVOKER;
+
−
+
−-- ########################################################################## --
+
−
+
−-- ---
+
−-- Data type for function dovecotuser(varchar, varchar)
+
−-- ---
+
−CREATE TYPE dovecotuser AS (
+
−    userid      varchar(320),
+
−    uid         bigint,
+
−    gid         bigint,
+
−    home        text,
+
−    mail        text
+
−);
+
−
+
−-- ---
+
−-- Parameters (from login name [localpart@the_domain]):
+
−--      varchar localpart
+
−--      varchar the_domain
+
−-- Returns: dovecotuser records
+
−--
+
−-- Required access privileges for your dovecot database user:
+
−--      GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;
+
−--
+
−-- For more details see http://wiki.dovecot.org/UserDatabase
+
−-- ---
+
−CREATE OR REPLACE FUNCTION dovecotuser(
+
−    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
+
−AS $$
+
−    DECLARE
+
−        record dovecotuser;
+
−        userid varchar(320) := localpart || '@' || the_domain;
+
−        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+
−    BEGIN
+
−        FOR record IN
+
−            SELECT userid, uid, did, domaindir ||'/'|| uid AS home,
+
−                   '~/'|| maillocation AS mail
+
−              FROM users, domain_data, maillocation
+
−             WHERE users.gid = did
+
−               AND users.local_part = localpart
+
−               AND maillocation.mid = users.mid
+
−               AND domain_data.gid = did
+
−            LOOP
+
−                RETURN NEXT record;
+
−            END LOOP;
+
−        RETURN;
+
−    END;
+
−$$ LANGUAGE plpgsql STABLE
+
−RETURNS NULL ON NULL INPUT
+
−EXTERNAL SECURITY INVOKER;
+
−
+
−-- ########################################################################## --
+
−
+
−-- ---
+
−-- Data type for function dovecotpassword(varchar, varchar)
+
−-- ---
+
−CREATE TYPE dovecotpassword AS (
+
−    userid      varchar(320),
+
−    password    varchar(74),
+
−    smtp        boolean,
+
−    pop3        boolean,
+
−    imap        boolean,
+
−    managesieve boolean
+
−);
+
−
+
−-- ---
+
−-- Parameters (from login name [localpart@the_domain]):
+
−--      varchar localpart
+
−--      varchar the_domain
+
−-- Returns: dovecotpassword records
+
−--
+
−-- Required access privileges for your dovecot database user:
+
−--      GRANT SELECT ON users, domain_name TO dovecot;
+
−--
+
−-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
+
−-- ---
+
−CREATE OR REPLACE FUNCTION dovecotpassword(
+
−    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
+
−AS $$
+
−    DECLARE
+
−        record dovecotpassword;
+
−        userid varchar(320) := localpart || '@' || the_domain;
+
−    BEGIN
+
−        FOR record IN
+
−            SELECT userid, passwd, smtp, pop3, imap, managesieve
+
−              FROM users
+
−             WHERE gid = (SELECT gid
+
−                            FROM domain_name
+
−                           WHERE domainname = the_domain)
+
−               AND local_part = localpart
+
−            LOOP
+
−                RETURN NEXT record;
+
−            END LOOP;
+
−        RETURN;
+
−    END;
+
−$$ LANGUAGE plpgsql STABLE
+
−RETURNS NULL ON NULL INPUT
+
−EXTERNAL SECURITY INVOKER;