Migrating CAS 4.x to 5.1 - Problem with jdbc attribute mapping

[SebastianU]

Hello,

I'stuck by migrating the cas configuration from 4.x version to 5.1.

In 4.x I got the following configuration for the attributes:

  <bean id="primaryPrincipalResolver"

    class="org.jasig.cas.authentication.principal.PersonDirectoryPrincipalResolver">

    <property name="attributeRepository" ref="attributeRepository"/>

  </bean>

  <bean id="attributeRepository"

    class="org.jasig.services.persondir.support.jdbc.MultiRowJdbcPersonAttributeDao">

    <constructor-arg index="0" ref="dataSource"/>

    <constructor-arg index="1"

      value="SELECT 'USER_ROLE' as role_name, FUNCTION as ROLENAME FROM FUNCTIONS WHERE  {0}"/>

    <property name="queryAttributeMapping">

      <map>

        <entry key="username" value="ID"/>

      </map>

    </property>

    <property name="nameValueColumnMappings">

      <map>

        <entry key="role_name" value="ROLENAME"/>

      </map>

    </property>

  </bean>

  <bean id="serviceRegistryDao" class="org.jasig.cas.services.InMemoryServiceRegistryDaoImpl"

    p:registeredServices-ref="registeredServicesList"/>

  <util:list id="registeredServicesList">

    <bean class="org.jasig.cas.services.RegisteredServiceImpl">

      <property name="id" value="0"/>

      <property name="name" value="HTTPS Services"/>

      <property name="description" value="YOUR HTTP Service"/>

      <property name="serviceId" value="https://**"/>

      <property name="allowedAttributes">

        <list>

          <value>USER_ROLE</value>

        </list>

      </property>

    </bean>

  </util:list>

Now I'm trying to configure CAS 5.1 to use the same functionality and I'm stuck...

Log:

DEBUG [org.apereo.services.persondir.support.jdbc.MultiRowJdbcPersonAttributeDao] (default task-19) Executed 'SELECT  'USER_ROLE' as role_name, FUNCTION  as ROLENAME FROM FUNCTIONS  WHERE {0}' with arguments [casuser] and got results [{ID=casuser, ROLE_NAME=USER_ROLE, ROLENAME=EDITOR}, {ID=casuser, ROLE_NAME=USER_ROLE, ROLENAME=VIEWER}]

2017-09-14 18:29:24,391 DEBUG [org.apereo.services.persondir.support.MergingPersonAttributeDaoImpl] (default task-19) Retrieved attributes='[NamedPersonImpl[name=CASUSER,attributes={}]]' for query='{username=[casuser]}', isFirstQuery=false, currentlyConsidering='org.apereo.services.persondir.support.jdbc.MultiRowJdbcPersonAttributeDao@7698881', resultAttributes='null'

DEBUG [org.apereo.services.persondir.support.MergingPersonAttributeDaoImpl] (default task-19) Aggregated search results '[NamedPersonImpl[name=CASUSER,attributes={}]]' for query='{username=[casuser]}'

DEBUG [org.apereo.services.persondir.support.CachingPersonAttributeDaoImpl] (default task-19) Retrieved query from wrapped IPersonAttributeDao and stored in cache for scopedTarget.attributeRepository. key='-265766846|-265796123', results='[NamedPersonImpl[name=CASUSER,attributes={}]]'

DEBUG [org.apereo.services.persondir.support.CachingPersonAttributeDaoImpl.statistics] (default task-19) Cache Stats scopedTarget.attributeRepository: queries=1, hits=0, misses=1

DEBUG [org.apereo.cas.authentication.principal.resolvers.PersonDirectoryPrincipalResolver] (default task-19) Principal id [casuser] did not specify any attributes

DEBUG [org.apereo.cas.authentication.principal.resolvers.PersonDirectoryPrincipalResolver] (default task-19) Returning the principal with id [casuser] without any attributes

My configuration cas.properties is:

# config Authentication Attributes

cas.authn.attributeRepository.expireInMinutes=30

cas.authn.attributeRepository.maximumCacheSize=10000

cas.authn.attributeRepository.merger=MERGE

cas.authn.attributeRepository.jdbc[0].attributes.uid=ROLENAME

cas.authn.attributeRepository.jdbc[0].singleRow=false

cas.authn.attributeRepository.jdbc[0].order=0

cas.authn.attributeRepository.jdbc[0].requireAllAttributes=true

cas.authn.attributeRepository.jdbc[0].caseCanonicalization=UPPER

# cas.authn.attributeRepository.jdbc[0].queryType=OR|AND

# Used only when there is a mapping of many rows to one user

cas.authn.attributeRepository.jdbc[0].columnMappings.role_name=ROLENAME

# cas.authn.attributeRepository.jdbc[0].columnMappings.columnAttrName2=columnAttrValue2

# cas.authn.attributeRepository.jdbc[0].columnMappings.columnAttrName3=columnAttrValue3

cas.authn.attributeRepository.jdbc[0].sql=SELECT  'USER_ROLE' as role_name, FUNCTION  as ROLENAME FROM FUNCTIONS  WHERE {0}

cas.authn.attributeRepository.jdbc[0].username=ID

cas.authn.attributeRepository.jdbc[0].isolateInternalQueries=false

cas.authn.attributeRepository.jdbc[0].failFast=true

cas.authn.attributeRepository.jdbc[0].isolationLevelName=ISOLATION_READ_COMMITTED

cas.authn.attributeRepository.jdbc[0].leakThreshold=10

cas.authn.attributeRepository.jdbc[0].propagationBehaviorName=PROPAGATION_REQUIRED

cas.authn.attributeRepository.jdbc[0].batchSize=1

cas.authn.attributeRepository.jdbc[0].autocommit=false

cas.authn.attributeRepository.jdbc[0].idleTimeout=5000

cas.authn.attributeRepository.jdbc[0].pool.suspension=false

cas.authn.attributeRepository.jdbc[0].pool.minSize=6

cas.authn.attributeRepository.jdbc[0].pool.maxSize=18

cas.authn.attributeRepository.jdbc[0].pool.maxWait=2000

cas.authn.attributeRepository.defaultAttributesToRelease=USER_ROLE,role_name

What am I missing?

Help appreciated!

Sebastian

[Andy Ng]

Hi Sebastian,

From dev of CAS, here's is an tutorial on how to set up jdbc in CAS 5.1.x: https://apereo.github.io/2017/02/22/cas51-dbauthn-tutorial/

Just for more reference, here is my settings:

cas.authn.jdbc.query[0].sql=SELECT * FROM test_users WHERE uid=?

cas.authn.jdbc.query[0].url=jdbc:mysql://mysql.yoursite.com/test

cas.authn.jdbc.query[0].dialect=org.hibernate.dialect.MySQLDialect

cas.authn.jdbc.query[0].user=test

cas.authn.jdbc.query[0].password=testdb

cas.authn.jdbc.query[0].driverClass=com.mysql.jdbc.Driver

cas.authn.jdbc.query[0].fieldPassword=psw

I can see that the main different between me and you is that you are using {0} and I am using ? as the input value, maybe that's the problem? Or you take a step back and try the tutorial, make an working jdbc connection, then resume to making your own connection works again.

Hope this is helpful to you!
- Andy

[SebastianU]

Hi!

thanks for the link!

Got it to work with a little trying.

First I got confused because the field and attributes names in this tutorial are the same...

Lack of documentation on cas attributes... 

Sebastian

[Andy Ng]

Hi,
Yeah I also agree that some part of the doc is still not written in depth.

I actually need to understand some of the attributes by reading the source code of cas.

- Andy