1CREATE or REPLACE FUNCTION legacy_privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ 2DECLARE 3 in_priv ALIAS FOR $1; 4 out_bits BIT(24); 5BEGIN 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; 46END 47$$ 48LANGUAGE 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-- 57CREATE or REPLACE FUNCTION bits_to_legacy_privilege( BIT(24) ) RETURNS TEXT AS $$ 58DECLARE 59 in_bits ALIAS FOR $1; 60 out_priv TEXT; 61BEGIN 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; 99END 100$$ 101LANGUAGE plpgsql IMMUTABLE STRICT; 102 103CREATE or REPLACE FUNCTION get_permissions( INT, INT ) RETURNS TEXT AS $$ 104DECLARE 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; 114BEGIN 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 ''; 153END; 154$$ LANGUAGE plpgsql IMMUTABLE STRICT; 155 156 157CREATE 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 161CREATE or REPLACE FUNCTION has_legacy_privilege( INT, TEXT, INT ) RETURNS BOOLEAN AS $$ 162DECLARE 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; 168BEGIN 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; 207END; 208$$ LANGUAGE plpgsql IMMUTABLE STRICT; 209 210 211-- Given a verbose DAV: or CalDAV: privilege name return the bitmask 212CREATE or REPLACE FUNCTION privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ 213DECLARE 214 raw_priv ALIAS FOR $1; 215 in_priv TEXT; 216BEGIN 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); 243END 244$$ 245LANGUAGE plpgsql IMMUTABLE STRICT; 246 247 248-- Given an array of verbose DAV: or CalDAV: privilege names return the bitmask 249CREATE or REPLACE FUNCTION privilege_to_bits( TEXT[] ) RETURNS BIT(24) AS $$ 250DECLARE 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; 258BEGIN 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; 270END 271$$ 272LANGUAGE 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-- 282CREATE or REPLACE FUNCTION bits_to_privilege( BIT(24) ) RETURNS TEXT[] AS $$ 283DECLARE 284 in_bits ALIAS FOR $1; 285 out_priv TEXT[]; 286BEGIN 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; 369END 370$$ 371LANGUAGE plpgsql IMMUTABLE STRICT; 372