1
2---
3--- set of triggers and functions to automatically subscribe
4--- all normal users to newly created public mailboxes
5---
6
7
8---
9--- list all user/mailboxes combinations for mailboxes
10--- owner by __public__
11---
12drop view if exists public_mailbox_users;
13create view public_mailbox_users (user_id, mailbox_id) as
14select u.user_idnr,b.mailbox_idnr from dbmail_mailboxes b
15        right outer join dbmail_users u on b.owner_idnr!=u.user_idnr
16where
17        userid not in ('__@!internal_delivery_user!@__','anyone','__public__') and
18        owner_idnr=(select user_idnr from dbmail_users where userid='__public__');
19
20
21---
22--- insert a subscription
23--- emulates replace into
24---
25drop function if exists resubscribe(integer, integer);
26create function resubscribe(integer, integer) returns void as $$
27begin
28	raise info 'resubscribe mailbox user: %, mailbox:% ...', $1, $2;
29	if not exists(select * from dbmail_subscription where user_id=$1 and mailbox_id=$2) then
30		raise info 'insert into dbmail_subscription (user_id, mailbox_id) values (%,%)', $1, $2;
31		insert into dbmail_subscription (user_id, mailbox_id) values ($1, $2);
32	end if;
33	return ;
34end;
35$$ language plpgsql;
36
37
38---
39--- insert acl
40--- emulates replace into
41---
42drop function if exists autogrant(integer, integer);
43create function autogrant(integer, integer) returns void as $$
44begin
45	if not exists(select * from dbmail_acl where user_id=$2 and mailbox_id=$1) then
46		raise info 'grant all on mailbox: % to %', $1,$2;
47		insert into dbmail_acl (
48			user_id,mailbox_id,lookup_flag,read_flag,seen_flag,
49			write_flag,insert_flag,post_flag,create_flag,
50			delete_flag,deleted_flag,expunge_flag,administer_flag) values ($2,$1, 1,1,1,1,1,1,1,1,1,1,1);
51	end if;
52	return;
53end;
54$$ language plpgsql;
55
56
57---
58--- trigger function for subscribing all users to a public
59--- mailboxes and granting the anyone user access to them
60---
61drop function if exists auto_subscriber() cascade;
62create function auto_subscriber() returns trigger as $$
63declare
64	prow RECORD;
65	anyoneid INTEGER;
66begin
67	anyoneid := user_idnr from dbmail_users where userid='anyone';
68	for prow in select * from public_mailbox_users loop
69		raise info 'resubscribe mailbox %,% ...', prow.user_id, prow.mailbox_id;
70		execute 'select resubscribe(' || prow.user_id || ',' || prow.mailbox_id || ')';
71		raise info 'grant public access on mailbox % ...', prow.mailbox_id;
72		execute 'select autogrant(' || prow.mailbox_id || ',' || anyoneid ||')';
73	end loop;
74	return NEW;
75end;
76$$ language plpgsql;
77
78---
79--- hook up the trigger
80---
81create trigger auto_subscribe_trigger after insert
82	on dbmail_mailboxes
83	for each row execute procedure auto_subscriber();
84
85
86