| commit | author | age | ||
| a33cb4 | 1 | --liquibase formatted sql |
| MH | 2 | |
| 3 | -- ============================================================================ | |
| d63e3f | 4 | --changeset test-customer-rbac-OBJECT:1 endDelimiter:--// |
| a33cb4 | 5 | -- ---------------------------------------------------------------------------- |
| d63e3f | 6 | call generateRelatedRbacObject('test_customer'); |
| a33cb4 | 7 | --// |
| d63e3f | 8 | |
| a33cb4 | 9 | |
| MH | 10 | -- ============================================================================ |
| 11 | --changeset test-customer-rbac-ROLE-DESCRIPTORS:1 endDelimiter:--// | |
| 12 | -- ---------------------------------------------------------------------------- | |
| d63e3f | 13 | call generateRbacRoleDescriptors('testCustomer', 'test_customer'); |
| a33cb4 | 14 | --// |
| MH | 15 | |
| 16 | ||
| 17 | -- ============================================================================ | |
| 18 | --changeset test-customer-rbac-ROLES-CREATION:1 endDelimiter:--// | |
| 19 | -- ---------------------------------------------------------------------------- | |
| 20 | ||
| 21 | /* | |
| 22 | Creates the roles and their assignments for a new customer for the AFTER INSERT TRIGGER. | |
| 23 | */ | |
| 24 | ||
| 25 | create or replace function createRbacRolesForTestCustomer() | |
| 26 | returns trigger | |
| 27 | language plpgsql | |
| 28 | strict as $$ | |
| 29 | declare | |
| 30 | testCustomerOwnerUuid uuid; | |
| 31 | customerAdminUuid uuid; | |
| 32 | begin | |
| 33 | if TG_OP <> 'INSERT' then | |
| 34 | raise exception 'invalid usage of TRIGGER AFTER INSERT'; | |
| 35 | end if; | |
| 36 | ||
| 37 | -- the owner role with full access for Hostsharing administrators | |
| 38 | testCustomerOwnerUuid = createRole( | |
| 39 | testCustomerOwner(NEW), | |
| 40 | grantingPermissions(forObjectUuid => NEW.uuid, permitOps => array ['*']), | |
| da793e | 41 | beneathRole(globalAdmin()) |
| a33cb4 | 42 | ); |
| MH | 43 | |
| 44 | -- the admin role for the customer's admins, who can view and add products | |
| 45 | customerAdminUuid = createRole( | |
| 46 | testCustomerAdmin(NEW), | |
| 47 | grantingPermissions(forObjectUuid => NEW.uuid, permitOps => array ['view', 'add-package']), | |
| 48 | -- NO auto assume for customer owner to avoid exploding permissions for administrators | |
| 49 | withUser(NEW.adminUserName, 'create'), -- implicitly ignored if null | |
| da793e | 50 | grantedByRole(globalAdmin()) |
| a33cb4 | 51 | ); |
| MH | 52 | |
| 53 | -- allow the customer owner role (thus administrators) to assume the customer admin role | |
| 54 | call grantRoleToRole(customerAdminUuid, testCustomerOwnerUuid, false); | |
| 55 | ||
| 56 | -- the tenant role which later can be used by owners+admins of sub-objects | |
| 57 | perform createRole( | |
| 58 | testCustomerTenant(NEW), | |
| 59 | grantingPermissions(forObjectUuid => NEW.uuid, permitOps => array ['view']) | |
| 60 | ); | |
| 61 | ||
| 62 | return NEW; | |
| 63 | end; $$; | |
| 64 | ||
| 65 | /* | |
| 66 | An AFTER INSERT TRIGGER which creates the role structure for a new customer. | |
| 67 | */ | |
| 68 | ||
| 69 | drop trigger if exists createRbacRolesForTestCustomer_Trigger on test_customer; | |
| 70 | create trigger createRbacRolesForTestCustomer_Trigger | |
| 71 | after insert | |
| 72 | on test_customer | |
| 73 | for each row | |
| 74 | execute procedure createRbacRolesForTestCustomer(); | |
| 75 | --// | |
| 76 | ||
| 77 | ||
| 78 | -- ============================================================================ | |
| 79 | --changeset test-customer-rbac-IDENTITY-VIEW:1 endDelimiter:--// | |
| 80 | -- ---------------------------------------------------------------------------- | |
| 81 | ||
| 82 | /* | |
| 83 | Creates a view to the customer main table which maps the identifying name | |
| 84 | (in this case, the prefix) to the objectUuid. | |
| 85 | */ | |
| 86 | drop view if exists test_customer_iv; | |
| 87 | create or replace view test_customer_iv as | |
| 88 | select target.uuid, target.prefix as idName | |
| 89 | from test_customer as target; | |
| 23796c | 90 | -- TODO.spec: Is it ok that everybody has access to this information? |
| a33cb4 | 91 | grant all privileges on test_customer_iv to restricted; |
| MH | 92 | |
| 93 | /* | |
| 94 | Returns the objectUuid for a given identifying name (in this case the prefix). | |
| 95 | */ | |
| 96 | create or replace function test_customerUuidByIdName(idName varchar) | |
| 97 | returns uuid | |
| 98 | language sql | |
| 99 | strict as $$ | |
| 100 | select uuid from test_customer_iv iv where iv.idName = test_customerUuidByIdName.idName; | |
| 101 | $$; | |
| 102 | ||
| 103 | /* | |
| 104 | Returns the identifying name for a given objectUuid (in this case the prefix). | |
| 105 | */ | |
| 106 | create or replace function test_customerIdNameByUuid(uuid uuid) | |
| 107 | returns varchar | |
| 108 | language sql | |
| 109 | strict as $$ | |
| 110 | select idName from test_customer_iv iv where iv.uuid = test_customerIdNameByUuid.uuid; | |
| 111 | $$; | |
| 112 | --// | |
| 113 | ||
| 114 | ||
| 115 | -- ============================================================================ | |
| 116 | --changeset test-customer-rbac-RESTRICTED-VIEW:1 endDelimiter:--// | |
| 117 | -- ---------------------------------------------------------------------------- | |
| 118 | /* | |
| 119 | Creates a view to the customer main table with row-level limitation | |
| 120 | based on the 'view' permission of the current user or assumed roles. | |
| 121 | */ | |
| 122 | set session session authorization default; | |
| 123 | drop view if exists test_customer_rv; | |
| 124 | create or replace view test_customer_rv as | |
| 125 | select target.* | |
| 126 | from test_customer as target | |
| 127 | where target.uuid in (select queryAccessibleObjectUuidsOfSubjectIds('view', 'test_customer', currentSubjectsUuids())); | |
| 128 | grant all privileges on test_customer_rv to restricted; | |
| 129 | --// | |
| 130 | ||
| 131 | ||
| 132 | -- ============================================================================ | |
| 133 | --changeset test-customer-rbac-ADD-CUSTOMER:1 endDelimiter:--// | |
| 134 | -- ---------------------------------------------------------------------------- | |
| 135 | /* | |
| 136 | Creates a global permission for add-customer and assigns it to the hostsharing admins role. | |
| 137 | */ | |
| 138 | do language plpgsql $$ | |
| 139 | declare | |
| 140 | addCustomerPermissions uuid[]; | |
| 141 | globalObjectUuid uuid; | |
| 142 | globalAdminRoleUuid uuid ; | |
| 143 | begin | |
| 144 | call defineContext('granting global add-customer permission to global admin role', null, null, null); | |
| 145 | ||
| da793e | 146 | globalAdminRoleUuid := findRoleId(globalAdmin()); |
| a33cb4 | 147 | globalObjectUuid := (select uuid from global); |
| MH | 148 | addCustomerPermissions := createPermissions(globalObjectUuid, array ['add-customer']); |
| 149 | call grantPermissionsToRole(globalAdminRoleUuid, addCustomerPermissions); | |
| 150 | end; | |
| 151 | $$; | |
| 152 | ||
| 153 | /** | |
| 154 | Used by the trigger to prevent the add-customer to current user respectively assumed roles. | |
| 155 | */ | |
| 156 | create or replace function addTestCustomerNotAllowedForCurrentSubjects() | |
| 157 | returns trigger | |
| 158 | language PLPGSQL | |
| 159 | as $$ | |
| 160 | begin | |
| 161 | raise exception '[403] add-customer not permitted for %', | |
| 162 | array_to_string(currentSubjects(), ';', 'null'); | |
| 163 | end; $$; | |
| 164 | ||
| 165 | /** | |
| 166 | Checks if the user or assumed roles are allowed to add a new customer. | |
| 167 | */ | |
| 168 | create trigger test_customer_insert_trigger | |
| 169 | before insert | |
| 170 | on test_customer | |
| 171 | for each row | |
| 2afdb3 | 172 | when ( not hasGlobalPermission('add-customer') ) |
| a33cb4 | 173 | execute procedure addTestCustomerNotAllowedForCurrentSubjects(); |
| MH | 174 | --// |
| 175 | ||
