1 | |
---|
2 | -- This database update converts the permissions into a bitmap stored |
---|
3 | -- as an integer to make calculation of merged permissions simpler |
---|
4 | -- through simple binary 'AND' |
---|
5 | |
---|
6 | CREATE or REPLACE FUNCTION legacy_privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ |
---|
7 | DECLARE |
---|
8 | in_priv ALIAS FOR $1; |
---|
9 | out_bits BIT(24); |
---|
10 | BEGIN |
---|
11 | out_bits := 0::BIT(24); |
---|
12 | IF in_priv ~* 'A' THEN |
---|
13 | out_bits = ~ out_bits; |
---|
14 | RETURN out_bits; |
---|
15 | END IF; |
---|
16 | |
---|
17 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
18 | -- 1 DAV:read |
---|
19 | -- 512 CalDAV:read-free-busy |
---|
20 | -- 4096 CALDAV:schedule-query-freebusy |
---|
21 | IF in_priv ~* 'R' THEN |
---|
22 | out_bits := out_bits | 4609::BIT(24); |
---|
23 | END IF; |
---|
24 | |
---|
25 | -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
26 | -- 2 DAV:write-properties |
---|
27 | -- 4 DAV:write-content |
---|
28 | -- 64 DAV:bind |
---|
29 | -- 128 DAV:unbind |
---|
30 | IF in_priv ~* 'W' THEN |
---|
31 | out_bits := out_bits | 198::BIT(24); |
---|
32 | END IF; |
---|
33 | |
---|
34 | -- 64 DAV:bind |
---|
35 | IF in_priv ~* 'B' THEN |
---|
36 | out_bits := out_bits | 64::BIT(24); |
---|
37 | END IF; |
---|
38 | |
---|
39 | -- 128 DAV:unbind |
---|
40 | IF in_priv ~* 'U' THEN |
---|
41 | out_bits := out_bits | 128::BIT(24); |
---|
42 | END IF; |
---|
43 | |
---|
44 | -- 512 CalDAV:read-free-busy |
---|
45 | -- 4096 CALDAV:schedule-query-freebusy |
---|
46 | IF in_priv ~* 'F' THEN |
---|
47 | out_bits := out_bits | 4608::BIT(24); |
---|
48 | END IF; |
---|
49 | |
---|
50 | RETURN out_bits; |
---|
51 | END |
---|
52 | $$ |
---|
53 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
54 | |
---|
55 | -- This legacy conversion function will eventually be removed, once all logic |
---|
56 | -- has been converted to use bitmaps, or to use the bits_to_priv() output. |
---|
57 | -- |
---|
58 | -- NOTE: Round-trip through this and then back through legacy_privilege_to_bits |
---|
59 | -- function is lossy! Through legacy_privilege_to_bits() and back through |
---|
60 | -- this one is not. |
---|
61 | -- |
---|
62 | CREATE or REPLACE FUNCTION bits_to_legacy_privilege( BIT(24) ) RETURNS TEXT AS $$ |
---|
63 | DECLARE |
---|
64 | in_bits ALIAS FOR $1; |
---|
65 | out_priv TEXT; |
---|
66 | BEGIN |
---|
67 | out_priv := ''; |
---|
68 | IF in_bits = (~ 0::BIT(24)) THEN |
---|
69 | out_priv = 'A'; |
---|
70 | RETURN out_priv; |
---|
71 | END IF; |
---|
72 | |
---|
73 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
74 | -- 1 DAV:read |
---|
75 | -- 512 CalDAV:read-free-busy |
---|
76 | -- 4096 CALDAV:schedule-query-freebusy |
---|
77 | IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN |
---|
78 | IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN |
---|
79 | out_priv := 'R'; |
---|
80 | ELSE |
---|
81 | out_priv := 'F'; |
---|
82 | END IF; |
---|
83 | END IF; |
---|
84 | |
---|
85 | -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
86 | -- 2 DAV:write-properties |
---|
87 | -- 4 DAV:write-content |
---|
88 | -- 64 DAV:bind |
---|
89 | -- 128 DAV:unbind |
---|
90 | IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN |
---|
91 | IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN |
---|
92 | out_priv := out_priv || 'W'; |
---|
93 | ELSE |
---|
94 | IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN |
---|
95 | out_priv := out_priv || 'B'; |
---|
96 | END IF; |
---|
97 | IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN |
---|
98 | out_priv := out_priv || 'U'; |
---|
99 | END IF; |
---|
100 | END IF; |
---|
101 | END IF; |
---|
102 | |
---|
103 | RETURN out_priv; |
---|
104 | END |
---|
105 | $$ |
---|
106 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
107 | |
---|
108 | CREATE or REPLACE FUNCTION get_permissions( INT, INT ) RETURNS TEXT AS $$ |
---|
109 | DECLARE |
---|
110 | in_from ALIAS FOR $1; |
---|
111 | in_to ALIAS FOR $2; |
---|
112 | out_confers TEXT; |
---|
113 | bit_confers BIT(24); |
---|
114 | group_role_no INT; |
---|
115 | tmp_txt TEXT; |
---|
116 | dbg TEXT DEFAULT ''; |
---|
117 | r RECORD; |
---|
118 | counter INT; |
---|
119 | BEGIN |
---|
120 | -- Self can always have full access |
---|
121 | IF in_from = in_to THEN |
---|
122 | RETURN 'A'; |
---|
123 | END IF; |
---|
124 | |
---|
125 | -- dbg := 'S-'; |
---|
126 | SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 |
---|
127 | WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group'); |
---|
128 | IF FOUND THEN |
---|
129 | RETURN dbg || out_confers; |
---|
130 | END IF; |
---|
131 | -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to; |
---|
132 | |
---|
133 | SELECT bit_or(r1.confers & r2.confers) INTO bit_confers |
---|
134 | FROM relationship r1 |
---|
135 | JOIN relationship r2 ON r1.to_user=r2.from_user |
---|
136 | WHERE r1.from_user=in_from AND r2.to_user=in_to |
---|
137 | AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group'); |
---|
138 | IF bit_confers != 0::BIT(24) THEN |
---|
139 | RETURN dbg || bits_to_legacy_privilege(bit_confers); |
---|
140 | END IF; |
---|
141 | |
---|
142 | RETURN ''; |
---|
143 | -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to; |
---|
144 | |
---|
145 | 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) |
---|
146 | WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user |
---|
147 | AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ; |
---|
148 | |
---|
149 | IF FOUND THEN |
---|
150 | -- dbg := 'H-'; |
---|
151 | -- RAISE NOTICE 'Permissions to shared group % ', out_confers; |
---|
152 | RETURN dbg || out_confers; |
---|
153 | END IF; |
---|
154 | |
---|
155 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
156 | |
---|
157 | RETURN ''; |
---|
158 | END; |
---|
159 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
160 | |
---|
161 | |
---|
162 | CREATE or REPLACE FUNCTION get_group_role_no() RETURNS INT AS $$ |
---|
163 | SELECT role_no FROM roles WHERE role_name = 'Group' |
---|
164 | $$ LANGUAGE 'SQL' IMMUTABLE; |
---|
165 | |
---|
166 | CREATE or REPLACE FUNCTION has_legacy_privilege( INT, TEXT, INT ) RETURNS BOOLEAN AS $$ |
---|
167 | DECLARE |
---|
168 | in_from ALIAS FOR $1; |
---|
169 | in_legacy_privilege ALIAS FOR $2; |
---|
170 | in_to ALIAS FOR $3; |
---|
171 | in_confers BIT(24); |
---|
172 | group_role_no INT; |
---|
173 | BEGIN |
---|
174 | -- Self can always have full access |
---|
175 | IF in_from = in_to THEN |
---|
176 | RETURN TRUE; |
---|
177 | END IF; |
---|
178 | |
---|
179 | SELECT get_group_role_no() INTO group_role_no; |
---|
180 | SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers; |
---|
181 | |
---|
182 | IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to |
---|
183 | AND (in_confers & confers) = in_confers |
---|
184 | AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN |
---|
185 | -- A direct relationship from A to B that grants sufficient |
---|
186 | -- RAISE NOTICE 'Permissions directly granted'; |
---|
187 | RETURN TRUE; |
---|
188 | END IF; |
---|
189 | |
---|
190 | IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user |
---|
191 | WHERE (in_confers & r1.confers & r2.confers) = in_confers |
---|
192 | AND r1.from_user=in_from AND r2.to_user=in_to |
---|
193 | AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN |
---|
194 | -- An indirect relationship from A to B via group G that grants sufficient |
---|
195 | -- RAISE NOTICE 'Permissions mediated via group'; |
---|
196 | RETURN TRUE; |
---|
197 | END IF; |
---|
198 | |
---|
199 | IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user |
---|
200 | WHERE (in_confers & r1.confers & r2.confers) = in_confers |
---|
201 | AND r1.from_user=in_from AND r2.from_user=in_to |
---|
202 | AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) |
---|
203 | AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN |
---|
204 | -- An indirect reflexive relationship from both A & B to group G which grants sufficient |
---|
205 | -- RAISE NOTICE 'Permissions to shared group'; |
---|
206 | RETURN TRUE; |
---|
207 | END IF; |
---|
208 | |
---|
209 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
210 | |
---|
211 | RETURN FALSE; |
---|
212 | END; |
---|
213 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
214 | |
---|
215 | |
---|
216 | -- Given a verbose DAV: or CalDAV: privilege name return the bitmask |
---|
217 | CREATE or REPLACE FUNCTION privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ |
---|
218 | DECLARE |
---|
219 | raw_priv ALIAS FOR $1; |
---|
220 | in_priv TEXT; |
---|
221 | BEGIN |
---|
222 | in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', ''))); |
---|
223 | IF in_priv = 'all' THEN |
---|
224 | RETURN ~ 0::BIT(24); |
---|
225 | END IF; |
---|
226 | |
---|
227 | RETURN (CASE |
---|
228 | WHEN in_priv = 'read' THEN 1 |
---|
229 | WHEN in_priv = 'write' THEN 198 -- 2 + 4 + 64 + 128 |
---|
230 | WHEN in_priv = 'write-properties' THEN 2 |
---|
231 | WHEN in_priv = 'write-content' THEN 4 |
---|
232 | WHEN in_priv = 'unlock' THEN 8 |
---|
233 | WHEN in_priv = 'read-acl' THEN 16 |
---|
234 | WHEN in_priv = 'read-current-user-privilege-set' THEN 32 |
---|
235 | WHEN in_priv = 'bind' THEN 64 |
---|
236 | WHEN in_priv = 'unbind' THEN 128 |
---|
237 | WHEN in_priv = 'write-acl' THEN 256 |
---|
238 | WHEN in_priv = 'read-free-busy' THEN 512 |
---|
239 | WHEN in_priv = 'schedule-deliver' THEN 7168 -- 1024 + 2048 + 4096 |
---|
240 | WHEN in_priv = 'schedule-deliver-invite' THEN 1024 |
---|
241 | WHEN in_priv = 'schedule-deliver-reply' THEN 2048 |
---|
242 | WHEN in_priv = 'schedule-query-freebusy' THEN 4096 |
---|
243 | WHEN in_priv = 'schedule-send' THEN 57344 -- 8192 + 16384 + 32768 |
---|
244 | WHEN in_priv = 'schedule-send-invite' THEN 8192 |
---|
245 | WHEN in_priv = 'schedule-send-reply' THEN 16384 |
---|
246 | WHEN in_priv = 'schedule-send-freebusy' THEN 32768 |
---|
247 | ELSE 0 END)::BIT(24); |
---|
248 | END |
---|
249 | $$ |
---|
250 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
251 | |
---|
252 | |
---|
253 | -- Given an array of verbose DAV: or CalDAV: privilege names return the bitmask |
---|
254 | CREATE or REPLACE FUNCTION privilege_to_bits( TEXT[] ) RETURNS BIT(24) AS $$ |
---|
255 | DECLARE |
---|
256 | raw_privs ALIAS FOR $1; |
---|
257 | in_priv TEXT; |
---|
258 | out_bits BIT(24); |
---|
259 | i INT; |
---|
260 | all BIT(24); |
---|
261 | start INT; |
---|
262 | finish INT; |
---|
263 | BEGIN |
---|
264 | out_bits := 0::BIT(24); |
---|
265 | all := ~ out_bits; |
---|
266 | SELECT array_lower(raw_privs,1) INTO start; |
---|
267 | SELECT array_upper(raw_privs,1) INTO finish; |
---|
268 | FOR i IN start .. finish LOOP |
---|
269 | SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits; |
---|
270 | IF out_bits = all THEN |
---|
271 | RETURN all; |
---|
272 | END IF; |
---|
273 | END LOOP; |
---|
274 | RETURN out_bits; |
---|
275 | END |
---|
276 | $$ |
---|
277 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
278 | |
---|
279 | |
---|
280 | -- This legacy conversion function will eventually be removed, once all logic |
---|
281 | -- has been converted to use bitmaps, or to use the bits_to_priv() output. |
---|
282 | -- |
---|
283 | -- NOTE: Round-trip through this and then back through privilege_to_bits |
---|
284 | -- function is lossy! Through privilege_to_bits() and back through |
---|
285 | -- this one is not. |
---|
286 | -- |
---|
287 | CREATE or REPLACE FUNCTION bits_to_privilege( BIT(24) ) RETURNS TEXT[] AS $$ |
---|
288 | DECLARE |
---|
289 | in_bits ALIAS FOR $1; |
---|
290 | out_priv TEXT[]; |
---|
291 | BEGIN |
---|
292 | IF in_bits = (~ 0::BIT(24)) THEN |
---|
293 | out_priv := out_priv || ARRAY['DAV:all']; |
---|
294 | END IF; |
---|
295 | |
---|
296 | IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN |
---|
297 | IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN |
---|
298 | out_priv := out_priv || ARRAY['DAV:read']; |
---|
299 | END IF; |
---|
300 | IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN |
---|
301 | out_priv := out_priv || ARRAY['caldav:read-free-busy']; |
---|
302 | END IF; |
---|
303 | END IF; |
---|
304 | |
---|
305 | IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN |
---|
306 | IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN |
---|
307 | out_priv := out_priv || ARRAY['DAV:write']; |
---|
308 | ELSE |
---|
309 | IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN |
---|
310 | out_priv := out_priv || ARRAY['DAV:write-properties']; |
---|
311 | END IF; |
---|
312 | IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN |
---|
313 | out_priv := out_priv || ARRAY['DAV:write-content']; |
---|
314 | END IF; |
---|
315 | IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN |
---|
316 | out_priv := out_priv || ARRAY['DAV:bind']; |
---|
317 | END IF; |
---|
318 | IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN |
---|
319 | out_priv := out_priv || ARRAY['DAV:unbind']; |
---|
320 | END IF; |
---|
321 | END IF; |
---|
322 | END IF; |
---|
323 | |
---|
324 | IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN |
---|
325 | out_priv := out_priv || ARRAY['DAV:unlock']; |
---|
326 | END IF; |
---|
327 | |
---|
328 | IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN |
---|
329 | out_priv := out_priv || ARRAY['DAV:read-acl']; |
---|
330 | END IF; |
---|
331 | |
---|
332 | IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN |
---|
333 | out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set']; |
---|
334 | END IF; |
---|
335 | |
---|
336 | IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN |
---|
337 | out_priv := out_priv || ARRAY['DAV:write-acl']; |
---|
338 | END IF; |
---|
339 | |
---|
340 | IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN |
---|
341 | IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN |
---|
342 | out_priv := out_priv || ARRAY['caldav:schedule-deliver']; |
---|
343 | ELSE |
---|
344 | IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN |
---|
345 | out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite']; |
---|
346 | END IF; |
---|
347 | IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN |
---|
348 | out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply']; |
---|
349 | END IF; |
---|
350 | IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN |
---|
351 | out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy']; |
---|
352 | END IF; |
---|
353 | END IF; |
---|
354 | END IF; |
---|
355 | |
---|
356 | IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN |
---|
357 | IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN |
---|
358 | out_priv := out_priv || ARRAY['caldav:schedule-send']; |
---|
359 | ELSE |
---|
360 | IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN |
---|
361 | out_priv := out_priv || ARRAY['caldav:schedule-send-invite']; |
---|
362 | END IF; |
---|
363 | IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN |
---|
364 | out_priv := out_priv || ARRAY['caldav:schedule-send-reply']; |
---|
365 | END IF; |
---|
366 | IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN |
---|
367 | out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy']; |
---|
368 | END IF; |
---|
369 | END IF; |
---|
370 | END IF; |
---|
371 | |
---|
372 | RETURN out_priv; |
---|
373 | END |
---|
374 | $$ |
---|
375 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
376 | |
---|
377 | |
---|
378 | |
---|
379 | |
---|
380 | |
---|
381 | |
---|
382 | |
---|
383 | |
---|
384 | |
---|
385 | |
---|
386 | |
---|
387 | BEGIN; |
---|
388 | SELECT check_db_revision(1,2,5); |
---|
389 | |
---|
390 | |
---|
391 | -- DAV Privileges implementation |
---|
392 | -- |
---|
393 | -- RFC 3744 - DAV ACLs |
---|
394 | -- 1 DAV:read |
---|
395 | -- DAV:write (aggregate = 198 = write-properties & write-content & bind & unbind) |
---|
396 | -- 2 DAV:write-properties |
---|
397 | -- 4 DAV:write-content |
---|
398 | -- 8 DAV:unlock |
---|
399 | -- 16 DAV:read-acl |
---|
400 | -- 32 DAV:read-current-user-privilege-set |
---|
401 | -- 64 DAV:bind |
---|
402 | -- 128 DAV:unbind |
---|
403 | -- 256 DAV:write-acl |
---|
404 | |
---|
405 | -- RFC 4791 - CalDAV |
---|
406 | -- 512 CalDAV:read-free-busy |
---|
407 | |
---|
408 | -- RFC ???? - Scheduling Extensions for CalDAV |
---|
409 | -- CALDAV:schedule-deliver (aggregate) => 7168 |
---|
410 | -- 1024 CALDAV:schedule-deliver-invite |
---|
411 | -- 2048 CALDAV:schedule-deliver-reply |
---|
412 | -- 4096 CALDAV:schedule-query-freebusy |
---|
413 | -- CALDAV:schedule-send (aggregate) => 57344 |
---|
414 | -- 8192 CALDAV:schedule-send-invite |
---|
415 | -- 16384 CALDAV:schedule-send-reply |
---|
416 | -- 32768 CALDAV:schedule-send-freebusy |
---|
417 | |
---|
418 | -- RFC 3744 - DAV ACLs |
---|
419 | -- DAV:all => all of the above and any new ones someone might invent! |
---|
420 | |
---|
421 | -- DAV:read-acl MUST NOT contain DAV:read, DAV:write, DAV:write-acl, DAV:write-properties, DAV:write-content, or DAV:read-current-user-privilege-set. |
---|
422 | -- DAV:write-acl MUST NOT contain DAV:write, DAV:read, DAV:read-acl, DAV:read-current-user-privilege-set. |
---|
423 | -- DAV:read-current-user-privilege-set MUST NOT contain DAV:write, DAV:read, DAV:read-acl, or DAV:write-acl. |
---|
424 | -- DAV:write MUST NOT contain DAV:read, DAV:read-acl, or DAV:read-current-user-privilege-set. |
---|
425 | -- DAV:read MUST NOT contain DAV:write, DAV:write-acl, DAV:write-properties, or DAV:write-content. |
---|
426 | -- DAV:write-acl COULD contain DAV:write-properties DAV:write-content DAV:unlock DAV:bind DAV:unbind BUT why would it? |
---|
427 | |
---|
428 | -- DAV:write => DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
429 | |
---|
430 | -- RFC 4791 - CalDAV |
---|
431 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
432 | |
---|
433 | -- RFC ???? - Scheduling Extensions for CalDAV |
---|
434 | -- DAV:all MUST contain CALDAV:schedule-send and CALDAV:schedule-deliver |
---|
435 | -- CALDAV:schedule-send MUST contain CALDAV:schedule-send-invite, CALDAV:schedule-send-reply, and CALDAV:schedule-send-freebusy; |
---|
436 | -- CALDAV:schedule-deliver MUST contain CALDAV:schedule-deliver-invite, CALDAV:schedule-deliver-reply, and CALDAV:schedule-query-freebusy. |
---|
437 | |
---|
438 | |
---|
439 | -- Me!!! |
---|
440 | -- CalDAV:read-free-busy privilege SHOULD contain CALDAV:schedule-query-freebusy |
---|
441 | -- => DAV:read privilege SHOULD contain CALDAV:schedule-query-freebusy |
---|
442 | -- We do this outside of these privileges though. |
---|
443 | |
---|
444 | |
---|
445 | -- This legacy conversion function will eventually be removed, once all logic |
---|
446 | -- has been converted to use bitmaps, or to use the bits_to_priv() output. |
---|
447 | CREATE or REPLACE FUNCTION legacy_privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ |
---|
448 | DECLARE |
---|
449 | in_priv ALIAS FOR $1; |
---|
450 | out_bits BIT(24); |
---|
451 | BEGIN |
---|
452 | out_bits := 0::BIT(24); |
---|
453 | IF in_priv ~* 'A' THEN |
---|
454 | out_bits = ~ out_bits; |
---|
455 | RETURN out_bits; |
---|
456 | END IF; |
---|
457 | |
---|
458 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
459 | -- 1 DAV:read |
---|
460 | -- 512 CalDAV:read-free-busy |
---|
461 | -- 4096 CALDAV:schedule-query-freebusy |
---|
462 | IF in_priv ~* 'R' THEN |
---|
463 | out_bits := out_bits | 4609::BIT(24); |
---|
464 | END IF; |
---|
465 | |
---|
466 | -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
467 | -- 2 DAV:write-properties |
---|
468 | -- 4 DAV:write-content |
---|
469 | -- 64 DAV:bind |
---|
470 | -- 128 DAV:unbind |
---|
471 | IF in_priv ~* 'W' THEN |
---|
472 | out_bits := out_bits | 198::BIT(24); |
---|
473 | END IF; |
---|
474 | |
---|
475 | -- 64 DAV:bind |
---|
476 | IF in_priv ~* 'B' THEN |
---|
477 | out_bits := out_bits | 64::BIT(24); |
---|
478 | END IF; |
---|
479 | |
---|
480 | -- 128 DAV:unbind |
---|
481 | IF in_priv ~* 'U' THEN |
---|
482 | out_bits := out_bits | 128::BIT(24); |
---|
483 | END IF; |
---|
484 | |
---|
485 | -- 512 CalDAV:read-free-busy |
---|
486 | -- 4096 CALDAV:schedule-query-freebusy |
---|
487 | IF in_priv ~* 'F' THEN |
---|
488 | out_bits := out_bits | 4608::BIT(24); |
---|
489 | END IF; |
---|
490 | |
---|
491 | RETURN out_bits; |
---|
492 | END |
---|
493 | $$ |
---|
494 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
495 | |
---|
496 | |
---|
497 | ALTER TABLE relationship_type ADD COLUMN bit_confers BIT(24) DEFAULT privilege_to_bits(ARRAY['DAV::read','DAV::write']); |
---|
498 | UPDATE relationship_type SET bit_confers = legacy_privilege_to_bits(confers); |
---|
499 | |
---|
500 | ALTER TABLE relationship ADD COLUMN confers BIT(24) DEFAULT privilege_to_bits('caldav:read-free-busy'); |
---|
501 | UPDATE relationship SET confers = (SELECT bit_confers FROM relationship_type AS rt WHERE rt.rt_id=relationship.rt_id); |
---|
502 | |
---|
503 | ALTER TABLE collection ADD COLUMN default_privileges BIT(24); |
---|
504 | |
---|
505 | INSERT INTO principal_type (principal_type_id, principal_type_desc) VALUES( 1, 'Person' ); |
---|
506 | INSERT INTO principal_type (principal_type_id, principal_type_desc) VALUES( 2, 'Resource' ); |
---|
507 | INSERT INTO principal_type (principal_type_id, principal_type_desc) VALUES( 3, 'Group' ); |
---|
508 | |
---|
509 | -- web needs SELECT,INSERT,UPDATE,DELETE |
---|
510 | DROP TABLE principal CASCADE; |
---|
511 | CREATE TABLE principal ( |
---|
512 | principal_id INT8 DEFAULT nextval('dav_id_seq') PRIMARY KEY, |
---|
513 | type_id INT8 NOT NULL REFERENCES principal_type(principal_type_id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE, |
---|
514 | user_no INT8 NULL REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
515 | displayname TEXT, |
---|
516 | default_privileges BIT(24) |
---|
517 | ); |
---|
518 | |
---|
519 | INSERT INTO principal (type_id, user_no, displayname, default_privileges) |
---|
520 | SELECT 1, user_no, fullname, privilege_to_bits(ARRAY['read-free-busy','schedule-send','schedule-deliver']) FROM usr |
---|
521 | WHERE NOT EXISTS(SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_name = 'Group' AND role_member.user_no = usr.user_no) |
---|
522 | AND NOT EXISTS(SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_name = 'Resource' AND role_member.user_no = usr.user_no) ; |
---|
523 | |
---|
524 | INSERT INTO principal (type_id, user_no, displayname, default_privileges) |
---|
525 | SELECT 2, user_no, fullname, privilege_to_bits(ARRAY['read','schedule-send','schedule-deliver']) FROM usr |
---|
526 | WHERE EXISTS(SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_name = 'Resource' AND role_member.user_no = usr.user_no); |
---|
527 | |
---|
528 | INSERT INTO principal (type_id, user_no, displayname, default_privileges) |
---|
529 | SELECT 3, user_no, fullname, privilege_to_bits(ARRAY['read-free-busy','schedule-send','schedule-deliver']) FROM usr |
---|
530 | WHERE EXISTS(SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_name = 'Group' AND role_member.user_no = usr.user_no); |
---|
531 | |
---|
532 | UPDATE collection SET default_privileges = CASE |
---|
533 | WHEN publicly_readable THEN privilege_to_bits(ARRAY['read']) |
---|
534 | ELSE NULL |
---|
535 | END; |
---|
536 | |
---|
537 | INSERT INTO group_member ( group_id, member_id) |
---|
538 | SELECT g.principal_id, m.principal_id |
---|
539 | FROM relationship JOIN principal g ON(to_user=g.user_no AND g.type_id = 3) -- Group |
---|
540 | JOIN principal m ON(from_user=m.user_no AND m.type_id IN (1, 2) ); -- Person or Resource |
---|
541 | |
---|
542 | DROP TABLE dav_resource_type CASCADE; |
---|
543 | DROP TABLE dav_resource CASCADE; |
---|
544 | DROP TABLE privilege CASCADE; |
---|
545 | |
---|
546 | CREATE TABLE grants ( |
---|
547 | by_principal INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
548 | by_collection INT8 REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
549 | to_principal INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
550 | privileges BIT(24), |
---|
551 | is_group BOOLEAN |
---|
552 | ) WITHOUT OIDS; |
---|
553 | CREATE UNIQUE INDEX grants_pk1 ON grants(by_principal,to_principal); |
---|
554 | CREATE UNIQUE INDEX grants_pk2 ON grants(by_collection,to_principal); |
---|
555 | |
---|
556 | |
---|
557 | INSERT INTO grants ( by_principal, to_principal, privileges, is_group ) |
---|
558 | SELECT pby.principal_id AS by_principal, pto.principal_id AS to_principal, |
---|
559 | confers AS privileges, pto.type_id > 2 AS is_group |
---|
560 | FROM relationship r JOIN usr f ON(f.user_no=r.from_user) |
---|
561 | JOIN usr t ON(t.user_no=r.to_user) |
---|
562 | JOIN principal pby ON(t.user_no=pby.user_no) |
---|
563 | JOIN principal pto ON(pto.user_no=f.user_no) |
---|
564 | WHERE rt_id < 4 AND pby.type_id < 3; |
---|
565 | |
---|
566 | -- It's always safe to kill these collections, so they will be recreated with the correct resourcetype |
---|
567 | DELETE FROM collection WHERE dav_name ~ E'/\.(in|out)/$'; |
---|
568 | |
---|
569 | SELECT new_db_revision(1,2,6, 'Juin' ); |
---|
570 | |
---|
571 | COMMIT; |
---|
572 | ROLLBACK; |
---|
573 | |
---|