Michael Hoennig
2022-09-16 d63e3f31e926a53cbfdfb8accda77472ea66e2cc
commit | author | age
d234ac 1 --liquibase formatted sql
f2d0fb 2
03ee2c 3 -- ============================================================================
MH 4 --changeset rbac-base-REFERENCE:1 endDelimiter:--//
5 -- ----------------------------------------------------------------------------
d234ac 6 /*
f2d0fb 7
d234ac 8  */
4c403b 9 create type ReferenceType as enum ('RbacUser', 'RbacRole', 'RbacPermission');
f2d0fb 10
4c403b 11 create table RbacReference
f2d0fb 12 (
4c403b 13     uuid uuid unique default uuid_generate_v4(),
f2d0fb 14     type ReferenceType not null
MH 15 );
16
4c403b 17 create or replace function assertReferenceType(argument varchar, referenceId uuid, expectedType ReferenceType)
MH 18     returns ReferenceType
19     language plpgsql as $$
20 declare
d234ac 21     actualType ReferenceType;
4c403b 22 begin
MH 23     actualType = (select type from RbacReference where uuid = referenceId);
24     if (actualType <> expectedType) then
25         raise exception '% must reference a %, but got a %', argument, expectedType, actualType;
d234ac 26     end if;
4c403b 27     return expectedType;
MH 28 end; $$;
d234ac 29 --//
MH 30
03ee2c 31 -- ============================================================================
MH 32 --changeset rbac-base-USER:1 endDelimiter:--//
33 -- ----------------------------------------------------------------------------
d234ac 34 /*
MH 35
36  */
4c403b 37 create table RbacUser
f2d0fb 38 (
4c403b 39     uuid uuid primary key references RbacReference (uuid) on delete cascade,
f2d0fb 40     name varchar(63) not null unique
MH 41 );
e880a1 42
258f8b 43 call create_journal('RbacUser');
f2d0fb 44
4c403b 45 create or replace function createRbacUser(userName varchar)
MH 46     returns uuid
47     returns null on null input
48     language plpgsql as $$
f2d0fb 49 declare
MH 50     objectId uuid;
4c403b 51 begin
MH 52     insert
2b630a 53         into RbacReference (type)
MH 54         values ('RbacUser')
55         returning uuid into objectId;
4c403b 56     insert
2b630a 57         into RbacUser (uuid, name)
MH 58         values (objectid, userName);
f2d0fb 59     return objectId;
4c403b 60 end;
f2d0fb 61 $$;
MH 62
41d3b6 63 create or replace function createRbacUser(refUuid uuid, userName varchar)
MH 64     returns uuid
65     called on null input
66     language plpgsql as $$
67 begin
68     insert
69         into RbacReference as r (uuid, type)
322736 70         values (coalesce(refUuid, uuid_generate_v4()), 'RbacUser')
41d3b6 71         returning r.uuid into refUuid;
MH 72     insert
73         into RbacUser (uuid, name)
74         values (refUuid, userName);
75     return refUuid;
76 end;
77 $$;
78
4c403b 79 create or replace function findRbacUserId(userName varchar)
MH 80     returns uuid
81     returns null on null input
82     language sql as $$
83 select uuid from RbacUser where name = userName
f2d0fb 84 $$;
d4eeb3 85
4c403b 86 create type RbacWhenNotExists as enum ('fail', 'create');
f2d0fb 87
4c403b 88 create or replace function getRbacUserId(userName varchar, whenNotExists RbacWhenNotExists)
MH 89     returns uuid
90     returns null on null input
91     language plpgsql as $$
92 declare
f2d0fb 93     userUuid uuid;
4c403b 94 begin
d4eeb3 95     userUuid = findRbacUserId(userName);
4c403b 96     if (userUuid is null) then
MH 97         if (whenNotExists = 'fail') then
98             raise exception 'RbacUser with name="%" not found', userName;
99         end if;
100         if (whenNotExists = 'create') then
f2d0fb 101             userUuid = createRbacUser(userName);
4c403b 102         end if;
MH 103     end if;
f2d0fb 104     return userUuid;
4c403b 105 end;
f2d0fb 106 $$;
MH 107
d234ac 108 --//
1dde6b 109
03ee2c 110 -- ============================================================================
MH 111 --changeset rbac-base-OBJECT:1 endDelimiter:--//
112 -- ----------------------------------------------------------------------------
d234ac 113 /*
1dde6b 114
d234ac 115  */
4c403b 116 create table RbacObject
d234ac 117 (
4c403b 118     uuid        uuid primary key default uuid_generate_v4(),
d234ac 119     objectTable varchar(64) not null,
MH 120     unique (objectTable, uuid)
121 );
1dde6b 122
258f8b 123 call create_journal('RbacObject');
e880a1 124
0b48e8 125 --//
MH 126
127
128 -- ============================================================================
129 --changeset rbac-base-GENERATE-RELATED-OBJECT:1 endDelimiter:--//
130 -- ----------------------------------------------------------------------------
131
132 /*
133     Inserts related RbacObject for use in the BEFORE ONSERT TRIGGERs on the business objects.
134  */
135 create or replace function insertRelatedRbacObject()
4c403b 136     returns trigger
MH 137     language plpgsql
138     strict as $$
139 declare
d234ac 140     objectUuid uuid;
4c403b 141 begin
MH 142     if TG_OP = 'INSERT' then
57cf31 143         if NEW.uuid is null then
MH 144             insert
145                 into RbacObject (objectTable)
146                 values (TG_TABLE_NAME)
147                 returning uuid into objectUuid;
148             NEW.uuid = objectUuid;
149         else
150             insert
151                 into RbacObject (uuid, objectTable)
152                 values (NEW.uuid, TG_TABLE_NAME)
153                 returning uuid into objectUuid;
154         end if;
4c403b 155         return NEW;
MH 156     else
157         raise exception 'invalid usage of TRIGGER AFTER INSERT';
158     end if;
159 end; $$;
0b48e8 160
MH 161 /*
162     Deletes related RbacObject for use in the BEFORE DELETE TRIGGERs on the business objects.
163  */
164 create or replace function deleteRelatedRbacObject()
165     returns trigger
166     language plpgsql
167     strict as $$
168 begin
169     if TG_OP = 'DELETE' then
170         delete from RbacObject where rbacobject.uuid = old.uuid;
171     else
172         raise exception 'invalid usage of TRIGGER BEFORE DELETE';
173     end if;
174     return old;
175 end; $$;
176
d234ac 177
03ee2c 178 -- ============================================================================
MH 179 --changeset rbac-base-ROLE:1 endDelimiter:--//
180 -- ----------------------------------------------------------------------------
d234ac 181 /*
MH 182
183  */
184
4c403b 185 create type RbacRoleType as enum ('owner', 'admin', 'tenant');
d234ac 186
4c403b 187 create table RbacRole
d234ac 188 (
0b48e8 189     uuid       uuid primary key references RbacReference (uuid) on delete cascade initially deferred, -- initially deferred
MH 190     objectUuid uuid not null references RbacObject (uuid) initially deferred,
191     roleType   RbacRoleType not null,
57cf31 192     unique (objectUuid, roleType)
d234ac 193 );
e880a1 194
258f8b 195 call create_journal('RbacRole');
d234ac 196
4c403b 197 create type RbacRoleDescriptor as
d234ac 198 (
23796c 199     objectTable varchar(63), -- for human readability and easier debugging
4c403b 200     objectUuid  uuid,
MH 201     roleType    RbacRoleType
d234ac 202 );
MH 203
4c403b 204 create or replace function roleDescriptor(objectTable varchar(63), objectUuid uuid, roleType RbacRoleType)
MH 205     returns RbacRoleDescriptor
206     returns null on null input
ece362 207     stable leakproof
4c403b 208     language sql as $$
MH 209 select objectTable, objectUuid, roleType::RbacRoleType;
d234ac 210 $$;
1dde6b 211
4c403b 212 create or replace function createRole(roleDescriptor RbacRoleDescriptor)
MH 213     returns uuid
214     returns null on null input
215     language plpgsql as $$
f2d0fb 216 declare
MH 217     referenceId uuid;
4c403b 218 begin
MH 219     insert
2b630a 220         into RbacReference (type)
MH 221         values ('RbacRole')
222         returning uuid into referenceId;
4c403b 223     insert
2b630a 224         into RbacRole (uuid, objectUuid, roleType)
MH 225         values (referenceId, roleDescriptor.objectUuid, roleDescriptor.roleType);
f2d0fb 226     return referenceId;
4c403b 227 end;
f2d0fb 228 $$;
MH 229
230
4c403b 231 create or replace procedure deleteRole(roleUUid uuid)
MH 232     language plpgsql as $$
233 begin
ac5f19 234     --raise exception '% deleting role uuid %', currentsubjectsuuids(), roleUUid;
4c403b 235     delete from RbacRole where uuid = roleUUid;
MH 236 end;
f2d0fb 237 $$;
MH 238
322736 239 create or replace function findRoleId(roleIdName varchar)
MH 240     returns uuid
241     returns null on null input
242     language plpgsql as $$
243 declare
244     roleParts                 text;
245     roleTypeFromRoleIdName    RbacRoleType;
246     objectNameFromRoleIdName  text;
247     objectTableFromRoleIdName text;
248     objectUuidOfRole          uuid;
249     roleUuid                  uuid;
250 begin
23796c 251     -- TODO.refact: extract function toRbacRoleDescriptor(roleIdName varchar) + find other occurrences
322736 252     roleParts = overlay(roleIdName placing '#' from length(roleIdName) + 1 - strpos(reverse(roleIdName), '.'));
MH 253     objectTableFromRoleIdName = split_part(roleParts, '#', 1);
254     objectNameFromRoleIdName = split_part(roleParts, '#', 2);
255     roleTypeFromRoleIdName = split_part(roleParts, '#', 3);
256     objectUuidOfRole = findObjectUuidByIdName(objectTableFromRoleIdName, objectNameFromRoleIdName);
257
a33cb4 258     raise notice $sql$findObjectUuidByIdName('%', '%') = %;$sql$, objectTableFromRoleIdName, objectNameFromRoleIdName, objectUuidOfRole;
MH 259     raise notice 'finding %, % (%), %', objectTableFromRoleIdName, objectNameFromRoleIdName, objectUuidOfRole, roleTypeFromRoleIdName;
260
322736 261     select uuid
MH 262         from RbacRole
263         where objectUuid = objectUuidOfRole
264           and roleType = roleTypeFromRoleIdName
265         into roleUuid;
266     return roleUuid;
267 end; $$;
268
4c403b 269 create or replace function findRoleId(roleDescriptor RbacRoleDescriptor)
MH 270     returns uuid
271     returns null on null input
272     language sql as $$
273 select uuid from RbacRole where objectUuid = roleDescriptor.objectUuid and roleType = roleDescriptor.roleType;
f2d0fb 274 $$;
MH 275
4c403b 276 create or replace function getRoleId(roleDescriptor RbacRoleDescriptor, whenNotExists RbacWhenNotExists)
MH 277     returns uuid
278     returns null on null input
279     language plpgsql as $$
280 declare
f2d0fb 281     roleUuid uuid;
4c403b 282 begin
1dde6b 283     roleUuid = findRoleId(roleDescriptor);
4c403b 284     if (roleUuid is null) then
MH 285         if (whenNotExists = 'fail') then
286             raise exception 'RbacRole "%#%.%" not found', roleDescriptor.objectTable, roleDescriptor.objectUuid, roleDescriptor.roleType;
287         end if;
288         if (whenNotExists = 'create') then
1dde6b 289             roleUuid = createRole(roleDescriptor);
4c403b 290         end if;
MH 291     end if;
f2d0fb 292     return roleUuid;
4c403b 293 end;
f2d0fb 294 $$;
ac5f19 295
MH 296
297 -- ============================================================================
0b48e8 298 --changeset rbac-base-BEFORE-DELETE-ROLE-TRIGGER:1 endDelimiter:--//
ac5f19 299 -- ----------------------------------------------------------------------------
MH 300
301 /*
302     RbacRole BEFORE DELETE TRIGGER function which deletes all related roles.
303  */
0b48e8 304 create or replace function deleteRbacGrantsOfRbacRole()
ac5f19 305     returns trigger
MH 306     language plpgsql
307     strict as $$
308 begin
309     if TG_OP = 'DELETE' then
310         delete from RbacGrants g where old.uuid in (g.grantedbyroleuuid, g.ascendantuuid, g.descendantuuid);
311     else
312         raise exception 'invalid usage of TRIGGER BEFORE DELETE';
313     end if;
314     return old;
315 end; $$;
316
317 /*
318     Installs the RbacRole BEFORE DELETE TRIGGER.
319  */
0b48e8 320 create trigger deleteRbacGrantsOfRbacRole_Trigger
ac5f19 321     before delete
MH 322     on RbacRole
323     for each row
0b48e8 324 execute procedure deleteRbacGrantsOfRbacRole();
MH 325 --//
326
327
328 -- ============================================================================
329 --changeset rbac-base-BEFORE-DELETE-OBJECT-TRIGGER:1 endDelimiter:--//
330 -- ----------------------------------------------------------------------------
331
332 /*
333     RbacObject BEFORE DELETE TRIGGER function which deletes all related roles.
334  */
335 create or replace function deleteRbacRolesOfRbacObject()
336     returns trigger
337     language plpgsql
338     strict as $$
339 begin
340     if TG_OP = 'DELETE' then
341         delete from RbacPermission p where p.objectuuid = old.uuid;
342         delete from RbacRole r where r.objectUuid = old.uuid;
343     else
344         raise exception 'invalid usage of TRIGGER BEFORE DELETE';
345     end if;
346     return old;
347 end; $$;
348
349 /*
350     Installs the RbacRole BEFORE DELETE TRIGGER.
351  */
352 create trigger deleteRbacRolesOfRbacObject_Trigger
353     before delete
354     on RbacObject
355     for each row
356         execute procedure deleteRbacRolesOfRbacObject();
ac5f19 357 --//
MH 358
f2d0fb 359
03ee2c 360 -- ============================================================================
MH 361 --changeset rbac-base-PERMISSION:1 endDelimiter:--//
362 -- ----------------------------------------------------------------------------
d234ac 363 /*
MH 364
365  */
4c403b 366 create domain RbacOp as varchar(67)
MH 367     check (
368                 VALUE = '*'
369             or VALUE = 'delete'
370             or VALUE = 'edit'
371             or VALUE = 'view'
372             or VALUE = 'assume'
373             or VALUE ~ '^add-[a-z]+$'
9720b3 374             or VALUE ~ '^new-[a-z]+$'
d234ac 375         );
MH 376
4c403b 377 create table RbacPermission
MH 378 (
379     uuid       uuid primary key references RbacReference (uuid) on delete cascade,
380     objectUuid uuid   not null references RbacObject,
381     op         RbacOp not null,
d234ac 382     unique (objectUuid, op)
MH 383 );
384
258f8b 385 call create_journal('RbacPermission');
e880a1 386
03ee2c 387 create or replace function permissionExists(forObjectUuid uuid, forOp RbacOp)
4c403b 388     returns bool
MH 389     language sql as $$
390 select exists(
391            select op
392                from RbacPermission p
393                where p.objectUuid = forObjectUuid
394                  and p.op in ('*', forOp)
395            );
f2d0fb 396 $$;
MH 397
4c403b 398 create or replace function createPermissions(forObjectUuid uuid, permitOps RbacOp[])
MH 399     returns uuid[]
400     language plpgsql as $$
401 declare
402     refId         uuid;
403     permissionIds uuid[] = array []::uuid[];
404 begin
405     raise notice 'createPermission for: % %', forObjectUuid, permitOps;
406     if (forObjectUuid is null) then
407         raise exception 'forObjectUuid must not be null';
408     end if;
409     if (array_length(permitOps, 1) > 1 and '*' = any (permitOps)) then
410         raise exception '"*" operation must not be assigned along with other operations: %', permitOps;
411     end if;
412
413     for i in array_lower(permitOps, 1)..array_upper(permitOps, 1)
414         loop
415             refId = (select uuid from RbacPermission where objectUuid = forObjectUuid and op = permitOps[i]);
416             if (refId is null) then
417                 raise notice 'createPermission: % %', forObjectUuid, permitOps[i];
418                 insert
2b630a 419                     into RbacReference ("type")
MH 420                     values ('RbacPermission')
421                     returning uuid into refId;
4c403b 422                 insert
2b630a 423                     into RbacPermission (uuid, objectUuid, op)
MH 424                     values (refId, forObjectUuid, permitOps[i]);
4c403b 425             end if;
MH 426             raise notice 'addPermission: %', refId;
427             permissionIds = permissionIds || refId;
428         end loop;
429
430     raise notice 'createPermissions returning: %', permissionIds;
431     return permissionIds;
432 end;
433 $$;
434
435 create or replace function findPermissionId(forObjectUuid uuid, forOp RbacOp)
436     returns uuid
437     returns null on null input
438     stable leakproof
439     language sql as $$
440 select uuid
441     from RbacPermission p
442     where p.objectUuid = forObjectUuid
443       and p.op in ('*', forOp)
f2d0fb 444 $$;
MH 445
d234ac 446 --//
MH 447
03ee2c 448 -- ============================================================================
MH 449 --changeset rbac-base-GRANTS:1 endDelimiter:--//
450 -- ----------------------------------------------------------------------------
d234ac 451 /*
322736 452     Table to store grants / role- or permission assignments to users or roles.
d234ac 453  */
4c403b 454 create table RbacGrants
d234ac 455 (
e880a1 456     uuid                uuid primary key default uuid_generate_v4(),
ac5f19 457     grantedByRoleUuid   uuid references RbacRole (uuid),
MH 458     ascendantUuid       uuid references RbacReference (uuid),
459     descendantUuid      uuid references RbacReference (uuid),
c8e835 460     assumed             boolean not null default true,  -- auto assumed (true) vs. needs assumeRoles (false)
e880a1 461     unique (ascendantUuid, descendantUuid)
d234ac 462 );
4c403b 463 create index on RbacGrants (ascendantUuid);
MH 464 create index on RbacGrants (descendantUuid);
d234ac 465
258f8b 466 call create_journal('RbacGrants');
d234ac 467
4c403b 468 create or replace function findGrantees(grantedId uuid)
MH 469     returns setof RbacReference
470     returns null on null input
471     language sql as $$
472 select reference.*
473     from (with recursive grants as (select descendantUuid,
474                                            ascendantUuid
475                                         from RbacGrants
476                                         where descendantUuid = grantedId
477                                     union all
478                                     select "grant".descendantUuid,
479                                            "grant".ascendantUuid
480                                         from RbacGrants "grant"
481                                                  inner join grants recur on recur.ascendantUuid = "grant".descendantUuid)
482           select ascendantUuid
483               from grants) as grantee
484              join RbacReference reference on reference.uuid = grantee.ascendantUuid;
d234ac 485 $$;
MH 486
4c403b 487 create or replace function isGranted(granteeId uuid, grantedId uuid)
MH 488     returns bool
489     returns null on null input
490     language sql as $$
491 select granteeId = grantedId or granteeId in (with recursive grants as (select descendantUuid, ascendantUuid
492                                                                             from RbacGrants
493                                                                             where descendantUuid = grantedId
494                                                                         union all
495                                                                         select "grant".descendantUuid, "grant".ascendantUuid
496                                                                             from RbacGrants "grant"
497                                                                                      inner join grants recur on recur.ascendantUuid = "grant".descendantUuid)
498                                               select ascendantUuid
499                                                   from grants);
d234ac 500 $$;
ece362 501
MH 502 create or replace function isGranted(granteeIds uuid[], grantedId uuid)
503     returns bool
504     returns null on null input
505     language plpgsql as $$
506 declare
507     granteeId uuid;
508 begin
23796c 509     -- TODO.perf: needs optimization
322736 510     foreach granteeId in array granteeIds
MH 511         loop
ece362 512             if isGranted(granteeId, grantedId) then
MH 513                 return true;
514             end if;
515         end loop;
516     return false;
517 end; $$;
d234ac 518
4c403b 519 create or replace function isPermissionGrantedToSubject(permissionId uuid, subjectId uuid)
MH 520     returns BOOL
521     stable leakproof
522     language sql as $$
523 select exists(
524            select *
525                from RbacUser
526                where uuid in (with recursive grants as (select descendantUuid,
527                                                                ascendantUuid
528                                                             from RbacGrants g
529                                                             where g.descendantUuid = permissionId
530                                                         union all
531                                                         select g.descendantUuid,
532                                                                g.ascendantUuid
533                                                             from RbacGrants g
534                                                                      inner join grants recur on recur.ascendantUuid = g.descendantUuid)
535                               select ascendantUuid
536                                   from grants
537                                   where ascendantUuid = subjectId)
d234ac 538            );
MH 539 $$;
f2d0fb 540
bef358 541 create or replace function hasGlobalRoleGranted(userUuid uuid)
MH 542     returns bool
543     stable leakproof
544     language sql as $$
545 select exists(
546            select r.uuid
322736 547                from RbacGrants as g
MH 548                         join RbacRole as r on r.uuid = g.descendantuuid
549                         join RbacObject as o on o.uuid = r.objectuuid
550                where g.ascendantuuid = userUuid
551                  and o.objecttable = 'global'
bef358 552            );
MH 553 $$;
554
4c403b 555 create or replace procedure grantPermissionsToRole(roleUuid uuid, permissionIds uuid[])
MH 556     language plpgsql as $$
557 begin
558     raise notice 'grantPermissionsToRole: % -> %', roleUuid, permissionIds;
2b630a 559     if cardinality(permissionIds) = 0 then return; end if;
MH 560
4c403b 561     for i in array_lower(permissionIds, 1)..array_upper(permissionIds, 1)
MH 562         loop
563             perform assertReferenceType('roleId (ascendant)', roleUuid, 'RbacRole');
564             perform assertReferenceType('permissionId (descendant)', permissionIds[i], 'RbacPermission');
f2d0fb 565
4c403b 566             insert
c8e835 567                 into RbacGrants (ascendantUuid, descendantUuid, assumed)
MH 568                 values (roleUuid, permissionIds[i], true)
03ee2c 569             on conflict do nothing; -- allow granting multiple times
4c403b 570         end loop;
MH 571 end;
f2d0fb 572 $$;
MH 573
322736 574 create or replace procedure grantRoleToRole(subRoleId uuid, superRoleId uuid, doAssume bool = true)
4c403b 575     language plpgsql as $$
MH 576 begin
f2d0fb 577     perform assertReferenceType('superRoleId (ascendant)', superRoleId, 'RbacRole');
4c403b 578     perform assertReferenceType('subRoleId (descendant)', subRoleId, 'RbacRole');
f2d0fb 579
c8e835 580     if isGranted(subRoleId, superRoleId) then
bef358 581         raise exception '[400] Cyclic role grant detected between % and %', subRoleId, superRoleId;
4c403b 582     end if;
f2d0fb 583
4c403b 584     insert
c8e835 585         into RbacGrants (ascendantuuid, descendantUuid, assumed)
MH 586         values (superRoleId, subRoleId, doAssume)
03ee2c 587     on conflict do nothing; -- allow granting multiple times
4c403b 588 end; $$;
f2d0fb 589
1dd631 590
MH 591 create or replace procedure grantRoleToRole(subRole RbacRoleDescriptor, superRole RbacRoleDescriptor, doAssume bool = true)
592     language plpgsql as $$
593 declare
594     superRoleId uuid;
595     subRoleId uuid;
596 begin
597     superRoleId := findRoleId(superRole);
598     subRoleId := findRoleId(subRole);
599
600     perform assertReferenceType('superRoleId (ascendant)', superRoleId, 'RbacRole');
601     perform assertReferenceType('subRoleId (descendant)', subRoleId, 'RbacRole');
602
603     if isGranted(subRoleId, superRoleId) then
604         raise exception '[400] Cyclic role grant detected between % and %', subRoleId, superRoleId;
605     end if;
606
607     insert
608         into RbacGrants (ascendantuuid, descendantUuid, assumed)
609         values (superRoleId, subRoleId, doAssume)
610     on conflict do nothing; -- allow granting multiple times
611     delete from RbacGrants where ascendantUuid = superRoleId and descendantUuid = subRoleId;
612     insert
613         into RbacGrants (ascendantuuid, descendantUuid, assumed)
614         values (superRoleId, subRoleId, doAssume); -- allow granting multiple times
615 end; $$;
616
4c403b 617 create or replace procedure revokeRoleFromRole(subRoleId uuid, superRoleId uuid)
MH 618     language plpgsql as $$
619 begin
f2d0fb 620     perform assertReferenceType('superRoleId (ascendant)', superRoleId, 'RbacRole');
4c403b 621     perform assertReferenceType('subRoleId (descendant)', subRoleId, 'RbacRole');
f2d0fb 622
1dd631 623     if (isGranted(superRoleId, subRoleId)) then
4c403b 624         delete from RbacGrants where ascendantUuid = superRoleId and descendantUuid = subRoleId;
MH 625     end if;
626 end; $$;
377b63 627
1dd631 628 create or replace procedure revokeRoleFromRole(subRole RbacRoleDescriptor, superRole RbacRoleDescriptor)
MH 629     language plpgsql as $$
630 declare
631     superRoleId uuid;
632     subRoleId uuid;
633 begin
634     superRoleId := findRoleId(superRole);
635     subRoleId := findRoleId(subRole);
636
637     perform assertReferenceType('superRoleId (ascendant)', superRoleId, 'RbacRole');
638     perform assertReferenceType('subRoleId (descendant)', subRoleId, 'RbacRole');
639
640     if (isGranted(superRoleId, subRoleId)) then
641         delete from RbacGrants where ascendantUuid = superRoleId and descendantUuid = subRoleId;
642     else
643         raise exception 'cannot revoke role % (%) from % (% because it is not granted',
644             subRole, subRoleId, superRole, superRoleId;
645     end if;
646 end; $$;
647
03ee2c 648 -- ============================================================================
MH 649 --changeset rbac-base-QUERY-ACCESSIBLE-OBJECT-UUIDS:1 endDelimiter:--//
650 -- ----------------------------------------------------------------------------
d234ac 651 /*
d4eeb3 652
d234ac 653  */
4c403b 654 create or replace function queryAccessibleObjectUuidsOfSubjectIds(
MH 655     requiredOp RbacOp,
656     forObjectTable varchar, -- reduces the result set, but is not really faster when used in restricted view
657     subjectIds uuid[],
658     maxObjects integer = 8000)
659     returns setof uuid
660     returns null on null input
661     language plpgsql as $$
662 declare
663     foundRows bigint;
664 begin
665     return query select distinct perm.objectUuid
666                      from (with recursive grants as (select descendantUuid, ascendantUuid, 1 as level
667                                                          from RbacGrants
322736 668                                                          where assumed
4c403b 669                                                            and ascendantUuid = any (subjectIds)
MH 670                                                      union
671                                                      distinct
672                                                      select "grant".descendantUuid, "grant".ascendantUuid, level + 1 as level
673                                                          from RbacGrants "grant"
674                                                                   inner join grants recur on recur.descendantUuid = "grant".ascendantUuid
322736 675                                                          where assumed)
4c403b 676                            select descendantUuid
MH 677                                from grants) as granted
678                               join RbacPermission perm
679                                    on granted.descendantUuid = perm.uuid and perm.op in ('*', requiredOp)
680                               join RbacObject obj on obj.uuid = perm.objectUuid and obj.objectTable = forObjectTable
681                      limit maxObjects + 1;
f2d0fb 682
4c403b 683     foundRows = lastRowCount();
MH 684     if foundRows > maxObjects then
bef358 685         raise exception '[400] Too many accessible objects, limit is %, found %.', maxObjects, foundRows
4c403b 686             using
MH 687                 errcode = 'P0003',
688                 hint = 'Please assume a sub-role and try again.';
689     end if;
690 end;
f2d0fb 691 $$;
MH 692
d234ac 693 --//
d4eeb3 694
03ee2c 695 -- ============================================================================
MH 696 --changeset rbac-base-QUERY-GRANTED-PERMISSIONS:1 endDelimiter:--//
697 -- ----------------------------------------------------------------------------
d234ac 698 /*
06996e 699     Returns all permissions accessible to the given subject UUID (user or role).
d234ac 700  */
06996e 701 create or replace function queryPermissionsGrantedToSubjectId(subjectId uuid)
4c403b 702     returns setof RbacPermission
MH 703     strict
704     language sql as $$
322736 705     -- @formatter:off
06996e 706 select *
4c403b 707     from RbacPermission
06996e 708     where uuid in (
MH 709             with recursive grants as (
710                 select distinct descendantUuid, ascendantUuid
711                     from RbacGrants
712                     where ascendantUuid = subjectId
713                 union all
714                 select "grant".descendantUuid, "grant".ascendantUuid
715                     from RbacGrants "grant"
716                     inner join grants recur on recur.descendantUuid = "grant".ascendantUuid
717             )
718             select descendantUuid
719                 from grants
720         );
721 -- @formatter:on
f2d0fb 722 $$;
d234ac 723 --//
f2d0fb 724
03ee2c 725 -- ============================================================================
MH 726 --changeset rbac-base-QUERY-USERS-WITH-PERMISSION-FOR-OBJECT:1 endDelimiter:--//
727 -- ----------------------------------------------------------------------------
1dde6b 728 /*
06996e 729     Returns all user UUIDs which have any permission for the given object UUID.
d234ac 730  */
f2d0fb 731
4c403b 732 create or replace function queryAllRbacUsersWithPermissionsFor(objectId uuid)
MH 733     returns setof RbacUser
734     returns null on null input
735     language sql as $$
736 select *
737     from RbacUser
7869d0 738     where uuid in (
MH 739         -- @formatter:off
740         with recursive grants as (
741             select descendantUuid, ascendantUuid
742                 from RbacGrants
743                 where descendantUuid = objectId
744             union all
745             select "grant".descendantUuid, "grant".ascendantUuid
746                 from RbacGrants "grant"
747                 inner join grants recur on recur.ascendantUuid = "grant".descendantUuid
748         )
749         -- @formatter:on
750         select ascendantUuid
751             from grants);
f2d0fb 752 $$;
d234ac 753 --//
f2d0fb 754
a478fe 755
MH 756 -- ============================================================================
03ee2c 757 --changeset rbac-base-PGSQL-ROLES:1 endDelimiter:--//
MH 758 -- ----------------------------------------------------------------------------
a478fe 759
2b630a 760 create role admin;
MH 761 grant all privileges on all tables in schema public to admin;
a478fe 762
2b630a 763 create role restricted;
MH 764 grant all privileges on all tables in schema public to restricted;
a478fe 765
ece362 766 --//