src/main/resources/db/changelog/050-rbac-base.sql
@@ -614,17 +614,64 @@ on conflict do nothing; -- allow granting multiple times end; $$; create or replace procedure grantRoleToRole(subRole RbacRoleDescriptor, superRole RbacRoleDescriptor, doAssume bool = true) language plpgsql as $$ declare superRoleId uuid; subRoleId uuid; begin superRoleId := findRoleId(superRole); subRoleId := findRoleId(subRole); perform assertReferenceType('superRoleId (ascendant)', superRoleId, 'RbacRole'); perform assertReferenceType('subRoleId (descendant)', subRoleId, 'RbacRole'); if isGranted(subRoleId, superRoleId) then raise exception '[400] Cyclic role grant detected between % and %', subRoleId, superRoleId; end if; insert into RbacGrants (ascendantuuid, descendantUuid, assumed) values (superRoleId, subRoleId, doAssume) on conflict do nothing; -- allow granting multiple times delete from RbacGrants where ascendantUuid = superRoleId and descendantUuid = subRoleId; insert into RbacGrants (ascendantuuid, descendantUuid, assumed) values (superRoleId, subRoleId, doAssume); -- allow granting multiple times end; $$; create or replace procedure revokeRoleFromRole(subRoleId uuid, superRoleId uuid) language plpgsql as $$ begin perform assertReferenceType('superRoleId (ascendant)', superRoleId, 'RbacRole'); perform assertReferenceType('subRoleId (descendant)', subRoleId, 'RbacRole'); if (isGranted(subRoleId, superRoleId)) then if (isGranted(superRoleId, subRoleId)) then delete from RbacGrants where ascendantUuid = superRoleId and descendantUuid = subRoleId; end if; end; $$; create or replace procedure revokeRoleFromRole(subRole RbacRoleDescriptor, superRole RbacRoleDescriptor) language plpgsql as $$ declare superRoleId uuid; subRoleId uuid; begin superRoleId := findRoleId(superRole); subRoleId := findRoleId(subRole); perform assertReferenceType('superRoleId (ascendant)', superRoleId, 'RbacRole'); perform assertReferenceType('subRoleId (descendant)', subRoleId, 'RbacRole'); if (isGranted(superRoleId, subRoleId)) then delete from RbacGrants where ascendantUuid = superRoleId and descendantUuid = subRoleId; else raise exception 'cannot revoke role % (%) from % (% because it is not granted', subRole, subRoleId, superRole, superRoleId; end if; end; $$; -- ============================================================================ --changeset rbac-base-QUERY-ACCESSIBLE-OBJECT-UUIDS:1 endDelimiter:--// -- ---------------------------------------------------------------------------- src/main/resources/db/changelog/208-hs-office-contact-test-data.sql
@@ -57,10 +57,18 @@ do language plpgsql $$ begin -- TODO: use better names call createHsOfficeContactTestData('first contact'); call createHsOfficeContactTestData('second contact'); call createHsOfficeContactTestData('third contact'); call createHsOfficeContactTestData('forth contact'); call createHsOfficeContactTestData('fifth contact'); call createHsOfficeContactTestData('sixth contact'); call createHsOfficeContactTestData('eighth contact'); call createHsOfficeContactTestData('ninth contact'); call createHsOfficeContactTestData('tenth contact'); call createHsOfficeContactTestData('eleventh contact'); call createHsOfficeContactTestData('twelfth contact'); end; $$; --// src/main/resources/db/changelog/213-hs-office-person-rbac.sql
@@ -114,11 +114,11 @@ /* Returns the objectUuid for a given identifying name (in this case the prefix). */ create or replace function hsOfficePersonUuidByIdName(idName varchar) create or replace function hs_office_personUuidByIdName(idName varchar) returns uuid language sql strict as $$ select uuid from hs_office_person_iv iv where iv.idName = hsOfficePersonUuidByIdName.idName; select uuid from hs_office_person_iv iv where iv.idName = hs_office_personUuidByIdName.idName; $$; /* src/main/resources/db/changelog/223-hs-office-partner-rbac.sql
@@ -11,7 +11,7 @@ --changeset hs-office-partner-rbac-ROLE-DESCRIPTORS:1 endDelimiter:--// -- ---------------------------------------------------------------------------- create or replace function HsOfficePartnerOwner(partner hs_office_partner) create or replace function hsOfficePartnerOwner(partner hs_office_partner) returns RbacRoleDescriptor language plpgsql strict as $$ @@ -19,7 +19,7 @@ return roleDescriptor('hs_office_partner', partner.uuid, 'owner'); end; $$; create or replace function HsOfficePartnerAdmin(partner hs_office_partner) create or replace function hsOfficePartnerAdmin(partner hs_office_partner) returns RbacRoleDescriptor language plpgsql strict as $$ @@ -27,7 +27,7 @@ return roleDescriptor('hs_office_partner', partner.uuid, 'admin'); end; $$; create or replace function HsOfficePartnerTenant(partner hs_office_partner) create or replace function hsOfficePartnerTenant(partner hs_office_partner) returns RbacRoleDescriptor language plpgsql strict as $$ @@ -42,47 +42,76 @@ -- ---------------------------------------------------------------------------- /* Creates the roles and their assignments for a new partner for the AFTER INSERT TRIGGER. Creates and updates the roles and their assignments for partner entities. */ create or replace function createRbacRolesForHsOfficePartner() create or replace function hsOfficePartnerRbacRolesTrigger() returns trigger language plpgsql strict as $$ declare ownerRole uuid; adminRole uuid; person hs_office_person; contact hs_office_contact; hsOfficePartnerTenant RbacRoleDescriptor; ownerRole uuid; adminRole uuid; oldPerson hs_office_person; newPerson hs_office_person; oldContact hs_office_contact; newContact hs_office_contact; begin if TG_OP <> 'INSERT' then raise exception 'invalid usage of TRIGGER AFTER INSERT'; hsOfficePartnerTenant := hsOfficePartnerTenant(NEW); select * from hs_office_person as p where p.uuid = NEW.personUuid into newPerson; select * from hs_office_contact as c where c.uuid = NEW.contactUuid into newContact; if TG_OP = 'INSERT' then -- the owner role with full access for the global admins ownerRole = createRole( hsOfficePartnerOwner(NEW), grantingPermissions(forObjectUuid => NEW.uuid, permitOps => array ['*']), beneathRole(globalAdmin()) ); -- the admin role with full access for the global admins adminRole = createRole( hsOfficePartnerAdmin(NEW), grantingPermissions(forObjectUuid => NEW.uuid, permitOps => array ['edit']), beneathRole(ownerRole) ); -- the tenant role for those related users who can view the data perform createRole( hsOfficePartnerTenant, grantingPermissions(forObjectUuid => NEW.uuid, permitOps => array ['view']), beneathRoles(array[hsOfficePartnerAdmin(NEW), hsOfficePersonAdmin(newPerson), hsOfficeContactAdmin(newContact)]), withSubRoles(array[hsOfficePersonTenant(newPerson), hsOfficeContactTenant(newContact)]) ); elsif TG_OP = 'UPDATE' then if OLD.personUuid <> NEW.personUuid then select * from hs_office_person as p where p.uuid = OLD.personUuid into oldPerson; call revokeRoleFromRole( hsOfficePartnerTenant, hsOfficePersonAdmin(oldPerson) ); call grantRoleToRole( hsOfficePartnerTenant, hsOfficePersonAdmin(newPerson) ); call revokeRoleFromRole( hsOfficePersonTenant(oldPerson), hsOfficePartnerTenant ); call grantRoleToRole( hsOfficePersonTenant(newPerson), hsOfficePartnerTenant ); end if; if OLD.contactUuid <> NEW.contactUuid then select * from hs_office_contact as c where c.uuid = OLD.contactUuid into oldContact; call revokeRoleFromRole( hsOfficePartnerTenant, hsOfficeContactAdmin(oldContact) ); call grantRoleToRole( hsOfficePartnerTenant, hsOfficeContactAdmin(newContact) ); call revokeRoleFromRole( hsOfficeContactTenant(oldContact), hsOfficePartnerTenant ); call grantRoleToRole( hsOfficeContactTenant(newContact), hsOfficePartnerTenant ); end if; else raise exception 'invalid usage of TRIGGER'; end if; select * from hs_office_person as p where p.uuid = NEW.personUuid into person; select * from hs_office_contact as c where c.uuid = NEW.contactUuid into contact; -- the owner role with full access for the global admins ownerRole = createRole( HsOfficePartnerOwner(NEW), grantingPermissions(forObjectUuid => NEW.uuid, permitOps => array ['*']), beneathRole(globalAdmin()) ); -- the admin role with full access for the global admins adminRole = createRole( HsOfficePartnerAdmin(NEW), grantingPermissions(forObjectUuid => NEW.uuid, permitOps => array ['edit']), beneathRole(ownerRole) ); -- the tenant role for those related users who can view the data perform createRole( HsOfficePartnerTenant(NEW), grantingPermissions(forObjectUuid => NEW.uuid, permitOps => array ['view']), beneathRoles(array[HsOfficePartnerAdmin(NEW), hsOfficePersonAdmin(person), hsOfficeContactAdmin(contact)]), withSubRoles(array[hsOfficePersonTenant(person), hsOfficeContactTenant(contact)]) ); return NEW; end; $$; @@ -90,12 +119,20 @@ /* An AFTER INSERT TRIGGER which creates the role structure for a new customer. */ create trigger createRbacRolesForHsOfficePartner_Trigger after insert on hs_office_partner for each row execute procedure createRbacRolesForHsOfficePartner(); execute procedure hsOfficePartnerRbacRolesTrigger(); /* An AFTER UPDATE TRIGGER which updates the role structure of a customer. */ create trigger updateRbacRolesForHsOfficePartner_Trigger after update on hs_office_partner for each row execute procedure hsOfficePartnerRbacRolesTrigger(); --// @@ -189,6 +226,7 @@ execute function insertHsOfficePartner(); --// -- ============================================================================ --changeset hs-office-partner-rbac-INSTEAD-OF-DELETE-TRIGGER:1 endDelimiter:--// -- ---------------------------------------------------------------------------- @@ -202,12 +240,11 @@ returns trigger language plpgsql as $$ begin if hasGlobalRoleGranted(currentUserUuid()) or old.uuid in (select queryAccessibleObjectUuidsOfSubjectIds('delete', 'hs_office_partner', currentSubjectsUuids())) then delete from hs_office_partner c where c.uuid = old.uuid; if old.uuid in (select queryAccessibleObjectUuidsOfSubjectIds('delete', 'hs_office_partner', currentSubjectsUuids())) then delete from hs_office_partner p where p.uuid = old.uuid; return old; end if; raise exception '[403] User % not allowed to delete partner uuid %', currentUser(), old.uuid; raise exception '[403] Subject % is not allowed to delete partner uuid %', currentSubjectsUuids(), old.uuid; end; $$; /* @@ -220,6 +257,46 @@ execute function deleteHsOfficePartner(); --/ -- ============================================================================ --changeset hs-office-partner-rbac-INSTEAD-OF-UPDATE-TRIGGER:1 endDelimiter:--// -- ---------------------------------------------------------------------------- /** Instead of update trigger function for hs_office_partner_rv. Checks if the current subject (user / assumed role) has the permission to update the row. */ create or replace function updateHsOfficePartner() returns trigger language plpgsql as $$ begin if old.uuid in (select queryAccessibleObjectUuidsOfSubjectIds('edit', 'hs_office_partner', currentSubjectsUuids())) then update hs_office_partner set personUuid = new.personUuid, contactUuid = new.contactUuid, registrationOffice = new.registrationOffice, registrationNumber = new.registrationNumber, birthday = new.birthday, birthName = new.birthName, dateOfDeath = new.dateOfDeath where uuid = old.uuid; return old; end if; raise exception '[403] Subject % is not allowed to update partner uuid %', currentSubjectsUuids(), old.uuid; end; $$; /* Creates an instead of delete trigger for the hs_office_partner_rv view. */ create trigger updateHsOfficePartner_Trigger instead of update on hs_office_partner_rv for each row execute function updateHsOfficePartner(); --/ -- ============================================================================ --changeset hs-office-partner-rbac-NEW-CONTACT:1 endDelimiter:--// -- ---------------------------------------------------------------------------- src/test/java/net/hostsharing/hsadminng/hs/office/partner/HsOfficePartnerControllerAcceptanceTest.java
@@ -300,6 +300,7 @@ // @formatter:on // finally, the partner is actually updated context.define("superuser-alex@hostsharing.net"); assertThat(partnerRepo.findByUuid(givenPartner.getUuid())).isPresent().get() .matches(person -> { assertThat(person.getPerson().getTradeName()).isEqualTo("Ostfriesische Kuhhandel OHG"); src/test/java/net/hostsharing/hsadminng/hs/office/partner/HsOfficePartnerRepositoryIntegrationTest.java
@@ -3,7 +3,6 @@ import net.hostsharing.hsadminng.context.Context; import net.hostsharing.hsadminng.context.ContextBasedTest; import net.hostsharing.hsadminng.hs.office.contact.HsOfficeContactRepository; import net.hostsharing.hsadminng.hs.office.person.HsOfficePersonEntity; import net.hostsharing.hsadminng.hs.office.person.HsOfficePersonRepository; import net.hostsharing.hsadminng.rbac.rbacgrant.RawRbacGrantRepository; import net.hostsharing.hsadminng.rbac.rbacrole.RawRbacRoleRepository; @@ -21,6 +20,7 @@ import javax.persistence.EntityManager; import javax.servlet.http.HttpServletRequest; import java.time.LocalDate; import java.util.HashSet; import java.util.List; import java.util.Set; @@ -76,11 +76,11 @@ // when final var result = attempt(em, () -> { final var newPartner = HsOfficePartnerEntity.builder() final var newPartner = toCleanup(HsOfficePartnerEntity.builder() .uuid(UUID.randomUUID()) .person(givenPerson) .contact(givenContact) .build(); .build()); return partnerRepo.save(newPartner); }); @@ -102,11 +102,11 @@ attempt(em, () -> { final var givenPerson = personRepo.findPersonByOptionalNameLike("Erben Bessler").get(0); final var givenContact = contactRepo.findContactByOptionalLabelLike("forth contact").get(0); final var newPartner = HsOfficePartnerEntity.builder() final var newPartner = toCleanup(HsOfficePartnerEntity.builder() .uuid(UUID.randomUUID()) .person(givenPerson) .contact(givenContact) .build(); .build()); return partnerRepo.save(newPartner); }); @@ -148,7 +148,11 @@ final var result = partnerRepo.findPartnerByOptionalNameLike(null); // then allThesePartnersAreReturned(result, "First Impressions GmbH", "Ostfriesische Kuhhandel OHG", "Rockshop e.K."); allThesePartnersAreReturned( result, "partner(Ostfriesische Kuhhandel OHG, third contact)", "partner(Rockshop e.K., second contact)", "partner(First Impressions GmbH, first contact)"); } @Test @@ -160,7 +164,7 @@ final var result = partnerRepo.findPartnerByOptionalNameLike(null); // then: exactlyThesePartnersAreReturned(result, "First Impressions GmbH"); exactlyThesePartnersAreReturned(result, "partner(First Impressions GmbH, first contact)"); } } @@ -173,10 +177,119 @@ context("superuser-alex@hostsharing.net"); // when final var result = partnerRepo.findPartnerByOptionalNameLike("Ostfriesische"); final var result = partnerRepo.findPartnerByOptionalNameLike("third contact"); // then exactlyThesePartnersAreReturned(result, "Ostfriesische Kuhhandel OHG"); exactlyThesePartnersAreReturned(result, "partner(Ostfriesische Kuhhandel OHG, third contact)"); } } @Nested class UpdatePartner { @Test public void hostsharingAdmin_withoutAssumedRole_canUpdateArbitraryPartner() { // given context("superuser-alex@hostsharing.net"); final var givenPartner = givenSomeTemporaryPartnerBessler("fifth contact"); assertThatPartnerIsVisibleForUserWithRole( givenPartner, "hs_office_person#ErbenBesslerMelBessler.admin"); assertThatPartnerActuallyInDatabase(givenPartner); context("superuser-alex@hostsharing.net"); final var givenNewPerson = personRepo.findPersonByOptionalNameLike("Ostfriesische Kuhhandel OHG").get(0); final var givenNewContact = contactRepo.findContactByOptionalLabelLike("sixth contact").get(0); // when final var result = jpaAttempt.transacted(() -> { context("superuser-alex@hostsharing.net"); givenPartner.setContact(givenNewContact); givenPartner.setPerson(givenNewPerson); givenPartner.setDateOfDeath(LocalDate.parse("2022-09-15")); return toCleanup(partnerRepo.save(givenPartner)); }); // then result.assertSuccessful(); assertThatPartnerIsVisibleForUserWithRole( result.returnedValue(), "global#global.admin"); assertThatPartnerIsVisibleForUserWithRole( result.returnedValue(), "hs_office_person#OstfriesischeKuhhandelOHG.admin"); assertThatPartnerIsNotVisibleForUserWithRole( result.returnedValue(), "hs_office_person#ErbenBesslerMelBessler.admin"); partnerRepo.deleteByUuid(givenPartner.getUuid()); } @Test public void personAdmin_canNotUpdateRelatedPartner() { // given context("superuser-alex@hostsharing.net"); final var givenPartner = givenSomeTemporaryPartnerBessler("eighth"); assertThatPartnerIsVisibleForUserWithRole( givenPartner, "hs_office_person#ErbenBesslerMelBessler.admin"); assertThatPartnerActuallyInDatabase(givenPartner); // when final var result = jpaAttempt.transacted(() -> { context("superuser-alex@hostsharing.net", "hs_office_person#ErbenBesslerMelBessler.admin"); givenPartner.setDateOfDeath(LocalDate.parse("2022-09-15")); return partnerRepo.save(givenPartner); }); // then result.assertExceptionWithRootCauseMessage(JpaSystemException.class, "[403] Subject ", " is not allowed to update partner uuid"); } @Test public void contactAdmin_canNotUpdateRelatedPartner() { // given context("superuser-alex@hostsharing.net"); final var givenPartner = givenSomeTemporaryPartnerBessler("ninth"); assertThatPartnerIsVisibleForUserWithRole( givenPartner, "hs_office_contact#ninthcontact.admin"); assertThatPartnerActuallyInDatabase(givenPartner); // when final var result = jpaAttempt.transacted(() -> { context("superuser-alex@hostsharing.net", "hs_office_contact#ninthcontact.admin"); givenPartner.setDateOfDeath(LocalDate.parse("2022-09-15")); return partnerRepo.save(givenPartner); }); // then result.assertExceptionWithRootCauseMessage(JpaSystemException.class, "[403] Subject ", " is not allowed to update partner uuid"); } private void assertThatPartnerActuallyInDatabase(final HsOfficePartnerEntity saved) { final var found = partnerRepo.findByUuid(saved.getUuid()); assertThat(found).isNotEmpty().get().isNotSameAs(saved).usingRecursiveComparison().isEqualTo(saved); } private void assertThatPartnerIsVisibleForUserWithRole( final HsOfficePartnerEntity entity, final String assumedRoles) { jpaAttempt.transacted(() -> { context("superuser-alex@hostsharing.net", assumedRoles); assertThatPartnerActuallyInDatabase(entity); }).assertSuccessful(); } private void assertThatPartnerIsNotVisibleForUserWithRole( final HsOfficePartnerEntity entity, final String assumedRoles) { jpaAttempt.transacted(() -> { context("superuser-alex@hostsharing.net", assumedRoles); final var found = partnerRepo.findByUuid(entity.getUuid()); assertThat(found).isEmpty(); }).assertSuccessful(); } } @@ -187,7 +300,7 @@ public void globalAdmin_withoutAssumedRole_canDeleteAnyPartner() { // given context("superuser-alex@hostsharing.net", null); final var givenPartner = givenSomeTemporaryPartnerBessler(); final var givenPartner = givenSomeTemporaryPartnerBessler("tenth"); // when final var result = jpaAttempt.transacted(() -> { @@ -207,7 +320,7 @@ public void nonGlobalAdmin_canNotDeleteTheirRelatedPartner() { // given context("superuser-alex@hostsharing.net", null); final var givenPartner = toCleanup(givenSomeTemporaryPartnerBessler()); final var givenPartner = givenSomeTemporaryPartnerBessler("eleventh"); // when final var result = jpaAttempt.transacted(() -> { @@ -220,7 +333,7 @@ // then result.assertExceptionWithRootCauseMessage( JpaSystemException.class, "[403] User person-ErbenBesslerMelBessler@example.com not allowed to delete partner"); "[403] Subject ", " not allowed to delete partner"); assertThat(jpaAttempt.transacted(() -> { context("superuser-alex@hostsharing.net"); return partnerRepo.findByUuid(givenPartner.getUuid()); @@ -233,7 +346,7 @@ context("superuser-alex@hostsharing.net"); final var initialRoleNames = Array.from(roleNamesOf(rawRoleRepo.findAll())); final var initialGrantNames = Array.from(grantDisplaysOf(rawGrantRepo.findAll())); final var givenPartner = givenSomeTemporaryPartnerBessler(); final var givenPartner = givenSomeTemporaryPartnerBessler("twelfth"); assumeThat(rawRoleRepo.findAll().size()).as("unexpected number of roles created") .isEqualTo(initialRoleNames.length + 3); assumeThat(rawGrantRepo.findAll().size()).as("unexpected number of grants created") @@ -253,16 +366,18 @@ } } private HsOfficePartnerEntity givenSomeTemporaryPartnerBessler() { private HsOfficePartnerEntity givenSomeTemporaryPartnerBessler(final String contact) { return jpaAttempt.transacted(() -> { context("superuser-alex@hostsharing.net"); final var givenPerson = personRepo.findPersonByOptionalNameLike("Erben Bessler").get(0); final var givenContact = contactRepo.findContactByOptionalLabelLike("forth contact").get(0); final var givenContact = contactRepo.findContactByOptionalLabelLike(contact).get(0); final var newPartner = HsOfficePartnerEntity.builder() .uuid(UUID.randomUUID()) .person(givenPerson) .contact(givenContact) .build(); toCleanup(newPartner); return partnerRepo.save(newPartner); }).assertSuccessful().returnedValue(); @@ -278,23 +393,22 @@ context("superuser-alex@hostsharing.net", null); tempPartners.forEach(tempPartner -> { System.out.println("DELETING temporary partner: " + tempPartner.getDisplayName()); final var count = partnerRepo.deleteByUuid(tempPartner.getUuid()); assertThat(count).isGreaterThan(0); if ( tempPartner.getContact().getLabel().equals("sixth contact")) { toString(); } partnerRepo.deleteByUuid(tempPartner.getUuid()); }); } void exactlyThesePartnersAreReturned(final List<HsOfficePartnerEntity> actualResult, final String... partnerTradeNames) { void exactlyThesePartnersAreReturned(final List<HsOfficePartnerEntity> actualResult, final String... partnerNames) { assertThat(actualResult) .hasSize(partnerTradeNames.length) .extracting(HsOfficePartnerEntity::getPerson) .extracting(HsOfficePersonEntity::getTradeName) .containsExactlyInAnyOrder(partnerTradeNames); .extracting(HsOfficePartnerEntity::getDisplayName) .containsExactlyInAnyOrder(partnerNames); } void allThesePartnersAreReturned(final List<HsOfficePartnerEntity> actualResult, final String... partnerTradeNames) { void allThesePartnersAreReturned(final List<HsOfficePartnerEntity> actualResult, final String... partnerNames) { assertThat(actualResult) .extracting(HsOfficePartnerEntity::getPerson) .extracting(HsOfficePersonEntity::getTradeName) .contains(partnerTradeNames); .extracting(HsOfficePartnerEntity::getDisplayName) .contains(partnerNames); } }
