[3733] | 1 | CREATE or REPLACE FUNCTION legacy_privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ |
---|
| 2 | DECLARE |
---|
| 3 | in_priv ALIAS FOR $1; |
---|
| 4 | out_bits BIT(24); |
---|
| 5 | BEGIN |
---|
| 6 | out_bits := 0::BIT(24); |
---|
| 7 | IF in_priv ~* 'A' THEN |
---|
| 8 | out_bits = ~ out_bits; |
---|
| 9 | RETURN out_bits; |
---|
| 10 | END IF; |
---|
| 11 | |
---|
| 12 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
| 13 | -- 1 DAV:read |
---|
| 14 | -- 512 CalDAV:read-free-busy |
---|
| 15 | -- 4096 CALDAV:schedule-query-freebusy |
---|
| 16 | IF in_priv ~* 'R' THEN |
---|
| 17 | out_bits := out_bits | 4609::BIT(24); |
---|
| 18 | END IF; |
---|
| 19 | |
---|
| 20 | -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
| 21 | -- 2 DAV:write-properties |
---|
| 22 | -- 4 DAV:write-content |
---|
| 23 | -- 64 DAV:bind |
---|
| 24 | -- 128 DAV:unbind |
---|
| 25 | IF in_priv ~* 'W' THEN |
---|
| 26 | out_bits := out_bits | 198::BIT(24); |
---|
| 27 | END IF; |
---|
| 28 | |
---|
| 29 | -- 64 DAV:bind |
---|
| 30 | IF in_priv ~* 'B' THEN |
---|
| 31 | out_bits := out_bits | 64::BIT(24); |
---|
| 32 | END IF; |
---|
| 33 | |
---|
| 34 | -- 128 DAV:unbind |
---|
| 35 | IF in_priv ~* 'U' THEN |
---|
| 36 | out_bits := out_bits | 128::BIT(24); |
---|
| 37 | END IF; |
---|
| 38 | |
---|
| 39 | -- 512 CalDAV:read-free-busy |
---|
| 40 | -- 4096 CALDAV:schedule-query-freebusy |
---|
| 41 | IF in_priv ~* 'F' THEN |
---|
| 42 | out_bits := out_bits | 4608::BIT(24); |
---|
| 43 | END IF; |
---|
| 44 | |
---|
| 45 | RETURN out_bits; |
---|
| 46 | END |
---|
| 47 | $$ |
---|
| 48 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
| 49 | |
---|
| 50 | -- This legacy conversion function will eventually be removed, once all logic |
---|
| 51 | -- has been converted to use bitmaps, or to use the bits_to_priv() output. |
---|
| 52 | -- |
---|
| 53 | -- NOTE: Round-trip through this and then back through legacy_privilege_to_bits |
---|
| 54 | -- function is lossy! Through legacy_privilege_to_bits() and back through |
---|
| 55 | -- this one is not. |
---|
| 56 | -- |
---|
| 57 | CREATE or REPLACE FUNCTION bits_to_legacy_privilege( BIT(24) ) RETURNS TEXT AS $$ |
---|
| 58 | DECLARE |
---|
| 59 | in_bits ALIAS FOR $1; |
---|
| 60 | out_priv TEXT; |
---|
| 61 | BEGIN |
---|
| 62 | out_priv := ''; |
---|
| 63 | IF in_bits = (~ 0::BIT(24)) THEN |
---|
| 64 | out_priv = 'A'; |
---|
| 65 | RETURN out_priv; |
---|
| 66 | END IF; |
---|
| 67 | |
---|
| 68 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
| 69 | -- 1 DAV:read |
---|
| 70 | -- 512 CalDAV:read-free-busy |
---|
| 71 | -- 4096 CALDAV:schedule-query-freebusy |
---|
| 72 | IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN |
---|
| 73 | IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN |
---|
| 74 | out_priv := 'R'; |
---|
| 75 | ELSE |
---|
| 76 | out_priv := 'F'; |
---|
| 77 | END IF; |
---|
| 78 | END IF; |
---|
| 79 | |
---|
| 80 | -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
| 81 | -- 2 DAV:write-properties |
---|
| 82 | -- 4 DAV:write-content |
---|
| 83 | -- 64 DAV:bind |
---|
| 84 | -- 128 DAV:unbind |
---|
| 85 | IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN |
---|
| 86 | IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN |
---|
| 87 | out_priv := out_priv || 'W'; |
---|
| 88 | ELSE |
---|
| 89 | IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN |
---|
| 90 | out_priv := out_priv || 'B'; |
---|
| 91 | END IF; |
---|
| 92 | IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN |
---|
| 93 | out_priv := out_priv || 'U'; |
---|
| 94 | END IF; |
---|
| 95 | END IF; |
---|
| 96 | END IF; |
---|
| 97 | |
---|
| 98 | RETURN out_priv; |
---|
| 99 | END |
---|
| 100 | $$ |
---|
| 101 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
| 102 | |
---|
| 103 | CREATE or REPLACE FUNCTION get_permissions( INT, INT ) RETURNS TEXT AS $$ |
---|
| 104 | DECLARE |
---|
| 105 | in_from ALIAS FOR $1; |
---|
| 106 | in_to ALIAS FOR $2; |
---|
| 107 | out_confers TEXT; |
---|
| 108 | bit_confers BIT(24); |
---|
| 109 | group_role_no INT; |
---|
| 110 | tmp_txt TEXT; |
---|
| 111 | dbg TEXT DEFAULT ''; |
---|
| 112 | r RECORD; |
---|
| 113 | counter INT; |
---|
| 114 | BEGIN |
---|
| 115 | -- Self can always have full access |
---|
| 116 | IF in_from = in_to THEN |
---|
| 117 | RETURN 'A'; |
---|
| 118 | END IF; |
---|
| 119 | |
---|
| 120 | -- dbg := 'S-'; |
---|
| 121 | SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 |
---|
| 122 | WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group'); |
---|
| 123 | IF FOUND THEN |
---|
| 124 | RETURN dbg || out_confers; |
---|
| 125 | END IF; |
---|
| 126 | -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to; |
---|
| 127 | |
---|
| 128 | SELECT bit_or(r1.confers & r2.confers) INTO bit_confers |
---|
| 129 | FROM relationship r1 |
---|
| 130 | JOIN relationship r2 ON r1.to_user=r2.from_user |
---|
| 131 | WHERE r1.from_user=in_from AND r2.to_user=in_to |
---|
| 132 | AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group'); |
---|
| 133 | IF bit_confers != 0::BIT(24) THEN |
---|
| 134 | RETURN dbg || bits_to_legacy_privilege(bit_confers); |
---|
| 135 | END IF; |
---|
| 136 | |
---|
| 137 | RETURN ''; |
---|
| 138 | -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to; |
---|
| 139 | |
---|
| 140 | 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) |
---|
| 141 | WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user |
---|
| 142 | AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ; |
---|
| 143 | |
---|
| 144 | IF FOUND THEN |
---|
| 145 | -- dbg := 'H-'; |
---|
| 146 | -- RAISE NOTICE 'Permissions to shared group % ', out_confers; |
---|
| 147 | RETURN dbg || out_confers; |
---|
| 148 | END IF; |
---|
| 149 | |
---|
| 150 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
| 151 | |
---|
| 152 | RETURN ''; |
---|
| 153 | END; |
---|
| 154 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 155 | |
---|
| 156 | |
---|
| 157 | CREATE or REPLACE FUNCTION get_group_role_no() RETURNS INT AS $$ |
---|
| 158 | SELECT role_no FROM roles WHERE role_name = 'Group' |
---|
| 159 | $$ LANGUAGE 'SQL' IMMUTABLE; |
---|
| 160 | |
---|
| 161 | CREATE or REPLACE FUNCTION has_legacy_privilege( INT, TEXT, INT ) RETURNS BOOLEAN AS $$ |
---|
| 162 | DECLARE |
---|
| 163 | in_from ALIAS FOR $1; |
---|
| 164 | in_legacy_privilege ALIAS FOR $2; |
---|
| 165 | in_to ALIAS FOR $3; |
---|
| 166 | in_confers BIT(24); |
---|
| 167 | group_role_no INT; |
---|
| 168 | BEGIN |
---|
| 169 | -- Self can always have full access |
---|
| 170 | IF in_from = in_to THEN |
---|
| 171 | RETURN TRUE; |
---|
| 172 | END IF; |
---|
| 173 | |
---|
| 174 | SELECT get_group_role_no() INTO group_role_no; |
---|
| 175 | SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers; |
---|
| 176 | |
---|
| 177 | IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to |
---|
| 178 | AND (in_confers & confers) = in_confers |
---|
| 179 | AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN |
---|
| 180 | -- A direct relationship from A to B that grants sufficient |
---|
| 181 | -- RAISE NOTICE 'Permissions directly granted'; |
---|
| 182 | RETURN TRUE; |
---|
| 183 | END IF; |
---|
| 184 | |
---|
| 185 | IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user |
---|
| 186 | WHERE (in_confers & r1.confers & r2.confers) = in_confers |
---|
| 187 | AND r1.from_user=in_from AND r2.to_user=in_to |
---|
| 188 | AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN |
---|
| 189 | -- An indirect relationship from A to B via group G that grants sufficient |
---|
| 190 | -- RAISE NOTICE 'Permissions mediated via group'; |
---|
| 191 | RETURN TRUE; |
---|
| 192 | END IF; |
---|
| 193 | |
---|
| 194 | IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user |
---|
| 195 | WHERE (in_confers & r1.confers & r2.confers) = in_confers |
---|
| 196 | AND r1.from_user=in_from AND r2.from_user=in_to |
---|
| 197 | AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) |
---|
| 198 | AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN |
---|
| 199 | -- An indirect reflexive relationship from both A & B to group G which grants sufficient |
---|
| 200 | -- RAISE NOTICE 'Permissions to shared group'; |
---|
| 201 | RETURN TRUE; |
---|
| 202 | END IF; |
---|
| 203 | |
---|
| 204 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
| 205 | |
---|
| 206 | RETURN FALSE; |
---|
| 207 | END; |
---|
| 208 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 209 | |
---|
| 210 | |
---|
| 211 | -- Given a verbose DAV: or CalDAV: privilege name return the bitmask |
---|
| 212 | CREATE or REPLACE FUNCTION privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ |
---|
| 213 | DECLARE |
---|
| 214 | raw_priv ALIAS FOR $1; |
---|
| 215 | in_priv TEXT; |
---|
| 216 | BEGIN |
---|
| 217 | in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', ''))); |
---|
| 218 | IF in_priv = 'all' THEN |
---|
| 219 | RETURN ~ 0::BIT(24); |
---|
| 220 | END IF; |
---|
| 221 | |
---|
| 222 | RETURN (CASE |
---|
| 223 | WHEN in_priv = 'read' THEN 4609 -- 1 + 512 + 4096 |
---|
| 224 | WHEN in_priv = 'write' THEN 198 -- 2 + 4 + 64 + 128 |
---|
| 225 | WHEN in_priv = 'write-properties' THEN 2 |
---|
| 226 | WHEN in_priv = 'write-content' THEN 4 |
---|
| 227 | WHEN in_priv = 'unlock' THEN 8 |
---|
| 228 | WHEN in_priv = 'read-acl' THEN 16 |
---|
| 229 | WHEN in_priv = 'read-current-user-privilege-set' THEN 32 |
---|
| 230 | WHEN in_priv = 'bind' THEN 64 |
---|
| 231 | WHEN in_priv = 'unbind' THEN 128 |
---|
| 232 | WHEN in_priv = 'write-acl' THEN 256 |
---|
| 233 | WHEN in_priv = 'read-free-busy' THEN 4608 -- 512 + 4096 |
---|
| 234 | WHEN in_priv = 'schedule-deliver' THEN 7168 -- 1024 + 2048 + 4096 |
---|
| 235 | WHEN in_priv = 'schedule-deliver-invite' THEN 1024 |
---|
| 236 | WHEN in_priv = 'schedule-deliver-reply' THEN 2048 |
---|
| 237 | WHEN in_priv = 'schedule-query-freebusy' THEN 4096 |
---|
| 238 | WHEN in_priv = 'schedule-send' THEN 57344 -- 8192 + 16384 + 32768 |
---|
| 239 | WHEN in_priv = 'schedule-send-invite' THEN 8192 |
---|
| 240 | WHEN in_priv = 'schedule-send-reply' THEN 16384 |
---|
| 241 | WHEN in_priv = 'schedule-send-freebusy' THEN 32768 |
---|
| 242 | ELSE 0 END)::BIT(24); |
---|
| 243 | END |
---|
| 244 | $$ |
---|
| 245 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
| 246 | |
---|
| 247 | |
---|
| 248 | -- Given an array of verbose DAV: or CalDAV: privilege names return the bitmask |
---|
| 249 | CREATE or REPLACE FUNCTION privilege_to_bits( TEXT[] ) RETURNS BIT(24) AS $$ |
---|
| 250 | DECLARE |
---|
| 251 | raw_privs ALIAS FOR $1; |
---|
| 252 | in_priv TEXT; |
---|
| 253 | out_bits BIT(24); |
---|
| 254 | i INT; |
---|
| 255 | all BIT(24); |
---|
| 256 | start INT; |
---|
| 257 | finish INT; |
---|
| 258 | BEGIN |
---|
| 259 | out_bits := 0::BIT(24); |
---|
| 260 | all := ~ out_bits; |
---|
| 261 | SELECT array_lower(raw_privs,1) INTO start; |
---|
| 262 | SELECT array_upper(raw_privs,1) INTO finish; |
---|
| 263 | FOR i IN start .. finish LOOP |
---|
| 264 | SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits; |
---|
| 265 | IF out_bits = all THEN |
---|
| 266 | RETURN all; |
---|
| 267 | END IF; |
---|
| 268 | END LOOP; |
---|
| 269 | RETURN out_bits; |
---|
| 270 | END |
---|
| 271 | $$ |
---|
| 272 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
| 273 | |
---|
| 274 | |
---|
| 275 | -- This legacy conversion function will eventually be removed, once all logic |
---|
| 276 | -- has been converted to use bitmaps, or to use the bits_to_priv() output. |
---|
| 277 | -- |
---|
| 278 | -- NOTE: Round-trip through this and then back through privilege_to_bits |
---|
| 279 | -- function is lossy! Through privilege_to_bits() and back through |
---|
| 280 | -- this one is not. |
---|
| 281 | -- |
---|
| 282 | CREATE or REPLACE FUNCTION bits_to_privilege( BIT(24) ) RETURNS TEXT[] AS $$ |
---|
| 283 | DECLARE |
---|
| 284 | in_bits ALIAS FOR $1; |
---|
| 285 | out_priv TEXT[]; |
---|
| 286 | BEGIN |
---|
| 287 | out_priv := ARRAY[]::text[]; |
---|
| 288 | IF in_bits = (~ 0::BIT(24)) THEN |
---|
| 289 | out_priv := out_priv || ARRAY['DAV:all']; |
---|
| 290 | END IF; |
---|
| 291 | |
---|
| 292 | IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN |
---|
| 293 | IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN |
---|
| 294 | out_priv := out_priv || ARRAY['DAV:read']; |
---|
| 295 | END IF; |
---|
| 296 | IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN |
---|
| 297 | out_priv := out_priv || ARRAY['caldav:read-free-busy']; |
---|
| 298 | END IF; |
---|
| 299 | END IF; |
---|
| 300 | |
---|
| 301 | IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN |
---|
| 302 | IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN |
---|
| 303 | out_priv := out_priv || ARRAY['DAV:write']; |
---|
| 304 | ELSE |
---|
| 305 | IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN |
---|
| 306 | out_priv := out_priv || ARRAY['DAV:write-properties']; |
---|
| 307 | END IF; |
---|
| 308 | IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN |
---|
| 309 | out_priv := out_priv || ARRAY['DAV:write-content']; |
---|
| 310 | END IF; |
---|
| 311 | IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN |
---|
| 312 | out_priv := out_priv || ARRAY['DAV:bind']; |
---|
| 313 | END IF; |
---|
| 314 | IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN |
---|
| 315 | out_priv := out_priv || ARRAY['DAV:unbind']; |
---|
| 316 | END IF; |
---|
| 317 | END IF; |
---|
| 318 | END IF; |
---|
| 319 | |
---|
| 320 | IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN |
---|
| 321 | out_priv := out_priv || ARRAY['DAV:unlock']; |
---|
| 322 | END IF; |
---|
| 323 | |
---|
| 324 | IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN |
---|
| 325 | out_priv := out_priv || ARRAY['DAV:read-acl']; |
---|
| 326 | END IF; |
---|
| 327 | |
---|
| 328 | IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN |
---|
| 329 | out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set']; |
---|
| 330 | END IF; |
---|
| 331 | |
---|
| 332 | IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN |
---|
| 333 | out_priv := out_priv || ARRAY['DAV:write-acl']; |
---|
| 334 | END IF; |
---|
| 335 | |
---|
| 336 | IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN |
---|
| 337 | IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN |
---|
| 338 | out_priv := out_priv || ARRAY['caldav:schedule-deliver']; |
---|
| 339 | ELSE |
---|
| 340 | IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN |
---|
| 341 | out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite']; |
---|
| 342 | END IF; |
---|
| 343 | IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN |
---|
| 344 | out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply']; |
---|
| 345 | END IF; |
---|
| 346 | IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN |
---|
| 347 | out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy']; |
---|
| 348 | END IF; |
---|
| 349 | END IF; |
---|
| 350 | END IF; |
---|
| 351 | |
---|
| 352 | IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN |
---|
| 353 | IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN |
---|
| 354 | out_priv := out_priv || ARRAY['caldav:schedule-send']; |
---|
| 355 | ELSE |
---|
| 356 | IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN |
---|
| 357 | out_priv := out_priv || ARRAY['caldav:schedule-send-invite']; |
---|
| 358 | END IF; |
---|
| 359 | IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN |
---|
| 360 | out_priv := out_priv || ARRAY['caldav:schedule-send-reply']; |
---|
| 361 | END IF; |
---|
| 362 | IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN |
---|
| 363 | out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy']; |
---|
| 364 | END IF; |
---|
| 365 | END IF; |
---|
| 366 | END IF; |
---|
| 367 | |
---|
| 368 | RETURN out_priv; |
---|
| 369 | END |
---|
| 370 | $$ |
---|
| 371 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|