# HG changeset patch # User Pascal Volk # Date 1279595757 0 # Node ID e21ceaabe871cb1324fed63cb7f55be60858e51d # Parent 62211b6a9b8e90153f50459680109ee09ae8854b pgsql: Added support for different mailbox formats. - users.passwd can store sha512-crypt.hex hashes now - Added new update scripts. diff -r 62211b6a9b8e -r e21ceaabe871 TODO --- 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`) diff -r 62211b6a9b8e -r e21ceaabe871 pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql --- 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, diff -r 62211b6a9b8e -r e21ceaabe871 pgsql/create_optional_types_and_functions.pgsql --- 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, diff -r 62211b6a9b8e -r e21ceaabe871 pgsql/create_tables-dovecot-1.2.x.pgsql --- 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) diff -r 62211b6a9b8e -r e21ceaabe871 pgsql/create_tables.pgsql --- 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) diff -r 62211b6a9b8e -r e21ceaabe871 pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql --- /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); diff -r 62211b6a9b8e -r e21ceaabe871 pgsql/update_tables_0.5.x-0.6.pgsql --- /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); diff -r 62211b6a9b8e -r e21ceaabe871 pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql --- /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; diff -r 62211b6a9b8e -r e21ceaabe871 pgsql/update_types_and_functions_0.5.x-0.6.pgsql --- /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;