Migrating Services from 5.2.x to 6.5.x

[Trevor Fong]

Hi Everyone,

Along the same lines as https://groups.google.com/a/apereo.org/d/msgid/cas-user/75ef5b42-d3b7-366b-8b5b-c784e578b5a2%40ndsu.edu?utm_medium=email&utm_source=footer - I'm trying to migrate services from a 5.2.x install with a JPA service registry to a 6.5.x JPA service registry.  The database schemas are totally different between the two.  The 6.5.x registered_services table is very straight forward, but the 5.2.x schema consists of several relational tables with some kind of encoding on some of the columns.  

I've looked at the blog post from Fawnoos but it seems to be for 5.3 - would this be applicable to 5.2?  (https://fawnoos.com/2021/01/19/cas53-service-registry-migration-to-cas63/)

Would anyone have any suggestions for migrating services from 5.2.x to 6.5.x?  

Does anyone know what kind of encoding is applied to the data in 5.2.x?  It's not Base64.

Perhaps some way to decode and collapse the 5.2.x relational data to 6.5.x format?

Thanks a lot,

Trev

[Andrea Colajacomo]

Hi Trevor,

how do you resolve finally ?

We have the same situation starting from 6.1 to 6.5

Bye

[Trevor Fong]

Hi Andrea,

Our JPA service registries for 5.2 and 6.5 are both in an Oracle database; separate schemas.

After a lot of back and forth, I ended up reverse-engineering the 5.2 field structure and exported just the bits that we needed as JSON fields to the 6.5 database.  

The following SQL does the translation we needed.  You might need to tweak it a bit to do what you need:

insert into registered_services
select 
ID,
'{"@class":"org.apereo.cas.services.RegexRegisteredService","serviceId":"' || SERVICEID || '","name":"' || NAME || '","id":' || ID || ',"description":"' || DESCRIPTION || '","theme":"ubc_cas","proxyTicketExpirationPolicy":{"@class":"org.apereo.cas.services.DefaultRegisteredServiceProxyTicketExpirationPolicy"},"serviceTicketExpirationPolicy":{"@class":"org.apereo.cas.services.DefaultRegisteredServiceServiceTicketExpirationPolicy"},"singleSignOnParticipationPolicy":{"@class":"org.apereo.cas.services.ChainingRegisteredServiceSingleSignOnParticipationPolicy"},"evaluationOrder":' || EVALUATION_ORDER || ',"singleSignOnParticipationPolicy":{"@class":"org.apereo.cas.services.ChainingRegisteredServiceSingleSignOnParticipationPolicy"},"multifactorPolicy":{"@class":"org.apereo.cas.services.DefaultRegisteredServiceMultifactorPolicy","multifactorAuthenticationProviders":["java.util.HashSet",["mfa-duo"]],"failureMode":"OPEN"' 
||
case when regexp_like(to_char(MFA_POLICY), '^.*bypassEnabled.*principalAttributeValueToMatchq[[:cntrl:]]~[[:cntrl:]][[:cntrl:]]xp' || chr(1) || '.*$') then
   ',"bypassEnabled":true'
   --''
else
   ''
end   
|| '},"attributeReleasePolicy":{"@class":"org.apereo.cas.services.ReturnAllowedAttributeReleasePolicy","allowedAttributes":["java.util.ArrayList",[' ||
case when length(substr(ATTRIBUTE_RELEASE, instr(ATTRIBUTE_RELEASE, 'w' || chr(4) || chr(0) || chr(0) || chr(0))+5))=2 then
   ''
else
   regexp_replace(regexp_replace(regexp_replace(substr(ATTRIBUTE_RELEASE, instr(ATTRIBUTE_RELEASE, 'w' || chr(4) || chr(0) || chr(0) || chr(0))+5), 't[[:cntrl:]][[:cntrl:]]', '","'), '^' || chr(9) || '",'), '.$') || '"'
end 
|| ']]}}' as body,
EVALUATION_ORDER,
0 as EVALUATION_PRIORITY,
NAME,
SERVICEID
from cas5_auth.REGEXREGISTEREDSERVICE

Note that "org.apereo.cas.services.RegexRegisteredService" will become deprecated after 6.6.x so you may need to find out what the replacement will be.  Currently 6.6.x doesn't work for me as I also get the "InvalidTicket" errors that someone else noted.

Thanks,

Trev

[Andrea Colajacomo]

Great, thank you 👍