pgsql: Merged types and functions into the create_tables… files.
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;