--- a/create_optional_types_and_functions-dovecot-1.2.x.pgsql Thu Oct 22 19:30:46 2009 +0000
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,374 +0,0 @@
--- --- Information:
--- This file contains some data types and functions these should speed up some
--- operations. Read the comment on each data type/functions for more details.
--- ---
-
--- ---
--- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
--- ---
-CREATE TYPE sender_login AS (
- sender varchar(320),
- login text
-);
-
--- ---
--- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
--- set of sender_login records.
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON domain_name, users, alias TO postfix;
---
--- For more details see postconf(5) section smtpd_sender_login_maps
--- ---
-CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
-AS $$
- DECLARE
- rec sender_login;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- sender varchar(320) := localpart || '@' || the_domain;
- BEGIN
- -- Get all addresses for 'localpart' in the primary and aliased domains
- FOR rec IN
- SELECT sender, local_part || '@' || domainname
- FROM domain_name, users
- WHERE domain_name.gid = did
- AND users.gid = did
- AND users.local_part = localpart
- LOOP
- RETURN NEXT rec;
- END LOOP;
- IF NOT FOUND THEN
- -- Loop over the alias addresses for localpart@the_domain
- FOR rec IN
- SELECT DISTINCT sender, destination
- FROM alias
- WHERE alias.gid = did
- AND alias.address = localpart
- LOOP
- RETURN NEXT rec;
- END LOOP;
- END IF;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function postfix_virtual_mailbox(varchar, varchar)
--- ---
-CREATE TYPE address_maildir AS (
- address varchar(320),
- maildir text
-);
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: address_maildir records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
---
--- For more details see postconf(5) section virtual_mailbox_maps
--- ---
-CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
-AS $$
- DECLARE
- rec address_maildir;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- address varchar(320) := localpart || '@' || the_domain;
- BEGIN
- FOR rec IN
- SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/'
- FROM domain_data, users, maillocation
- WHERE domain_data.gid = did
- AND users.gid = did
- AND users.local_part = localpart
- AND maillocation.mid = users.mid
- LOOP
- RETURN NEXT rec;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for functions: postfix_relocated_map(varchar, varchar)
--- postfix_virtual_alias_map(varchar, varchar)
---
--- ---
-CREATE TYPE recipient_destination AS (
- recipient varchar(320),
- destination text
-);
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: recipient_destination records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON alias, domain_name TO postfix;
---
--- For more details see postconf(5) section virtual_alias_maps and virtual(5)
--- ---
-CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
- IN localpart varchar, IN the_domain varchar)
- RETURNS SETOF recipient_destination
-AS $$
- DECLARE
- record recipient_destination;
- recipient varchar(320) := localpart || '@' || the_domain;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- BEGIN
- FOR record IN
- SELECT recipient, destination
- FROM alias
- WHERE gid = did
- AND address = localpart
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: recipient_destination records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON domain_name, relocated TO postfix;
---
--- For more details see postconf(5) section relocated_maps and relocated(5)
--- ---
-CREATE OR REPLACE FUNCTION postfix_relocated_map(
- IN localpart varchar, IN the_domain varchar)
- RETURNS SETOF recipient_destination
-AS $$
- DECLARE
- record recipient_destination;
- recipient varchar(320) := localpart || '@' || the_domain;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- BEGIN
- FOR record IN
- SELECT recipient, destination
- FROM relocated
- WHERE gid = did
- AND address = localpart
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function postfix_transport_map(varchar, varchar)
--- ---
-CREATE TYPE recipient_transport AS (
- recipient varchar(320),
- transport text
-);
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: recipient_transport records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON users, transport, domain_name TO postfix;
---
--- For more details see postconf(5) section transport_maps and transport(5)
--- ---
-CREATE OR REPLACE FUNCTION postfix_transport_map(
- IN localpart varchar, IN the_domain varchar)
- RETURNS SETOF recipient_transport
-AS $$
- DECLARE
- record recipient_transport;
- recipient varchar(320) := localpart || '@' || the_domain;
- BEGIN
- FOR record IN
- SELECT recipient, transport
- FROM transport
- WHERE tid = (SELECT tid
- FROM users
- WHERE gid = (SELECT gid
- FROM domain_name
- WHERE domainname = the_domain)
- AND local_part = localpart)
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function postfix_virtual_uid_map(varchar, varchar)
--- ---
-CREATE TYPE recipient_uid AS (
- recipient varchar(320),
- uid bigint
-);
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: recipient_uid records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON users, domain_name TO postfix;
---
--- For more details see postconf(5) section virtual_uid_maps
--- ---
-CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
-AS $$
- DECLARE
- record recipient_uid;
- recipient varchar(320) := localpart || '@' || the_domain;
- BEGIN
- FOR record IN
- SELECT recipient, uid
- FROM users
- WHERE gid = (SELECT gid
- FROM domain_name
- WHERE domainname = the_domain)
- AND local_part = localpart
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function dovecotuser(varchar, varchar)
--- ---
-CREATE TYPE dovecotuser AS (
- userid varchar(320),
- uid bigint,
- gid bigint,
- home text,
- mail text
-);
-
--- ---
--- Parameters (from login name [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: dovecotuser records
---
--- Required access privileges for your dovecot database user:
--- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;
---
--- For more details see http://wiki.dovecot.org/UserDatabase
--- ---
-CREATE OR REPLACE FUNCTION dovecotuser(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
-AS $$
- DECLARE
- record dovecotuser;
- userid varchar(320) := localpart || '@' || the_domain;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- BEGIN
- FOR record IN
- SELECT userid, uid, did, domaindir ||'/'|| uid AS home,
- '~/'|| maillocation AS mail
- FROM users, domain_data, maillocation
- WHERE users.gid = did
- AND users.local_part = localpart
- AND maillocation.mid = users.mid
- AND domain_data.gid = did
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function dovecotpassword(varchar, varchar)
--- ---
-CREATE TYPE dovecotpassword AS (
- userid varchar(320),
- password varchar(74),
- smtp boolean,
- pop3 boolean,
- imap boolean,
- sieve boolean
-);
-
--- ---
--- Parameters (from login name [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: dovecotpassword records
---
--- Required access privileges for your dovecot database user:
--- GRANT SELECT ON users, domain_name TO dovecot;
---
--- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
--- ---
-CREATE OR REPLACE FUNCTION dovecotpassword(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
-AS $$
- DECLARE
- record dovecotpassword;
- userid varchar(320) := localpart || '@' || the_domain;
- BEGIN
- FOR record IN
- SELECT userid, passwd, smtp, pop3, imap, sieve
- FROM users
- WHERE gid = (SELECT gid
- FROM domain_name
- WHERE domainname = the_domain)
- AND local_part = localpart
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
--- a/create_optional_types_and_functions.pgsql Thu Oct 22 19:30:46 2009 +0000
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,374 +0,0 @@
--- --- Information:
--- This file contains some data types and functions these should speed up some
--- operations. Read the comment on each data type/functions for more details.
--- ---
-
--- ---
--- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
--- ---
-CREATE TYPE sender_login AS (
- sender varchar(320),
- login text
-);
-
--- ---
--- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
--- set of sender_login records.
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON domain_name, users, alias TO postfix;
---
--- For more details see postconf(5) section smtpd_sender_login_maps
--- ---
-CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
-AS $$
- DECLARE
- rec sender_login;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- sender varchar(320) := localpart || '@' || the_domain;
- BEGIN
- -- Get all addresses for 'localpart' in the primary and aliased domains
- FOR rec IN
- SELECT sender, local_part || '@' || domainname
- FROM domain_name, users
- WHERE domain_name.gid = did
- AND users.gid = did
- AND users.local_part = localpart
- LOOP
- RETURN NEXT rec;
- END LOOP;
- IF NOT FOUND THEN
- -- Loop over the alias addresses for localpart@the_domain
- FOR rec IN
- SELECT DISTINCT sender, destination
- FROM alias
- WHERE alias.gid = did
- AND alias.address = localpart
- LOOP
- RETURN NEXT rec;
- END LOOP;
- END IF;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function postfix_virtual_mailbox(varchar, varchar)
--- ---
-CREATE TYPE address_maildir AS (
- address varchar(320),
- maildir text
-);
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: address_maildir records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
---
--- For more details see postconf(5) section virtual_mailbox_maps
--- ---
-CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
-AS $$
- DECLARE
- rec address_maildir;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- address varchar(320) := localpart || '@' || the_domain;
- BEGIN
- FOR rec IN
- SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/'
- FROM domain_data, users, maillocation
- WHERE domain_data.gid = did
- AND users.gid = did
- AND users.local_part = localpart
- AND maillocation.mid = users.mid
- LOOP
- RETURN NEXT rec;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for functions: postfix_relocated_map(varchar, varchar)
--- postfix_virtual_alias_map(varchar, varchar)
---
--- ---
-CREATE TYPE recipient_destination AS (
- recipient varchar(320),
- destination text
-);
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: recipient_destination records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON alias, domain_name TO postfix;
---
--- For more details see postconf(5) section virtual_alias_maps and virtual(5)
--- ---
-CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
- IN localpart varchar, IN the_domain varchar)
- RETURNS SETOF recipient_destination
-AS $$
- DECLARE
- record recipient_destination;
- recipient varchar(320) := localpart || '@' || the_domain;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- BEGIN
- FOR record IN
- SELECT recipient, destination
- FROM alias
- WHERE gid = did
- AND address = localpart
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: recipient_destination records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON domain_name, relocated TO postfix;
---
--- For more details see postconf(5) section relocated_maps and relocated(5)
--- ---
-CREATE OR REPLACE FUNCTION postfix_relocated_map(
- IN localpart varchar, IN the_domain varchar)
- RETURNS SETOF recipient_destination
-AS $$
- DECLARE
- record recipient_destination;
- recipient varchar(320) := localpart || '@' || the_domain;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- BEGIN
- FOR record IN
- SELECT recipient, destination
- FROM relocated
- WHERE gid = did
- AND address = localpart
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function postfix_transport_map(varchar, varchar)
--- ---
-CREATE TYPE recipient_transport AS (
- recipient varchar(320),
- transport text
-);
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: recipient_transport records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON users, transport, domain_name TO postfix;
---
--- For more details see postconf(5) section transport_maps and transport(5)
--- ---
-CREATE OR REPLACE FUNCTION postfix_transport_map(
- IN localpart varchar, IN the_domain varchar)
- RETURNS SETOF recipient_transport
-AS $$
- DECLARE
- record recipient_transport;
- recipient varchar(320) := localpart || '@' || the_domain;
- BEGIN
- FOR record IN
- SELECT recipient, transport
- FROM transport
- WHERE tid = (SELECT tid
- FROM users
- WHERE gid = (SELECT gid
- FROM domain_name
- WHERE domainname = the_domain)
- AND local_part = localpart)
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function postfix_virtual_uid_map(varchar, varchar)
--- ---
-CREATE TYPE recipient_uid AS (
- recipient varchar(320),
- uid bigint
-);
-
--- ---
--- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: recipient_uid records
---
--- Required access privileges for your postfix database user:
--- GRANT SELECT ON users, domain_name TO postfix;
---
--- For more details see postconf(5) section virtual_uid_maps
--- ---
-CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
-AS $$
- DECLARE
- record recipient_uid;
- recipient varchar(320) := localpart || '@' || the_domain;
- BEGIN
- FOR record IN
- SELECT recipient, uid
- FROM users
- WHERE gid = (SELECT gid
- FROM domain_name
- WHERE domainname = the_domain)
- AND local_part = localpart
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function dovecotuser(varchar, varchar)
--- ---
-CREATE TYPE dovecotuser AS (
- userid varchar(320),
- uid bigint,
- gid bigint,
- home text,
- mail text
-);
-
--- ---
--- Parameters (from login name [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: dovecotuser records
---
--- Required access privileges for your dovecot database user:
--- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;
---
--- For more details see http://wiki.dovecot.org/UserDatabase
--- ---
-CREATE OR REPLACE FUNCTION dovecotuser(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
-AS $$
- DECLARE
- record dovecotuser;
- userid varchar(320) := localpart || '@' || the_domain;
- did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
- BEGIN
- FOR record IN
- SELECT userid, uid, did, domaindir ||'/'|| uid AS home,
- '~/'|| maillocation AS mail
- FROM users, domain_data, maillocation
- WHERE users.gid = did
- AND users.local_part = localpart
- AND maillocation.mid = users.mid
- AND domain_data.gid = did
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-
--- ########################################################################## --
-
--- ---
--- Data type for function dovecotpassword(varchar, varchar)
--- ---
-CREATE TYPE dovecotpassword AS (
- userid varchar(320),
- password varchar(74),
- smtp boolean,
- pop3 boolean,
- imap boolean,
- managesieve boolean
-);
-
--- ---
--- Parameters (from login name [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: dovecotpassword records
---
--- Required access privileges for your dovecot database user:
--- GRANT SELECT ON users, domain_name TO dovecot;
---
--- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
--- ---
-CREATE OR REPLACE FUNCTION dovecotpassword(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
-AS $$
- DECLARE
- record dovecotpassword;
- userid varchar(320) := localpart || '@' || the_domain;
- BEGIN
- FOR record IN
- SELECT userid, passwd, smtp, pop3, imap, managesieve
- FROM users
- WHERE gid = (SELECT gid
- FROM domain_name
- WHERE domainname = the_domain)
- AND local_part = localpart
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
--- a/create_tables-dovecot-1.2.x.pgsql Thu Oct 22 19:30:46 2009 +0000
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,196 +0,0 @@
-SET client_encoding = 'UTF8';
-SET client_min_messages = warning;
-
-
-CREATE SEQUENCE transport_id;
-
-CREATE SEQUENCE maillocation_id;
-
-CREATE SEQUENCE domain_gid
- START WITH 70000
- INCREMENT BY 1
- MINVALUE 70000
- MAXVALUE 4294967294
- NO CYCLE;
-
-CREATE SEQUENCE users_uid
- START WITH 70000
- INCREMENT BY 1
- MINVALUE 70000
- MAXVALUE 4294967294
- NO CYCLE;
-
-
-CREATE TABLE transport (
- tid bigint NOT NULL DEFAULT nextval('transport_id'),
- transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
- CONSTRAINT pkey_transport PRIMARY KEY (tid),
- CONSTRAINT ukey_transport UNIQUE (transport)
-);
--- Insert default transport
-INSERT INTO transport(transport) VALUES ('dovecot:');
-
-CREATE TABLE maillocation(
- mid bigint NOT NULL DEFAULT nextval('maillocation_id'),
- maillocation varchar(20) NOT NULL,
- CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
- CONSTRAINT ukey_maillocation UNIQUE (maillocation)
-);
--- Insert default Maildir-folder name
-INSERT INTO maillocation(maillocation) VALUES ('Maildir');
-
-CREATE TABLE domain_data (
- gid bigint NOT NULL DEFAULT nextval('domain_gid'),
- tid bigint NOT NULL DEFAULT 1, -- defualt transport
- domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
- CONSTRAINT pkey_domain_data PRIMARY KEY (gid),
- CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid)
- REFERENCES transport (tid)
-);
-
-CREATE TABLE domain_name (
- domainname varchar(255) NOT NULL,
- gid bigint NOT NULL,
- is_primary boolean NOT NULL,
- CONSTRAINT pkey_domain_name PRIMARY KEY (domainname),
- CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-CREATE TABLE users (
- local_part varchar(64) NOT NULL,-- only localpart w/o '@'
- passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
- name varchar(128) NULL,
- uid bigint NOT NULL DEFAULT nextval('users_uid'),
- gid bigint NOT NULL,
- mid bigint NOT NULL DEFAULT 1,
- tid bigint NOT NULL DEFAULT 1,
- smtp boolean NOT NULL DEFAULT TRUE,
- pop3 boolean NOT NULL DEFAULT TRUE,
- imap boolean NOT NULL DEFAULT TRUE,
- sieve boolean NOT NULL DEFAULT TRUE,
- CONSTRAINT pkey_users PRIMARY KEY (local_part, gid),
- CONSTRAINT ukey_users_uid UNIQUE (uid),
- CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid),
- CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
- REFERENCES maillocation (mid),
- CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
- REFERENCES transport (tid)
-);
-
-CREATE TABLE alias (
- gid bigint NOT NULL,
- address varchar(64) NOT NULL,-- only localpart w/o '@'
- destination varchar(320) NOT NULL,
- CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination),
- CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-CREATE TABLE relocated (
- gid bigint NOT NULL,
- address varchar(64) NOT NULL,
- destination varchar(320) NOT NULL,
- CONSTRAINT pkey_relocated PRIMARY KEY (gid, address),
- CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-CREATE OR REPLACE VIEW dovecot_password AS
- SELECT local_part || '@' || domain_name.domainname AS "user",
- passwd AS "password", smtp, pop3, imap, sieve
- FROM users
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW dovecot_user AS
- SELECT local_part || '@' || domain_name.domainname AS userid,
- uid, gid, domain_data.domaindir || '/' || uid AS home,
- '~/' || maillocation.maillocation AS mail
- FROM users
- LEFT JOIN domain_data USING (gid)
- LEFT JOIN domain_name USING (gid)
- LEFT JOIN maillocation USING (mid);
-
-CREATE OR REPLACE VIEW postfix_gid AS
- SELECT gid, domainname
- FROM domain_name;
-
-CREATE OR REPLACE VIEW postfix_uid AS
- SELECT local_part || '@' || domain_name.domainname AS address, uid
- FROM users
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW postfix_maildir AS
- SELECT local_part || '@' || domain_name.domainname AS address,
- domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
- AS maildir
- FROM users
- LEFT JOIN domain_data USING (gid)
- LEFT JOIN domain_name USING (gid)
- LEFT JOIN maillocation USING (mid);
-
-CREATE OR REPLACE VIEW postfix_relocated AS
- SELECT address || '@' || domain_name.domainname AS address, destination
- FROM relocated
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW postfix_alias AS
- SELECT address || '@' || domain_name.domainname AS address, destination, gid
- FROM alias
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW postfix_transport AS
- SELECT local_part || '@' || domain_name.domainname AS address,
- transport.transport
- FROM users
- LEFT JOIN transport USING (tid)
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW vmm_domain_info AS
- SELECT gid, domainname, transport, domaindir,
- count(uid) AS accounts,
- (SELECT count(DISTINCT address)
- FROM alias
- WHERE alias.gid = domain_data.gid) AS aliases,
- (SELECT count(gid)
- FROM relocated
- WHERE relocated.gid = domain_data.gid) AS relocated,
- (SELECT count(gid)
- FROM domain_name
- WHERE domain_name.gid = domain_data.gid
- AND NOT domain_name.is_primary) AS aliasdomains
- FROM domain_data
- LEFT JOIN domain_name USING (gid)
- LEFT JOIN transport USING (tid)
- LEFT JOIN users USING (gid)
- WHERE domain_name.is_primary
- GROUP BY gid, domainname, transport, domaindir;
-
-
-CREATE LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
-DECLARE
- primary_count bigint;
-BEGIN
- SELECT INTO primary_count count(gid) + NEW.is_primary::integer
- FROM domain_name
- WHERE domain_name.gid = NEW.gid
- AND is_primary;
-
- IF (primary_count > 1) THEN
- RAISE EXCEPTION 'There can only be one domain marked as primary.';
- END IF;
-
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql STABLE;
-
-
-CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
- FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
-
-CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
- FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
--- a/create_tables.pgsql Thu Oct 22 19:30:46 2009 +0000
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,196 +0,0 @@
-SET client_encoding = 'UTF8';
-SET client_min_messages = warning;
-
-
-CREATE SEQUENCE transport_id;
-
-CREATE SEQUENCE maillocation_id;
-
-CREATE SEQUENCE domain_gid
- START WITH 70000
- INCREMENT BY 1
- MINVALUE 70000
- MAXVALUE 4294967294
- NO CYCLE;
-
-CREATE SEQUENCE users_uid
- START WITH 70000
- INCREMENT BY 1
- MINVALUE 70000
- MAXVALUE 4294967294
- NO CYCLE;
-
-
-CREATE TABLE transport (
- tid bigint NOT NULL DEFAULT nextval('transport_id'),
- transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
- CONSTRAINT pkey_transport PRIMARY KEY (tid),
- CONSTRAINT ukey_transport UNIQUE (transport)
-);
--- Insert default transport
-INSERT INTO transport(transport) VALUES ('dovecot:');
-
-CREATE TABLE maillocation(
- mid bigint NOT NULL DEFAULT nextval('maillocation_id'),
- maillocation varchar(20) NOT NULL,
- CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
- CONSTRAINT ukey_maillocation UNIQUE (maillocation)
-);
--- Insert default Maildir-folder name
-INSERT INTO maillocation(maillocation) VALUES ('Maildir');
-
-CREATE TABLE domain_data (
- gid bigint NOT NULL DEFAULT nextval('domain_gid'),
- tid bigint NOT NULL DEFAULT 1, -- defualt transport
- domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
- CONSTRAINT pkey_domain_data PRIMARY KEY (gid),
- CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid)
- REFERENCES transport (tid)
-);
-
-CREATE TABLE domain_name (
- domainname varchar(255) NOT NULL,
- gid bigint NOT NULL,
- is_primary boolean NOT NULL,
- CONSTRAINT pkey_domain_name PRIMARY KEY (domainname),
- CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-CREATE TABLE users (
- local_part varchar(64) NOT NULL,-- only localpart w/o '@'
- passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
- name varchar(128) NULL,
- uid bigint NOT NULL DEFAULT nextval('users_uid'),
- gid bigint NOT NULL,
- mid bigint NOT NULL DEFAULT 1,
- tid bigint NOT NULL DEFAULT 1,
- smtp boolean NOT NULL DEFAULT TRUE,
- pop3 boolean NOT NULL DEFAULT TRUE,
- imap boolean NOT NULL DEFAULT TRUE,
- managesieve boolean NOT NULL DEFAULT TRUE,
- CONSTRAINT pkey_users PRIMARY KEY (local_part, gid),
- CONSTRAINT ukey_users_uid UNIQUE (uid),
- CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid),
- CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
- REFERENCES maillocation (mid),
- CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
- REFERENCES transport (tid)
-);
-
-CREATE TABLE alias (
- gid bigint NOT NULL,
- address varchar(64) NOT NULL,-- only localpart w/o '@'
- destination varchar(320) NOT NULL,
- CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination),
- CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-CREATE TABLE relocated (
- gid bigint NOT NULL,
- address varchar(64) NOT NULL,
- destination varchar(320) NOT NULL,
- CONSTRAINT pkey_relocated PRIMARY KEY (gid, address),
- CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-CREATE OR REPLACE VIEW dovecot_password AS
- SELECT local_part || '@' || domain_name.domainname AS "user",
- passwd AS "password", smtp, pop3, imap, managesieve
- FROM users
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW dovecot_user AS
- SELECT local_part || '@' || domain_name.domainname AS userid,
- uid, gid, domain_data.domaindir || '/' || uid AS home,
- '~/' || maillocation.maillocation AS mail
- FROM users
- LEFT JOIN domain_data USING (gid)
- LEFT JOIN domain_name USING (gid)
- LEFT JOIN maillocation USING (mid);
-
-CREATE OR REPLACE VIEW postfix_gid AS
- SELECT gid, domainname
- FROM domain_name;
-
-CREATE OR REPLACE VIEW postfix_uid AS
- SELECT local_part || '@' || domain_name.domainname AS address, uid
- FROM users
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW postfix_maildir AS
- SELECT local_part || '@' || domain_name.domainname AS address,
- domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
- AS maildir
- FROM users
- LEFT JOIN domain_data USING (gid)
- LEFT JOIN domain_name USING (gid)
- LEFT JOIN maillocation USING (mid);
-
-CREATE OR REPLACE VIEW postfix_relocated AS
- SELECT address || '@' || domain_name.domainname AS address, destination
- FROM relocated
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW postfix_alias AS
- SELECT address || '@' || domain_name.domainname AS address, destination, gid
- FROM alias
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW postfix_transport AS
- SELECT local_part || '@' || domain_name.domainname AS address,
- transport.transport
- FROM users
- LEFT JOIN transport USING (tid)
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW vmm_domain_info AS
- SELECT gid, domainname, transport, domaindir,
- count(uid) AS accounts,
- (SELECT count(DISTINCT address)
- FROM alias
- WHERE alias.gid = domain_data.gid) AS aliases,
- (SELECT count(gid)
- FROM relocated
- WHERE relocated.gid = domain_data.gid) AS relocated,
- (SELECT count(gid)
- FROM domain_name
- WHERE domain_name.gid = domain_data.gid
- AND NOT domain_name.is_primary) AS aliasdomains
- FROM domain_data
- LEFT JOIN domain_name USING (gid)
- LEFT JOIN transport USING (tid)
- LEFT JOIN users USING (gid)
- WHERE domain_name.is_primary
- GROUP BY gid, domainname, transport, domaindir;
-
-
-CREATE LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
-DECLARE
- primary_count bigint;
-BEGIN
- SELECT INTO primary_count count(gid) + NEW.is_primary::integer
- FROM domain_name
- WHERE domain_name.gid = NEW.gid
- AND is_primary;
-
- IF (primary_count > 1) THEN
- RAISE EXCEPTION 'There can only be one domain marked as primary.';
- END IF;
-
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql STABLE;
-
-
-CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
- FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
-
-CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
- FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql Fri Oct 23 00:20:33 2009 +0000
@@ -0,0 +1,374 @@
+-- --- Information:
+-- This file contains some data types and functions these should speed up some
+-- operations. Read the comment on each data type/functions for more details.
+-- ---
+
+-- ---
+-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
+-- ---
+CREATE TYPE sender_login AS (
+ sender varchar(320),
+ login text
+);
+
+-- ---
+-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
+-- set of sender_login records.
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON domain_name, users, alias TO postfix;
+--
+-- For more details see postconf(5) section smtpd_sender_login_maps
+-- ---
+CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
+AS $$
+ DECLARE
+ rec sender_login;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ sender varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ -- Get all addresses for 'localpart' in the primary and aliased domains
+ FOR rec IN
+ SELECT sender, local_part || '@' || domainname
+ FROM domain_name, users
+ WHERE domain_name.gid = did
+ AND users.gid = did
+ AND users.local_part = localpart
+ LOOP
+ RETURN NEXT rec;
+ END LOOP;
+ IF NOT FOUND THEN
+ -- Loop over the alias addresses for localpart@the_domain
+ FOR rec IN
+ SELECT DISTINCT sender, destination
+ FROM alias
+ WHERE alias.gid = did
+ AND alias.address = localpart
+ LOOP
+ RETURN NEXT rec;
+ END LOOP;
+ END IF;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function postfix_virtual_mailbox(varchar, varchar)
+-- ---
+CREATE TYPE address_maildir AS (
+ address varchar(320),
+ maildir text
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: address_maildir records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
+--
+-- For more details see postconf(5) section virtual_mailbox_maps
+-- ---
+CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
+AS $$
+ DECLARE
+ rec address_maildir;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ address varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ FOR rec IN
+ SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/'
+ FROM domain_data, users, maillocation
+ WHERE domain_data.gid = did
+ AND users.gid = did
+ AND users.local_part = localpart
+ AND maillocation.mid = users.mid
+ LOOP
+ RETURN NEXT rec;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for functions: postfix_relocated_map(varchar, varchar)
+-- postfix_virtual_alias_map(varchar, varchar)
+--
+-- ---
+CREATE TYPE recipient_destination AS (
+ recipient varchar(320),
+ destination text
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: recipient_destination records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON alias, domain_name TO postfix;
+--
+-- For more details see postconf(5) section virtual_alias_maps and virtual(5)
+-- ---
+CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
+ IN localpart varchar, IN the_domain varchar)
+ RETURNS SETOF recipient_destination
+AS $$
+ DECLARE
+ record recipient_destination;
+ recipient varchar(320) := localpart || '@' || the_domain;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ BEGIN
+ FOR record IN
+ SELECT recipient, destination
+ FROM alias
+ WHERE gid = did
+ AND address = localpart
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: recipient_destination records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON domain_name, relocated TO postfix;
+--
+-- For more details see postconf(5) section relocated_maps and relocated(5)
+-- ---
+CREATE OR REPLACE FUNCTION postfix_relocated_map(
+ IN localpart varchar, IN the_domain varchar)
+ RETURNS SETOF recipient_destination
+AS $$
+ DECLARE
+ record recipient_destination;
+ recipient varchar(320) := localpart || '@' || the_domain;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ BEGIN
+ FOR record IN
+ SELECT recipient, destination
+ FROM relocated
+ WHERE gid = did
+ AND address = localpart
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function postfix_transport_map(varchar, varchar)
+-- ---
+CREATE TYPE recipient_transport AS (
+ recipient varchar(320),
+ transport text
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: recipient_transport records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON users, transport, domain_name TO postfix;
+--
+-- For more details see postconf(5) section transport_maps and transport(5)
+-- ---
+CREATE OR REPLACE FUNCTION postfix_transport_map(
+ IN localpart varchar, IN the_domain varchar)
+ RETURNS SETOF recipient_transport
+AS $$
+ DECLARE
+ record recipient_transport;
+ recipient varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ FOR record IN
+ SELECT recipient, transport
+ FROM transport
+ WHERE tid = (SELECT tid
+ FROM users
+ WHERE gid = (SELECT gid
+ FROM domain_name
+ WHERE domainname = the_domain)
+ AND local_part = localpart)
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function postfix_virtual_uid_map(varchar, varchar)
+-- ---
+CREATE TYPE recipient_uid AS (
+ recipient varchar(320),
+ uid bigint
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: recipient_uid records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON users, domain_name TO postfix;
+--
+-- For more details see postconf(5) section virtual_uid_maps
+-- ---
+CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
+AS $$
+ DECLARE
+ record recipient_uid;
+ recipient varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ FOR record IN
+ SELECT recipient, uid
+ FROM users
+ WHERE gid = (SELECT gid
+ FROM domain_name
+ WHERE domainname = the_domain)
+ AND local_part = localpart
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function dovecotuser(varchar, varchar)
+-- ---
+CREATE TYPE dovecotuser AS (
+ userid varchar(320),
+ uid bigint,
+ gid bigint,
+ home text,
+ mail text
+);
+
+-- ---
+-- Parameters (from login name [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: dovecotuser records
+--
+-- Required access privileges for your dovecot database user:
+-- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;
+--
+-- For more details see http://wiki.dovecot.org/UserDatabase
+-- ---
+CREATE OR REPLACE FUNCTION dovecotuser(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
+AS $$
+ DECLARE
+ record dovecotuser;
+ userid varchar(320) := localpart || '@' || the_domain;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ BEGIN
+ FOR record IN
+ SELECT userid, uid, did, domaindir ||'/'|| uid AS home,
+ '~/'|| maillocation AS mail
+ FROM users, domain_data, maillocation
+ WHERE users.gid = did
+ AND users.local_part = localpart
+ AND maillocation.mid = users.mid
+ AND domain_data.gid = did
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function dovecotpassword(varchar, varchar)
+-- ---
+CREATE TYPE dovecotpassword AS (
+ userid varchar(320),
+ password varchar(74),
+ smtp boolean,
+ pop3 boolean,
+ imap boolean,
+ sieve boolean
+);
+
+-- ---
+-- Parameters (from login name [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: dovecotpassword records
+--
+-- Required access privileges for your dovecot database user:
+-- GRANT SELECT ON users, domain_name TO dovecot;
+--
+-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
+-- ---
+CREATE OR REPLACE FUNCTION dovecotpassword(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
+AS $$
+ DECLARE
+ record dovecotpassword;
+ userid varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ FOR record IN
+ SELECT userid, passwd, smtp, pop3, imap, sieve
+ FROM users
+ WHERE gid = (SELECT gid
+ FROM domain_name
+ WHERE domainname = the_domain)
+ AND local_part = localpart
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/create_optional_types_and_functions.pgsql Fri Oct 23 00:20:33 2009 +0000
@@ -0,0 +1,374 @@
+-- --- Information:
+-- This file contains some data types and functions these should speed up some
+-- operations. Read the comment on each data type/functions for more details.
+-- ---
+
+-- ---
+-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
+-- ---
+CREATE TYPE sender_login AS (
+ sender varchar(320),
+ login text
+);
+
+-- ---
+-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
+-- set of sender_login records.
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON domain_name, users, alias TO postfix;
+--
+-- For more details see postconf(5) section smtpd_sender_login_maps
+-- ---
+CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
+AS $$
+ DECLARE
+ rec sender_login;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ sender varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ -- Get all addresses for 'localpart' in the primary and aliased domains
+ FOR rec IN
+ SELECT sender, local_part || '@' || domainname
+ FROM domain_name, users
+ WHERE domain_name.gid = did
+ AND users.gid = did
+ AND users.local_part = localpart
+ LOOP
+ RETURN NEXT rec;
+ END LOOP;
+ IF NOT FOUND THEN
+ -- Loop over the alias addresses for localpart@the_domain
+ FOR rec IN
+ SELECT DISTINCT sender, destination
+ FROM alias
+ WHERE alias.gid = did
+ AND alias.address = localpart
+ LOOP
+ RETURN NEXT rec;
+ END LOOP;
+ END IF;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function postfix_virtual_mailbox(varchar, varchar)
+-- ---
+CREATE TYPE address_maildir AS (
+ address varchar(320),
+ maildir text
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: address_maildir records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
+--
+-- For more details see postconf(5) section virtual_mailbox_maps
+-- ---
+CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
+AS $$
+ DECLARE
+ rec address_maildir;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ address varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ FOR rec IN
+ SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/'
+ FROM domain_data, users, maillocation
+ WHERE domain_data.gid = did
+ AND users.gid = did
+ AND users.local_part = localpart
+ AND maillocation.mid = users.mid
+ LOOP
+ RETURN NEXT rec;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for functions: postfix_relocated_map(varchar, varchar)
+-- postfix_virtual_alias_map(varchar, varchar)
+--
+-- ---
+CREATE TYPE recipient_destination AS (
+ recipient varchar(320),
+ destination text
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: recipient_destination records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON alias, domain_name TO postfix;
+--
+-- For more details see postconf(5) section virtual_alias_maps and virtual(5)
+-- ---
+CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
+ IN localpart varchar, IN the_domain varchar)
+ RETURNS SETOF recipient_destination
+AS $$
+ DECLARE
+ record recipient_destination;
+ recipient varchar(320) := localpart || '@' || the_domain;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ BEGIN
+ FOR record IN
+ SELECT recipient, destination
+ FROM alias
+ WHERE gid = did
+ AND address = localpart
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: recipient_destination records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON domain_name, relocated TO postfix;
+--
+-- For more details see postconf(5) section relocated_maps and relocated(5)
+-- ---
+CREATE OR REPLACE FUNCTION postfix_relocated_map(
+ IN localpart varchar, IN the_domain varchar)
+ RETURNS SETOF recipient_destination
+AS $$
+ DECLARE
+ record recipient_destination;
+ recipient varchar(320) := localpart || '@' || the_domain;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ BEGIN
+ FOR record IN
+ SELECT recipient, destination
+ FROM relocated
+ WHERE gid = did
+ AND address = localpart
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function postfix_transport_map(varchar, varchar)
+-- ---
+CREATE TYPE recipient_transport AS (
+ recipient varchar(320),
+ transport text
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: recipient_transport records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON users, transport, domain_name TO postfix;
+--
+-- For more details see postconf(5) section transport_maps and transport(5)
+-- ---
+CREATE OR REPLACE FUNCTION postfix_transport_map(
+ IN localpart varchar, IN the_domain varchar)
+ RETURNS SETOF recipient_transport
+AS $$
+ DECLARE
+ record recipient_transport;
+ recipient varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ FOR record IN
+ SELECT recipient, transport
+ FROM transport
+ WHERE tid = (SELECT tid
+ FROM users
+ WHERE gid = (SELECT gid
+ FROM domain_name
+ WHERE domainname = the_domain)
+ AND local_part = localpart)
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function postfix_virtual_uid_map(varchar, varchar)
+-- ---
+CREATE TYPE recipient_uid AS (
+ recipient varchar(320),
+ uid bigint
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: recipient_uid records
+--
+-- Required access privileges for your postfix database user:
+-- GRANT SELECT ON users, domain_name TO postfix;
+--
+-- For more details see postconf(5) section virtual_uid_maps
+-- ---
+CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
+AS $$
+ DECLARE
+ record recipient_uid;
+ recipient varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ FOR record IN
+ SELECT recipient, uid
+ FROM users
+ WHERE gid = (SELECT gid
+ FROM domain_name
+ WHERE domainname = the_domain)
+ AND local_part = localpart
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function dovecotuser(varchar, varchar)
+-- ---
+CREATE TYPE dovecotuser AS (
+ userid varchar(320),
+ uid bigint,
+ gid bigint,
+ home text,
+ mail text
+);
+
+-- ---
+-- Parameters (from login name [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: dovecotuser records
+--
+-- Required access privileges for your dovecot database user:
+-- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;
+--
+-- For more details see http://wiki.dovecot.org/UserDatabase
+-- ---
+CREATE OR REPLACE FUNCTION dovecotuser(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
+AS $$
+ DECLARE
+ record dovecotuser;
+ userid varchar(320) := localpart || '@' || the_domain;
+ did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+ BEGIN
+ FOR record IN
+ SELECT userid, uid, did, domaindir ||'/'|| uid AS home,
+ '~/'|| maillocation AS mail
+ FROM users, domain_data, maillocation
+ WHERE users.gid = did
+ AND users.local_part = localpart
+ AND maillocation.mid = users.mid
+ AND domain_data.gid = did
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function dovecotpassword(varchar, varchar)
+-- ---
+CREATE TYPE dovecotpassword AS (
+ userid varchar(320),
+ password varchar(74),
+ smtp boolean,
+ pop3 boolean,
+ imap boolean,
+ managesieve boolean
+);
+
+-- ---
+-- Parameters (from login name [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: dovecotpassword records
+--
+-- Required access privileges for your dovecot database user:
+-- GRANT SELECT ON users, domain_name TO dovecot;
+--
+-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
+-- ---
+CREATE OR REPLACE FUNCTION dovecotpassword(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
+AS $$
+ DECLARE
+ record dovecotpassword;
+ userid varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ FOR record IN
+ SELECT userid, passwd, smtp, pop3, imap, managesieve
+ FROM users
+ WHERE gid = (SELECT gid
+ FROM domain_name
+ WHERE domainname = the_domain)
+ AND local_part = localpart
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Fri Oct 23 00:20:33 2009 +0000
@@ -0,0 +1,196 @@
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+
+CREATE SEQUENCE transport_id;
+
+CREATE SEQUENCE maillocation_id;
+
+CREATE SEQUENCE domain_gid
+ START WITH 70000
+ INCREMENT BY 1
+ MINVALUE 70000
+ MAXVALUE 4294967294
+ NO CYCLE;
+
+CREATE SEQUENCE users_uid
+ START WITH 70000
+ INCREMENT BY 1
+ MINVALUE 70000
+ MAXVALUE 4294967294
+ NO CYCLE;
+
+
+CREATE TABLE transport (
+ tid bigint NOT NULL DEFAULT nextval('transport_id'),
+ transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
+ CONSTRAINT pkey_transport PRIMARY KEY (tid),
+ CONSTRAINT ukey_transport UNIQUE (transport)
+);
+-- Insert default transport
+INSERT INTO transport(transport) VALUES ('dovecot:');
+
+CREATE TABLE maillocation(
+ mid bigint NOT NULL DEFAULT nextval('maillocation_id'),
+ maillocation varchar(20) NOT NULL,
+ CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
+ CONSTRAINT ukey_maillocation UNIQUE (maillocation)
+);
+-- Insert default Maildir-folder name
+INSERT INTO maillocation(maillocation) VALUES ('Maildir');
+
+CREATE TABLE domain_data (
+ gid bigint NOT NULL DEFAULT nextval('domain_gid'),
+ tid bigint NOT NULL DEFAULT 1, -- defualt transport
+ domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
+ CONSTRAINT pkey_domain_data PRIMARY KEY (gid),
+ CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid)
+ REFERENCES transport (tid)
+);
+
+CREATE TABLE domain_name (
+ domainname varchar(255) NOT NULL,
+ gid bigint NOT NULL,
+ is_primary boolean NOT NULL,
+ CONSTRAINT pkey_domain_name PRIMARY KEY (domainname),
+ CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid)
+);
+
+CREATE TABLE users (
+ local_part varchar(64) NOT NULL,-- only localpart w/o '@'
+ passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
+ name varchar(128) NULL,
+ uid bigint NOT NULL DEFAULT nextval('users_uid'),
+ gid bigint NOT NULL,
+ mid bigint NOT NULL DEFAULT 1,
+ tid bigint NOT NULL DEFAULT 1,
+ smtp boolean NOT NULL DEFAULT TRUE,
+ pop3 boolean NOT NULL DEFAULT TRUE,
+ imap boolean NOT NULL DEFAULT TRUE,
+ sieve boolean NOT NULL DEFAULT TRUE,
+ CONSTRAINT pkey_users PRIMARY KEY (local_part, gid),
+ CONSTRAINT ukey_users_uid UNIQUE (uid),
+ CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid),
+ CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
+ REFERENCES maillocation (mid),
+ CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
+ REFERENCES transport (tid)
+);
+
+CREATE TABLE alias (
+ gid bigint NOT NULL,
+ address varchar(64) NOT NULL,-- only localpart w/o '@'
+ destination varchar(320) NOT NULL,
+ CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination),
+ CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid)
+);
+
+CREATE TABLE relocated (
+ gid bigint NOT NULL,
+ address varchar(64) NOT NULL,
+ destination varchar(320) NOT NULL,
+ CONSTRAINT pkey_relocated PRIMARY KEY (gid, address),
+ CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid)
+);
+
+CREATE OR REPLACE VIEW dovecot_password AS
+ SELECT local_part || '@' || domain_name.domainname AS "user",
+ passwd AS "password", smtp, pop3, imap, sieve
+ FROM users
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW dovecot_user AS
+ SELECT local_part || '@' || domain_name.domainname AS userid,
+ uid, gid, domain_data.domaindir || '/' || uid AS home,
+ '~/' || maillocation.maillocation AS mail
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW postfix_gid AS
+ SELECT gid, domainname
+ FROM domain_name;
+
+CREATE OR REPLACE VIEW postfix_uid AS
+ SELECT local_part || '@' || domain_name.domainname AS address, uid
+ FROM users
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_maildir AS
+ SELECT local_part || '@' || domain_name.domainname AS address,
+ domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
+ AS maildir
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW postfix_relocated AS
+ SELECT address || '@' || domain_name.domainname AS address, destination
+ FROM relocated
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_alias AS
+ SELECT address || '@' || domain_name.domainname AS address, destination, gid
+ FROM alias
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_transport AS
+ SELECT local_part || '@' || domain_name.domainname AS address,
+ transport.transport
+ FROM users
+ LEFT JOIN transport USING (tid)
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW vmm_domain_info AS
+ SELECT gid, domainname, transport, domaindir,
+ count(uid) AS accounts,
+ (SELECT count(DISTINCT address)
+ FROM alias
+ WHERE alias.gid = domain_data.gid) AS aliases,
+ (SELECT count(gid)
+ FROM relocated
+ WHERE relocated.gid = domain_data.gid) AS relocated,
+ (SELECT count(gid)
+ FROM domain_name
+ WHERE domain_name.gid = domain_data.gid
+ AND NOT domain_name.is_primary) AS aliasdomains
+ FROM domain_data
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN transport USING (tid)
+ LEFT JOIN users USING (gid)
+ WHERE domain_name.is_primary
+ GROUP BY gid, domainname, transport, domaindir;
+
+
+CREATE LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
+DECLARE
+ primary_count bigint;
+BEGIN
+ SELECT INTO primary_count count(gid) + NEW.is_primary::integer
+ FROM domain_name
+ WHERE domain_name.gid = NEW.gid
+ AND is_primary;
+
+ IF (primary_count > 1) THEN
+ RAISE EXCEPTION 'There can only be one domain marked as primary.';
+ END IF;
+
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql STABLE;
+
+
+CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
+ FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
+
+CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
+ FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/create_tables.pgsql Fri Oct 23 00:20:33 2009 +0000
@@ -0,0 +1,196 @@
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+
+CREATE SEQUENCE transport_id;
+
+CREATE SEQUENCE maillocation_id;
+
+CREATE SEQUENCE domain_gid
+ START WITH 70000
+ INCREMENT BY 1
+ MINVALUE 70000
+ MAXVALUE 4294967294
+ NO CYCLE;
+
+CREATE SEQUENCE users_uid
+ START WITH 70000
+ INCREMENT BY 1
+ MINVALUE 70000
+ MAXVALUE 4294967294
+ NO CYCLE;
+
+
+CREATE TABLE transport (
+ tid bigint NOT NULL DEFAULT nextval('transport_id'),
+ transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
+ CONSTRAINT pkey_transport PRIMARY KEY (tid),
+ CONSTRAINT ukey_transport UNIQUE (transport)
+);
+-- Insert default transport
+INSERT INTO transport(transport) VALUES ('dovecot:');
+
+CREATE TABLE maillocation(
+ mid bigint NOT NULL DEFAULT nextval('maillocation_id'),
+ maillocation varchar(20) NOT NULL,
+ CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
+ CONSTRAINT ukey_maillocation UNIQUE (maillocation)
+);
+-- Insert default Maildir-folder name
+INSERT INTO maillocation(maillocation) VALUES ('Maildir');
+
+CREATE TABLE domain_data (
+ gid bigint NOT NULL DEFAULT nextval('domain_gid'),
+ tid bigint NOT NULL DEFAULT 1, -- defualt transport
+ domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
+ CONSTRAINT pkey_domain_data PRIMARY KEY (gid),
+ CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid)
+ REFERENCES transport (tid)
+);
+
+CREATE TABLE domain_name (
+ domainname varchar(255) NOT NULL,
+ gid bigint NOT NULL,
+ is_primary boolean NOT NULL,
+ CONSTRAINT pkey_domain_name PRIMARY KEY (domainname),
+ CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid)
+);
+
+CREATE TABLE users (
+ local_part varchar(64) NOT NULL,-- only localpart w/o '@'
+ passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
+ name varchar(128) NULL,
+ uid bigint NOT NULL DEFAULT nextval('users_uid'),
+ gid bigint NOT NULL,
+ mid bigint NOT NULL DEFAULT 1,
+ tid bigint NOT NULL DEFAULT 1,
+ smtp boolean NOT NULL DEFAULT TRUE,
+ pop3 boolean NOT NULL DEFAULT TRUE,
+ imap boolean NOT NULL DEFAULT TRUE,
+ managesieve boolean NOT NULL DEFAULT TRUE,
+ CONSTRAINT pkey_users PRIMARY KEY (local_part, gid),
+ CONSTRAINT ukey_users_uid UNIQUE (uid),
+ CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid),
+ CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
+ REFERENCES maillocation (mid),
+ CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
+ REFERENCES transport (tid)
+);
+
+CREATE TABLE alias (
+ gid bigint NOT NULL,
+ address varchar(64) NOT NULL,-- only localpart w/o '@'
+ destination varchar(320) NOT NULL,
+ CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination),
+ CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid)
+);
+
+CREATE TABLE relocated (
+ gid bigint NOT NULL,
+ address varchar(64) NOT NULL,
+ destination varchar(320) NOT NULL,
+ CONSTRAINT pkey_relocated PRIMARY KEY (gid, address),
+ CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid)
+);
+
+CREATE OR REPLACE VIEW dovecot_password AS
+ SELECT local_part || '@' || domain_name.domainname AS "user",
+ passwd AS "password", smtp, pop3, imap, managesieve
+ FROM users
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW dovecot_user AS
+ SELECT local_part || '@' || domain_name.domainname AS userid,
+ uid, gid, domain_data.domaindir || '/' || uid AS home,
+ '~/' || maillocation.maillocation AS mail
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW postfix_gid AS
+ SELECT gid, domainname
+ FROM domain_name;
+
+CREATE OR REPLACE VIEW postfix_uid AS
+ SELECT local_part || '@' || domain_name.domainname AS address, uid
+ FROM users
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_maildir AS
+ SELECT local_part || '@' || domain_name.domainname AS address,
+ domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
+ AS maildir
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW postfix_relocated AS
+ SELECT address || '@' || domain_name.domainname AS address, destination
+ FROM relocated
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_alias AS
+ SELECT address || '@' || domain_name.domainname AS address, destination, gid
+ FROM alias
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_transport AS
+ SELECT local_part || '@' || domain_name.domainname AS address,
+ transport.transport
+ FROM users
+ LEFT JOIN transport USING (tid)
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW vmm_domain_info AS
+ SELECT gid, domainname, transport, domaindir,
+ count(uid) AS accounts,
+ (SELECT count(DISTINCT address)
+ FROM alias
+ WHERE alias.gid = domain_data.gid) AS aliases,
+ (SELECT count(gid)
+ FROM relocated
+ WHERE relocated.gid = domain_data.gid) AS relocated,
+ (SELECT count(gid)
+ FROM domain_name
+ WHERE domain_name.gid = domain_data.gid
+ AND NOT domain_name.is_primary) AS aliasdomains
+ FROM domain_data
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN transport USING (tid)
+ LEFT JOIN users USING (gid)
+ WHERE domain_name.is_primary
+ GROUP BY gid, domainname, transport, domaindir;
+
+
+CREATE LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
+DECLARE
+ primary_count bigint;
+BEGIN
+ SELECT INTO primary_count count(gid) + NEW.is_primary::integer
+ FROM domain_name
+ WHERE domain_name.gid = NEW.gid
+ AND is_primary;
+
+ IF (primary_count > 1) THEN
+ RAISE EXCEPTION 'There can only be one domain marked as primary.';
+ END IF;
+
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql STABLE;
+
+
+CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
+ FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
+
+CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
+ FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/update_tables_0.4.x-0.5.pgsql Fri Oct 23 00:20:33 2009 +0000
@@ -0,0 +1,156 @@
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+ALTER SEQUENCE domains_gid RENAME TO domain_gid;
+
+
+CREATE TABLE domain_data (
+ gid bigint NOT NULL DEFAULT nextval('domain_gid'),
+ tid bigint NOT NULL DEFAULT 1,
+ domaindir varchar(40) NOT NULL,
+ CONSTRAINT pkey_domain_data PRIMARY KEY (gid),
+ CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid)
+ REFERENCES transport (tid)
+);
+
+CREATE TABLE domain_name (
+ domainname varchar(255) NOT NULL,
+ gid bigint NOT NULL,
+ is_primary boolean NOT NULL,
+ CONSTRAINT pkey_domain_name PRIMARY KEY (domainname),
+ CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid)
+);
+
+INSERT INTO domain_data (gid, tid, domaindir)
+ SELECT gid, tid, domaindir
+ FROM domains;
+
+INSERT INTO domain_name (domainname, gid, is_primary)
+ SELECT domainname, gid, TRUE
+ FROM domains;
+
+
+ALTER TABLE users DROP CONSTRAINT pkye_users;
+ALTER TABLE users ADD CONSTRAINT pkey_users PRIMARY KEY (local_part, gid);
+ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains;
+ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid);
+
+ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;
+ALTER TABLE alias DROP CONSTRAINT pkey_alias;
+ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
+ REFERENCES domain_data (gid);
+
+ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;
+ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data
+ FOREIGN KEY (gid) REFERENCES domain_data (gid);
+
+
+CREATE OR REPLACE VIEW dovecot_password AS
+ SELECT local_part || '@' || domain_name.domainname AS "user",
+ passwd AS "password", smtp, pop3, imap, managesieve
+ FROM users
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW dovecot_user AS
+ SELECT local_part || '@' || domain_name.domainname AS userid,
+ uid, gid, domain_data.domaindir || '/' || uid AS home,
+ '~/' || maillocation.maillocation AS mail
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW postfix_gid AS
+ SELECT gid, domainname
+ FROM domain_name;
+
+CREATE OR REPLACE VIEW postfix_uid AS
+ SELECT local_part || '@' || domain_name.domainname AS address, uid
+ FROM users
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_maildir AS
+ SELECT local_part || '@' || domain_name.domainname AS address,
+ domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
+ AS maildir
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW postfix_relocated AS
+ SELECT address || '@' || domain_name.domainname AS address, destination
+ FROM relocated
+ LEFT JOIN domain_name USING (gid);
+
+DROP VIEW postfix_alias;
+DROP VIEW vmm_domain_info;
+DROP VIEW vmm_alias_count;
+
+ALTER TABLE alias ALTER address TYPE varchar(64);
+ALTER TABLE alias ADD CONSTRAINT pkey_alias
+ PRIMARY KEY (gid, address, destination);
+
+CREATE OR REPLACE VIEW postfix_alias AS
+ SELECT address || '@' || domain_name.domainname AS address, destination, gid
+ FROM alias
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_transport AS
+ SELECT local_part || '@' || domain_name.domainname AS address,
+ transport.transport
+ FROM users
+ LEFT JOIN transport USING (tid)
+ LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW vmm_domain_info AS
+ SELECT gid, domainname, transport, domaindir,
+ count(uid) AS accounts,
+ (SELECT count(DISTINCT address)
+ FROM alias
+ WHERE alias.gid = domain_data.gid) AS aliases,
+ (SELECT count(gid)
+ FROM relocated
+ WHERE relocated.gid = domain_data.gid) AS relocated,
+ (SELECT count(gid)
+ FROM domain_name
+ WHERE domain_name.gid = domain_data.gid
+ AND NOT domain_name.is_primary) AS aliasdomains
+ FROM domain_data
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN transport USING (tid)
+ LEFT JOIN users USING (gid)
+ WHERE domain_name.is_primary
+ GROUP BY gid, domainname, transport, domaindir;
+
+
+DROP TABLE domains;
+
+
+CREATE LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
+DECLARE
+ primary_count bigint;
+BEGIN
+ SELECT INTO primary_count count(gid) + NEW.is_primary::integer
+ FROM domain_name
+ WHERE domain_name.gid = NEW.gid
+ AND is_primary;
+
+ IF (primary_count > 1) THEN
+ RAISE EXCEPTION 'There can only be one domain marked as primary.';
+ END IF;
+
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql STABLE;
+
+DROP TRIGGER IF EXISTS primary_count ON domain_name;
+CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
+ FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
+
+CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
+ FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/update_tables_0.5.x_for_dovecot-1.2.x.pgsql Fri Oct 23 00:20:33 2009 +0000
@@ -0,0 +1,12 @@
+-- ---
+-- with Dovecot v1.2.x the service managesieve was renamed to sieve
+-- ---
+ALTER TABLE users RENAME managesieve TO sieve;
+
+DROP VIEW dovecot_password;
+CREATE OR REPLACE VIEW dovecot_password AS
+ SELECT local_part || '@' || domain_name.domainname AS "user",
+ passwd AS "password", smtp, pop3, imap, sieve
+ FROM users
+ LEFT JOIN domain_name USING (gid);
+
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql Fri Oct 23 00:20:33 2009 +0000
@@ -0,0 +1,51 @@
+-- ---
+-- Clean out the old stuff
+-- ---
+DROP TYPE dovecotpassword CASCADE;
+
+-- ---
+-- Data type for function dovecotpassword(varchar, varchar)
+-- ---
+CREATE TYPE dovecotpassword AS (
+ userid varchar(320),
+ password varchar(74),
+ smtp boolean,
+ pop3 boolean,
+ imap boolean,
+ sieve boolean
+);
+
+-- ---
+-- Parameters (from login name [localpart@the_domain]):
+-- varchar localpart
+-- varchar the_domain
+-- Returns: dovecotpassword records
+--
+-- Required access privileges for your dovecot database user:
+-- GRANT SELECT ON users, domain_name TO dovecot;
+--
+-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
+-- ---
+CREATE OR REPLACE FUNCTION dovecotpassword(
+ IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
+AS $$
+ DECLARE
+ record dovecotpassword;
+ userid varchar(320) := localpart || '@' || the_domain;
+ BEGIN
+ FOR record IN
+ SELECT userid, passwd, smtp, pop3, imap, sieve
+ FROM users
+ WHERE gid = (SELECT gid
+ FROM domain_name
+ WHERE domainname = the_domain)
+ AND local_part = localpart
+ LOOP
+ RETURN NEXT record;
+ END LOOP;
+ RETURN;
+ END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
--- a/update_tables_0.4.x-0.5.pgsql Thu Oct 22 19:30:46 2009 +0000
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,156 +0,0 @@
-SET client_encoding = 'UTF8';
-SET client_min_messages = warning;
-
-ALTER SEQUENCE domains_gid RENAME TO domain_gid;
-
-
-CREATE TABLE domain_data (
- gid bigint NOT NULL DEFAULT nextval('domain_gid'),
- tid bigint NOT NULL DEFAULT 1,
- domaindir varchar(40) NOT NULL,
- CONSTRAINT pkey_domain_data PRIMARY KEY (gid),
- CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid)
- REFERENCES transport (tid)
-);
-
-CREATE TABLE domain_name (
- domainname varchar(255) NOT NULL,
- gid bigint NOT NULL,
- is_primary boolean NOT NULL,
- CONSTRAINT pkey_domain_name PRIMARY KEY (domainname),
- CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid)
-);
-
-INSERT INTO domain_data (gid, tid, domaindir)
- SELECT gid, tid, domaindir
- FROM domains;
-
-INSERT INTO domain_name (domainname, gid, is_primary)
- SELECT domainname, gid, TRUE
- FROM domains;
-
-
-ALTER TABLE users DROP CONSTRAINT pkye_users;
-ALTER TABLE users ADD CONSTRAINT pkey_users PRIMARY KEY (local_part, gid);
-ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains;
-ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid);
-
-ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;
-ALTER TABLE alias DROP CONSTRAINT pkey_alias;
-ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
- REFERENCES domain_data (gid);
-
-ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;
-ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data
- FOREIGN KEY (gid) REFERENCES domain_data (gid);
-
-
-CREATE OR REPLACE VIEW dovecot_password AS
- SELECT local_part || '@' || domain_name.domainname AS "user",
- passwd AS "password", smtp, pop3, imap, managesieve
- FROM users
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW dovecot_user AS
- SELECT local_part || '@' || domain_name.domainname AS userid,
- uid, gid, domain_data.domaindir || '/' || uid AS home,
- '~/' || maillocation.maillocation AS mail
- FROM users
- LEFT JOIN domain_data USING (gid)
- LEFT JOIN domain_name USING (gid)
- LEFT JOIN maillocation USING (mid);
-
-CREATE OR REPLACE VIEW postfix_gid AS
- SELECT gid, domainname
- FROM domain_name;
-
-CREATE OR REPLACE VIEW postfix_uid AS
- SELECT local_part || '@' || domain_name.domainname AS address, uid
- FROM users
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW postfix_maildir AS
- SELECT local_part || '@' || domain_name.domainname AS address,
- domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
- AS maildir
- FROM users
- LEFT JOIN domain_data USING (gid)
- LEFT JOIN domain_name USING (gid)
- LEFT JOIN maillocation USING (mid);
-
-CREATE OR REPLACE VIEW postfix_relocated AS
- SELECT address || '@' || domain_name.domainname AS address, destination
- FROM relocated
- LEFT JOIN domain_name USING (gid);
-
-DROP VIEW postfix_alias;
-DROP VIEW vmm_domain_info;
-DROP VIEW vmm_alias_count;
-
-ALTER TABLE alias ALTER address TYPE varchar(64);
-ALTER TABLE alias ADD CONSTRAINT pkey_alias
- PRIMARY KEY (gid, address, destination);
-
-CREATE OR REPLACE VIEW postfix_alias AS
- SELECT address || '@' || domain_name.domainname AS address, destination, gid
- FROM alias
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW postfix_transport AS
- SELECT local_part || '@' || domain_name.domainname AS address,
- transport.transport
- FROM users
- LEFT JOIN transport USING (tid)
- LEFT JOIN domain_name USING (gid);
-
-CREATE OR REPLACE VIEW vmm_domain_info AS
- SELECT gid, domainname, transport, domaindir,
- count(uid) AS accounts,
- (SELECT count(DISTINCT address)
- FROM alias
- WHERE alias.gid = domain_data.gid) AS aliases,
- (SELECT count(gid)
- FROM relocated
- WHERE relocated.gid = domain_data.gid) AS relocated,
- (SELECT count(gid)
- FROM domain_name
- WHERE domain_name.gid = domain_data.gid
- AND NOT domain_name.is_primary) AS aliasdomains
- FROM domain_data
- LEFT JOIN domain_name USING (gid)
- LEFT JOIN transport USING (tid)
- LEFT JOIN users USING (gid)
- WHERE domain_name.is_primary
- GROUP BY gid, domainname, transport, domaindir;
-
-
-DROP TABLE domains;
-
-
-CREATE LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
-DECLARE
- primary_count bigint;
-BEGIN
- SELECT INTO primary_count count(gid) + NEW.is_primary::integer
- FROM domain_name
- WHERE domain_name.gid = NEW.gid
- AND is_primary;
-
- IF (primary_count > 1) THEN
- RAISE EXCEPTION 'There can only be one domain marked as primary.';
- END IF;
-
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql STABLE;
-
-DROP TRIGGER IF EXISTS primary_count ON domain_name;
-CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
- FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
-
-CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
- FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
--- a/update_tables_0.5.x_for_dovecot-1.2.x.pgsql Thu Oct 22 19:30:46 2009 +0000
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,12 +0,0 @@
--- ---
--- with Dovecot v1.2.x the service managesieve was renamed to sieve
--- ---
-ALTER TABLE users RENAME managesieve TO sieve;
-
-DROP VIEW dovecot_password;
-CREATE OR REPLACE VIEW dovecot_password AS
- SELECT local_part || '@' || domain_name.domainname AS "user",
- passwd AS "password", smtp, pop3, imap, sieve
- FROM users
- LEFT JOIN domain_name USING (gid);
-
--- a/update_types_and_functions_0.5.x_for_dovecot-1.2.x.pgsql Thu Oct 22 19:30:46 2009 +0000
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,51 +0,0 @@
--- ---
--- Clean out the old stuff
--- ---
-DROP TYPE dovecotpassword CASCADE;
-
--- ---
--- Data type for function dovecotpassword(varchar, varchar)
--- ---
-CREATE TYPE dovecotpassword AS (
- userid varchar(320),
- password varchar(74),
- smtp boolean,
- pop3 boolean,
- imap boolean,
- sieve boolean
-);
-
--- ---
--- Parameters (from login name [localpart@the_domain]):
--- varchar localpart
--- varchar the_domain
--- Returns: dovecotpassword records
---
--- Required access privileges for your dovecot database user:
--- GRANT SELECT ON users, domain_name TO dovecot;
---
--- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
--- ---
-CREATE OR REPLACE FUNCTION dovecotpassword(
- IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
-AS $$
- DECLARE
- record dovecotpassword;
- userid varchar(320) := localpart || '@' || the_domain;
- BEGIN
- FOR record IN
- SELECT userid, passwd, smtp, pop3, imap, sieve
- FROM users
- WHERE gid = (SELECT gid
- FROM domain_name
- WHERE domainname = the_domain)
- AND local_part = localpart
- LOOP
- RETURN NEXT record;
- END LOOP;
- RETURN;
- END;
-$$ LANGUAGE plpgsql STABLE
-RETURNS NULL ON NULL INPUT
-EXTERNAL SECURITY INVOKER;
-