--- a/pgsql/create_tables-dovecot-1.2.x.pgsql Mon Nov 07 03:22:15 2011 +0000
+++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Thu Jun 28 19:26:50 2012 +0000
@@ -4,8 +4,14 @@
CREATE SEQUENCE transport_id;
+CREATE SEQUENCE mailboxformat_id;
+
CREATE SEQUENCE maillocation_id;
+CREATE SEQUENCE quotalimit_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,
+ sieve boolean NOT NULL DEFAULT TRUE,
+ CONSTRAINT pkey_service_set PRIMARY KEY (ssid),
+ CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, sieve)
+);
+-- Insert all possible service combinations
+COPY service_set (smtp, pop3, imap, sieve) 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,
- sieve 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 (
+ uid bigint NOT NULL,
+ bytes bigint NOT NULL DEFAULT 0,
+ messages integer NOT NULL DEFAULT 0,
+ CONSTRAINT pkey_userquota PRIMARY KEY (uid),
+ CONSTRAINT fkey_userquota_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, sieve
- 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,
+ sieve 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,463 @@
CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
+
+
+CREATE OR REPLACE FUNCTION merge_userquota() RETURNS TRIGGER AS $$
+BEGIN
+ IF NEW.messages < 0 OR NEW.messages IS NULL THEN
+ IF NEW.messages IS NULL THEN
+ NEW.messages = 0;
+ ELSE
+ NEW.messages = -NEW.messages;
+ END IF;
+ RETURN NEW;
+ END IF;
+ LOOP
+ UPDATE userquota
+ SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages
+ WHERE uid = NEW.uid;
+ IF found THEN
+ RETURN NULL;
+ END IF;
+ BEGIN
+ IF NEW.messages = 0 THEN
+ INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, NULL);
+ ELSE
+ INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, -NEW.messages);
+ END IF;
+ RETURN NULL;
+ EXCEPTION
+ WHEN unique_violation THEN
+ -- do nothing, and loop to try the UPDATE again
+ WHEN foreign_key_violation THEN
+ -- break the loop: a non matching uid means no such user
+ RETURN NULL;
+ END;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota
+ FOR EACH ROW EXECUTE PROCEDURE merge_userquota();
+
+-- ######################## 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, sieve
+ FROM users, service_set, domain_data
+ WHERE users.gid = (SELECT gid
+ FROM domain_name
+ WHERE domainname = the_domain)
+ AND local_part = localpart
+ 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;