Service Registry - Store in MySQL database

[Bob]

Hello,

We are upgrading to CAS 6.1.x.

Most things seem to work fine (LDAP and reading Service Registry from json file) but we cannot get it to save the Service Registry in a MySQL casdb.

Is there a way to manually enter a Service Registry into a MySQL database?

Running CAS has created 3 tables in our MySQL database:

regex_registered_service

regex_registered_service_regex_registered_service_property

regex_registered_service_registered_service_impl_contact

Table regex_registered_service has the following columns:

+----------------------------------+
| COLUMN_NAME                      |
+----------------------------------+
| access_strategy                  |
| attribute_release                |
| description                      |
| environments                     |
| evaluation_order                 |
| expiration_policy                |
| expression_type                  |
| id                               |
| information_Url                  |
| logo                             |
| logout_type                      |
| logout_url                       |
| mfa_policy                       |
| name                             |
| privacy_Url                      |
| proxy_policy                     |
| proxy_ticket_expiration_policy   |
| public_key                       |
| required_handlers                |
| response_Type                    |
| service_Id                       |
| service_ticket_expiration_policy |
| sso_participation_policy         |
| theme                            |
| username_attr                    |
+----------------------------------+
25 rows in set (0.00 sec)

How would I get the following json into this table?

{
  "@class" : "org.apereo.cas.services.RegexRegisteredService",
  "serviceId" : "https://localhost:9000/dashboard",
  "name" : "My App",
  "id" : 10001000,
  "description" : "My Dashboard App",
  "attributeReleasePolicy" : {
    "@class" : "org.apereo.cas.services.ReturnMappedAttributeReleasePolicy",
    "allowedAttributes" : {
      "@class" : "java.util.TreeMap",
      "memberOf" : "authorities"
    }
  },
  "evaluationOrder" : 100,
  "accessStrategy" : {
    "@class" : "org.apereo.cas.services.DefaultRegisteredServiceAccessStrategy",
    "enabled" : true,
    "ssoEnabled" : true
  }
}

Thanks in advance!

Bob

[Ray Bon]

Bob,

Are you using the cas management server, https://github.com/apereo/cas-management-overlay?

If you are, what do the logs say when you try to save?

Ray

-- 

Ray Bon

Programmer Analyst

Development Services, University Systems

2507218831 | CLE 019 | rb...@uvic.ca

I respectfully acknowledge that my place of work is located within the ancestral, traditional and unceded territory of the Songhees, Esquimalt and WSÁNEĆ Nations.

[Bob]

Hi Ray,

No, I'm currently just using the cas overlay (6.1.x).

I did try to get cas management working but had some issue with a pre-defined service registry in some kind of git repo.

Whenever I tried to enter a service via cas management, there was no option to save it to my database. All it ever did was show this 1 entry from a git repo.

SInce I did get it working (reading my json file and store it in MySQL database) without cas management for version 5.3.9, I assumed it would work for version 6 as well.

Do you think cas management is the only way to get it stored in the database? I might have another look at it then.

Thanks,

Bob

[Ray Bon]

Bob,

We are using the 5.1.5 version of cas management. You only need to upgrade it if you want newer features, etc.

I also have grumblings about the 6.x version. I put off upgrading cas management until it settles.

Ray

[Misagh Moayyed]

Something along the following lines should work:

cas.serviceRegistry.initFromJson=true

cas.serviceRegistry.json.location=file:/etc/cas/config/services

- Then, make sure your JSON files are in the above noted directory.

- Then, make sure your overlay contains a reference to the JPA service registry

(You do not need to include the JSON service registry, IIRC, in the overlay)

Then, on startup, CAS will import your JSON files from that directory over to the real (JPA) service registry.

YMMV.

[Bob]

Thanks Misagh and Ray,

I cloned the cas-overlay-template in a new location and used the settings Misagh suggested but I still have the same problem that my casdb still has an empty regex_registered_service table.

Here's what I'm using:

Here's what I'm using:

CAS Version: 6.1.0

CAS Branch: 6.1.x

CAS Commit Id: c92590730249df0cf26fd1b4bebd8aea8447b256

CAS Build Date/Time: 2019-10-28T02:43:18Z

Spring Boot Version: 2.2.0.RELEASE

Spring Version: 5.2.0.RELEASE

Java Home: C:\Programs\Java\amazon-corretto\jdk11.0.3_7

Java Vendor: Amazon.com Inc.

Java Version: 11.0.3

My dependencies in build.gradle:

dependencies {

    // Other CAS dependencies/modules may be listed here...

    compile "org.apereo.cas:cas-server-support-ldap:${project.'cas.version'}"

    compile "org.apereo.cas:cas-server-support-jdbc-drivers:${project.'cas.version'}"

    compile "org.apereo.cas:cas-server-support-jpa-service-registry:${project.'cas.version'}"

}

My minimum cas.propertie (I omitted the LDAP part since that's working fine):

cas.server.name: https://localhost:8443

cas.server.prefix: ${cas.server.name}/cas

logging.config: file:/etc/cas/config/log4j2.xml

server.ssl.key-store=file:/etc/cas/keystore.jks

server.ssl.key-store-password=xxxxxxxx

server.ssl.key-password=xxxxxxx

cas.serviceRegistry.initFromJson=true

cas.serviceRegistry.json.location=file:/etc/cas/config/services

cas.serviceRegistry.jpa.user=xxxxxxxxxx

cas.serviceRegistry.jpa.password=xxxxxxxxxxx

cas.serviceRegistry.jpa.driverClass=com.mysql.cj.jdbc.Driver

cas.serviceRegistry.jpa.url=jdbc:mysql://localhost:3306/casdb?allowPublicKeyRetrieval=true&characterEncoding=UTF-8&useSSL=FALSE

cas.serviceRegistry.jpa.dialect=org.hibernate.dialect.MySQL57InnoDBDialect

cas.serviceRegistry.jpa.ddlAuto=update

When I run CAS 6.1.0 I see: 

[org.apereo.cas.services.AbstractServicesManager] - <Loaded [1] service(s) from [EmbeddedResourceBasedServiceRegistry,JpaServiceRegistry].>

I can then use CAS to login to my application, but it does not insert any Service Registry into the MySQL casdb.

Do you see anything wrong in my setup?

Thanks,

Bob

[Ray Bon]

Bob,

You can see what SQL is being created, https://apereo.github.io/cas/6.1.x/configuration/Configuration-Properties-Common.html#hibernate--jdbc. Also turn up logging, maybe for org.springframework.orm.jpa, or add an entry for hibernate.

Ray

[Michele Melluso]

Hi Bob,

I had a similar regression from 6.0 to 6.1. The problem is that EmbeddedResourceBasedServiceRegistry is registered in the registries chain.

So, when the ServiceRegistryInitializer.java checks at row 66 if the json service exists already, it is found from EmbeddedResourceBasedServiceRegistry and then it is not added to JpaTicketRegistry.

I solved somehow by avoiding the "embeddedJsonServiceRegistryExecutionPlanConfigurer" bean, just commenting it away in CasServiceRegistryInitializationConfiguration.java

// @Bean

// @ConditionalOnMissingBean(name = "embeddedJsonServiceRegistryExecutionPlanConfigurer")

// public ServiceRegistryExecutionPlanConfigurer embeddedJsonServiceRegistryExecutionPlanConfigurer() {

// return plan -> plan.registerServiceRegistry(embeddedJsonServiceRegistry());

// }

I don't know if there will be any collateral problems, by the way now json services are correctly copied on JpaTicketRegistry.

Wouldn't know how to fix it in a clean way as to make a pr.

Hope it helps

Regards

Michele

[Bob]

Thank you Michele,

If I can disable it just once to get some data in my MySQL database that would be enough for me.

I'll try it out.

By the way, I'm currently testing with CAS 6.1.4 and don't see your code snippet so it probably changed already. Will do some digging to get the same result.

Your reply is much appreciated!

Thanks,

Bob

[Mathieu HETRU]

Hello Bob,

You should install cas-management web interface because the service entry in the db got java objects serialized in the db blob fields in the db tables.

I have not use the initFromJson feature.

Sincerly,

Mathieu

[Bob]

Hello Michele,

Finally managed to get it working by commenting out the code you mentioned. Now it does read the JSON files and store it in MySQL database.

I exported the database records as a SQL script and then reinstated the code so it should be good to go.

Thank you very much again!

Regards,

Bob

On Saturday, March 14, 2020 at 12:20:46 AM UTC+1, Michele Melluso wrote:

[Bob]

Thanks Mathieu,

I tried that also but never could get cas-management 6.1.x working (it kept using the built-in git repo, despite having properties set not to use that) with the MySQL data.

Finally I gave up on cas-management and have used the workaround from Michele instead.

Thanks,

Bob

[Michele Melluso]

Hi Bob,

I'm glad to hear it. I will try to see if it deserves a PR at least to start a discussion about it.

Regards

Michele

[Mathieu HETRU]

hello Bob,

i have the solution : remove the git feature from the war file (produced by gradlew) in order to cas-management 6.1.x works fine :

add this line in the build.gradle (in the bootWar step) :

---

excludes = ["**/cas-mgmt-config-version-control*.jar", "**/cas-mgmt-config-delegated*.jar", "**/HikariCP-java7-2.4.13.jar"]

---

Sincerly,

Mathieu

--
- Website: https://apereo.github.io/cas
- Gitter Chatroom: https://gitter.im/apereo/cas
- List Guidelines: https://goo.gl/1VRrw7
- Contributions: https://goo.gl/mh7qDG
---
You received this message because you are subscribed to the Google Groups "CAS Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cas-user+u...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/85c1a43d-a4b1-4d91-9106-3ed8a4e9d92d%40apereo.org.

[Nguyen Tran Thanh Lam]

Dear Mr Mathieu HETRU

I have tried your solution, It's working fine but the service seem store temporary on RAM.

It doesn't write to directory (/etc/cas/services-repo) or database.

This lead to service will disappear when I restart cas-management.

Thanks

Vào Th 3, 24 thg 3, 2020 vào lúc 23:13 Mathieu HETRU <ipsy...@gmail.com> đã viết:

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/CAHNwO5maybN1prurCk8g73_PbEVpk8tDQnVgbKfeXTmAZDdk5g%40mail.gmail.com.

[Mathieu HETRU]

Hello,

The next step is to configure which service store registry you want use :

- json

- jpa

You just need to install the dependencies gradle (compile and deploy) and key config in the properties file.

Mathieu

Le 25 mars 2020 à 03:21, Nguyen Tran Thanh Lam <naphalu...@gmail.com> a écrit :



To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/CAEjUsAhDRfkj6-Qw6RYnvuq_b%2BVR%2BzAa%3DYF6tH6wVWRKHvcpDA%40mail.gmail.com.

[Bob]

Thank you very much Mathieu!

That workaround fixed it for me and I now have CAS Management (6.1.0-RC4) saving the services in the MySQL database.

Much appreciated!

Regards,

Bob

To unsubscribe from this group and stop receiving emails from it, send an email to cas-...@apereo.org.

[Mathieu HETRU]

you are welcome :)

Sincerly,

Mathieu

To unsubscribe from this group and stop receiving emails from it, send an email to cas-user+u...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/25507c9d-54e8-4c95-9995-11b77564864c%40apereo.org.