author | Pascal Volk <neverseen@users.sourceforge.net> |
Wed, 13 Jan 2010 00:08:44 +0000 | |
branch | v0.6.x |
changeset 166 | b152ad5c7071 |
parent 160 | 639cf4003965 |
permissions | -rw-r--r-- |
123
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
1 |
-- --- |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
2 |
-- Clean out the old stuff |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
3 |
-- --- |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
4 |
DROP TYPE dovecotpassword CASCADE; |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
5 |
|
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
6 |
-- --- |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
7 |
-- Data type for function dovecotpassword(varchar, varchar) |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
8 |
-- --- |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
9 |
CREATE TYPE dovecotpassword AS ( |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
10 |
userid varchar(320), |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
11 |
password varchar(74), |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
12 |
smtp boolean, |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
13 |
pop3 boolean, |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
14 |
imap boolean, |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
15 |
sieve boolean |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
16 |
); |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
17 |
|
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
18 |
-- --- |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
19 |
-- Parameters (from login name [localpart@the_domain]): |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
20 |
-- varchar localpart |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
21 |
-- varchar the_domain |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
22 |
-- Returns: dovecotpassword records |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
23 |
-- |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
24 |
-- Required access privileges for your dovecot database user: |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
25 |
-- GRANT SELECT ON users, domain_name TO dovecot; |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
26 |
-- |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
27 |
-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
28 |
-- --- |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
29 |
CREATE OR REPLACE FUNCTION dovecotpassword( |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
30 |
IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
31 |
AS $$ |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
32 |
DECLARE |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
33 |
record dovecotpassword; |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
34 |
userid varchar(320) := localpart || '@' || the_domain; |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
35 |
BEGIN |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
36 |
FOR record IN |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
37 |
SELECT userid, passwd, smtp, pop3, imap, sieve |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
38 |
FROM users |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
39 |
WHERE gid = (SELECT gid |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
40 |
FROM domain_name |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
41 |
WHERE domainname = the_domain) |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
42 |
AND local_part = localpart |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
43 |
LOOP |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
44 |
RETURN NEXT record; |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
45 |
END LOOP; |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
46 |
RETURN; |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
47 |
END; |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
48 |
$$ LANGUAGE plpgsql STABLE |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
49 |
RETURNS NULL ON NULL INPUT |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
50 |
EXTERNAL SECURITY INVOKER; |
626c008a4a04
Added update SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
51 |