equal
deleted
inserted
replaced
86 rec address_maildir; |
86 rec address_maildir; |
87 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
87 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
88 address varchar(320) := localpart || '@' || the_domain; |
88 address varchar(320) := localpart || '@' || the_domain; |
89 BEGIN |
89 BEGIN |
90 FOR rec IN |
90 FOR rec IN |
91 SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/' |
91 SELECT address, domaindir||'/'||users.uid||'/'||directory||'/' |
92 FROM domain_data, users, maillocation |
92 FROM domain_data, users, maillocation |
93 WHERE domain_data.gid = did |
93 WHERE domain_data.gid = did |
94 AND users.gid = did |
94 AND users.gid = did |
95 AND users.local_part = localpart |
95 AND users.local_part = localpart |
96 AND maillocation.mid = users.mid |
96 AND maillocation.mid = users.mid |
106 -- ########################################################################## -- |
106 -- ########################################################################## -- |
107 |
107 |
108 -- --- |
108 -- --- |
109 -- Data type for functions: postfix_relocated_map(varchar, varchar) |
109 -- Data type for functions: postfix_relocated_map(varchar, varchar) |
110 -- postfix_virtual_alias_map(varchar, varchar) |
110 -- postfix_virtual_alias_map(varchar, varchar) |
111 -- |
|
112 -- --- |
111 -- --- |
113 CREATE TYPE recipient_destination AS ( |
112 CREATE TYPE recipient_destination AS ( |
114 recipient varchar(320), |
113 recipient varchar(320), |
115 destination text |
114 destination text |
116 ); |
115 ); |
294 -- varchar localpart |
293 -- varchar localpart |
295 -- varchar the_domain |
294 -- varchar the_domain |
296 -- Returns: dovecotuser records |
295 -- Returns: dovecotuser records |
297 -- |
296 -- |
298 -- Required access privileges for your dovecot database user: |
297 -- Required access privileges for your dovecot database user: |
299 -- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot; |
298 -- GRANT SELECT |
|
299 -- ON users, domain_data, domain_name, maillocation, mailboxformat |
|
300 -- TO dovecot; |
300 -- |
301 -- |
301 -- For more details see http://wiki.dovecot.org/UserDatabase |
302 -- For more details see http://wiki.dovecot.org/UserDatabase |
302 -- --- |
303 -- --- |
303 CREATE OR REPLACE FUNCTION dovecotuser( |
304 CREATE OR REPLACE FUNCTION dovecotuser( |
304 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
305 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
307 record dovecotuser; |
308 record dovecotuser; |
308 userid varchar(320) := localpart || '@' || the_domain; |
309 userid varchar(320) := localpart || '@' || the_domain; |
309 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
310 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
310 BEGIN |
311 BEGIN |
311 FOR record IN |
312 FOR record IN |
312 SELECT userid, uid, did, domaindir ||'/'|| uid AS home, |
313 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
313 '~/'|| maillocation AS mail |
314 format || ':~/' || directory AS mail |
314 FROM users, domain_data, maillocation |
315 FROM users, domain_data, mailboxformat, maillocation |
315 WHERE users.gid = did |
316 WHERE users.gid = did |
316 AND users.local_part = localpart |
317 AND users.local_part = localpart |
317 AND maillocation.mid = users.mid |
318 AND maillocation.mid = users.mid |
|
319 AND mailboxformat.fid = maillocation.fid |
318 AND domain_data.gid = did |
320 AND domain_data.gid = did |
319 LOOP |
321 LOOP |
320 RETURN NEXT record; |
322 RETURN NEXT record; |
321 END LOOP; |
323 END LOOP; |
322 RETURN; |
324 RETURN; |
330 -- --- |
332 -- --- |
331 -- Data type for function dovecotpassword(varchar, varchar) |
333 -- Data type for function dovecotpassword(varchar, varchar) |
332 -- --- |
334 -- --- |
333 CREATE TYPE dovecotpassword AS ( |
335 CREATE TYPE dovecotpassword AS ( |
334 userid varchar(320), |
336 userid varchar(320), |
335 password varchar(74), |
337 password varchar(270), |
336 smtp boolean, |
338 smtp boolean, |
337 pop3 boolean, |
339 pop3 boolean, |
338 imap boolean, |
340 imap boolean, |
339 managesieve boolean |
341 managesieve boolean |
340 ); |
342 ); |