# HG changeset patch # User Pascal Volk # Date 1220670448 0 # Node ID af813ede1e1991913d8868a517c6a0eb9dd47325 # Parent 67a454ea5472a05722f4e289a70f9f08faf64e39 * 'create_optional_types_and_functions.pgsql' - Fixed first query in postfix_smtpd_sender_login_map() - Added types: + recipient_uid + dovecotuser + dovecotpassword - Added functions: + postfix_virtual_uid_map + dovecotuser() + dovecotpassword() diff -r 67a454ea5472 -r af813ede1e19 INSTALL --- a/INSTALL Wed Sep 03 01:44:25 2008 +0000 +++ b/INSTALL Sat Sep 06 03:07:28 2008 +0000 @@ -59,15 +59,16 @@ * /etc/dovecot/dovecot.conf # all your other settings + #disable_plaintext_auth = no mail_location = maildir:~/Maildir mail_privileged_group = mail first_valid_uid = 70000 first_valid_gid = 70000 protocol lda { - postmaster_address = postmaster@domain.tld + postmaster_address = postmaster@YOUR-DOMAIN.TLD } auth default { - mechanisms = plain login cram-md5 digest-md5 + mechanisms = plain login cram-md5 passdb sql { args = /etc/dovecot/dovecot-sql.conf } @@ -119,7 +120,7 @@ Configuring Postfix's main.cf # virtual domains - virtual_mailbox_domains = pgsql:/etc/postfix/pgsql-virtual_mailbox_domains.cf + virtual_mailbox_domains = pgsql:/etc/postfix/pgsql-virtual_mailbox_domains.cf virtual_alias_maps = pgsql:/etc/postfix/pgsql-virtual_alias_maps.cf transport_maps = pgsql:/etc/postfix/pgsql-transport.cf virtual_minimum_uid = 70000 @@ -138,6 +139,8 @@ smtpd_sasl_auth_enable = yes smtpd_sasl_local_domain = $myhostname smtpd_sasl_security_options = noplaintext, noanonymous + #smtpd_sasl_security_options = noanonymous + #broken_sasl_auth_clients = yes smtpd_recipient_restrictions = permit_mynetworks diff -r 67a454ea5472 -r af813ede1e19 create_optional_types_and_functions.pgsql --- a/create_optional_types_and_functions.pgsql Wed Sep 03 01:44:25 2008 +0000 +++ b/create_optional_types_and_functions.pgsql Sat Sep 06 03:07:28 2008 +0000 @@ -38,6 +38,7 @@ 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; @@ -223,7 +224,51 @@ WHERE gid = (SELECT gid FROM domain_name WHERE domainname = the_domain) - AND local_part = localpart) + 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; @@ -232,3 +277,100 @@ $$ 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; diff -r 67a454ea5472 -r af813ede1e19 pgsql-virtual_mailbox_domains.cf --- a/pgsql-virtual_mailbox_domains.cf Wed Sep 03 01:44:25 2008 +0000 +++ b/pgsql-virtual_mailbox_domains.cf Sat Sep 06 03:07:28 2008 +0000 @@ -9,4 +9,4 @@ dbname = mailsys # Postfix 2.2 and later The SQL query template. See pgsql_table(5). -query = SELECT gid FROM postfix_gid WHERE domainname='%s' +query = SELECT gid FROM postfix_gid WHERE domainname = '%d'