Moved VirtualMailManager/Exceptions to VirtualMailManager/errors.
Renamed VMM*Exception classes to *Error.
No longer add the attribute 'message' to VMMError if it doesn't exist, like in
Python 2.4. It has been deprecated as of Python 2.6.
Also removed the methods code() and msg(), the values are now accessible via
the attributes 'code' and 'msg'.
-- --- Information:
-- This file contains some data types and functions these should speed up some
-- operations. Read the comment on each data type/functions for more details.
-- ---
-- ---
-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
-- ---
CREATE TYPE sender_login AS (
sender varchar(320),
login text
);
-- ---
-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
-- varchar localpart
-- varchar the_domain
-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
-- set of sender_login records.
--
-- Required access privileges for your postfix database user:
-- GRANT SELECT ON domain_name, users, alias TO postfix;
--
-- For more details see postconf(5) section smtpd_sender_login_maps
-- ---
CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
AS $$
DECLARE
rec sender_login;
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
sender varchar(320) := localpart || '@' || the_domain;
BEGIN
-- Get all addresses for 'localpart' in the primary and aliased domains
FOR rec IN
SELECT sender, local_part || '@' || domainname
FROM domain_name, users
WHERE domain_name.gid = did
AND users.gid = did
AND users.local_part = localpart
LOOP
RETURN NEXT rec;
END LOOP;
IF NOT FOUND THEN
-- Loop over the alias addresses for localpart@the_domain
FOR rec IN
SELECT DISTINCT sender, destination
FROM alias
WHERE alias.gid = did
AND alias.address = localpart
LOOP
RETURN NEXT rec;
END LOOP;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE
RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY INVOKER;
-- ########################################################################## --
-- ---
-- Data type for function postfix_virtual_mailbox(varchar, varchar)
-- ---
CREATE TYPE address_maildir AS (
address varchar(320),
maildir text
);
-- ---
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
-- varchar localpart
-- varchar the_domain
-- Returns: address_maildir records
--
-- Required access privileges for your postfix database user:
-- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
--
-- For more details see postconf(5) section virtual_mailbox_maps
-- ---
CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
AS $$
DECLARE
rec address_maildir;
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
address varchar(320) := localpart || '@' || the_domain;
BEGIN
FOR rec IN
SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/'
FROM domain_data, users, maillocation
WHERE domain_data.gid = did
AND users.gid = did
AND users.local_part = localpart
AND maillocation.mid = users.mid
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE
RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY INVOKER;
-- ########################################################################## --
-- ---
-- Data type for functions: postfix_relocated_map(varchar, varchar)
-- postfix_virtual_alias_map(varchar, varchar)
--
-- ---
CREATE TYPE recipient_destination AS (
recipient varchar(320),
destination text
);
-- ---
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
-- varchar localpart
-- varchar the_domain
-- Returns: recipient_destination records
--
-- Required access privileges for your postfix database user:
-- GRANT SELECT ON alias, domain_name TO postfix;
--
-- For more details see postconf(5) section virtual_alias_maps and virtual(5)
-- ---
CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
IN localpart varchar, IN the_domain varchar)
RETURNS SETOF recipient_destination
AS $$
DECLARE
record recipient_destination;
recipient varchar(320) := localpart || '@' || the_domain;
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
BEGIN
FOR record IN
SELECT recipient, destination
FROM alias
WHERE gid = did
AND address = localpart
LOOP
RETURN NEXT record;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE
RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY INVOKER;
-- ########################################################################## --
-- ---
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
-- varchar localpart
-- varchar the_domain
-- Returns: recipient_destination records
--
-- Required access privileges for your postfix database user:
-- GRANT SELECT ON domain_name, relocated TO postfix;
--
-- For more details see postconf(5) section relocated_maps and relocated(5)
-- ---
CREATE OR REPLACE FUNCTION postfix_relocated_map(
IN localpart varchar, IN the_domain varchar)
RETURNS SETOF recipient_destination
AS $$
DECLARE
record recipient_destination;
recipient varchar(320) := localpart || '@' || the_domain;
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
BEGIN
FOR record IN
SELECT recipient, destination
FROM relocated
WHERE gid = did
AND address = localpart
LOOP
RETURN NEXT record;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE
RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY INVOKER;
-- ########################################################################## --
-- ---
-- Data type for function postfix_transport_map(varchar, varchar)
-- ---
CREATE TYPE recipient_transport AS (
recipient varchar(320),
transport text
);
-- ---
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
-- varchar localpart
-- varchar the_domain
-- Returns: recipient_transport records
--
-- Required access privileges for your postfix database user:
-- GRANT SELECT ON users, transport, domain_name TO postfix;
--
-- For more details see postconf(5) section transport_maps and transport(5)
-- ---
CREATE OR REPLACE FUNCTION postfix_transport_map(
IN localpart varchar, IN the_domain varchar)
RETURNS SETOF recipient_transport
AS $$
DECLARE
record recipient_transport;
recipient varchar(320) := localpart || '@' || the_domain;
BEGIN
FOR record IN
SELECT recipient, transport
FROM transport
WHERE tid = (SELECT tid
FROM users
WHERE gid = (SELECT gid
FROM domain_name
WHERE domainname = the_domain)
AND local_part = localpart)
LOOP
RETURN NEXT record;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE
RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY INVOKER;
-- ########################################################################## --
-- ---
-- Data type for function postfix_virtual_uid_map(varchar, varchar)
-- ---
CREATE TYPE recipient_uid AS (
recipient varchar(320),
uid bigint
);
-- ---
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
-- varchar localpart
-- varchar the_domain
-- Returns: recipient_uid records
--
-- Required access privileges for your postfix database user:
-- GRANT SELECT ON users, domain_name TO postfix;
--
-- For more details see postconf(5) section virtual_uid_maps
-- ---
CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
AS $$
DECLARE
record recipient_uid;
recipient varchar(320) := localpart || '@' || the_domain;
BEGIN
FOR record IN
SELECT recipient, uid
FROM users
WHERE gid = (SELECT gid
FROM domain_name
WHERE domainname = the_domain)
AND local_part = localpart
LOOP
RETURN NEXT record;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE
RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY INVOKER;
-- ########################################################################## --
-- ---
-- Data type for function dovecotuser(varchar, varchar)
-- ---
CREATE TYPE dovecotuser AS (
userid varchar(320),
uid bigint,
gid bigint,
home text,
mail text
);
-- ---
-- Parameters (from login name [localpart@the_domain]):
-- varchar localpart
-- varchar the_domain
-- Returns: dovecotuser records
--
-- Required access privileges for your dovecot database user:
-- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;
--
-- For more details see http://wiki.dovecot.org/UserDatabase
-- ---
CREATE OR REPLACE FUNCTION dovecotuser(
IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
AS $$
DECLARE
record dovecotuser;
userid varchar(320) := localpart || '@' || the_domain;
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
BEGIN
FOR record IN
SELECT userid, uid, did, domaindir ||'/'|| uid AS home,
'~/'|| maillocation AS mail
FROM users, domain_data, maillocation
WHERE users.gid = did
AND users.local_part = localpart
AND maillocation.mid = users.mid
AND domain_data.gid = did
LOOP
RETURN NEXT record;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE
RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY INVOKER;
-- ########################################################################## --
-- ---
-- Data type for function dovecotpassword(varchar, varchar)
-- ---
CREATE TYPE dovecotpassword AS (
userid varchar(320),
password varchar(74),
smtp boolean,
pop3 boolean,
imap boolean,
managesieve boolean
);
-- ---
-- Parameters (from login name [localpart@the_domain]):
-- varchar localpart
-- varchar the_domain
-- Returns: dovecotpassword records
--
-- Required access privileges for your dovecot database user:
-- GRANT SELECT ON users, domain_name TO dovecot;
--
-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
-- ---
CREATE OR REPLACE FUNCTION dovecotpassword(
IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
AS $$
DECLARE
record dovecotpassword;
userid varchar(320) := localpart || '@' || the_domain;
BEGIN
FOR record IN
SELECT userid, passwd, smtp, pop3, imap, managesieve
FROM users
WHERE gid = (SELECT gid
FROM domain_name
WHERE domainname = the_domain)
AND local_part = localpart
LOOP
RETURN NEXT record;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE
RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY INVOKER;