[3733] | 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 | |
---|