equal
deleted
inserted
replaced
|
1 -- --- |
|
2 -- Clean out the old stuff |
|
3 -- --- |
|
4 DROP TYPE dovecotpassword CASCADE; |
|
5 |
|
6 -- --- |
|
7 -- Data type for function dovecotpassword(varchar, varchar) |
|
8 -- --- |
|
9 CREATE TYPE dovecotpassword AS ( |
|
10 userid varchar(320), |
|
11 password varchar(74), |
|
12 smtp boolean, |
|
13 pop3 boolean, |
|
14 imap boolean, |
|
15 sieve boolean |
|
16 ); |
|
17 |
|
18 -- --- |
|
19 -- Parameters (from login name [localpart@the_domain]): |
|
20 -- varchar localpart |
|
21 -- varchar the_domain |
|
22 -- Returns: dovecotpassword records |
|
23 -- |
|
24 -- Required access privileges for your dovecot database user: |
|
25 -- GRANT SELECT ON users, domain_name TO dovecot; |
|
26 -- |
|
27 -- For more details see http://wiki.dovecot.org/AuthDatabase/SQL |
|
28 -- --- |
|
29 CREATE OR REPLACE FUNCTION dovecotpassword( |
|
30 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
|
31 AS $$ |
|
32 DECLARE |
|
33 record dovecotpassword; |
|
34 userid varchar(320) := localpart || '@' || the_domain; |
|
35 BEGIN |
|
36 FOR record IN |
|
37 SELECT userid, passwd, smtp, pop3, imap, sieve |
|
38 FROM users |
|
39 WHERE gid = (SELECT gid |
|
40 FROM domain_name |
|
41 WHERE domainname = the_domain) |
|
42 AND local_part = localpart |
|
43 LOOP |
|
44 RETURN NEXT record; |
|
45 END LOOP; |
|
46 RETURN; |
|
47 END; |
|
48 $$ LANGUAGE plpgsql STABLE |
|
49 RETURNS NULL ON NULL INPUT |
|
50 EXTERNAL SECURITY INVOKER; |
|
51 |