diff -r c0e1fb1b0145 -r a4aead244f75 pgsql/create_tables.pgsql --- a/pgsql/create_tables.pgsql Mon Nov 07 03:22:15 2011 +0000 +++ b/pgsql/create_tables.pgsql Thu Jun 28 19:26:50 2012 +0000 @@ -4,8 +4,14 @@ CREATE SEQUENCE transport_id; +CREATE SEQUENCE mailboxformat_id; + +CREATE SEQUENCE quotalimit_id; + CREATE SEQUENCE maillocation_id; +CREATE SEQUENCE service_set_id; + CREATE SEQUENCE domain_gid START WITH 70000 INCREMENT BY 1 @@ -30,20 +36,80 @@ -- Insert default transport INSERT INTO transport(transport) VALUES ('dovecot:'); -CREATE TABLE maillocation( - mid bigint NOT NULL DEFAULT nextval('maillocation_id'), - maillocation varchar(20) NOT NULL, +CREATE TABLE mailboxformat ( + fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'), + format varchar(20) NOT NULL, + CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid), + CONSTRAINT ukey_mailboxformat UNIQUE (format) +); +-- Insert supported mailbox formats +INSERT INTO mailboxformat(format) VALUES ('maildir'); +INSERT INTO mailboxformat(format) VALUES ('mdbox'); +INSERT INTO mailboxformat(format) VALUES ('sdbox'); + +CREATE TABLE maillocation ( + mid bigint NOT NULL DEFAULT nextval('maillocation_id'), + fid bigint NOT NULL DEFAULT 1, + directory varchar(20) NOT NULL, + extra varchar(1024), CONSTRAINT pkey_maillocation PRIMARY KEY (mid), - CONSTRAINT ukey_maillocation UNIQUE (maillocation) + CONSTRAINT fkey_maillocation_fid_mailboxformat FOREIGN KEY (fid) + REFERENCES mailboxformat (fid) ); -- Insert default Maildir-folder name -INSERT INTO maillocation(maillocation) VALUES ('Maildir'); +INSERT INTO maillocation(directory) VALUES ('Maildir'); + +CREATE TABLE quotalimit ( + qid bigint NOT NULL DEFAULT nextval('quotalimit_id'), + bytes bigint NOT NULL, + messages integer NOT NULL DEFAULT 0, + CONSTRAINT pkey_quotalimit PRIMARY KEY (qid), + CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) +); +-- Insert default (non) quota limit +INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); + +CREATE TABLE service_set ( + ssid bigint NOT NULL DEFAULT nextval('service_set_id'), + smtp boolean NOT NULL DEFAULT TRUE, + pop3 boolean NOT NULL DEFAULT TRUE, + imap boolean NOT NULL DEFAULT TRUE, + managesieve boolean NOT NULL DEFAULT TRUE, + CONSTRAINT pkey_service_set PRIMARY KEY (ssid), + CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, managesieve) +); +-- Insert all possible service combinations +COPY service_set (smtp, pop3, imap, managesieve) FROM stdin; +TRUE TRUE TRUE TRUE +FALSE TRUE TRUE TRUE +TRUE FALSE TRUE TRUE +FALSE FALSE TRUE TRUE +TRUE TRUE FALSE TRUE +FALSE TRUE FALSE TRUE +TRUE FALSE FALSE TRUE +FALSE FALSE FALSE TRUE +TRUE TRUE TRUE FALSE +FALSE TRUE TRUE FALSE +TRUE FALSE TRUE FALSE +FALSE FALSE TRUE FALSE +TRUE TRUE FALSE FALSE +FALSE TRUE FALSE FALSE +TRUE FALSE FALSE FALSE +FALSE FALSE FALSE FALSE +\. CREATE TABLE domain_data ( gid bigint NOT NULL DEFAULT nextval('domain_gid'), - tid bigint NOT NULL DEFAULT 1, -- defualt transport + qid bigint NOT NULL DEFAULT 1, -- default quota limit + ssid bigint NOT NULL DEFAULT 1, -- default service_set + tid bigint NOT NULL DEFAULT 1, -- default transport domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 + note text NULL DEFAULT NULL, CONSTRAINT pkey_domain_data PRIMARY KEY (gid), + CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid) + REFERENCES quotalimit (qid), + CONSTRAINT fkey_domain_data_ssid_service_set FOREIGN KEY (ssid) + REFERENCES service_set (ssid), CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) REFERENCES transport (tid) ); @@ -59,26 +125,38 @@ CREATE TABLE users ( local_part varchar(64) NOT NULL,-- only localpart w/o '@' - passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers + passwd varchar(270) NOT NULL, name varchar(128) NULL, uid bigint NOT NULL DEFAULT nextval('users_uid'), gid bigint NOT NULL, mid bigint NOT NULL DEFAULT 1, - tid bigint NOT NULL DEFAULT 1, - smtp boolean NOT NULL DEFAULT TRUE, - pop3 boolean NOT NULL DEFAULT TRUE, - imap boolean NOT NULL DEFAULT TRUE, - managesieve boolean NOT NULL DEFAULT TRUE, + qid bigint NULL DEFAULT NULL, + ssid bigint NULL DEFAULT NULL, + tid bigint NULL DEFAULT NULL, + note text NULL DEFAULT NULL, CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), CONSTRAINT ukey_users_uid UNIQUE (uid), CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) REFERENCES domain_data (gid), CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) REFERENCES maillocation (mid), + CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid) + REFERENCES quotalimit (qid), + CONSTRAINT fkey_users_ssid_service_set FOREIGN KEY (ssid) + REFERENCES service_set (ssid), CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) REFERENCES transport (tid) ); +CREATE TABLE userquota_11 ( + uid bigint NOT NULL, + path varchar(16) NOT NULL, + current bigint NOT NULL DEFAULT 0, + CONSTRAINT pkey_userquota_11 PRIMARY KEY (uid, path), + CONSTRAINT fkey_userquota_11_uid_users FOREIGN KEY (uid) + REFERENCES users (uid) ON DELETE CASCADE +); + CREATE TABLE alias ( gid bigint NOT NULL, address varchar(64) NOT NULL,-- only localpart w/o '@' @@ -97,59 +175,20 @@ REFERENCES domain_data (gid) ); -CREATE OR REPLACE VIEW dovecot_password AS - SELECT local_part || '@' || domain_name.domainname AS "user", - passwd AS "password", smtp, pop3, imap, managesieve - FROM users - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW dovecot_user AS - SELECT local_part || '@' || domain_name.domainname AS userid, - uid, gid, domain_data.domaindir || '/' || uid AS home, - '~/' || maillocation.maillocation AS mail - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid); +CREATE TABLE catchall ( + gid bigint NOT NULL, + destination varchar(320) NOT NULL, + CONSTRAINT pkey_catchall PRIMARY KEY (gid, destination), + CONSTRAINT fkey_catchall_gid_domain_data FOREIGN KEY (gid) + REFERENCES domain_data (gid) +); CREATE OR REPLACE VIEW postfix_gid AS SELECT gid, domainname FROM domain_name; -CREATE OR REPLACE VIEW postfix_uid AS - SELECT local_part || '@' || domain_name.domainname AS address, uid - FROM users - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW postfix_maildir AS - SELECT local_part || '@' || domain_name.domainname AS address, - domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' - AS maildir - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid); - -CREATE OR REPLACE VIEW postfix_relocated AS - SELECT address || '@' || domain_name.domainname AS address, destination - FROM relocated - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW postfix_alias AS - SELECT address || '@' || domain_name.domainname AS address, destination, gid - FROM alias - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW postfix_transport AS - SELECT local_part || '@' || domain_name.domainname AS address, - transport.transport - FROM users - LEFT JOIN transport USING (tid) - LEFT JOIN domain_name USING (gid); - CREATE OR REPLACE VIEW vmm_domain_info AS - SELECT gid, domainname, transport, domaindir, - count(uid) AS accounts, + SELECT gid, count(uid) AS accounts, (SELECT count(DISTINCT address) FROM alias WHERE alias.gid = domain_data.gid) AS aliases, @@ -159,17 +198,92 @@ (SELECT count(gid) FROM domain_name WHERE domain_name.gid = domain_data.gid - AND NOT domain_name.is_primary) AS aliasdomains + AND NOT domain_name.is_primary) AS aliasdomains, + (SELECT count(gid) + FROM catchall + WHERE catchall.gid = domain_data.gid) AS catchall FROM domain_data LEFT JOIN domain_name USING (gid) - LEFT JOIN transport USING (tid) LEFT JOIN users USING (gid) WHERE domain_name.is_primary - GROUP BY gid, domainname, transport, domaindir; + GROUP BY gid; +-- ########################################################################## -- CREATE LANGUAGE plpgsql; +-- ######################## TYPEs ########################################### -- + +-- --- +-- Data type for function postfix_virtual_mailbox(varchar, varchar) +-- --- +CREATE TYPE address_maildir AS ( + address varchar(320), + maildir text +); +-- --- +-- Data type for function dovecotpassword(varchar, varchar) +-- --- +CREATE TYPE dovecotpassword AS ( + userid varchar(320), + password varchar(270), + smtp boolean, + pop3 boolean, + imap boolean, + managesieve boolean +); +-- --- +-- Data type for function dovecotquotauser(varchar, varchar) +-- --- +CREATE TYPE dovecotquotauser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text, + quota_rule text +); +-- --- +-- Data type for function dovecotuser(varchar, varchar) +-- --- +CREATE TYPE dovecotuser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text +); +-- --- +-- 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 +); +-- --- +-- Data type for function postfix_transport_map(varchar, varchar) +-- --- +CREATE TYPE recipient_transport AS ( + recipient varchar(320), + transport text +); +-- --- +-- Data type for function postfix_virtual_uid_map(varchar, varchar) +-- --- +CREATE TYPE recipient_uid AS ( + recipient varchar(320), + uid bigint +); +-- --- +-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) +-- --- +CREATE TYPE sender_login AS ( + sender varchar(320), + login text +); + +-- ######################## TRIGGERs ######################################## -- CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$ DECLARE @@ -194,3 +308,441 @@ CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); + + +CREATE OR REPLACE FUNCTION merge_userquota_11() RETURNS TRIGGER AS $$ +BEGIN + UPDATE userquota_11 + SET current = current + NEW.current + WHERE uid = NEW.uid AND path = NEW.path; + IF found THEN + RETURN NULL; + ELSE + RETURN NEW; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11 + FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11(); + +-- ######################## FUNCTIONs ####################################### -- + +-- --- +-- 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, service_set 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, service_set, domain_data + WHERE users.gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart + AND service_set.ssid = users.ssid + AND users.gid = domain_data.gid + AND CASE WHEN + users.ssid IS NOT NULL + THEN + service_set.ssid = users.ssid + ELSE + service_set.ssid = domain_data.ssid + END + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; +-- --- +-- Nearly the same as function dovecotuser below. It returns additionally the +-- field quota_rule. +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT +-- ON users, domain_data, domain_name, maillocation, mailboxformat, +-- quotalimit +-- TO dovecot; +-- --- +CREATE OR REPLACE FUNCTION dovecotquotauser( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser +AS $$ + DECLARE + record dovecotquotauser; + 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, + format || ':~/' || directory AS mail, '*:bytes=' || + bytes || ':messages=' || messages AS quota_rule + FROM users, domain_data, mailboxformat, maillocation, quotalimit + WHERE users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + AND mailboxformat.fid = maillocation.fid + AND domain_data.gid = did + AND CASE WHEN + users.qid IS NOT NULL + THEN + quotalimit.qid = users.qid + ELSE + quotalimit.qid = domain_data.qid + END + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; +-- --- +-- 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, mailboxformat +-- 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, + format || ':~/' || directory AS mail + FROM users, domain_data, mailboxformat, maillocation + WHERE users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + AND mailboxformat.fid = maillocation.fid + AND domain_data.gid = did + 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; +-- --- +-- 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; +-- --- +-- 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; + did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); + transport_id bigint; + BEGIN + IF did IS NULL THEN + RETURN; + END IF; + + SELECT tid INTO transport_id + FROM users + WHERE gid = did AND local_part = localpart; + + IF transport_id IS NULL THEN + SELECT tid INTO STRICT transport_id + FROM domain_data + WHERE gid = did; + END IF; + + FOR record IN + SELECT recipient, transport + FROM transport + WHERE tid = transport_id + 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 alias, domain_name TO postfix; +-- +-- For more details see postconf(5) section virtual_alias_maps and virtual(5) +-- --- +CREATE OR REPLACE FUNCTION _interpolate_destination( + IN destination varchar, localpart varchar, IN the_domain varchar) + RETURNS varchar +AS $$ + DECLARE + result varchar(320); + BEGIN + IF position('%' in destination) = 0 THEN + RETURN destination; + END IF; + result := replace(destination, '%n', localpart); + result := replace(result, '%d', the_domain); + result := replace(result, '%=', localpart || '=' || the_domain); + RETURN result; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( + IN localpart varchar, IN the_domain varchar) + RETURNS SETOF recipient_destination +AS $$ + DECLARE + recordc recipient_destination; + record recipient_destination; + catchall_cursor refcursor; + recipient varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT recipient, + _interpolate_destination(destination, localpart, the_domain) + FROM alias + WHERE gid = did + AND address = localpart + LOOP + RETURN NEXT record; + END LOOP; + + IF NOT FOUND THEN + -- There is no matching virtual_alias. If there are no catchall + -- records for this domain, we can just return NULL since Postfix + -- will then later consult mailboxes/relocated itself. But if + -- there is a catchall destination, then it would take precedence + -- over mailboxes/relocated, which is not what we want. Therefore, + -- we must first find out if the query is for an existing mailbox + -- or relocated entry and return the identity mapping if that is + -- the case + OPEN catchall_cursor FOR + SELECT recipient, + _interpolate_destination(destination, localpart, the_domain) + FROM catchall + WHERE gid = did; + FETCH NEXT FROM catchall_cursor INTO recordc; + + IF recordc IS NOT NULL THEN + -- Since there are catchall records for this domain + -- check the mailbox and relocated records and return identity + -- if a matching record exists. + FOR record IN + SELECT recipient, recipient as destination + FROM users + WHERE gid = did + AND local_part = localpart + UNION SELECT recipient, recipient as destination + FROM relocated + WHERE gid = did + AND address = localpart + LOOP + RETURN NEXT record; + END LOOP; + + IF NOT FOUND THEN + -- There were no records found for mailboxes/relocated, + -- so now we can actually iterate the cursor and populate + -- the return set + LOOP + RETURN NEXT recordc; + FETCH NEXT FROM catchall_cursor INTO recordc; + EXIT WHEN recordc IS NULL; + END LOOP; + END IF; + END IF; + CLOSE catchall_cursor; + END IF; + 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: 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||'/'||directory||'/' + 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; +-- --- +-- 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;