# HG changeset patch # User Pascal Volk # Date 1359298003 0 # Node ID 4f00a6de934c6abc7402204f456bf170f46dbcb5 # Parent 932cd76bf879a8d6d790e9323e4e37910d9913f4 pgsql: Removed files for Dovecot < 1.2.0. diff -r 932cd76bf879 -r 4f00a6de934c pgsql/create_tables.pgsql --- 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; diff -r 932cd76bf879 -r 4f00a6de934c pgsql/update_tables_0.5.x-0.6.pgsql --- 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;