pgsql: Added support for different mailbox formats.
- users.passwd can store sha512-crypt.hex hashes now
- Added new update scripts.
--- a/TODO Mon Jul 12 01:53:32 2010 +0000
+++ b/TODO Tue Jul 20 03:15:57 2010 +0000
@@ -19,9 +19,5 @@
+ alias domains
Database:
- public.users.passwd: increase to "character varying(264)"
- why? len(VirtualMailManager.password.pwhash('1', 'sha512-crypt.hex')) -> 264
- if Dovecot >= v2.0.beta6 || changeset >= 11278:2ead7574bb08 and
- misc.crypt_sha512_rounds >= 100000000
public.users.digestmd5: add "character varying(48)"
Outlook will love it. (`doveadm pw -s DIGEST-MD5.hex -p 1 -u 0`)
--- a/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql Mon Jul 12 01:53:32 2010 +0000
+++ b/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql Tue Jul 20 03:15:57 2010 +0000
@@ -88,7 +88,7 @@
address varchar(320) := localpart || '@' || the_domain;
BEGIN
FOR rec IN
- SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/'
+ SELECT address, domaindir||'/'||users.uid||'/'||directory||'/'
FROM domain_data, users, maillocation
WHERE domain_data.gid = did
AND users.gid = did
@@ -108,7 +108,6 @@
-- ---
-- Data type for functions: postfix_relocated_map(varchar, varchar)
-- postfix_virtual_alias_map(varchar, varchar)
---
-- ---
CREATE TYPE recipient_destination AS (
recipient varchar(320),
@@ -296,7 +295,9 @@
-- Returns: dovecotuser records
--
-- Required access privileges for your dovecot database user:
--- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;
+-- GRANT SELECT
+-- ON users, domain_data, domain_name, maillocation, mailboxformat
+-- TO dovecot;
--
-- For more details see http://wiki.dovecot.org/UserDatabase
-- ---
@@ -309,12 +310,13 @@
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
+ 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;
@@ -332,7 +334,7 @@
-- ---
CREATE TYPE dovecotpassword AS (
userid varchar(320),
- password varchar(74),
+ password varchar(270),
smtp boolean,
pop3 boolean,
imap boolean,
--- a/pgsql/create_optional_types_and_functions.pgsql Mon Jul 12 01:53:32 2010 +0000
+++ b/pgsql/create_optional_types_and_functions.pgsql Tue Jul 20 03:15:57 2010 +0000
@@ -88,7 +88,7 @@
address varchar(320) := localpart || '@' || the_domain;
BEGIN
FOR rec IN
- SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/'
+ SELECT address, domaindir||'/'||users.uid||'/'||directory||'/'
FROM domain_data, users, maillocation
WHERE domain_data.gid = did
AND users.gid = did
@@ -108,7 +108,6 @@
-- ---
-- Data type for functions: postfix_relocated_map(varchar, varchar)
-- postfix_virtual_alias_map(varchar, varchar)
---
-- ---
CREATE TYPE recipient_destination AS (
recipient varchar(320),
@@ -296,7 +295,9 @@
-- Returns: dovecotuser records
--
-- Required access privileges for your dovecot database user:
--- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;
+-- GRANT SELECT
+-- ON users, domain_data, domain_name, maillocation, mailboxformat
+-- TO dovecot;
--
-- For more details see http://wiki.dovecot.org/UserDatabase
-- ---
@@ -309,12 +310,13 @@
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
+ 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;
@@ -332,7 +334,7 @@
-- ---
CREATE TYPE dovecotpassword AS (
userid varchar(320),
- password varchar(74),
+ password varchar(270),
smtp boolean,
pop3 boolean,
imap boolean,
--- a/pgsql/create_tables-dovecot-1.2.x.pgsql Mon Jul 12 01:53:32 2010 +0000
+++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Tue Jul 20 03:15:57 2010 +0000
@@ -4,6 +4,8 @@
CREATE SEQUENCE transport_id;
+CREATE SEQUENCE mailboxformat_id;
+
CREATE SEQUENCE maillocation_id;
CREATE SEQUENCE domain_gid
@@ -30,14 +32,28 @@
-- 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,
+CREATE TABLE mailboxformat (
+ fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
+ format varchar(20) NOT NULL,
+ CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid),
+ CONSTRAINT ukey_mailboxformat UNIQUE (format)
+);
+-- Insert supported mailbox formats
+INSERT INTO mailboxformat(format) VALUES ('maildir');
+INSERT INTO mailboxformat(format) VALUES ('mdbox');
+INSERT INTO mailboxformat(format) VALUES ('sdbox');
+
+CREATE TABLE maillocation (
+ mid bigint NOT NULL DEFAULT nextval('maillocation_id'),
+ fid bigint NOT NULL DEFAULT 1,
+ directory varchar(20) NOT NULL,
+ extra varchar(1024),
CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
- CONSTRAINT ukey_maillocation UNIQUE (maillocation)
+ CONSTRAINT fkey_maillocation_fid_mailboxformat FOREIGN KEY (fid)
+ REFERENCES mailboxformat (fid);
);
-- Insert default Maildir-folder name
-INSERT INTO maillocation(maillocation) VALUES ('Maildir');
+INSERT INTO maillocation(directory) VALUES ('Maildir');
CREATE TABLE domain_data (
gid bigint NOT NULL DEFAULT nextval('domain_gid'),
@@ -59,7 +75,7 @@
CREATE TABLE users (
local_part varchar(64) NOT NULL,-- only localpart w/o '@'
- passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
+ passwd varchar(270) NOT NULL,
name varchar(128) NULL,
uid bigint NOT NULL DEFAULT nextval('users_uid'),
gid bigint NOT NULL,
@@ -106,11 +122,12 @@
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
+ mailboxformat.format || ':~/' || maillocation.directory AS mail
FROM users
LEFT JOIN domain_data USING (gid)
LEFT JOIN domain_name USING (gid)
- LEFT JOIN maillocation USING (mid);
+ LEFT JOIN maillocation USING (mid)
+ LEFT JOIN mailboxformat USING (fid);
CREATE OR REPLACE VIEW postfix_gid AS
SELECT gid, domainname
@@ -123,7 +140,7 @@
CREATE OR REPLACE VIEW postfix_maildir AS
SELECT local_part || '@' || domain_name.domainname AS address,
- domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
+ domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
AS maildir
FROM users
LEFT JOIN domain_data USING (gid)
--- a/pgsql/create_tables.pgsql Mon Jul 12 01:53:32 2010 +0000
+++ b/pgsql/create_tables.pgsql Tue Jul 20 03:15:57 2010 +0000
@@ -4,6 +4,8 @@
CREATE SEQUENCE transport_id;
+CREATE SEQUENCE mailboxformat_id;
+
CREATE SEQUENCE maillocation_id;
CREATE SEQUENCE domain_gid
@@ -30,14 +32,28 @@
-- 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,
+CREATE TABLE mailboxformat (
+ fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
+ format varchar(20) NOT NULL,
+ CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid),
+ CONSTRAINT ukey_mailboxformat UNIQUE (format)
+);
+-- Insert supported mailbox formats
+INSERT INTO mailboxformat(format) VALUES ('maildir');
+INSERT INTO mailboxformat(format) VALUES ('mdbox');
+INSERT INTO mailboxformat(format) VALUES ('sdbox');
+
+CREATE TABLE maillocation (
+ mid bigint NOT NULL DEFAULT nextval('maillocation_id'),
+ fid bigint NOT NULL DEFAULT 1,
+ directory varchar(20) NOT NULL,
+ extra varchar(1024),
CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
- CONSTRAINT ukey_maillocation UNIQUE (maillocation)
+ CONSTRAINT fkey_maillocation_fid_mailboxformat FOREIGN KEY (fid)
+ REFERENCES mailboxformat (fid);
);
-- Insert default Maildir-folder name
-INSERT INTO maillocation(maillocation) VALUES ('Maildir');
+INSERT INTO maillocation(directory) VALUES ('Maildir');
CREATE TABLE domain_data (
gid bigint NOT NULL DEFAULT nextval('domain_gid'),
@@ -59,7 +75,7 @@
CREATE TABLE users (
local_part varchar(64) NOT NULL,-- only localpart w/o '@'
- passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
+ passwd varchar(270) NOT NULL,
name varchar(128) NULL,
uid bigint NOT NULL DEFAULT nextval('users_uid'),
gid bigint NOT NULL,
@@ -106,11 +122,12 @@
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
+ mailboxformat.format || ':~/' || maillocation.directory AS mail
FROM users
LEFT JOIN domain_data USING (gid)
LEFT JOIN domain_name USING (gid)
- LEFT JOIN maillocation USING (mid);
+ LEFT JOIN maillocation USING (mid)
+ LEFT JOIN mailboxformat USING (fid);
CREATE OR REPLACE VIEW postfix_gid AS
SELECT gid, domainname
@@ -123,7 +140,7 @@
CREATE OR REPLACE VIEW postfix_maildir AS
SELECT local_part || '@' || domain_name.domainname AS address,
- domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
+ domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
AS maildir
FROM users
LEFT JOIN domain_data USING (gid)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Tue Jul 20 03:15:57 2010 +0000
@@ -0,0 +1,64 @@
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+
+-- ---
+-- Make room for sha512-crypt.hex hashed passwords
+-- ---
+DROP VIEW dovecot_password;
+
+ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
+
+CREATE 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);
+
+-- ---
+-- Make room for different mailbox formats.
+-- ---
+DROP VIEW dovecot_user;
+DROP VIEW postfix_maildir;
+
+CREATE SEQUENCE mailboxformat_id;
+
+CREATE TABLE mailboxformat (
+ fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
+ format varchar(20) NOT NULL,
+ CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid),
+ CONSTRAINT ukey_mailboxformat UNIQUE (format)
+);
+-- Insert supported mailbox formats
+INSERT INTO mailboxformat(format) VALUES ('maildir');
+INSERT INTO mailboxformat(format) VALUES ('mdbox');
+INSERT INTO mailboxformat(format) VALUES ('sdbox');
+
+-- Adjust maillocation table
+ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation;
+ALTER TABLE maillocation RENAME COLUMN maillocation TO directory;
+ALTER TABLE maillocation
+ ADD COLUMN fid bigint NOT NULL DEFAULT 1,
+ ADD COLUMN extra varchar(1024);
+ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat
+ FOREIGN KEY (fid) REFERENCES mailboxformat (fid);
+
+
+CREATE VIEW dovecot_user AS
+ SELECT local_part || '@' || domain_name.domainname AS userid,
+ uid, gid, domain_data.domaindir || '/' || uid AS home,
+ mailboxformat.format || ':~/' || maillocation.directory AS mail
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid)
+ LEFT JOIN mailboxformat USING (fid);
+
+CREATE VIEW postfix_maildir AS
+ SELECT local_part || '@' || domain_name.domainname AS address,
+ domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
+ AS maildir
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/update_tables_0.5.x-0.6.pgsql Tue Jul 20 03:15:57 2010 +0000
@@ -0,0 +1,64 @@
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+
+-- ---
+-- Make room for sha512-crypt.hex hashed passwords
+-- ---
+DROP VIEW dovecot_password;
+
+ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
+
+CREATE 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);
+
+-- ---
+-- Make room for different mailbox formats.
+-- ---
+DROP VIEW dovecot_user;
+DROP VIEW postfix_maildir;
+
+CREATE SEQUENCE mailboxformat_id;
+
+CREATE TABLE mailboxformat (
+ fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
+ format varchar(20) NOT NULL,
+ CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid),
+ CONSTRAINT ukey_mailboxformat UNIQUE (format)
+);
+-- Insert supported mailbox formats
+INSERT INTO mailboxformat(format) VALUES ('maildir');
+INSERT INTO mailboxformat(format) VALUES ('mdbox');
+INSERT INTO mailboxformat(format) VALUES ('sdbox');
+
+-- Adjust maillocation table
+ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation;
+ALTER TABLE maillocation RENAME COLUMN maillocation TO directory;
+ALTER TABLE maillocation
+ ADD COLUMN fid bigint NOT NULL DEFAULT 1,
+ ADD COLUMN extra varchar(1024);
+ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat
+ FOREIGN KEY (fid) REFERENCES mailboxformat (fid);
+
+
+CREATE VIEW dovecot_user AS
+ SELECT local_part || '@' || domain_name.domainname AS userid,
+ uid, gid, domain_data.domaindir || '/' || uid AS home,
+ mailboxformat.format || ':~/' || maillocation.directory AS mail
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid)
+ LEFT JOIN mailboxformat USING (fid);
+
+CREATE VIEW postfix_maildir AS
+ SELECT local_part || '@' || domain_name.domainname AS address,
+ domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
+ AS maildir
+ FROM users
+ LEFT JOIN domain_data USING (gid)
+ LEFT JOIN domain_name USING (gid)
+ LEFT JOIN maillocation USING (mid);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql Tue Jul 20 03:15:57 2010 +0000
@@ -0,0 +1,123 @@
+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
+ 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 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;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/update_types_and_functions_0.5.x-0.6.pgsql Tue Jul 20 03:15:57 2010 +0000
@@ -0,0 +1,123 @@
+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
+ 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 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;