# HG changeset patch # User Pascal Volk # Date 1256257233 0 # Node ID 639cf400396598422df998480b43a13a72f0722e # Parent 78b6b06188d3b0011bb80fa7fb11d106783f3ed2 *.pgsql: moved to pgsql/ diff -r 78b6b06188d3 -r 639cf4003965 create_optional_types_and_functions-dovecot-1.2.x.pgsql --- a/create_optional_types_and_functions-dovecot-1.2.x.pgsql Thu Oct 22 19:30:46 2009 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,374 +0,0 @@ --- --- Information: --- This file contains some data types and functions these should speed up some --- operations. Read the comment on each data type/functions for more details. --- --- - --- --- --- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) --- --- -CREATE TYPE sender_login AS ( - sender varchar(320), - login text -); - --- --- --- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): --- set of sender_login records. --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON domain_name, users, alias TO postfix; --- --- For more details see postconf(5) section smtpd_sender_login_maps --- --- -CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login -AS $$ - DECLARE - rec sender_login; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - sender varchar(320) := localpart || '@' || the_domain; - BEGIN - -- Get all addresses for 'localpart' in the primary and aliased domains - FOR rec IN - SELECT sender, local_part || '@' || domainname - FROM domain_name, users - WHERE domain_name.gid = did - AND users.gid = did - AND users.local_part = localpart - LOOP - RETURN NEXT rec; - END LOOP; - IF NOT FOUND THEN - -- Loop over the alias addresses for localpart@the_domain - FOR rec IN - SELECT DISTINCT sender, destination - FROM alias - WHERE alias.gid = did - AND alias.address = localpart - LOOP - RETURN NEXT rec; - END LOOP; - END IF; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function postfix_virtual_mailbox(varchar, varchar) --- --- -CREATE TYPE address_maildir AS ( - address varchar(320), - maildir text -); - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: address_maildir records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix; --- --- For more details see postconf(5) section virtual_mailbox_maps --- --- -CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir -AS $$ - DECLARE - rec address_maildir; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - address varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR rec IN - SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/' - FROM domain_data, users, maillocation - WHERE domain_data.gid = did - AND users.gid = did - AND users.local_part = localpart - AND maillocation.mid = users.mid - LOOP - RETURN NEXT rec; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for functions: postfix_relocated_map(varchar, varchar) --- postfix_virtual_alias_map(varchar, varchar) --- --- --- -CREATE TYPE recipient_destination AS ( - recipient varchar(320), - destination text -); - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: recipient_destination records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON alias, domain_name TO postfix; --- --- For more details see postconf(5) section virtual_alias_maps and virtual(5) --- --- -CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( - IN localpart varchar, IN the_domain varchar) - RETURNS SETOF recipient_destination -AS $$ - DECLARE - record recipient_destination; - recipient varchar(320) := localpart || '@' || the_domain; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - BEGIN - FOR record IN - SELECT recipient, destination - FROM alias - WHERE gid = did - AND address = localpart - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: recipient_destination records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON domain_name, relocated TO postfix; --- --- For more details see postconf(5) section relocated_maps and relocated(5) --- --- -CREATE OR REPLACE FUNCTION postfix_relocated_map( - IN localpart varchar, IN the_domain varchar) - RETURNS SETOF recipient_destination -AS $$ - DECLARE - record recipient_destination; - recipient varchar(320) := localpart || '@' || the_domain; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - BEGIN - FOR record IN - SELECT recipient, destination - FROM relocated - WHERE gid = did - AND address = localpart - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function postfix_transport_map(varchar, varchar) --- --- -CREATE TYPE recipient_transport AS ( - recipient varchar(320), - transport text -); - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: recipient_transport records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON users, transport, domain_name TO postfix; --- --- For more details see postconf(5) section transport_maps and transport(5) --- --- -CREATE OR REPLACE FUNCTION postfix_transport_map( - IN localpart varchar, IN the_domain varchar) - RETURNS SETOF recipient_transport -AS $$ - DECLARE - record recipient_transport; - recipient varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR record IN - SELECT recipient, transport - FROM transport - WHERE tid = (SELECT tid - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart) - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function postfix_virtual_uid_map(varchar, varchar) --- --- -CREATE TYPE recipient_uid AS ( - recipient varchar(320), - uid bigint -); - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: recipient_uid records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON users, domain_name TO postfix; --- --- For more details see postconf(5) section virtual_uid_maps --- --- -CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid -AS $$ - DECLARE - record recipient_uid; - recipient varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR record IN - SELECT recipient, uid - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function dovecotuser(varchar, varchar) --- --- -CREATE TYPE dovecotuser AS ( - userid varchar(320), - uid bigint, - gid bigint, - home text, - mail text -); - --- --- --- Parameters (from login name [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: dovecotuser records --- --- Required access privileges for your dovecot database user: --- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot; --- --- For more details see http://wiki.dovecot.org/UserDatabase --- --- -CREATE OR REPLACE FUNCTION dovecotuser( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser -AS $$ - DECLARE - record dovecotuser; - userid varchar(320) := localpart || '@' || the_domain; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - BEGIN - FOR record IN - SELECT userid, uid, did, domaindir ||'/'|| uid AS home, - '~/'|| maillocation AS mail - FROM users, domain_data, maillocation - WHERE users.gid = did - AND users.local_part = localpart - AND maillocation.mid = users.mid - AND domain_data.gid = did - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function dovecotpassword(varchar, varchar) --- --- -CREATE TYPE dovecotpassword AS ( - userid varchar(320), - password varchar(74), - smtp boolean, - pop3 boolean, - imap boolean, - sieve boolean -); - --- --- --- Parameters (from login name [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: dovecotpassword records --- --- Required access privileges for your dovecot database user: --- GRANT SELECT ON users, domain_name TO dovecot; --- --- For more details see http://wiki.dovecot.org/AuthDatabase/SQL --- --- -CREATE OR REPLACE FUNCTION dovecotpassword( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword -AS $$ - DECLARE - record dovecotpassword; - userid varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR record IN - SELECT userid, passwd, smtp, pop3, imap, sieve - 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 78b6b06188d3 -r 639cf4003965 create_optional_types_and_functions.pgsql --- a/create_optional_types_and_functions.pgsql Thu Oct 22 19:30:46 2009 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,374 +0,0 @@ --- --- Information: --- This file contains some data types and functions these should speed up some --- operations. Read the comment on each data type/functions for more details. --- --- - --- --- --- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) --- --- -CREATE TYPE sender_login AS ( - sender varchar(320), - login text -); - --- --- --- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): --- set of sender_login records. --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON domain_name, users, alias TO postfix; --- --- For more details see postconf(5) section smtpd_sender_login_maps --- --- -CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login -AS $$ - DECLARE - rec sender_login; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - sender varchar(320) := localpart || '@' || the_domain; - BEGIN - -- Get all addresses for 'localpart' in the primary and aliased domains - FOR rec IN - SELECT sender, local_part || '@' || domainname - FROM domain_name, users - WHERE domain_name.gid = did - AND users.gid = did - AND users.local_part = localpart - LOOP - RETURN NEXT rec; - END LOOP; - IF NOT FOUND THEN - -- Loop over the alias addresses for localpart@the_domain - FOR rec IN - SELECT DISTINCT sender, destination - FROM alias - WHERE alias.gid = did - AND alias.address = localpart - LOOP - RETURN NEXT rec; - END LOOP; - END IF; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function postfix_virtual_mailbox(varchar, varchar) --- --- -CREATE TYPE address_maildir AS ( - address varchar(320), - maildir text -); - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: address_maildir records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix; --- --- For more details see postconf(5) section virtual_mailbox_maps --- --- -CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir -AS $$ - DECLARE - rec address_maildir; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - address varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR rec IN - SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/' - FROM domain_data, users, maillocation - WHERE domain_data.gid = did - AND users.gid = did - AND users.local_part = localpart - AND maillocation.mid = users.mid - LOOP - RETURN NEXT rec; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for functions: postfix_relocated_map(varchar, varchar) --- postfix_virtual_alias_map(varchar, varchar) --- --- --- -CREATE TYPE recipient_destination AS ( - recipient varchar(320), - destination text -); - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: recipient_destination records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON alias, domain_name TO postfix; --- --- For more details see postconf(5) section virtual_alias_maps and virtual(5) --- --- -CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( - IN localpart varchar, IN the_domain varchar) - RETURNS SETOF recipient_destination -AS $$ - DECLARE - record recipient_destination; - recipient varchar(320) := localpart || '@' || the_domain; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - BEGIN - FOR record IN - SELECT recipient, destination - FROM alias - WHERE gid = did - AND address = localpart - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: recipient_destination records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON domain_name, relocated TO postfix; --- --- For more details see postconf(5) section relocated_maps and relocated(5) --- --- -CREATE OR REPLACE FUNCTION postfix_relocated_map( - IN localpart varchar, IN the_domain varchar) - RETURNS SETOF recipient_destination -AS $$ - DECLARE - record recipient_destination; - recipient varchar(320) := localpart || '@' || the_domain; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - BEGIN - FOR record IN - SELECT recipient, destination - FROM relocated - WHERE gid = did - AND address = localpart - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function postfix_transport_map(varchar, varchar) --- --- -CREATE TYPE recipient_transport AS ( - recipient varchar(320), - transport text -); - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: recipient_transport records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON users, transport, domain_name TO postfix; --- --- For more details see postconf(5) section transport_maps and transport(5) --- --- -CREATE OR REPLACE FUNCTION postfix_transport_map( - IN localpart varchar, IN the_domain varchar) - RETURNS SETOF recipient_transport -AS $$ - DECLARE - record recipient_transport; - recipient varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR record IN - SELECT recipient, transport - FROM transport - WHERE tid = (SELECT tid - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart) - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function postfix_virtual_uid_map(varchar, varchar) --- --- -CREATE TYPE recipient_uid AS ( - recipient varchar(320), - uid bigint -); - --- --- --- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: recipient_uid records --- --- Required access privileges for your postfix database user: --- GRANT SELECT ON users, domain_name TO postfix; --- --- For more details see postconf(5) section virtual_uid_maps --- --- -CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid -AS $$ - DECLARE - record recipient_uid; - recipient varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR record IN - SELECT recipient, uid - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function dovecotuser(varchar, varchar) --- --- -CREATE TYPE dovecotuser AS ( - userid varchar(320), - uid bigint, - gid bigint, - home text, - mail text -); - --- --- --- Parameters (from login name [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: dovecotuser records --- --- Required access privileges for your dovecot database user: --- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot; --- --- For more details see http://wiki.dovecot.org/UserDatabase --- --- -CREATE OR REPLACE FUNCTION dovecotuser( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser -AS $$ - DECLARE - record dovecotuser; - userid varchar(320) := localpart || '@' || the_domain; - did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); - BEGIN - FOR record IN - SELECT userid, uid, did, domaindir ||'/'|| uid AS home, - '~/'|| maillocation AS mail - FROM users, domain_data, maillocation - WHERE users.gid = did - AND users.local_part = localpart - AND maillocation.mid = users.mid - AND domain_data.gid = did - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; - --- ########################################################################## -- - --- --- --- Data type for function dovecotpassword(varchar, varchar) --- --- -CREATE TYPE dovecotpassword AS ( - userid varchar(320), - password varchar(74), - smtp boolean, - pop3 boolean, - imap boolean, - managesieve boolean -); - --- --- --- Parameters (from login name [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: dovecotpassword records --- --- Required access privileges for your dovecot database user: --- GRANT SELECT ON users, domain_name TO dovecot; --- --- For more details see http://wiki.dovecot.org/AuthDatabase/SQL --- --- -CREATE OR REPLACE FUNCTION dovecotpassword( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword -AS $$ - DECLARE - record dovecotpassword; - userid varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR record IN - SELECT userid, passwd, smtp, pop3, imap, managesieve - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; diff -r 78b6b06188d3 -r 639cf4003965 create_tables-dovecot-1.2.x.pgsql --- a/create_tables-dovecot-1.2.x.pgsql Thu Oct 22 19:30:46 2009 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,196 +0,0 @@ -SET client_encoding = 'UTF8'; -SET client_min_messages = warning; - - -CREATE SEQUENCE transport_id; - -CREATE SEQUENCE maillocation_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 maillocation( - mid bigint NOT NULL DEFAULT nextval('maillocation_id'), - maillocation varchar(20) NOT NULL, - CONSTRAINT pkey_maillocation PRIMARY KEY (mid), - CONSTRAINT ukey_maillocation UNIQUE (maillocation) -); --- Insert default Maildir-folder name -INSERT INTO maillocation(maillocation) VALUES ('Maildir'); - -CREATE TABLE domain_data ( - gid bigint NOT NULL DEFAULT nextval('domain_gid'), - tid bigint NOT NULL DEFAULT 1, -- defualt transport - domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 - CONSTRAINT pkey_domain_data PRIMARY KEY (gid), - 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(74) NOT NULL,-- {CRAM-MD5}+64hex numbers - 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, - 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_tid_transport FOREIGN KEY (tid) - REFERENCES transport (tid) -); - -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 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 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 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 - 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; - - -CREATE LANGUAGE plpgsql; - - -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(); diff -r 78b6b06188d3 -r 639cf4003965 create_tables.pgsql --- a/create_tables.pgsql Thu Oct 22 19:30:46 2009 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,196 +0,0 @@ -SET client_encoding = 'UTF8'; -SET client_min_messages = warning; - - -CREATE SEQUENCE transport_id; - -CREATE SEQUENCE maillocation_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 maillocation( - mid bigint NOT NULL DEFAULT nextval('maillocation_id'), - maillocation varchar(20) NOT NULL, - CONSTRAINT pkey_maillocation PRIMARY KEY (mid), - CONSTRAINT ukey_maillocation UNIQUE (maillocation) -); --- Insert default Maildir-folder name -INSERT INTO maillocation(maillocation) VALUES ('Maildir'); - -CREATE TABLE domain_data ( - gid bigint NOT NULL DEFAULT nextval('domain_gid'), - tid bigint NOT NULL DEFAULT 1, -- defualt transport - domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 - CONSTRAINT pkey_domain_data PRIMARY KEY (gid), - 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(74) NOT NULL,-- {CRAM-MD5}+64hex numbers - name varchar(128) NULL, - uid bigint NOT NULL DEFAULT nextval('users_uid'), - gid bigint NOT NULL, - mid bigint NOT NULL DEFAULT 1, - tid bigint NOT NULL DEFAULT 1, - smtp boolean NOT NULL DEFAULT TRUE, - pop3 boolean NOT NULL DEFAULT TRUE, - imap boolean NOT NULL DEFAULT TRUE, - managesieve boolean NOT NULL DEFAULT TRUE, - 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_tid_transport FOREIGN KEY (tid) - REFERENCES transport (tid) -); - -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 OR REPLACE VIEW dovecot_password AS - SELECT local_part || '@' || domain_name.domainname AS "user", - passwd AS "password", smtp, pop3, imap, managesieve - FROM users - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW dovecot_user AS - SELECT local_part || '@' || domain_name.domainname AS userid, - uid, gid, domain_data.domaindir || '/' || uid AS home, - '~/' || maillocation.maillocation AS mail - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid); - -CREATE 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 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 - 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; - - -CREATE LANGUAGE plpgsql; - - -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(); diff -r 78b6b06188d3 -r 639cf4003965 pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql Fri Oct 23 00:20:33 2009 +0000 @@ -0,0 +1,374 @@ +-- --- Information: +-- This file contains some data types and functions these should speed up some +-- operations. Read the comment on each data type/functions for more details. +-- --- + +-- --- +-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) +-- --- +CREATE TYPE sender_login AS ( + sender varchar(320), + login text +); + +-- --- +-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): +-- set of sender_login records. +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON domain_name, users, alias TO postfix; +-- +-- For more details see postconf(5) section smtpd_sender_login_maps +-- --- +CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login +AS $$ + DECLARE + rec sender_login; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + sender varchar(320) := localpart || '@' || the_domain; + BEGIN + -- Get all addresses for 'localpart' in the primary and aliased domains + FOR rec IN + SELECT sender, local_part || '@' || domainname + FROM domain_name, users + WHERE domain_name.gid = did + AND users.gid = did + AND users.local_part = localpart + LOOP + RETURN NEXT rec; + END LOOP; + IF NOT FOUND THEN + -- Loop over the alias addresses for localpart@the_domain + FOR rec IN + SELECT DISTINCT sender, destination + FROM alias + WHERE alias.gid = did + AND alias.address = localpart + LOOP + RETURN NEXT rec; + END LOOP; + END IF; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function postfix_virtual_mailbox(varchar, varchar) +-- --- +CREATE TYPE address_maildir AS ( + address varchar(320), + maildir text +); + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: address_maildir records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix; +-- +-- For more details see postconf(5) section virtual_mailbox_maps +-- --- +CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir +AS $$ + DECLARE + rec address_maildir; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + address varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR rec IN + SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/' + FROM domain_data, users, maillocation + WHERE domain_data.gid = did + AND users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + LOOP + RETURN NEXT rec; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for functions: postfix_relocated_map(varchar, varchar) +-- postfix_virtual_alias_map(varchar, varchar) +-- +-- --- +CREATE TYPE recipient_destination AS ( + recipient varchar(320), + destination text +); + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_destination records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON alias, domain_name TO postfix; +-- +-- For more details see postconf(5) section virtual_alias_maps and virtual(5) +-- --- +CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( + IN localpart varchar, IN the_domain varchar) + RETURNS SETOF recipient_destination +AS $$ + DECLARE + record recipient_destination; + recipient varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT recipient, destination + FROM alias + WHERE gid = did + AND address = localpart + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_destination records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON domain_name, relocated TO postfix; +-- +-- For more details see postconf(5) section relocated_maps and relocated(5) +-- --- +CREATE OR REPLACE FUNCTION postfix_relocated_map( + IN localpart varchar, IN the_domain varchar) + RETURNS SETOF recipient_destination +AS $$ + DECLARE + record recipient_destination; + recipient varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT recipient, destination + FROM relocated + WHERE gid = did + AND address = localpart + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function postfix_transport_map(varchar, varchar) +-- --- +CREATE TYPE recipient_transport AS ( + recipient varchar(320), + transport text +); + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_transport records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON users, transport, domain_name TO postfix; +-- +-- For more details see postconf(5) section transport_maps and transport(5) +-- --- +CREATE OR REPLACE FUNCTION postfix_transport_map( + IN localpart varchar, IN the_domain varchar) + RETURNS SETOF recipient_transport +AS $$ + DECLARE + record recipient_transport; + recipient varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR record IN + SELECT recipient, transport + FROM transport + WHERE tid = (SELECT tid + FROM users + WHERE gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart) + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function postfix_virtual_uid_map(varchar, varchar) +-- --- +CREATE TYPE recipient_uid AS ( + recipient varchar(320), + uid bigint +); + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_uid records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON users, domain_name TO postfix; +-- +-- For more details see postconf(5) section virtual_uid_maps +-- --- +CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid +AS $$ + DECLARE + record recipient_uid; + recipient varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR record IN + SELECT recipient, uid + FROM users + WHERE gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function dovecotuser(varchar, varchar) +-- --- +CREATE TYPE dovecotuser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text +); + +-- --- +-- Parameters (from login name [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: dovecotuser records +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot; +-- +-- For more details see http://wiki.dovecot.org/UserDatabase +-- --- +CREATE OR REPLACE FUNCTION dovecotuser( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser +AS $$ + DECLARE + record dovecotuser; + userid varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT userid, uid, did, domaindir ||'/'|| uid AS home, + '~/'|| maillocation AS mail + FROM users, domain_data, maillocation + WHERE users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + AND domain_data.gid = did + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function dovecotpassword(varchar, varchar) +-- --- +CREATE TYPE dovecotpassword AS ( + userid varchar(320), + password varchar(74), + smtp boolean, + pop3 boolean, + imap boolean, + sieve boolean +); + +-- --- +-- Parameters (from login name [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: dovecotpassword records +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT ON users, domain_name TO dovecot; +-- +-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL +-- --- +CREATE OR REPLACE FUNCTION dovecotpassword( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword +AS $$ + DECLARE + record dovecotpassword; + userid varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR record IN + SELECT userid, passwd, smtp, pop3, imap, sieve + 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 78b6b06188d3 -r 639cf4003965 pgsql/create_optional_types_and_functions.pgsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pgsql/create_optional_types_and_functions.pgsql Fri Oct 23 00:20:33 2009 +0000 @@ -0,0 +1,374 @@ +-- --- Information: +-- This file contains some data types and functions these should speed up some +-- operations. Read the comment on each data type/functions for more details. +-- --- + +-- --- +-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) +-- --- +CREATE TYPE sender_login AS ( + sender varchar(320), + login text +); + +-- --- +-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): +-- set of sender_login records. +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON domain_name, users, alias TO postfix; +-- +-- For more details see postconf(5) section smtpd_sender_login_maps +-- --- +CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login +AS $$ + DECLARE + rec sender_login; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + sender varchar(320) := localpart || '@' || the_domain; + BEGIN + -- Get all addresses for 'localpart' in the primary and aliased domains + FOR rec IN + SELECT sender, local_part || '@' || domainname + FROM domain_name, users + WHERE domain_name.gid = did + AND users.gid = did + AND users.local_part = localpart + LOOP + RETURN NEXT rec; + END LOOP; + IF NOT FOUND THEN + -- Loop over the alias addresses for localpart@the_domain + FOR rec IN + SELECT DISTINCT sender, destination + FROM alias + WHERE alias.gid = did + AND alias.address = localpart + LOOP + RETURN NEXT rec; + END LOOP; + END IF; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function postfix_virtual_mailbox(varchar, varchar) +-- --- +CREATE TYPE address_maildir AS ( + address varchar(320), + maildir text +); + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: address_maildir records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix; +-- +-- For more details see postconf(5) section virtual_mailbox_maps +-- --- +CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir +AS $$ + DECLARE + rec address_maildir; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + address varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR rec IN + SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/' + FROM domain_data, users, maillocation + WHERE domain_data.gid = did + AND users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + LOOP + RETURN NEXT rec; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for functions: postfix_relocated_map(varchar, varchar) +-- postfix_virtual_alias_map(varchar, varchar) +-- +-- --- +CREATE TYPE recipient_destination AS ( + recipient varchar(320), + destination text +); + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_destination records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON alias, domain_name TO postfix; +-- +-- For more details see postconf(5) section virtual_alias_maps and virtual(5) +-- --- +CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( + IN localpart varchar, IN the_domain varchar) + RETURNS SETOF recipient_destination +AS $$ + DECLARE + record recipient_destination; + recipient varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT recipient, destination + FROM alias + WHERE gid = did + AND address = localpart + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_destination records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON domain_name, relocated TO postfix; +-- +-- For more details see postconf(5) section relocated_maps and relocated(5) +-- --- +CREATE OR REPLACE FUNCTION postfix_relocated_map( + IN localpart varchar, IN the_domain varchar) + RETURNS SETOF recipient_destination +AS $$ + DECLARE + record recipient_destination; + recipient varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT recipient, destination + FROM relocated + WHERE gid = did + AND address = localpart + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function postfix_transport_map(varchar, varchar) +-- --- +CREATE TYPE recipient_transport AS ( + recipient varchar(320), + transport text +); + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_transport records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON users, transport, domain_name TO postfix; +-- +-- For more details see postconf(5) section transport_maps and transport(5) +-- --- +CREATE OR REPLACE FUNCTION postfix_transport_map( + IN localpart varchar, IN the_domain varchar) + RETURNS SETOF recipient_transport +AS $$ + DECLARE + record recipient_transport; + recipient varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR record IN + SELECT recipient, transport + FROM transport + WHERE tid = (SELECT tid + FROM users + WHERE gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart) + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function postfix_virtual_uid_map(varchar, varchar) +-- --- +CREATE TYPE recipient_uid AS ( + recipient varchar(320), + uid bigint +); + +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_uid records +-- +-- Required access privileges for your postfix database user: +-- GRANT SELECT ON users, domain_name TO postfix; +-- +-- For more details see postconf(5) section virtual_uid_maps +-- --- +CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid +AS $$ + DECLARE + record recipient_uid; + recipient varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR record IN + SELECT recipient, uid + FROM users + WHERE gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function dovecotuser(varchar, varchar) +-- --- +CREATE TYPE dovecotuser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text +); + +-- --- +-- Parameters (from login name [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: dovecotuser records +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot; +-- +-- For more details see http://wiki.dovecot.org/UserDatabase +-- --- +CREATE OR REPLACE FUNCTION dovecotuser( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser +AS $$ + DECLARE + record dovecotuser; + userid varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT userid, uid, did, domaindir ||'/'|| uid AS home, + '~/'|| maillocation AS mail + FROM users, domain_data, maillocation + WHERE users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + AND domain_data.gid = did + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + +-- ########################################################################## -- + +-- --- +-- Data type for function dovecotpassword(varchar, varchar) +-- --- +CREATE TYPE dovecotpassword AS ( + userid varchar(320), + password varchar(74), + smtp boolean, + pop3 boolean, + imap boolean, + managesieve boolean +); + +-- --- +-- Parameters (from login name [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: dovecotpassword records +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT ON users, domain_name TO dovecot; +-- +-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL +-- --- +CREATE OR REPLACE FUNCTION dovecotpassword( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword +AS $$ + DECLARE + record dovecotpassword; + userid varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR record IN + SELECT userid, passwd, smtp, pop3, imap, managesieve + FROM users + WHERE gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; diff -r 78b6b06188d3 -r 639cf4003965 pgsql/create_tables-dovecot-1.2.x.pgsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Fri Oct 23 00:20:33 2009 +0000 @@ -0,0 +1,196 @@ +SET client_encoding = 'UTF8'; +SET client_min_messages = warning; + + +CREATE SEQUENCE transport_id; + +CREATE SEQUENCE maillocation_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 maillocation( + mid bigint NOT NULL DEFAULT nextval('maillocation_id'), + maillocation varchar(20) NOT NULL, + CONSTRAINT pkey_maillocation PRIMARY KEY (mid), + CONSTRAINT ukey_maillocation UNIQUE (maillocation) +); +-- Insert default Maildir-folder name +INSERT INTO maillocation(maillocation) VALUES ('Maildir'); + +CREATE TABLE domain_data ( + gid bigint NOT NULL DEFAULT nextval('domain_gid'), + tid bigint NOT NULL DEFAULT 1, -- defualt transport + domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 + CONSTRAINT pkey_domain_data PRIMARY KEY (gid), + 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(74) NOT NULL,-- {CRAM-MD5}+64hex numbers + 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, + 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_tid_transport FOREIGN KEY (tid) + REFERENCES transport (tid) +); + +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 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 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 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 + 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; + + +CREATE LANGUAGE plpgsql; + + +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(); diff -r 78b6b06188d3 -r 639cf4003965 pgsql/create_tables.pgsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pgsql/create_tables.pgsql Fri Oct 23 00:20:33 2009 +0000 @@ -0,0 +1,196 @@ +SET client_encoding = 'UTF8'; +SET client_min_messages = warning; + + +CREATE SEQUENCE transport_id; + +CREATE SEQUENCE maillocation_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 maillocation( + mid bigint NOT NULL DEFAULT nextval('maillocation_id'), + maillocation varchar(20) NOT NULL, + CONSTRAINT pkey_maillocation PRIMARY KEY (mid), + CONSTRAINT ukey_maillocation UNIQUE (maillocation) +); +-- Insert default Maildir-folder name +INSERT INTO maillocation(maillocation) VALUES ('Maildir'); + +CREATE TABLE domain_data ( + gid bigint NOT NULL DEFAULT nextval('domain_gid'), + tid bigint NOT NULL DEFAULT 1, -- defualt transport + domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 + CONSTRAINT pkey_domain_data PRIMARY KEY (gid), + 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(74) NOT NULL,-- {CRAM-MD5}+64hex numbers + name varchar(128) NULL, + uid bigint NOT NULL DEFAULT nextval('users_uid'), + gid bigint NOT NULL, + mid bigint NOT NULL DEFAULT 1, + tid bigint NOT NULL DEFAULT 1, + smtp boolean NOT NULL DEFAULT TRUE, + pop3 boolean NOT NULL DEFAULT TRUE, + imap boolean NOT NULL DEFAULT TRUE, + managesieve boolean NOT NULL DEFAULT TRUE, + 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_tid_transport FOREIGN KEY (tid) + REFERENCES transport (tid) +); + +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 OR REPLACE VIEW dovecot_password AS + SELECT local_part || '@' || domain_name.domainname AS "user", + passwd AS "password", smtp, pop3, imap, managesieve + FROM users + LEFT JOIN domain_name USING (gid); + +CREATE OR REPLACE VIEW dovecot_user AS + SELECT local_part || '@' || domain_name.domainname AS userid, + uid, gid, domain_data.domaindir || '/' || uid AS home, + '~/' || maillocation.maillocation AS mail + FROM users + LEFT JOIN domain_data USING (gid) + LEFT JOIN domain_name USING (gid) + LEFT JOIN maillocation USING (mid); + +CREATE 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 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 + 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; + + +CREATE LANGUAGE plpgsql; + + +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(); diff -r 78b6b06188d3 -r 639cf4003965 pgsql/update_tables_0.4.x-0.5.pgsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pgsql/update_tables_0.4.x-0.5.pgsql Fri Oct 23 00:20:33 2009 +0000 @@ -0,0 +1,156 @@ +SET client_encoding = 'UTF8'; +SET client_min_messages = warning; + +ALTER SEQUENCE domains_gid RENAME TO domain_gid; + + +CREATE TABLE domain_data ( + gid bigint NOT NULL DEFAULT nextval('domain_gid'), + tid bigint NOT NULL DEFAULT 1, + domaindir varchar(40) NOT NULL, + CONSTRAINT pkey_domain_data PRIMARY KEY (gid), + 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) +); + +INSERT INTO domain_data (gid, tid, domaindir) + SELECT gid, tid, domaindir + FROM domains; + +INSERT INTO domain_name (domainname, gid, is_primary) + SELECT domainname, gid, TRUE + FROM domains; + + +ALTER TABLE users DROP CONSTRAINT pkye_users; +ALTER TABLE users ADD CONSTRAINT pkey_users PRIMARY KEY (local_part, gid); +ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains; +ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) + REFERENCES domain_data (gid); + +ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains; +ALTER TABLE alias DROP CONSTRAINT pkey_alias; +ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid) + REFERENCES domain_data (gid); + +ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains; +ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data + FOREIGN KEY (gid) REFERENCES domain_data (gid); + + +CREATE OR REPLACE VIEW dovecot_password AS + SELECT local_part || '@' || domain_name.domainname AS "user", + passwd AS "password", smtp, pop3, imap, managesieve + FROM users + LEFT JOIN domain_name USING (gid); + +CREATE OR REPLACE VIEW dovecot_user AS + SELECT local_part || '@' || domain_name.domainname AS userid, + uid, gid, domain_data.domaindir || '/' || uid AS home, + '~/' || maillocation.maillocation AS mail + FROM users + LEFT JOIN domain_data USING (gid) + LEFT JOIN domain_name USING (gid) + LEFT JOIN maillocation USING (mid); + +CREATE 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); + +DROP VIEW postfix_alias; +DROP VIEW vmm_domain_info; +DROP VIEW vmm_alias_count; + +ALTER TABLE alias ALTER address TYPE varchar(64); +ALTER TABLE alias ADD CONSTRAINT pkey_alias + PRIMARY KEY (gid, address, destination); + +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 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 + 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; + + +DROP TABLE domains; + + +CREATE LANGUAGE plpgsql; + +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; + +DROP TRIGGER IF EXISTS primary_count ON domain_name; +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(); diff -r 78b6b06188d3 -r 639cf4003965 pgsql/update_tables_0.5.x_for_dovecot-1.2.x.pgsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pgsql/update_tables_0.5.x_for_dovecot-1.2.x.pgsql Fri Oct 23 00:20:33 2009 +0000 @@ -0,0 +1,12 @@ +-- --- +-- with Dovecot v1.2.x the service managesieve was renamed to sieve +-- --- +ALTER TABLE users RENAME managesieve TO sieve; + +DROP VIEW dovecot_password; +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); + diff -r 78b6b06188d3 -r 639cf4003965 pgsql/update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pgsql/update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql Fri Oct 23 00:20:33 2009 +0000 @@ -0,0 +1,51 @@ +-- --- +-- Clean out the old stuff +-- --- +DROP TYPE dovecotpassword CASCADE; + +-- --- +-- Data type for function dovecotpassword(varchar, varchar) +-- --- +CREATE TYPE dovecotpassword AS ( + userid varchar(320), + password varchar(74), + smtp boolean, + pop3 boolean, + imap boolean, + sieve boolean +); + +-- --- +-- Parameters (from login name [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: dovecotpassword records +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT ON users, domain_name TO dovecot; +-- +-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL +-- --- +CREATE OR REPLACE FUNCTION dovecotpassword( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword +AS $$ + DECLARE + record dovecotpassword; + userid varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR record IN + SELECT userid, passwd, smtp, pop3, imap, sieve + 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 78b6b06188d3 -r 639cf4003965 update_tables_0.4.x-0.5.pgsql --- a/update_tables_0.4.x-0.5.pgsql Thu Oct 22 19:30:46 2009 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,156 +0,0 @@ -SET client_encoding = 'UTF8'; -SET client_min_messages = warning; - -ALTER SEQUENCE domains_gid RENAME TO domain_gid; - - -CREATE TABLE domain_data ( - gid bigint NOT NULL DEFAULT nextval('domain_gid'), - tid bigint NOT NULL DEFAULT 1, - domaindir varchar(40) NOT NULL, - CONSTRAINT pkey_domain_data PRIMARY KEY (gid), - 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) -); - -INSERT INTO domain_data (gid, tid, domaindir) - SELECT gid, tid, domaindir - FROM domains; - -INSERT INTO domain_name (domainname, gid, is_primary) - SELECT domainname, gid, TRUE - FROM domains; - - -ALTER TABLE users DROP CONSTRAINT pkye_users; -ALTER TABLE users ADD CONSTRAINT pkey_users PRIMARY KEY (local_part, gid); -ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains; -ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) - REFERENCES domain_data (gid); - -ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains; -ALTER TABLE alias DROP CONSTRAINT pkey_alias; -ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid) - REFERENCES domain_data (gid); - -ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains; -ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data - FOREIGN KEY (gid) REFERENCES domain_data (gid); - - -CREATE OR REPLACE VIEW dovecot_password AS - SELECT local_part || '@' || domain_name.domainname AS "user", - passwd AS "password", smtp, pop3, imap, managesieve - FROM users - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW dovecot_user AS - SELECT local_part || '@' || domain_name.domainname AS userid, - uid, gid, domain_data.domaindir || '/' || uid AS home, - '~/' || maillocation.maillocation AS mail - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid); - -CREATE 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); - -DROP VIEW postfix_alias; -DROP VIEW vmm_domain_info; -DROP VIEW vmm_alias_count; - -ALTER TABLE alias ALTER address TYPE varchar(64); -ALTER TABLE alias ADD CONSTRAINT pkey_alias - PRIMARY KEY (gid, address, destination); - -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 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 - 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; - - -DROP TABLE domains; - - -CREATE LANGUAGE plpgsql; - -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; - -DROP TRIGGER IF EXISTS primary_count ON domain_name; -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(); diff -r 78b6b06188d3 -r 639cf4003965 update_tables_0.5.x_for_dovecot-1.2.x.pgsql --- a/update_tables_0.5.x_for_dovecot-1.2.x.pgsql Thu Oct 22 19:30:46 2009 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,12 +0,0 @@ --- --- --- with Dovecot v1.2.x the service managesieve was renamed to sieve --- --- -ALTER TABLE users RENAME managesieve TO sieve; - -DROP VIEW dovecot_password; -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); - diff -r 78b6b06188d3 -r 639cf4003965 update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql --- a/update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql Thu Oct 22 19:30:46 2009 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,51 +0,0 @@ --- --- --- Clean out the old stuff --- --- -DROP TYPE dovecotpassword CASCADE; - --- --- --- Data type for function dovecotpassword(varchar, varchar) --- --- -CREATE TYPE dovecotpassword AS ( - userid varchar(320), - password varchar(74), - smtp boolean, - pop3 boolean, - imap boolean, - sieve boolean -); - --- --- --- Parameters (from login name [localpart@the_domain]): --- varchar localpart --- varchar the_domain --- Returns: dovecotpassword records --- --- Required access privileges for your dovecot database user: --- GRANT SELECT ON users, domain_name TO dovecot; --- --- For more details see http://wiki.dovecot.org/AuthDatabase/SQL --- --- -CREATE OR REPLACE FUNCTION dovecotpassword( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword -AS $$ - DECLARE - record dovecotpassword; - userid varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR record IN - SELECT userid, passwd, smtp, pop3, imap, sieve - 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; -