# HG changeset patch # User Pascal Volk # Date 1333911925 0 # Node ID e1b32377032fd50ea04a0cd64c71090f90f83ebf # Parent f2387d60624b705531b1ced049738f4082998215 pgsql: Merged types and functions into the create_tables… files. diff -r f2387d60624b -r e1b32377032f pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql --- a/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql Sun Apr 08 16:29:37 2012 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,426 +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||'/'||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; - --- ########################################################################## -- - --- --- --- 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 -); --- --- --- 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 -); - --- --- --- 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; - --- --- --- Nearly the same as function dovecotuser above. 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 quotalimit.qid = users.qid - 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(270), - 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, service_set TO dovecot; --- --- For more details see http://wiki.dovecot.org/AuthDatabase/SQL --- --- -CREATE OR REPLACE FUNCTION dovecotpassword( - IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword -AS $$ - DECLARE - record dovecotpassword; - userid varchar(320) := localpart || '@' || the_domain; - BEGIN - FOR record IN - SELECT userid, passwd, smtp, pop3, imap, sieve - FROM users, service_set - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart - AND service_set.ssid = users.ssid - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; diff -r f2387d60624b -r e1b32377032f pgsql/create_optional_types_and_functions.pgsql --- a/pgsql/create_optional_types_and_functions.pgsql Sun Apr 08 16:29:37 2012 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,426 +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||'/'||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; - --- ########################################################################## -- - --- --- --- 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 -); --- --- --- 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 -); - --- --- --- 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; - --- --- --- Nearly the same as function dovecotuser above. 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 quotalimit.qid = users.qid - 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(270), - 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, 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 - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart - AND service_set.ssid = users.ssid - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; diff -r f2387d60624b -r e1b32377032f pgsql/create_tables-dovecot-1.2.x.pgsql --- a/pgsql/create_tables-dovecot-1.2.x.pgsql Sun Apr 08 16:29:37 2012 +0000 +++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Sun Apr 08 19:05:25 2012 +0000 @@ -195,9 +195,82 @@ 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, + sieve boolean +); +-- --- +-- Data type for function dovecotquotauser(varchar, varchar) +-- --- +CREATE TYPE dovecotquotauser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text, + quota_rule text +); +-- --- +-- Data type for function dovecotuser(varchar, varchar) +-- --- +CREATE TYPE dovecotuser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text +); +-- --- +-- Data type for functions: postfix_relocated_map(varchar, varchar) +-- postfix_virtual_alias_map(varchar, varchar) +-- --- +CREATE TYPE recipient_destination AS ( + recipient varchar(320), + destination text +); +-- --- +-- Data type for function postfix_transport_map(varchar, varchar) +-- --- +CREATE TYPE recipient_transport AS ( + recipient varchar(320), + transport text +); +-- --- +-- Data type for function postfix_virtual_uid_map(varchar, varchar) +-- --- +CREATE TYPE recipient_uid AS ( + recipient varchar(320), + uid bigint +); +-- --- +-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) +-- --- +CREATE TYPE sender_login AS ( + sender varchar(320), + login text +); + +-- ######################## TRIGGERs ######################################## -- CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$ DECLARE @@ -262,3 +335,330 @@ CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota FOR EACH ROW EXECUTE PROCEDURE merge_userquota(); + +-- ######################## FUNCTIONs ####################################### -- + +-- --- +-- Parameters (from login name [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: dovecotpassword records +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT ON users, domain_name, service_set TO dovecot; +-- +-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL +-- --- +CREATE OR REPLACE FUNCTION dovecotpassword( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword +AS $$ + DECLARE + record dovecotpassword; + userid varchar(320) := localpart || '@' || the_domain; + BEGIN + FOR record IN + SELECT userid, passwd, smtp, pop3, imap, sieve + FROM users, service_set + WHERE gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart + AND service_set.ssid = users.ssid + 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 quotalimit.qid = users.qid + 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; + 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; +-- --- +-- 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: 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 f2387d60624b -r e1b32377032f pgsql/create_tables.pgsql --- a/pgsql/create_tables.pgsql Sun Apr 08 16:29:37 2012 +0000 +++ b/pgsql/create_tables.pgsql Sun Apr 08 19:05:25 2012 +0000 @@ -195,9 +195,82 @@ 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 @@ -239,3 +312,330 @@ 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 + WHERE gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart + AND service_set.ssid = users.ssid + 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 quotalimit.qid = users.qid + 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; + 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; +-- --- +-- 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: 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 f2387d60624b -r e1b32377032f pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql --- a/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Sun Apr 08 16:29:37 2012 +0000 +++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Sun Apr 08 19:05:25 2012 +0000 @@ -40,7 +40,7 @@ -- --- DROP VIEW dovecot_user; DROP VIEW dovecot_password; -DROP VIEW postfix_alias +DROP VIEW postfix_alias; DROP VIEW postfix_maildir; DROP VIEW postfix_relocated; DROP VIEW postfix_transport; @@ -184,3 +184,364 @@ 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, + sieve boolean +); +-- --- +-- Data type for function dovecotquotauser(varchar, varchar) +-- --- +CREATE TYPE dovecotquotauser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text, + quota_rule text +); +-- --- +-- Data type for function dovecotuser(varchar, varchar) +-- --- +CREATE TYPE dovecotuser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text +); +-- --- +-- Data type for functions: postfix_relocated_map(varchar, varchar) +-- postfix_virtual_alias_map(varchar, varchar) +-- --- +CREATE TYPE recipient_destination AS ( + recipient varchar(320), + destination text +); +-- --- +-- Data type for function postfix_transport_map(varchar, varchar) +-- --- +CREATE TYPE recipient_transport AS ( + recipient varchar(320), + transport text +); +-- --- +-- Data type for function postfix_virtual_uid_map(varchar, varchar) +-- --- +CREATE TYPE recipient_uid AS ( + recipient varchar(320), + uid bigint +); +-- --- +-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) +-- --- +CREATE TYPE sender_login AS ( + sender varchar(320), + login text +); + +-- ######################## 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, sieve + FROM users, service_set + WHERE gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart + AND service_set.ssid = users.ssid + 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 quotalimit.qid = users.qid + 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; + 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; +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_destination records +-- --- +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: 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; diff -r f2387d60624b -r e1b32377032f pgsql/update_tables_0.5.x-0.6.pgsql --- a/pgsql/update_tables_0.5.x-0.6.pgsql Sun Apr 08 16:29:37 2012 +0000 +++ b/pgsql/update_tables_0.5.x-0.6.pgsql Sun Apr 08 19:05:25 2012 +0000 @@ -162,3 +162,364 @@ 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 + WHERE gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) + AND local_part = localpart + AND service_set.ssid = users.ssid + 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 quotalimit.qid = users.qid + 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; + 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; +-- --- +-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): +-- varchar localpart +-- varchar the_domain +-- Returns: recipient_destination records +-- --- +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: 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; diff -r f2387d60624b -r e1b32377032f pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql --- a/pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql Sun Apr 08 16:29:37 2012 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,172 +0,0 @@ -SET client_encoding = 'UTF8'; -SET client_min_messages = warning; - - --- --- --- 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; - - -DROP TYPE dovecotpassword CASCADE; --- --- --- Data type for function dovecotpassword(varchar, varchar) --- --- -CREATE TYPE dovecotpassword AS ( - userid varchar(320), - password varchar(270), - smtp boolean, - pop3 boolean, - imap boolean, - sieve boolean -); --- --- --- 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, service_set - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart - AND service_set.ssid = users.ssid - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; --- --- --- 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 -); --- --- --- 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; --- --- --- Nearly the same as function dovecotuser above. 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 quotalimit.qid = users.qid - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER; diff -r f2387d60624b -r e1b32377032f pgsql/update_types_and_functions_0.5.x-0.6.pgsql --- a/pgsql/update_types_and_functions_0.5.x-0.6.pgsql Sun Apr 08 16:29:37 2012 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,172 +0,0 @@ -SET client_encoding = 'UTF8'; -SET client_min_messages = warning; - - --- --- --- 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; - - -DROP TYPE dovecotpassword CASCADE; --- --- --- 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 -); --- --- --- 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, service_set - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart - AND service_set.ssid = users.ssid - 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; --- --- --- 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 -); --- --- --- Nearly the same as function dovecotuser above. 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 quotalimit.qid = users.qid - LOOP - RETURN NEXT record; - END LOOP; - RETURN; - END; -$$ LANGUAGE plpgsql STABLE -RETURNS NULL ON NULL INPUT -EXTERNAL SECURITY INVOKER;