--- a/pgsql/create_tables.pgsql Wed Jan 23 22:16:38 2013 +0000
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,747 +0,0 @@
-SET client_encoding = 'UTF8';
-SET client_min_messages = warning;
-
-
-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
- MINVALUE 70000
- MAXVALUE 4294967294
- NO CYCLE;
-
-CREATE SEQUENCE users_uid
- START WITH 70000
- INCREMENT BY 1
- MINVALUE 70000
- MAXVALUE 4294967294
- NO CYCLE;
-
-
-CREATE TABLE transport (
- tid bigint NOT NULL DEFAULT nextval('transport_id'),
- transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
- CONSTRAINT pkey_transport PRIMARY KEY (tid),
- CONSTRAINT ukey_transport UNIQUE (transport)
-);
--- Insert default transport
-INSERT INTO transport(transport) VALUES ('dovecot:');
-
-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 fkey_maillocation_fid_mailboxformat FOREIGN KEY (fid)
- REFERENCES mailboxformat (fid)
-);
--- Insert default Maildir-folder name
-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'),
- 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)
-);
-
-CREATE TABLE domain_name (
- domainname varchar(255) NOT NULL,
- gid bigint NOT NULL,
- is_primary boolean NOT NULL,
- CONSTRAINT pkey_domain_name PRIMARY KEY (domainname),
- CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-CREATE TABLE users (
- local_part varchar(64) NOT NULL,-- only localpart w/o '@'
- 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,
- 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 '@'
- destination varchar(320) NOT NULL,
- CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination),
- CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-CREATE TABLE relocated (
- gid bigint NOT NULL,
- address varchar(64) NOT NULL,
- destination varchar(320) NOT NULL,
- CONSTRAINT pkey_relocated PRIMARY KEY (gid, address),
- CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-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 vmm_domain_info AS
- SELECT gid, count(uid) AS accounts,
- (SELECT count(DISTINCT address)
- FROM alias
- WHERE alias.gid = domain_data.gid) AS aliases,
- (SELECT count(gid)
- FROM relocated
- WHERE relocated.gid = domain_data.gid) AS relocated,
- (SELECT count(gid)
- FROM domain_name
- WHERE domain_name.gid = domain_data.gid
- 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 users USING (gid)
- WHERE domain_name.is_primary
- 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
- primary_count bigint;
-BEGIN
- SELECT INTO primary_count count(gid) + NEW.is_primary::integer
- FROM domain_name
- WHERE domain_name.gid = NEW.gid
- AND is_primary;
-
- IF (primary_count > 1) THEN
- RAISE EXCEPTION 'There can only be one domain marked as primary.';
- END IF;
-
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql STABLE;
-
-
-CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
- FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
-
-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 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;
--- a/pgsql/update_tables_0.5.x-0.6.pgsql Wed Jan 23 22:16:38 2013 +0000
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,658 +0,0 @@
-SET client_encoding = 'UTF8';
-SET client_min_messages = warning;
-
--- ---
--- Create the new service_set table and insert all possible combinations
--- --
-CREATE SEQUENCE service_set_id;
-
-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)
-);
-
-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
-\.
-
--- ---
--- Drop the obsolete VIEWs, we've functions now.
--- ---
-DROP VIEW dovecot_user;
-DROP VIEW dovecot_password;
-DROP VIEW postfix_alias;
-DROP VIEW postfix_maildir;
-DROP VIEW postfix_relocated;
-DROP VIEW postfix_transport;
-DROP VIEW postfix_uid;
--- the vmm_domain_info view will be restored later
-DROP VIEW vmm_domain_info;
-
-CREATE SEQUENCE mailboxformat_id;
-CREATE SEQUENCE quotalimit_id;
-
-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');
-
--- Adjust maillocation table
-ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation;
-ALTER TABLE maillocation RENAME COLUMN maillocation TO directory;
-ALTER TABLE maillocation
- ADD COLUMN fid bigint NOT NULL DEFAULT 1,
- ADD COLUMN extra varchar(1024);
-ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat
- FOREIGN KEY (fid) REFERENCES mailboxformat (fid);
-
-ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
-
--- ---
--- Add quota stuff
--- ---
-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);
-
--- Adjust tables (quota)
-ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1;
-ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit
- FOREIGN KEY (qid) REFERENCES quotalimit (qid);
-
-ALTER TABLE users ADD COLUMN qid bigint NULL DEFAULT NULL;
-ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit
- FOREIGN KEY (qid) REFERENCES quotalimit (qid);
-
-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 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();
-
--- Adjust tables (services)
-ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
-ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set
- FOREIGN KEY (ssid) REFERENCES service_set (ssid);
-
-ALTER TABLE users ADD COLUMN ssid bigint NULL DEFAULT NULL;
--- save current service sets
-UPDATE users u
- SET ssid = ss.ssid
- FROM service_set ss
- WHERE ss.smtp = u.smtp
- AND ss.pop3 = u.pop3
- AND ss.imap = u.imap
- AND ss.managesieve = u.managesieve;
-
-ALTER TABLE users DROP COLUMN smtp;
-ALTER TABLE users DROP COLUMN pop3;
-ALTER TABLE users DROP COLUMN imap;
-ALTER TABLE users DROP COLUMN managesieve;
-ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set
- FOREIGN KEY (ssid) REFERENCES service_set (ssid);
-
--- ---
--- Catchall
--- ---
--- Quota/Service/Transport inheritance
--- ---
-ALTER TABLE users ALTER COLUMN tid DROP NOT NULL;
-ALTER TABLE users ALTER COLUMN tid SET DEFAULT NULL;
--- The qid and ssid columns have already been defined accordingly above.
--- The rest of the logic will take place in the functions.
-
--- While qid and ssid are new and it's perfectly okay for existing users to
--- get NULL values (i.e. inherit from the domain's default), tid existed in
--- vmm 0.5.x. A sensible way forward seems thus to NULL all user records' tid
--- fields where the tid duplicates the value stored in the domain's record.
-UPDATE users
- SET tid = NULL
- WHERE tid = (SELECT tid
- FROM domain_data
- WHERE domain_data.gid = users.gid);
-
--- ---
-
-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)
-);
-
--- ---
--- Account/domain notes
--- ---
-
-ALTER TABLE users ADD COLUMN note text NULL DEFAULT NULL;
-ALTER TABLE domain_data ADD COLUMN note text NULL DEFAULT NULL;
-
--- ---
--- Restore view
--- ---
-CREATE VIEW vmm_domain_info AS
- SELECT gid, count(uid) AS accounts,
- (SELECT count(DISTINCT address)
- FROM alias
- WHERE alias.gid = domain_data.gid) AS aliases,
- (SELECT count(gid)
- FROM relocated
- WHERE relocated.gid = domain_data.gid) AS relocated,
- (SELECT count(gid)
- FROM domain_name
- WHERE domain_name.gid = domain_data.gid
- 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 users USING (gid)
- WHERE domain_name.is_primary
- GROUP BY gid;
-
--- ---
--- Drop all known v0.5 types (the dirty way)
--- ---
-DROP TYPE address_maildir CASCADE;
-DROP TYPE dovecotpassword CASCADE;
-DROP TYPE dovecotuser CASCADE;
-DROP TYPE recipient_destination CASCADE;
-DROP TYPE recipient_transport CASCADE;
-DROP TYPE recipient_uid CASCADE;
-DROP TYPE sender_login CASCADE;
-
--- ######################## 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
-);
-
--- ######################## FUNCTIONs ####################################### --
-
--- ---
--- Parameters (from login name [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: dovecotpassword records
--- ---
-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 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.
--- ---
-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
--- ---
-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
--- ---
-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.
--- ---
-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
--- ---
-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
--- ---
-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
--- ---
-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
--- ---
-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;