OHDSI Home | Forums | Wiki | Github

Copy user/role configuration from one webAPI to another

We want to migrate the user/role configuration of one webapi instance to another. We assume that the only tables that must be copied are the sec_* tables (sec_permission, sec_role, sec_role_group, sec_role_permission, sec_user, sec_user_role). Does any other table need to be copied?

I think that’s it, but remember there are sequences invovled, so when you copy the data over, you will need to reset sequences (sec_permission_seq, for example)., by resetting the sequence to the MAX() value of the id column in the table.

I found an example from here to do this:

SELECT setval('webapi.sec_permission_seq', COALESCE((SELECT MAX(permission_id)+1 FROM webapi.sec_permission), 1), false);

You’d have to do this for each sequence (each sec_ table has a sequence, except sec_user_role, i think because that is just a xref table)

1 Like