Ok, this is the updated migration script content that I tested on a local dev instance of Postgresql which I have 95% confidence that you can run in your own environment.
Replace the contents of the file: db/migration/postgresql/V2.8.0.20201022120031__concept_ancestor_and_descendants.sql
with the following:
CREATE TEMP TABLE temp_migration (
from_perm_id int,
new_value character varying(255)
);
INSERT INTO temp_migration (from_perm_id, new_value)
SELECT sp.id as from_id,
REPLACE('vocabulary:%s:concept:*:ancestorAndDescendant:get', '%s', REPLACE(REPLACE(value, 'source:', ''), ':access', '')) as new_value
FROM ${ohdsiSchema}.sec_permission sp
WHERE sp.value LIKE 'source:%:access';
INSERT INTO ${ohdsiSchema}.sec_permission (id, value)
SELECT nextval('${ohdsiSchema}.sec_permission_id_seq'), new_value
FROM temp_migration;
INSERT INTO ${ohdsiSchema}.sec_role_permission (id,role_id, permission_id)
SELECT nextval('${ohdsiSchema}.sec_role_permission_sequence'),
srp.role_id,
sp.id as permission_id
FROM temp_migration m
JOIN ${ohdsiSchema}.sec_permission sp on m.new_value = sp.value
JOIN ${ohdsiSchema}.sec_role_permission srp on m.from_perm_id = srp.permission_id;
This is what it is doing:
First, it creates a temp_migration temp table to store the conversion of existing permissions that match source:%:access and stores the original permission_id for source access, and generates a new permission string based on the REPLACE. We are doing this so that we can take all the existing sources you’ve created permissions for, and introduce a new permission that looks like ancestorAndDescendant:get.
Next, we’re inserting new sec_permission records by getting a new permission_id (via the sec_role_permission_sequence) and using the generated ancestorAndDescendant permission for each source.
Finally, we need to assign the same roles that are assigned to the permission for source:%:access. So, we do a 3-way join on temp_migration (to get the map between the source:%:access permission id, and the new permission value), a join to sec_permission to get the new permission_id for the new ancestorAndDescendant permission, and a join to sec_role_permission so we can find all the roles that were assigned to the source:%:access so we can copy those role assignments to the new ancestorAndDescendant permission.
Whew! Hope that all made sense, but I would appreciate it if you could replace the above migration script with your existing one, then rebuild WebAPI and try to migrate again. If it works, we’ll take this updated migration script and release a hotfix.
We may get new errors with later migrations, but I believe this will get us past this migration step.
-Chris
