1 | /** |
---|
2 | * PostgreSQL Functions for CalDAV handling |
---|
3 | * |
---|
4 | * @package rscds |
---|
5 | * @subpackage database |
---|
6 | * @author Andrew McMillan <andrew@catalyst.net.nz> |
---|
7 | * @copyright Catalyst IT Ltd |
---|
8 | * @license http://gnu.org/copyleft/gpl.html GNU GPL v2 |
---|
9 | */ |
---|
10 | |
---|
11 | CREATE or REPLACE FUNCTION apply_month_byday( TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS TIMESTAMP WITH TIME ZONE AS $$ |
---|
12 | DECLARE |
---|
13 | in_time ALIAS FOR $1; |
---|
14 | byday ALIAS FOR $2; |
---|
15 | weeks INT; |
---|
16 | dow INT; |
---|
17 | temp_txt TEXT; |
---|
18 | dd INT; |
---|
19 | mm INT; |
---|
20 | yy INT; |
---|
21 | our_dow INT; |
---|
22 | our_answer TIMESTAMP WITH TIME ZONE; |
---|
23 | BEGIN |
---|
24 | dow := position(substring( byday from '..$') in 'SUMOTUWETHFRSA') / 2; |
---|
25 | temp_txt := substring(byday from '([0-9]+)'); |
---|
26 | weeks := temp_txt::int; |
---|
27 | |
---|
28 | -- RAISE NOTICE 'DOW: %, Weeks: %(%s)', dow, weeks, temp_txt; |
---|
29 | |
---|
30 | IF substring(byday for 1) = '-' THEN |
---|
31 | -- Last XX of month, or possibly second-to-last, but unlikely |
---|
32 | mm := extract( 'month' from in_time); |
---|
33 | yy := extract( 'year' from in_time); |
---|
34 | |
---|
35 | -- Start with the last day of the month |
---|
36 | our_answer := (yy::text || '-' || (mm+1)::text || '-01')::timestamp - '1 day'::interval; |
---|
37 | dd := extract( 'dow' from our_answer); |
---|
38 | dd := dd - dow; |
---|
39 | IF dd < 0 THEN |
---|
40 | dd := dd + 7; |
---|
41 | END IF; |
---|
42 | |
---|
43 | -- Having calculated the right day of the month, we now apply that back to in_time |
---|
44 | -- which contains the otherwise-unobtainable timezone detail (and the time) |
---|
45 | our_answer = our_answer - (dd::text || 'days')::interval; |
---|
46 | dd := extract( 'day' from our_answer) - extract( 'day' from in_time); |
---|
47 | our_answer := in_time + (dd::text || 'days')::interval; |
---|
48 | |
---|
49 | IF weeks > 1 THEN |
---|
50 | weeks := weeks - 1; |
---|
51 | our_answer := our_answer - (weeks::text || 'weeks')::interval; |
---|
52 | END IF; |
---|
53 | |
---|
54 | ELSE |
---|
55 | |
---|
56 | -- Shift our date to the correct day of week.. |
---|
57 | our_dow := extract( 'dow' from in_time); |
---|
58 | our_dow := our_dow - dow; |
---|
59 | dd := extract( 'day' from in_time); |
---|
60 | IF our_dow >= dd THEN |
---|
61 | our_dow := our_dow - 7; |
---|
62 | END IF; |
---|
63 | our_answer := in_time - (our_dow::text || 'days')::interval; |
---|
64 | dd = extract( 'day' from our_answer); |
---|
65 | |
---|
66 | -- Shift the date to the correct week... |
---|
67 | dd := weeks - ((dd+6) / 7); |
---|
68 | IF dd != 0 THEN |
---|
69 | our_answer := our_answer + ((dd::text || 'weeks')::interval); |
---|
70 | END IF; |
---|
71 | |
---|
72 | END IF; |
---|
73 | |
---|
74 | RETURN our_answer; |
---|
75 | |
---|
76 | END; |
---|
77 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
78 | |
---|
79 | |
---|
80 | CREATE or REPLACE FUNCTION calculate_later_timestamp( TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS TIMESTAMP WITH TIME ZONE AS $$ |
---|
81 | DECLARE |
---|
82 | earliest ALIAS FOR $1; |
---|
83 | basedate ALIAS FOR $2; |
---|
84 | repeatrule ALIAS FOR $3; |
---|
85 | frequency TEXT; |
---|
86 | temp_txt TEXT; |
---|
87 | length INT; |
---|
88 | count INT; |
---|
89 | byday TEXT; |
---|
90 | bymonthday INT; |
---|
91 | basediff INTERVAL; |
---|
92 | past_repeats INT8; |
---|
93 | units TEXT; |
---|
94 | dow TEXT; |
---|
95 | our_answer TIMESTAMP WITH TIME ZONE; |
---|
96 | loopcount INT; |
---|
97 | BEGIN |
---|
98 | IF basedate > earliest THEN |
---|
99 | RETURN basedate; |
---|
100 | END IF; |
---|
101 | |
---|
102 | temp_txt := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)'); |
---|
103 | IF temp_txt IS NOT NULL AND temp_txt::timestamp with time zone < earliest THEN |
---|
104 | RETURN NULL; |
---|
105 | END IF; |
---|
106 | |
---|
107 | frequency := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)'); |
---|
108 | IF frequency IS NULL THEN |
---|
109 | RETURN NULL; |
---|
110 | END IF; |
---|
111 | |
---|
112 | past_repeats = 0; |
---|
113 | length = 1; |
---|
114 | temp_txt := substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)'); |
---|
115 | IF temp_txt IS NOT NULL THEN |
---|
116 | length := temp_txt::int; |
---|
117 | basediff := earliest - basedate; |
---|
118 | |
---|
119 | -- RAISE NOTICE 'Frequency: %, Length: %(%), Basediff: %', frequency, length, temp_txt, basediff; |
---|
120 | |
---|
121 | -- Calculate the number of past periods between our base date and our earliest date |
---|
122 | IF frequency = 'WEEKLY' OR frequency = 'DAILY' THEN |
---|
123 | past_repeats := extract('epoch' from basediff)::INT8 / 86400; |
---|
124 | -- RAISE NOTICE 'Days: %', past_repeats; |
---|
125 | IF frequency = 'WEEKLY' THEN |
---|
126 | past_repeats := past_repeats / 7; |
---|
127 | END IF; |
---|
128 | ELSE |
---|
129 | past_repeats = extract( 'years' from basediff ); |
---|
130 | IF frequency = 'MONTHLY' THEN |
---|
131 | past_repeats = (past_repeats *12) + extract( 'months' from basediff ); |
---|
132 | END IF; |
---|
133 | END IF; |
---|
134 | IF length IS NOT NULL THEN |
---|
135 | past_repeats = (past_repeats / length) + 1; |
---|
136 | END IF; |
---|
137 | END IF; |
---|
138 | |
---|
139 | -- Check that we have not exceeded the COUNT= limit |
---|
140 | temp_txt := substring(repeatrule from 'COUNT=([0-9]+)(;|$)'); |
---|
141 | IF temp_txt IS NOT NULL THEN |
---|
142 | count := temp_txt::int; |
---|
143 | -- RAISE NOTICE 'Periods: %, Count: %(%), length: %', past_repeats, count, temp_txt, length; |
---|
144 | IF ( count <= past_repeats ) THEN |
---|
145 | RETURN NULL; |
---|
146 | END IF; |
---|
147 | ELSE |
---|
148 | count := NULL; |
---|
149 | END IF; |
---|
150 | |
---|
151 | temp_txt := substring(repeatrule from 'BYSETPOS=([0-9-]+)(;|$)'); |
---|
152 | byday := substring(repeatrule from 'BYDAY=([0-9A-Z,]+-)(;|$)'); |
---|
153 | IF byday IS NOT NULL AND frequency = 'MONTHLY' THEN |
---|
154 | -- Since this could move the date around a month we go back one |
---|
155 | -- period just to be extra sure. |
---|
156 | past_repeats = past_repeats - 1; |
---|
157 | |
---|
158 | IF temp_txt IS NOT NULL THEN |
---|
159 | -- Crudely hack the BYSETPOS onto the front of BYDAY. While this |
---|
160 | -- is not as per rfc2445, RRULE syntax is so complex and overblown |
---|
161 | -- that nobody correctly uses comma-separated BYDAY or BYSETPOS, and |
---|
162 | -- certainly not within a MONTHLY RRULE. |
---|
163 | byday := temp_txt || byday; |
---|
164 | END IF; |
---|
165 | END IF; |
---|
166 | |
---|
167 | past_repeats = past_repeats * length; |
---|
168 | |
---|
169 | units := CASE |
---|
170 | WHEN frequency = 'DAILY' THEN 'days' |
---|
171 | WHEN frequency = 'WEEKLY' THEN 'weeks' |
---|
172 | WHEN frequency = 'MONTHLY' THEN 'months' |
---|
173 | WHEN frequency = 'YEARLY' THEN 'years' |
---|
174 | END; |
---|
175 | |
---|
176 | temp_txt := substring(repeatrule from 'BYMONTHDAY=([0-9,]+)(;|$)'); |
---|
177 | bymonthday := temp_txt::int; |
---|
178 | |
---|
179 | -- With all of the above calculation, this date should be close to (but less than) |
---|
180 | -- the target, and we should only loop once or twice. |
---|
181 | our_answer := basedate + (past_repeats::text || units)::interval; |
---|
182 | |
---|
183 | IF our_answer IS NULL THEN |
---|
184 | RAISE EXCEPTION 'our_answer IS NULL! basedate:% past_repeats:% units:%', basedate, past_repeats, units; |
---|
185 | END IF; |
---|
186 | |
---|
187 | |
---|
188 | loopcount := 500; -- Desirable to stop an infinite loop if there is something we cannot handle |
---|
189 | LOOP |
---|
190 | -- RAISE NOTICE 'Testing date: %', our_answer; |
---|
191 | IF frequency = 'DAILY' THEN |
---|
192 | IF byday IS NOT NULL THEN |
---|
193 | LOOP |
---|
194 | dow = substring( to_char( our_answer, 'DY' ) for 2); |
---|
195 | EXIT WHEN byday ~* dow; |
---|
196 | -- Increment for our next time through the loop... |
---|
197 | our_answer := our_answer + (length::text || units)::interval; |
---|
198 | END LOOP; |
---|
199 | END IF; |
---|
200 | ELSIF frequency = 'WEEKLY' THEN |
---|
201 | -- Weekly repeats are only on specific days |
---|
202 | -- This is really not right, since a WEEKLY on MO,WE,FR should |
---|
203 | -- occur three times each week and this will only be once a week. |
---|
204 | dow = substring( to_char( our_answer, 'DY' ) for 2); |
---|
205 | ELSIF frequency = 'MONTHLY' THEN |
---|
206 | IF byday IS NOT NULL THEN |
---|
207 | -- This works fine, except that maybe there are multiple BYDAY |
---|
208 | -- components. e.g. 1TU,3TU might be 1st & 3rd tuesdays. |
---|
209 | our_answer := apply_month_byday( our_answer, byday ); |
---|
210 | ELSE |
---|
211 | -- If we did not get a BYDAY= then we kind of have to assume it is the same day each month |
---|
212 | our_answer := our_answer + '1 month'::interval; |
---|
213 | END IF; |
---|
214 | ELSIF bymonthday IS NOT NULL AND frequency = 'MONTHLY' AND bymonthday < 1 THEN |
---|
215 | -- We do not deal with this situation at present |
---|
216 | RAISE NOTICE 'The case of negative BYMONTHDAY is not handled yet.'; |
---|
217 | END IF; |
---|
218 | |
---|
219 | EXIT WHEN our_answer >= earliest; |
---|
220 | |
---|
221 | -- Give up if we have exceeded the count |
---|
222 | IF ( count IS NOT NULL AND past_repeats > count ) THEN |
---|
223 | RETURN NULL; |
---|
224 | ELSE |
---|
225 | past_repeats := past_repeats + 1; |
---|
226 | END IF; |
---|
227 | |
---|
228 | loopcount := loopcount - 1; |
---|
229 | IF loopcount < 0 THEN |
---|
230 | RAISE NOTICE 'Giving up on repeat rule "%" - after 100 increments from % we are still not after %', repeatrule, basedate, earliest; |
---|
231 | RETURN NULL; |
---|
232 | END IF; |
---|
233 | |
---|
234 | -- Increment for our next time through the loop... |
---|
235 | our_answer := our_answer + (length::text || units)::interval; |
---|
236 | |
---|
237 | END LOOP; |
---|
238 | |
---|
239 | RETURN our_answer; |
---|
240 | |
---|
241 | END; |
---|
242 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
243 | |
---|
244 | |
---|
245 | CREATE or REPLACE FUNCTION usr_is_role( INT, TEXT ) RETURNS BOOLEAN AS $$ |
---|
246 | SELECT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=$1 AND roles.role_name=$2 ) |
---|
247 | $$ LANGUAGE 'sql' IMMUTABLE STRICT; |
---|
248 | |
---|
249 | CREATE or REPLACE FUNCTION legacy_get_permissions( INT, INT ) RETURNS TEXT AS $$ |
---|
250 | DECLARE |
---|
251 | in_from ALIAS FOR $1; |
---|
252 | in_to ALIAS FOR $2; |
---|
253 | out_confers TEXT; |
---|
254 | tmp_confers1 TEXT; |
---|
255 | tmp_confers2 TEXT; |
---|
256 | tmp_txt TEXT; |
---|
257 | dbg TEXT DEFAULT ''; |
---|
258 | r RECORD; |
---|
259 | counter INT; |
---|
260 | BEGIN |
---|
261 | -- Self can always have full access |
---|
262 | IF in_from = in_to THEN |
---|
263 | RETURN 'A'; |
---|
264 | END IF; |
---|
265 | |
---|
266 | -- dbg := 'S-'; |
---|
267 | SELECT rt1.confers INTO out_confers FROM relationship r1 JOIN relationship_type rt1 USING ( rt_id ) |
---|
268 | WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group'); |
---|
269 | IF FOUND THEN |
---|
270 | RETURN dbg || out_confers; |
---|
271 | END IF; |
---|
272 | -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to; |
---|
273 | |
---|
274 | out_confers := ''; |
---|
275 | FOR r IN SELECT rt1.confers AS r1, rt2.confers AS r2 FROM relationship r1 JOIN relationship_type rt1 USING(rt_id) |
---|
276 | JOIN relationship r2 ON r1.to_user=r2.from_user JOIN relationship_type rt2 ON r2.rt_id=rt2.rt_id |
---|
277 | WHERE r1.from_user=in_from AND r2.to_user=in_to |
---|
278 | AND EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.to_user AND roles.role_name='Group') |
---|
279 | AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r2.to_user AND roles.role_name='Group') |
---|
280 | AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.from_user AND roles.role_name='Group') |
---|
281 | LOOP |
---|
282 | -- RAISE NOTICE 'Permissions to group % from group %', r.r1, r.r2; |
---|
283 | -- FIXME: This is an oversimplification |
---|
284 | -- dbg := 'C-'; |
---|
285 | tmp_confers1 := r.r1; |
---|
286 | tmp_confers2 := r.r2; |
---|
287 | IF tmp_confers1 != tmp_confers2 THEN |
---|
288 | IF tmp_confers1 ~* 'A' THEN |
---|
289 | -- Ensure that A is expanded to all supported privs before being used as a mask |
---|
290 | tmp_confers1 := 'AFBRWU'; |
---|
291 | END IF; |
---|
292 | IF tmp_confers2 ~* 'A' THEN |
---|
293 | -- Ensure that A is expanded to all supported privs before being used as a mask |
---|
294 | tmp_confers2 := 'AFBRWU'; |
---|
295 | END IF; |
---|
296 | -- RAISE NOTICE 'Expanded permissions to group % from group %', tmp_confers1, tmp_confers2; |
---|
297 | tmp_txt = ''; |
---|
298 | FOR counter IN 1 .. length(tmp_confers2) LOOP |
---|
299 | IF tmp_confers1 ~* substring(tmp_confers2,counter,1) THEN |
---|
300 | tmp_txt := tmp_txt || substring(tmp_confers2,counter,1); |
---|
301 | END IF; |
---|
302 | END LOOP; |
---|
303 | tmp_confers2 := tmp_txt; |
---|
304 | END IF; |
---|
305 | FOR counter IN 1 .. length(tmp_confers2) LOOP |
---|
306 | IF NOT out_confers ~* substring(tmp_confers2,counter,1) THEN |
---|
307 | out_confers := out_confers || substring(tmp_confers2,counter,1); |
---|
308 | END IF; |
---|
309 | END LOOP; |
---|
310 | END LOOP; |
---|
311 | IF out_confers ~* 'A' OR (out_confers ~* 'B' AND out_confers ~* 'F' AND out_confers ~* 'R' AND out_confers ~* 'W' AND out_confers ~* 'U') THEN |
---|
312 | out_confers := 'A'; |
---|
313 | END IF; |
---|
314 | IF out_confers != '' THEN |
---|
315 | RETURN dbg || out_confers; |
---|
316 | END IF; |
---|
317 | |
---|
318 | -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to; |
---|
319 | |
---|
320 | SELECT rt1.confers INTO out_confers, tmp_confers1 FROM relationship r1 JOIN relationship_type rt1 ON ( r1.rt_id = rt1.rt_id ) |
---|
321 | LEFT OUTER JOIN relationship r2 ON ( rt1.rt_id = r2.rt_id ) |
---|
322 | WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user AND r1.to_user = r2.to_user |
---|
323 | AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) |
---|
324 | AND usr_is_role(r1.to_user,'Group'); |
---|
325 | |
---|
326 | IF FOUND THEN |
---|
327 | -- dbg := 'H-'; |
---|
328 | -- RAISE NOTICE 'Permissions to shared group % ', out_confers; |
---|
329 | RETURN dbg || out_confers; |
---|
330 | END IF; |
---|
331 | |
---|
332 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
333 | |
---|
334 | RETURN ''; |
---|
335 | END; |
---|
336 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
337 | |
---|
338 | |
---|
339 | -- Function to convert a PostgreSQL date into UTC + the format used by iCalendar |
---|
340 | CREATE or REPLACE FUNCTION to_ical_utc( TIMESTAMP WITH TIME ZONE ) RETURNS TEXT AS $$ |
---|
341 | SELECT to_char( $1 at time zone 'UTC', 'YYYYMMDD"T"HH24MISS"Z"' ) |
---|
342 | $$ LANGUAGE 'sql' IMMUTABLE STRICT; |
---|
343 | |
---|
344 | -- Function to set an arbitrary DAV property |
---|
345 | CREATE or REPLACE FUNCTION set_dav_property( TEXT, INTEGER, TEXT, TEXT ) RETURNS BOOLEAN AS $$ |
---|
346 | DECLARE |
---|
347 | path ALIAS FOR $1; |
---|
348 | user ALIAS FOR $2; |
---|
349 | key ALIAS FOR $3; |
---|
350 | value ALIAS FOR $4; |
---|
351 | tmp_int INT; |
---|
352 | BEGIN |
---|
353 | -- Check that there is either a resource, collection or user at this location. |
---|
354 | IF NOT EXISTS( SELECT 1 FROM caldav_data WHERE dav_name = path |
---|
355 | UNION SELECT 1 FROM collection WHERE dav_name = path |
---|
356 | UNION SELECT 1 FROM dav_principal WHERE dav_name = path |
---|
357 | UNION SELECT 1 FROM dav_binding WHERE dav_name = path |
---|
358 | ) THEN |
---|
359 | RETURN FALSE; |
---|
360 | END IF; |
---|
361 | SELECT changed_by INTO tmp_int FROM property WHERE dav_name = path AND property_name = key; |
---|
362 | IF FOUND THEN |
---|
363 | UPDATE property SET changed_by=user, changed_on=current_timestamp, property_value=value WHERE dav_name = path AND property_name = key; |
---|
364 | ELSE |
---|
365 | INSERT INTO property ( dav_name, changed_by, changed_on, property_name, property_value ) VALUES( path, user, current_timestamp, key, value ); |
---|
366 | END IF; |
---|
367 | RETURN TRUE; |
---|
368 | END; |
---|
369 | $$ LANGUAGE 'plpgsql' STRICT; |
---|
370 | |
---|
371 | -- List a user's relationships as a text string |
---|
372 | CREATE or REPLACE FUNCTION relationship_list( INT8 ) RETURNS TEXT AS $$ |
---|
373 | DECLARE |
---|
374 | user ALIAS FOR $1; |
---|
375 | r RECORD; |
---|
376 | rlist TEXT; |
---|
377 | BEGIN |
---|
378 | rlist := ''; |
---|
379 | FOR r IN SELECT rt_name, fullname FROM relationship |
---|
380 | LEFT JOIN relationship_type USING(rt_id) LEFT JOIN usr tgt ON to_user = tgt.user_no |
---|
381 | WHERE from_user = user |
---|
382 | LOOP |
---|
383 | rlist := rlist |
---|
384 | || CASE WHEN rlist = '' THEN '' ELSE ', ' END |
---|
385 | || r.rt_name || '(' || r.fullname || ')'; |
---|
386 | END LOOP; |
---|
387 | RETURN rlist; |
---|
388 | END; |
---|
389 | $$ LANGUAGE 'plpgsql'; |
---|
390 | |
---|
391 | DROP FUNCTION rename_davical_user( TEXT, TEXT ); |
---|
392 | DROP TRIGGER usr_modified ON usr CASCADE; |
---|
393 | CREATE or REPLACE FUNCTION usr_modified() RETURNS TRIGGER AS $$ |
---|
394 | DECLARE |
---|
395 | oldpath TEXT; |
---|
396 | newpath TEXT; |
---|
397 | BEGIN |
---|
398 | -- in case we trigger on other events in future |
---|
399 | IF TG_OP = 'UPDATE' THEN |
---|
400 | IF NEW.username != OLD.username THEN |
---|
401 | oldpath := '/' || OLD.username || '/'; |
---|
402 | newpath := '/' || NEW.username || '/'; |
---|
403 | UPDATE collection |
---|
404 | SET parent_container = replace( parent_container, oldpath, newpath), |
---|
405 | dav_name = replace( dav_name, oldpath, newpath) |
---|
406 | WHERE substring(dav_name from 1 for char_length(oldpath)) = oldpath; |
---|
407 | END IF; |
---|
408 | END IF; |
---|
409 | RETURN NEW; |
---|
410 | END; |
---|
411 | $$ LANGUAGE plpgsql; |
---|
412 | CREATE TRIGGER usr_modified AFTER UPDATE ON usr |
---|
413 | FOR EACH ROW EXECUTE PROCEDURE usr_modified(); |
---|
414 | |
---|
415 | |
---|
416 | DROP TRIGGER collection_modified ON collection CASCADE; |
---|
417 | CREATE or REPLACE FUNCTION collection_modified() RETURNS TRIGGER AS $$ |
---|
418 | DECLARE |
---|
419 | BEGIN |
---|
420 | -- in case we trigger on other events in future |
---|
421 | IF TG_OP = 'UPDATE' THEN |
---|
422 | IF NEW.dav_name != OLD.dav_name THEN |
---|
423 | UPDATE caldav_data |
---|
424 | SET dav_name = replace( dav_name, OLD.dav_name, NEW.dav_name), |
---|
425 | user_no = NEW.user_no |
---|
426 | WHERE substring(dav_name from 1 for char_length(OLD.dav_name)) = OLD.dav_name; |
---|
427 | END IF; |
---|
428 | END IF; |
---|
429 | RETURN NEW; |
---|
430 | END; |
---|
431 | $$ LANGUAGE plpgsql; |
---|
432 | CREATE TRIGGER collection_modified AFTER UPDATE ON collection |
---|
433 | FOR EACH ROW EXECUTE PROCEDURE collection_modified(); |
---|
434 | |
---|
435 | |
---|
436 | DROP TRIGGER caldav_data_modified ON caldav_data CASCADE; |
---|
437 | CREATE or REPLACE FUNCTION caldav_data_modified() RETURNS TRIGGER AS $$ |
---|
438 | DECLARE |
---|
439 | coll_id caldav_data.collection_id%TYPE; |
---|
440 | BEGIN |
---|
441 | IF TG_OP = 'UPDATE' THEN |
---|
442 | IF NEW.caldav_data = OLD.caldav_data AND NEW.collection_id = OLD.collection_id THEN |
---|
443 | -- Nothing for us to do |
---|
444 | RETURN NEW; |
---|
445 | END IF; |
---|
446 | END IF; |
---|
447 | |
---|
448 | IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN |
---|
449 | -- On insert or update modified, we set the NEW collection tag to the md5 of the |
---|
450 | -- etag of the updated row which gives us something predictable for our regression |
---|
451 | -- tests, but something different from the actual etag of the new event. |
---|
452 | UPDATE collection |
---|
453 | SET modified = current_timestamp, dav_etag = md5(NEW.dav_etag) |
---|
454 | WHERE collection_id = NEW.collection_id; |
---|
455 | IF TG_OP = 'INSERT' THEN |
---|
456 | RETURN NEW; |
---|
457 | END IF; |
---|
458 | END IF; |
---|
459 | |
---|
460 | IF TG_OP = 'DELETE' THEN |
---|
461 | -- On delete we set the OLD collection tag to the md5 of the old path & the old |
---|
462 | -- etag, which again gives us something predictable for our regression tests. |
---|
463 | UPDATE collection |
---|
464 | SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag) |
---|
465 | WHERE collection_id = OLD.collection_id; |
---|
466 | RETURN OLD; |
---|
467 | END IF; |
---|
468 | |
---|
469 | IF NEW.collection_id != OLD.collection_id THEN |
---|
470 | -- If we've switched the collection_id of this event, then we also need to update |
---|
471 | -- the etag of the old collection - as we do for delete. |
---|
472 | UPDATE collection |
---|
473 | SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag) |
---|
474 | WHERE collection_id = OLD.collection_id; |
---|
475 | END IF; |
---|
476 | RETURN NEW; |
---|
477 | END; |
---|
478 | $$ LANGUAGE plpgsql; |
---|
479 | CREATE TRIGGER caldav_data_modified AFTER INSERT OR UPDATE OR DELETE ON caldav_data |
---|
480 | FOR EACH ROW EXECUTE PROCEDURE caldav_data_modified(); |
---|
481 | |
---|
482 | |
---|
483 | DROP TRIGGER caldav_data_sync_dav_id ON caldav_data CASCADE; |
---|
484 | DROP TRIGGER calendar_item_sync_dav_id ON calendar_item CASCADE; |
---|
485 | CREATE or REPLACE FUNCTION sync_dav_id ( ) RETURNS TRIGGER AS $$ |
---|
486 | DECLARE |
---|
487 | BEGIN |
---|
488 | |
---|
489 | IF TG_OP = 'DELETE' THEN |
---|
490 | -- Just let the ON DELETE CASCADE handle this case |
---|
491 | RETURN OLD; |
---|
492 | END IF; |
---|
493 | |
---|
494 | IF NEW.dav_id IS NULL THEN |
---|
495 | NEW.dav_id = nextval('dav_id_seq'); |
---|
496 | END IF; |
---|
497 | |
---|
498 | IF TG_OP = 'UPDATE' THEN |
---|
499 | IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id |
---|
500 | OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN |
---|
501 | UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no, |
---|
502 | collection_id = NEW.collection_id, dav_name = NEW.dav_name |
---|
503 | WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id; |
---|
504 | END IF; |
---|
505 | RETURN NEW; |
---|
506 | END IF; |
---|
507 | |
---|
508 | UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no, |
---|
509 | collection_id = NEW.collection_id, dav_name = NEW.dav_name |
---|
510 | WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id; |
---|
511 | |
---|
512 | RETURN NEW; |
---|
513 | |
---|
514 | END |
---|
515 | $$ LANGUAGE 'plpgsql'; |
---|
516 | CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data |
---|
517 | FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); |
---|
518 | |
---|
519 | |
---|
520 | |
---|
521 | -- New in 1.2.6 |
---|
522 | |
---|
523 | CREATE or REPLACE FUNCTION legacy_privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ |
---|
524 | DECLARE |
---|
525 | in_priv ALIAS FOR $1; |
---|
526 | out_bits BIT(24); |
---|
527 | BEGIN |
---|
528 | out_bits := 0::BIT(24); |
---|
529 | IF in_priv ~* 'A' THEN |
---|
530 | out_bits = ~ out_bits; |
---|
531 | RETURN out_bits; |
---|
532 | END IF; |
---|
533 | |
---|
534 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
535 | -- 1 DAV:read |
---|
536 | -- 512 CalDAV:read-free-busy |
---|
537 | -- 4096 CALDAV:schedule-query-freebusy |
---|
538 | IF in_priv ~* 'R' THEN |
---|
539 | out_bits := out_bits | 4609::BIT(24); |
---|
540 | END IF; |
---|
541 | |
---|
542 | -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
543 | -- 2 DAV:write-properties |
---|
544 | -- 4 DAV:write-content |
---|
545 | -- 64 DAV:bind |
---|
546 | -- 128 DAV:unbind |
---|
547 | IF in_priv ~* 'W' THEN |
---|
548 | out_bits := out_bits | 198::BIT(24); |
---|
549 | END IF; |
---|
550 | |
---|
551 | -- 64 DAV:bind |
---|
552 | IF in_priv ~* 'B' THEN |
---|
553 | out_bits := out_bits | 64::BIT(24); |
---|
554 | END IF; |
---|
555 | |
---|
556 | -- 128 DAV:unbind |
---|
557 | IF in_priv ~* 'U' THEN |
---|
558 | out_bits := out_bits | 128::BIT(24); |
---|
559 | END IF; |
---|
560 | |
---|
561 | -- 512 CalDAV:read-free-busy |
---|
562 | -- 4096 CALDAV:schedule-query-freebusy |
---|
563 | IF in_priv ~* 'F' THEN |
---|
564 | out_bits := out_bits | 4608::BIT(24); |
---|
565 | END IF; |
---|
566 | |
---|
567 | RETURN out_bits; |
---|
568 | END |
---|
569 | $$ |
---|
570 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
571 | |
---|
572 | -- This legacy conversion function will eventually be removed, once all logic |
---|
573 | -- has been converted to use bitmaps, or to use the bits_to_priv() output. |
---|
574 | -- |
---|
575 | -- NOTE: Round-trip through this and then back through legacy_privilege_to_bits |
---|
576 | -- function is lossy! Through legacy_privilege_to_bits() and back through |
---|
577 | -- this one is not. |
---|
578 | -- |
---|
579 | CREATE or REPLACE FUNCTION bits_to_legacy_privilege( BIT(24) ) RETURNS TEXT AS $$ |
---|
580 | DECLARE |
---|
581 | in_bits ALIAS FOR $1; |
---|
582 | out_priv TEXT; |
---|
583 | BEGIN |
---|
584 | out_priv := ''; |
---|
585 | IF in_bits = (~ 0::BIT(24)) THEN |
---|
586 | out_priv = 'A'; |
---|
587 | RETURN out_priv; |
---|
588 | END IF; |
---|
589 | |
---|
590 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
591 | -- 1 DAV:read |
---|
592 | -- 512 CalDAV:read-free-busy |
---|
593 | -- 4096 CALDAV:schedule-query-freebusy |
---|
594 | IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN |
---|
595 | IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN |
---|
596 | out_priv := 'R'; |
---|
597 | ELSE |
---|
598 | out_priv := 'F'; |
---|
599 | END IF; |
---|
600 | END IF; |
---|
601 | |
---|
602 | -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
603 | -- 2 DAV:write-properties |
---|
604 | -- 4 DAV:write-content |
---|
605 | -- 64 DAV:bind |
---|
606 | -- 128 DAV:unbind |
---|
607 | IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN |
---|
608 | IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN |
---|
609 | out_priv := out_priv || 'W'; |
---|
610 | ELSE |
---|
611 | IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN |
---|
612 | out_priv := out_priv || 'B'; |
---|
613 | END IF; |
---|
614 | IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN |
---|
615 | out_priv := out_priv || 'U'; |
---|
616 | END IF; |
---|
617 | END IF; |
---|
618 | END IF; |
---|
619 | |
---|
620 | RETURN out_priv; |
---|
621 | END |
---|
622 | $$ |
---|
623 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
624 | |
---|
625 | CREATE or REPLACE FUNCTION get_permissions( INT, INT ) RETURNS TEXT AS $$ |
---|
626 | DECLARE |
---|
627 | in_from ALIAS FOR $1; |
---|
628 | in_to ALIAS FOR $2; |
---|
629 | out_confers TEXT; |
---|
630 | bit_confers BIT(24); |
---|
631 | group_role_no INT; |
---|
632 | tmp_txt TEXT; |
---|
633 | dbg TEXT DEFAULT ''; |
---|
634 | r RECORD; |
---|
635 | counter INT; |
---|
636 | BEGIN |
---|
637 | -- Self can always have full access |
---|
638 | IF in_from = in_to THEN |
---|
639 | RETURN 'A'; |
---|
640 | END IF; |
---|
641 | |
---|
642 | -- dbg := 'S-'; |
---|
643 | SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 |
---|
644 | WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group'); |
---|
645 | IF FOUND THEN |
---|
646 | RETURN dbg || out_confers; |
---|
647 | END IF; |
---|
648 | -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to; |
---|
649 | |
---|
650 | SELECT bit_or(r1.confers & r2.confers) INTO bit_confers |
---|
651 | FROM relationship r1 |
---|
652 | JOIN relationship r2 ON r1.to_user=r2.from_user |
---|
653 | WHERE r1.from_user=in_from AND r2.to_user=in_to |
---|
654 | AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group'); |
---|
655 | IF bit_confers != 0::BIT(24) THEN |
---|
656 | RETURN dbg || bits_to_legacy_privilege(bit_confers); |
---|
657 | END IF; |
---|
658 | |
---|
659 | RETURN ''; |
---|
660 | -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to; |
---|
661 | |
---|
662 | SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 LEFT OUTER JOIN relationship r2 ON(r1.to_user = r2.to_user) |
---|
663 | WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user |
---|
664 | AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ; |
---|
665 | |
---|
666 | IF FOUND THEN |
---|
667 | -- dbg := 'H-'; |
---|
668 | -- RAISE NOTICE 'Permissions to shared group % ', out_confers; |
---|
669 | RETURN dbg || out_confers; |
---|
670 | END IF; |
---|
671 | |
---|
672 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
673 | |
---|
674 | RETURN ''; |
---|
675 | END; |
---|
676 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
677 | |
---|
678 | |
---|
679 | CREATE or REPLACE FUNCTION get_group_role_no() RETURNS INT AS $$ |
---|
680 | SELECT role_no FROM roles WHERE role_name = 'Group' |
---|
681 | $$ LANGUAGE 'SQL' IMMUTABLE; |
---|
682 | |
---|
683 | CREATE or REPLACE FUNCTION has_legacy_privilege( INT, TEXT, INT ) RETURNS BOOLEAN AS $$ |
---|
684 | DECLARE |
---|
685 | in_from ALIAS FOR $1; |
---|
686 | in_legacy_privilege ALIAS FOR $2; |
---|
687 | in_to ALIAS FOR $3; |
---|
688 | in_confers BIT(24); |
---|
689 | group_role_no INT; |
---|
690 | BEGIN |
---|
691 | -- Self can always have full access |
---|
692 | IF in_from = in_to THEN |
---|
693 | RETURN TRUE; |
---|
694 | END IF; |
---|
695 | |
---|
696 | SELECT get_group_role_no() INTO group_role_no; |
---|
697 | SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers; |
---|
698 | |
---|
699 | IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to |
---|
700 | AND (in_confers & confers) = in_confers |
---|
701 | AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN |
---|
702 | -- A direct relationship from A to B that grants sufficient |
---|
703 | -- RAISE NOTICE 'Permissions directly granted'; |
---|
704 | RETURN TRUE; |
---|
705 | END IF; |
---|
706 | |
---|
707 | IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user |
---|
708 | WHERE (in_confers & r1.confers & r2.confers) = in_confers |
---|
709 | AND r1.from_user=in_from AND r2.to_user=in_to |
---|
710 | AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN |
---|
711 | -- An indirect relationship from A to B via group G that grants sufficient |
---|
712 | -- RAISE NOTICE 'Permissions mediated via group'; |
---|
713 | RETURN TRUE; |
---|
714 | END IF; |
---|
715 | |
---|
716 | IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user |
---|
717 | WHERE (in_confers & r1.confers & r2.confers) = in_confers |
---|
718 | AND r1.from_user=in_from AND r2.from_user=in_to |
---|
719 | AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) |
---|
720 | AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN |
---|
721 | -- An indirect reflexive relationship from both A & B to group G which grants sufficient |
---|
722 | -- RAISE NOTICE 'Permissions to shared group'; |
---|
723 | RETURN TRUE; |
---|
724 | END IF; |
---|
725 | |
---|
726 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
727 | |
---|
728 | RETURN FALSE; |
---|
729 | END; |
---|
730 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
731 | |
---|
732 | |
---|
733 | -- Given a verbose DAV: or CalDAV: privilege name return the bitmask |
---|
734 | CREATE or REPLACE FUNCTION privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ |
---|
735 | DECLARE |
---|
736 | raw_priv ALIAS FOR $1; |
---|
737 | in_priv TEXT; |
---|
738 | BEGIN |
---|
739 | in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', ''))); |
---|
740 | IF in_priv = 'all' THEN |
---|
741 | RETURN ~ 0::BIT(24); |
---|
742 | END IF; |
---|
743 | |
---|
744 | RETURN (CASE |
---|
745 | WHEN in_priv = 'read' THEN 4609 -- 1 + 512 + 4096 |
---|
746 | WHEN in_priv = 'write' THEN 198 -- 2 + 4 + 64 + 128 |
---|
747 | WHEN in_priv = 'write-properties' THEN 2 |
---|
748 | WHEN in_priv = 'write-content' THEN 4 |
---|
749 | WHEN in_priv = 'unlock' THEN 8 |
---|
750 | WHEN in_priv = 'read-acl' THEN 16 |
---|
751 | WHEN in_priv = 'read-current-user-privilege-set' THEN 32 |
---|
752 | WHEN in_priv = 'bind' THEN 64 |
---|
753 | WHEN in_priv = 'unbind' THEN 128 |
---|
754 | WHEN in_priv = 'write-acl' THEN 256 |
---|
755 | WHEN in_priv = 'read-free-busy' THEN 4608 -- 512 + 4096 |
---|
756 | WHEN in_priv = 'schedule-deliver' THEN 7168 -- 1024 + 2048 + 4096 |
---|
757 | WHEN in_priv = 'schedule-deliver-invite' THEN 1024 |
---|
758 | WHEN in_priv = 'schedule-deliver-reply' THEN 2048 |
---|
759 | WHEN in_priv = 'schedule-query-freebusy' THEN 4096 |
---|
760 | WHEN in_priv = 'schedule-send' THEN 57344 -- 8192 + 16384 + 32768 |
---|
761 | WHEN in_priv = 'schedule-send-invite' THEN 8192 |
---|
762 | WHEN in_priv = 'schedule-send-reply' THEN 16384 |
---|
763 | WHEN in_priv = 'schedule-send-freebusy' THEN 32768 |
---|
764 | ELSE 0 END)::BIT(24); |
---|
765 | END |
---|
766 | $$ |
---|
767 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
768 | |
---|
769 | |
---|
770 | -- Given an array of verbose DAV: or CalDAV: privilege names return the bitmask |
---|
771 | CREATE or REPLACE FUNCTION privilege_to_bits( TEXT[] ) RETURNS BIT(24) AS $$ |
---|
772 | DECLARE |
---|
773 | raw_privs ALIAS FOR $1; |
---|
774 | in_priv TEXT; |
---|
775 | out_bits BIT(24); |
---|
776 | i INT; |
---|
777 | allprivs BIT(24); |
---|
778 | start INT; |
---|
779 | finish INT; |
---|
780 | BEGIN |
---|
781 | out_bits := 0::BIT(24); |
---|
782 | allprivs := ~ out_bits; |
---|
783 | SELECT array_lower(raw_privs,1) INTO start; |
---|
784 | SELECT array_upper(raw_privs,1) INTO finish; |
---|
785 | FOR i IN start .. finish LOOP |
---|
786 | SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits; |
---|
787 | IF out_bits = allprivs THEN |
---|
788 | RETURN allprivs; |
---|
789 | END IF; |
---|
790 | END LOOP; |
---|
791 | RETURN out_bits; |
---|
792 | END |
---|
793 | $$ |
---|
794 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
795 | |
---|
796 | |
---|
797 | -- NOTE: Round-trip through this and then back through privilege_to_bits |
---|
798 | -- function is lossy! Through privilege_to_bits() and back through |
---|
799 | -- this one is not. |
---|
800 | -- |
---|
801 | CREATE or REPLACE FUNCTION bits_to_privilege( BIT(24) ) RETURNS TEXT[] AS $$ |
---|
802 | DECLARE |
---|
803 | in_bits ALIAS FOR $1; |
---|
804 | out_priv TEXT[]; |
---|
805 | BEGIN |
---|
806 | IF in_bits = (~ 0::BIT(24)) THEN |
---|
807 | out_priv := out_priv || ARRAY['DAV:all']; |
---|
808 | END IF; |
---|
809 | |
---|
810 | IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN |
---|
811 | IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN |
---|
812 | out_priv := out_priv || ARRAY['DAV:read']; |
---|
813 | END IF; |
---|
814 | IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN |
---|
815 | out_priv := out_priv || ARRAY['caldav:read-free-busy']; |
---|
816 | END IF; |
---|
817 | END IF; |
---|
818 | |
---|
819 | IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN |
---|
820 | IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN |
---|
821 | out_priv := out_priv || ARRAY['DAV:write']; |
---|
822 | ELSE |
---|
823 | IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN |
---|
824 | out_priv := out_priv || ARRAY['DAV:write-properties']; |
---|
825 | END IF; |
---|
826 | IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN |
---|
827 | out_priv := out_priv || ARRAY['DAV:write-content']; |
---|
828 | END IF; |
---|
829 | IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN |
---|
830 | out_priv := out_priv || ARRAY['DAV:bind']; |
---|
831 | END IF; |
---|
832 | IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN |
---|
833 | out_priv := out_priv || ARRAY['DAV:unbind']; |
---|
834 | END IF; |
---|
835 | END IF; |
---|
836 | END IF; |
---|
837 | |
---|
838 | IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN |
---|
839 | out_priv := out_priv || ARRAY['DAV:unlock']; |
---|
840 | END IF; |
---|
841 | |
---|
842 | IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN |
---|
843 | out_priv := out_priv || ARRAY['DAV:read-acl']; |
---|
844 | END IF; |
---|
845 | |
---|
846 | IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN |
---|
847 | out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set']; |
---|
848 | END IF; |
---|
849 | |
---|
850 | IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN |
---|
851 | out_priv := out_priv || ARRAY['DAV:write-acl']; |
---|
852 | END IF; |
---|
853 | |
---|
854 | IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN |
---|
855 | IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN |
---|
856 | out_priv := out_priv || ARRAY['caldav:schedule-deliver']; |
---|
857 | ELSE |
---|
858 | IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN |
---|
859 | out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite']; |
---|
860 | END IF; |
---|
861 | IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN |
---|
862 | out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply']; |
---|
863 | END IF; |
---|
864 | IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN |
---|
865 | out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy']; |
---|
866 | END IF; |
---|
867 | END IF; |
---|
868 | END IF; |
---|
869 | |
---|
870 | IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN |
---|
871 | IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN |
---|
872 | out_priv := out_priv || ARRAY['caldav:schedule-send']; |
---|
873 | ELSE |
---|
874 | IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN |
---|
875 | out_priv := out_priv || ARRAY['caldav:schedule-send-invite']; |
---|
876 | END IF; |
---|
877 | IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN |
---|
878 | out_priv := out_priv || ARRAY['caldav:schedule-send-reply']; |
---|
879 | END IF; |
---|
880 | IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN |
---|
881 | out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy']; |
---|
882 | END IF; |
---|
883 | END IF; |
---|
884 | END IF; |
---|
885 | |
---|
886 | RETURN out_priv; |
---|
887 | END |
---|
888 | $$ |
---|
889 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
890 | |
---|
891 | |
---|
892 | -- Expanded group memberships out to some depth |
---|
893 | CREATE or REPLACE FUNCTION expand_memberships( INT8, INT ) RETURNS SETOF INT8 AS $$ |
---|
894 | SELECT group_id FROM group_member WHERE member_id = $1 |
---|
895 | UNION |
---|
896 | SELECT expanded.g_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id |
---|
897 | FROM group_member WHERE member_id = $1) AS expanded |
---|
898 | WHERE expanded.g_id IS NOT NULL; |
---|
899 | $$ LANGUAGE 'SQL' STABLE STRICT; |
---|
900 | |
---|
901 | -- Expanded group members out to some depth |
---|
902 | CREATE or REPLACE FUNCTION expand_members( INT8, INT ) RETURNS SETOF INT8 AS $$ |
---|
903 | SELECT member_id FROM group_member WHERE group_id = $1 |
---|
904 | UNION |
---|
905 | SELECT expanded.m_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_members( member_id, $2 - 1) END AS m_id |
---|
906 | FROM group_member WHERE group_id = $1) AS expanded |
---|
907 | WHERE expanded.m_id IS NOT NULL; |
---|
908 | $$ LANGUAGE 'SQL' STABLE STRICT; |
---|
909 | |
---|
910 | |
---|
911 | |
---|
912 | |
---|
913 | -- Privileges from accessor to grantor, by principal_id |
---|
914 | CREATE or REPLACE FUNCTION pprivs( INT8, INT8, INT ) RETURNS BIT(24) AS $$ |
---|
915 | DECLARE |
---|
916 | in_accessor ALIAS FOR $1; |
---|
917 | in_grantor ALIAS FOR $2; |
---|
918 | in_depth ALIAS FOR $3; |
---|
919 | out_conferred BIT(24); |
---|
920 | BEGIN |
---|
921 | out_conferred := 0::BIT(24); |
---|
922 | -- Self can always have full access |
---|
923 | IF in_grantor = in_accessor THEN |
---|
924 | RETURN ~ out_conferred; |
---|
925 | END IF; |
---|
926 | |
---|
927 | SELECT bit_or(subquery.privileges) INTO out_conferred FROM |
---|
928 | ( |
---|
929 | SELECT privileges FROM grants WHERE by_principal=in_grantor AND by_collection IS NULL |
---|
930 | AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth))) |
---|
931 | UNION |
---|
932 | SELECT 32::BIT(24) AS privileges FROM expand_memberships(in_accessor,in_depth) WHERE expand_memberships = in_grantor |
---|
933 | ) AS subquery ; |
---|
934 | |
---|
935 | IF out_conferred IS NULL THEN |
---|
936 | SELECT default_privileges INTO out_conferred FROM principal WHERE principal_id = in_grantor; |
---|
937 | END IF; |
---|
938 | |
---|
939 | RETURN out_conferred; |
---|
940 | END; |
---|
941 | $$ LANGUAGE 'plpgsql' STABLE STRICT; |
---|
942 | |
---|
943 | |
---|
944 | -- Privileges from accessor to grantor, by user_no |
---|
945 | CREATE or REPLACE FUNCTION uprivs( INT8, INT8, INT ) RETURNS BIT(24) AS $$ |
---|
946 | DECLARE |
---|
947 | in_accessor ALIAS FOR $1; |
---|
948 | in_grantor ALIAS FOR $2; |
---|
949 | in_depth ALIAS FOR $3; |
---|
950 | out_conferred BIT(24); |
---|
951 | BEGIN |
---|
952 | out_conferred := 0::BIT(24); |
---|
953 | -- Self can always have full access |
---|
954 | IF in_grantor = in_accessor THEN |
---|
955 | RETURN ~ out_conferred; |
---|
956 | END IF; |
---|
957 | |
---|
958 | SELECT pprivs( p1.principal_id, p2.principal_id, in_depth ) INTO out_conferred |
---|
959 | FROM principal p1, principal p2 |
---|
960 | WHERE p1.user_no = in_accessor AND p2.user_no = in_grantor; |
---|
961 | |
---|
962 | RETURN out_conferred; |
---|
963 | END; |
---|
964 | $$ LANGUAGE 'plpgsql' STABLE STRICT; |
---|
965 | |
---|
966 | |
---|
967 | -- Privileges from accessor (by principal_id) to path |
---|
968 | CREATE or REPLACE FUNCTION path_privs( INT8, TEXT, INT ) RETURNS BIT(24) AS $$ |
---|
969 | DECLARE |
---|
970 | in_accessor ALIAS FOR $1; |
---|
971 | in_path ALIAS FOR $2; |
---|
972 | in_depth ALIAS FOR $3; |
---|
973 | |
---|
974 | alt1_path TEXT; |
---|
975 | alt2_path TEXT; |
---|
976 | grantor_collection INT8; |
---|
977 | grantor_principal INT8; |
---|
978 | collection_path TEXT; |
---|
979 | collection_privileges BIT(24); |
---|
980 | out_conferred BIT(24); |
---|
981 | BEGIN |
---|
982 | out_conferred := 0::BIT(24); |
---|
983 | |
---|
984 | IF in_path ~ '^/?$' THEN |
---|
985 | -- RAISE NOTICE 'Collection is root: Collection: %', in_path; |
---|
986 | RETURN 1; -- basic read privileges on root directory |
---|
987 | END IF; |
---|
988 | |
---|
989 | -- We need to canonicalise the path, so: |
---|
990 | -- If it matches '/' + some characters (+ optional '/') => a principal URL |
---|
991 | IF in_path ~ '^/[^/]+/?$' THEN |
---|
992 | alt1_path := replace(in_path, '/', ''); |
---|
993 | SELECT pprivs(in_accessor,principal_id, in_depth) INTO out_conferred FROM usr JOIN principal USING(user_no) WHERE username = alt1_path; |
---|
994 | -- RAISE NOTICE 'Path is Principal: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; |
---|
995 | RETURN out_conferred; |
---|
996 | END IF; |
---|
997 | |
---|
998 | -- Otherwise look for the longest segment matching up to the last '/', or if we append one, or if we replace a final '.ics' with one. |
---|
999 | alt1_path := in_path; |
---|
1000 | IF alt1_path ~ E'\\.ics$' THEN |
---|
1001 | alt1_path := substr(alt1_path, 1, length(alt1_path) - 4) || '/'; |
---|
1002 | END IF; |
---|
1003 | alt2_path := regexp_replace( in_path, '[^/]*$', ''); |
---|
1004 | SELECT collection.collection_id, grantor.principal_id, collection.dav_name, collection.default_privileges |
---|
1005 | INTO grantor_collection, grantor_principal, collection_path, collection_privileges |
---|
1006 | FROM collection JOIN principal grantor USING (user_no) |
---|
1007 | WHERE dav_name = in_path || '/' OR dav_name = alt1_path OR dav_name = alt2_path |
---|
1008 | ORDER BY LENGTH(collection.dav_name) DESC LIMIT 1; |
---|
1009 | |
---|
1010 | -- Self will always need full access to their own collections! |
---|
1011 | IF grantor_principal = in_accessor THEN |
---|
1012 | -- RAISE NOTICE 'Principal IS owner: Principal: %, Collection: %', in_accessor, in_path; |
---|
1013 | RETURN ~ out_conferred; |
---|
1014 | END IF; |
---|
1015 | |
---|
1016 | SELECT privileges INTO out_conferred FROM grants |
---|
1017 | WHERE by_collection = grantor_collection |
---|
1018 | AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth))); |
---|
1019 | |
---|
1020 | IF out_conferred IS NULL THEN |
---|
1021 | IF collection_privileges IS NULL THEN |
---|
1022 | IF grantor_principal IS NULL THEN |
---|
1023 | alt1_path := regexp_replace( in_path, '/[^/]+/?$', '/'); |
---|
1024 | SELECT path_privs(in_accessor,alt1_path,in_depth) INTO out_conferred; |
---|
1025 | -- RAISE NOTICE 'Collection is NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; |
---|
1026 | ELSE |
---|
1027 | SELECT pprivs(in_accessor,grantor_principal,in_depth) INTO out_conferred; |
---|
1028 | -- RAISE NOTICE 'Collection priveleges are NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; |
---|
1029 | END IF; |
---|
1030 | ELSE |
---|
1031 | out_conferred := collection_privileges; |
---|
1032 | -- RAISE NOTICE 'Default Collection priveleges apply: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; |
---|
1033 | END IF; |
---|
1034 | END IF; |
---|
1035 | |
---|
1036 | RETURN out_conferred; |
---|
1037 | END; |
---|
1038 | $$ LANGUAGE 'plpgsql' STABLE STRICT; |
---|
1039 | |
---|
1040 | |
---|
1041 | -- List a user's memberships as a text string |
---|
1042 | CREATE or REPLACE FUNCTION is_member_of_list( INT8 ) RETURNS TEXT AS $$ |
---|
1043 | DECLARE |
---|
1044 | in_member_id ALIAS FOR $1; |
---|
1045 | m RECORD; |
---|
1046 | mlist TEXT; |
---|
1047 | BEGIN |
---|
1048 | mlist := ''; |
---|
1049 | FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (group_id = principal_id) |
---|
1050 | WHERE member_id = in_member_id |
---|
1051 | LOOP |
---|
1052 | mlist := mlist |
---|
1053 | || CASE WHEN mlist = '' THEN '' ELSE ', ' END |
---|
1054 | || COALESCE( m.displayname, m.group_id::text); |
---|
1055 | END LOOP; |
---|
1056 | RETURN mlist; |
---|
1057 | END; |
---|
1058 | $$ LANGUAGE 'plpgsql' STRICT; |
---|
1059 | |
---|
1060 | |
---|
1061 | -- List a user's members as a text string |
---|
1062 | CREATE or REPLACE FUNCTION has_members_list( INT8 ) RETURNS TEXT AS $$ |
---|
1063 | DECLARE |
---|
1064 | in_member_id ALIAS FOR $1; |
---|
1065 | m RECORD; |
---|
1066 | mlist TEXT; |
---|
1067 | BEGIN |
---|
1068 | mlist := ''; |
---|
1069 | FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (member_id = principal_id) |
---|
1070 | WHERE group_id = in_member_id |
---|
1071 | LOOP |
---|
1072 | mlist := mlist |
---|
1073 | || CASE WHEN mlist = '' THEN '' ELSE ', ' END |
---|
1074 | || COALESCE( m.displayname, m.group_id::text); |
---|
1075 | END LOOP; |
---|
1076 | RETURN mlist; |
---|
1077 | END; |
---|
1078 | $$ LANGUAGE 'plpgsql' STRICT; |
---|
1079 | |
---|
1080 | |
---|
1081 | -- List the privileges as a text string |
---|
1082 | CREATE or REPLACE FUNCTION privileges_list( BIT(24) ) RETURNS TEXT AS $$ |
---|
1083 | DECLARE |
---|
1084 | in_privileges ALIAS FOR $1; |
---|
1085 | privileges TEXT[]; |
---|
1086 | plist TEXT; |
---|
1087 | start INT; |
---|
1088 | finish INT; |
---|
1089 | i INT; |
---|
1090 | BEGIN |
---|
1091 | plist := ''; |
---|
1092 | |
---|
1093 | privileges := bits_to_privilege(in_privileges); |
---|
1094 | SELECT array_lower(privileges,1) INTO start; |
---|
1095 | IF start IS NOT NULL THEN |
---|
1096 | SELECT array_upper(privileges,1) INTO finish; |
---|
1097 | FOR i IN start .. finish LOOP |
---|
1098 | plist := plist |
---|
1099 | || CASE WHEN plist = '' THEN '' ELSE ', ' END |
---|
1100 | || privileges[i]; |
---|
1101 | END LOOP; |
---|
1102 | END IF; |
---|
1103 | RETURN plist; |
---|
1104 | END; |
---|
1105 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
1106 | |
---|
1107 | |
---|
1108 | DROP TRIGGER principal_modified ON principal CASCADE; |
---|
1109 | CREATE or REPLACE FUNCTION principal_modified() RETURNS TRIGGER AS $$ |
---|
1110 | DECLARE |
---|
1111 | BEGIN |
---|
1112 | -- in case we trigger on other events in future |
---|
1113 | IF TG_OP = 'UPDATE' THEN |
---|
1114 | IF NEW.type_id != OLD.type_id THEN |
---|
1115 | UPDATE grants |
---|
1116 | SET is_group = (NEW.type_id = 3) |
---|
1117 | WHERE grants.to_principal = NEW.principal_id; |
---|
1118 | END IF; |
---|
1119 | END IF; |
---|
1120 | RETURN NEW; |
---|
1121 | END; |
---|
1122 | $$ LANGUAGE plpgsql; |
---|
1123 | CREATE TRIGGER principal_modified AFTER UPDATE ON principal |
---|
1124 | FOR EACH ROW EXECUTE PROCEDURE principal_modified(); |
---|
1125 | |
---|
1126 | |
---|
1127 | DROP TRIGGER grants_modified ON grants CASCADE; |
---|
1128 | CREATE or REPLACE FUNCTION grants_modified() RETURNS TRIGGER AS $$ |
---|
1129 | DECLARE |
---|
1130 | old_to_principal INT8; |
---|
1131 | new_is_group BOOL; |
---|
1132 | BEGIN |
---|
1133 | -- in case we trigger on other events in future |
---|
1134 | IF TG_OP = 'INSERT' THEN |
---|
1135 | old_to_principal := NULL; |
---|
1136 | ELSE |
---|
1137 | old_to_principal := OLD.to_principal; |
---|
1138 | END IF; |
---|
1139 | IF TG_OP = 'INSERT' OR NEW.to_principal != old_to_principal THEN |
---|
1140 | SELECT (type_id = 3) INTO new_is_group FROM principal WHERE principal_id = NEW.to_principal; |
---|
1141 | IF NEW.is_group != new_is_group THEN |
---|
1142 | NEW.is_group := new_is_group; |
---|
1143 | END IF; |
---|
1144 | END IF; |
---|
1145 | RETURN NEW; |
---|
1146 | END; |
---|
1147 | $$ LANGUAGE plpgsql; |
---|
1148 | CREATE TRIGGER grants_modified AFTER INSERT OR UPDATE ON grants |
---|
1149 | FOR EACH ROW EXECUTE PROCEDURE grants_modified(); |
---|
1150 | |
---|
1151 | |
---|
1152 | |
---|
1153 | -- An expanded list of the grants this principal has access to |
---|
1154 | CREATE or REPLACE FUNCTION p_has_proxy_access_to( INT8, INT ) RETURNS SETOF INT8 AS $$ |
---|
1155 | SELECT by_principal |
---|
1156 | FROM ( |
---|
1157 | SELECT by_principal FROM grants |
---|
1158 | WHERE to_principal IN (SELECT $1 UNION SELECT expand_memberships($1,$2)) |
---|
1159 | AND (privileges & 5::BIT(24)) != 0::BIT(24) |
---|
1160 | AND by_collection IS NULL |
---|
1161 | AND by_principal != $1 |
---|
1162 | UNION |
---|
1163 | SELECT principal_id AS by_principal FROM principal |
---|
1164 | WHERE (default_privileges & 5::BIT(24)) != 0::BIT(24) |
---|
1165 | AND principal_id != $1 |
---|
1166 | ) subquery; |
---|
1167 | $$ LANGUAGE 'SQL' STABLE STRICT; |
---|
1168 | |
---|
1169 | |
---|
1170 | -- A list of the principals who can proxy to this principal |
---|
1171 | CREATE or REPLACE FUNCTION grants_proxy_access_from_p( INT8, INT ) RETURNS SETOF INT8 AS $$ |
---|
1172 | SELECT DISTINCT by_principal |
---|
1173 | FROM grants |
---|
1174 | WHERE by_collection IS NULL AND by_principal != $1 |
---|
1175 | AND by_principal IN (SELECT expand_members(g2.to_principal,$2) FROM grants g2 WHERE g2.by_principal = $1) |
---|
1176 | ; |
---|
1177 | $$ LANGUAGE 'SQL' STABLE STRICT; |
---|
1178 | |
---|
1179 | |
---|
1180 | |
---|
1181 | -- New in 1.2.7 |
---|
1182 | |
---|
1183 | CREATE or REPLACE FUNCTION write_sync_change( INT8, INT, TEXT ) RETURNS BOOLEAN AS $$ |
---|
1184 | DECLARE |
---|
1185 | in_collection_id ALIAS FOR $1; |
---|
1186 | in_status ALIAS FOR $2; |
---|
1187 | in_dav_name ALIAS FOR $3; |
---|
1188 | tmp_int INT8; |
---|
1189 | BEGIN |
---|
1190 | SELECT 1 INTO tmp_int FROM sync_tokens |
---|
1191 | WHERE collection_id = in_collection_id |
---|
1192 | LIMIT 1; |
---|
1193 | IF NOT FOUND THEN |
---|
1194 | RETURN FALSE; |
---|
1195 | END IF; |
---|
1196 | SELECT dav_id INTO tmp_int FROM calendar_item WHERE dav_name = in_dav_name; |
---|
1197 | INSERT INTO sync_changes ( collection_id, sync_status, dav_id, dav_name) |
---|
1198 | VALUES( in_collection_id, in_status, tmp_int, in_dav_name); |
---|
1199 | RETURN TRUE; |
---|
1200 | END |
---|
1201 | $$ LANGUAGE 'PlPgSQL' VOLATILE STRICT; |
---|
1202 | |
---|
1203 | |
---|
1204 | CREATE or REPLACE FUNCTION new_sync_token( INT8, INT8 ) RETURNS INT8 AS $$ |
---|
1205 | DECLARE |
---|
1206 | in_old_sync_token ALIAS FOR $1; |
---|
1207 | in_collection_id ALIAS FOR $2; |
---|
1208 | tmp_int INT8; |
---|
1209 | BEGIN |
---|
1210 | IF in_old_sync_token > 0 THEN |
---|
1211 | SELECT 1 INTO tmp_int FROM sync_changes |
---|
1212 | WHERE collection_id = in_collection_id |
---|
1213 | AND sync_time > (SELECT modification_time FROM sync_tokens WHERE sync_token = in_old_sync_token) |
---|
1214 | LIMIT 1; |
---|
1215 | IF NOT FOUND THEN |
---|
1216 | RETURN in_old_sync_token; |
---|
1217 | END IF; |
---|
1218 | END IF; |
---|
1219 | SELECT nextval('sync_tokens_sync_token_seq') INTO tmp_int; |
---|
1220 | INSERT INTO sync_tokens(collection_id, sync_token) VALUES( in_collection_id, tmp_int ); |
---|
1221 | RETURN tmp_int; |
---|
1222 | END |
---|
1223 | $$ LANGUAGE 'PlPgSQL' STRICT; |
---|
1224 | |
---|
1225 | |
---|
1226 | DROP TRIGGER alarm_changed ON calendar_alarm CASCADE; |
---|
1227 | CREATE or REPLACE FUNCTION alarm_changed() RETURNS TRIGGER AS $$ |
---|
1228 | DECLARE |
---|
1229 | oldcomponent TEXT; |
---|
1230 | newcomponent TEXT; |
---|
1231 | BEGIN |
---|
1232 | -- in case we trigger on other events in future |
---|
1233 | IF TG_OP = 'UPDATE' THEN |
---|
1234 | IF NEW.component != OLD.component THEN |
---|
1235 | UPDATE caldav_data |
---|
1236 | SET caldav_data = replace( caldav_data, OLD.component, NEW.component ), |
---|
1237 | dav_etag = md5(replace( caldav_data, OLD.component, NEW.component )) |
---|
1238 | WHERE caldav_data.dav_id = NEW.dav_id; |
---|
1239 | END IF; |
---|
1240 | END IF; |
---|
1241 | RETURN NEW; |
---|
1242 | END; |
---|
1243 | $$ LANGUAGE plpgsql; |
---|
1244 | CREATE TRIGGER alarm_changed AFTER UPDATE ON calendar_alarm |
---|
1245 | FOR EACH ROW EXECUTE PROCEDURE alarm_changed(); |
---|