Correcting Organizations Foreign Key

Registry v4.0.0 introduced Organizations as a new Registry object type. The table definition included in v4.0.0 had an error that was corrected in Registry v4.0.1. However, the database schema management may not correctly fix this error. Deployments that ran v4.0.0 and then upgraded to v4.0.1 should manually correct the table definition:

SQL> alter table cm_organizations drop constraint cm_organizations_organization_id_fkey;
SQL> alter table cm_organizations add constraint cm_organizations_organization_id_fkey foreign key (organization_id) references cm_organizations (id);

Correcting Group Memberships Metadata Set By BulkLoad Shell

Registry v4.0.2 fixes a bug where automatic COU groups created by BulkLoad Shell had an incorrect metadata attribute set during bulk loading, causing the group membership to be removed on a subsequent Pipeline sync operation. (Subsequent save actions on the affected CO Person Role would cause the automatic group to be recreated.)

This bug affects deployments where

  1. BulkLoad Shell was used to perform an initial bulk load, AND
  2. The CO People loaded via BulkLoad Shell were placed into COUs (ie: given CO Person Roles with a non-null cou_id) by way of an Organizational Identity Source Linkage.

Correcting the metadata attribute requires the use of the SQL terminal. First, determine the set of affected groups. This is probably something like

SQL> SELECT id,name FROM cm_co_groups WHERE auto=true AND cou_id IS NOT NULL;

However, it may be appropriate to select a smaller set of Groups if some were created after the initial bulk load. Once the correct set of groups has been identified, remove the source_org_identity_id value for CO Group Memberships associated with these groups. (warning) Do not null out this value for all group memberships, since it must be non-null for Memberships created via Org Identity Source Group Mappings.

To verify which set of Memberships are effected, use something like

SQL> SELECT id FROM cm_co_group_members WHERE co_group_id IN (SELECT id FROM cm_co_groups WHERE auto=true AND cou_id IS NOT NULL) AND source_org_identity_id IS NOT NULL;

If no rows are returned, the deployment is not affected by this issue. Otherwise, fix the metadata with something like

SQL> UPDATE cm_co_group_members SET source_org_identity_id=null WHERE co_group_id IN (SELECT id FROM cm_co_groups WHERE auto=true AND cou_id IS NOT NULL) AND source_org_identity_id IS NOT NULL;
  • No labels