JDBC attribute repository sql search by principal

[Pablo Vidaurri]

I have a single row lookup, so i have in my config:

cas.authn.attribute-repository.jdbc[0].sql=select * from user_table where {0}

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

This seems to search by the user id entered at the login page. But I'd like to use the value from the resolved principle provided by LDAP:

cas.authn.ldap[0].principalAttributeId: uid

So user logs in with jsmith88 and ldap resolves the principle to be j.s...@example.com.

I'd like to use the principle value to look up jdbc userAttributes.

Any way to configure CAS to do that?

[Pablo Vidaurri]

Config info:

cas.authn.attribute-repository.jdbc[0].sql=select a, b, c from user_table where {0}

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

cas.authn.ldap[0].principalAttributeId: uid   <-- uid is jsmith but login user name at UI is john....@foobar.com

Looks like principle (uid) is not being used and instead the username from credentials. Is this a bug?

Log info:

2023-07-19 13:22:08,418 DEBUG [org.apereo.services.persondir.support.jdbc.SingleRowJdbcPersonAttributeDao] - <Adding attribute 'USER_ID' with value '[john....@foobar.com]' to query builder 'null'>

2023-07-19 13:22:08,429 DEBUG [org.apereo.services.persondir.support.jdbc.SingleRowJdbcPersonAttributeDao] - <Generated query builder 'sql=[USER_ID = ?] args=[john....@foobar.com]' from query Map {principal=[jsmith], Email=[john....@foobar.com], firstName=[John], GivenName=[John], lastName=[Smith], credentialClass=[UsernamePasswordCredential], credentialId=[john....@foobar.com], username=[john....@foobar.com]}.>

2023-07-19 13:22:08,430 DEBUG [org.apereo.services.persondir.support.jdbc.SingleRowJdbcPersonAttributeDao] - <Executing 'SELECT A, B, C from USER_TABLE WHERE {0}' with arguments [john....@foobar.com]>

2023-07-19 13:22:09,818 DEBUG [org.apereo.services.persondir.support.jdbc.SingleRowJdbcPersonAttributeDao] - <Executed 'SELECT A, B, C from  USER_TABLE   WHERE {0}' with arguments [john....@foobar.com] and got results []>

[Pablo Vidaurri]

Digging thru code:

SimpleUsernameAttributeProvider.java, method getUsernameAttributeValues, there is this line:

if (query.containsKey(this.usernameAttribute)) { ...}

I see I can perhaps override the username with a userNameAttribute. I have not found any config where I can define this value. 

Anyone know what property controls this?

-psv

[Anthony Oslund]

Our model cas 6.6.6 is not exactly as yours (ours is DBMS/Azure rather than LDAP) but likely parallels the issue so may provide some perspective.

Authentication model where some users authenticated with DBMS name/pass and others through Azure.  Challenge was how to make the released parameters consistent for the client application regardless of how authenticated.

For DBMS the primary id was the username, for Azure the primary id was the user's email (as the separate concept of the username was specific to the DBMS).

For DBMS name/pass not a problem as the username was already the primary id.  For Azure to get translated to username authentication, had to add in a few additional configuration settings (shown below)

# config properties used with azure and property resolution

<other azure connection properties>

...

cas.authn.pac4j.oidc[0].azure.scope=...,email

...

cas.authn.pac4j.oidc[0].azure.principal-attribute-id=email

cas.authn.authentication-attribute-release.enabled=true

# included both primary attributes in attribute release (as well as other attributes not shown)
cas.authn.attribute-repository.core.default-attributes-to-release=username,email,...

# set the expiration time to 0 to disable caching these attributes in memory,
# so they will be retrieved each time
cas.authn.attribute-repository.core.expiration-time=0

# use cascade so that the attributes from initial queries can be used as the
# query for the next repository
cas.authn.attribute-repository.core.aggregation=CASCADE

# in our case set merger to REPLACE
cas.authn.attribute-repository.core.merger=REPLACE

...

# setup an attribute repository to be used as a person directory that in
# turn is used to translate Azure release attributes into DBMS username
# attribute AFTER the Azure authentication has taken place
cas.authn.attribute-repository.jdbc[0].id=azuretostandardusername

... (plus other jdbc connection settings)

# <user record view> has been defined to return at most one row per Azure email
# and that row will be the user record associated with the email
cas.authn.attribute-repository.jdbc[0].sql=SELECT * FROM <user record view> WHERE {0}

cas.authn.attribute-repository.jdbc[0].isolate-internal-queries=false
cas.authn.attribute-repository.jdbc[0].single-row=true
cas.authn.attribute-repository.jdbc[0].require-all-attributes=false

cas.authn.attribute-repository.jdbc[0].attributes.email=email

cas.authn.attribute-repository.jdbc[0].attributes.username=username

... <other attributes from the user record view also included in the repository>

cas.authn.attribute-repository.jdbc[0].username=email

cas.authn.attribute-repository.jdbc[0].case-insensitive-query-attributes=email->LOWER

...

#
# added for person directory resolution which is used to translate
# from Azure authentication results to DBMS username attribute
#
cas.person-directory.active-attribute-repository-ids=azuretostandardusername
cas.person-directory.attribute-resolution-enabled=true

## these lines left here as documentation
## these are ignored... they are overridden by the
## cas.authn.pac4j.oidc[0].azure.principal-attribute-id=email
## defined above
##
## cas.person-directory.principal-attribute=email
## cas.person-directory.principal-attribute=unique_name

cas.person-directory.principal-resolution-conflict-strategy=first
cas.person-directory.principal-resolution-failure-fatal=false
cas.person-directory.principal-transformation.case-conversion=LOWERCASE
cas.person-directory.return-null=true

# in this case principal id refers to email from azure
cas.person-directory.use-existing-principal-id=true

Added the following to service file to treat the resulting username as the primary attribute for both

authentication approaches so that after the above attribute repository (resolution) it would always use

the username as the "usernameAttribute" (for the benefit of the client app)

Note that in this case releasing attributes from azuretostandardusername

 "usernameAttributeProvider" : {

    "@class" : "org.apereo.cas.services.PrincipalAttributeRegisteredServiceUsernameProvider",
    "usernameAttribute" : "username",
    "canonicalizationMode" : "NONE"

  },

"attributeReleasePolicy" : {
    "principalAttributesRepository" : {
      "@class" : "org.apereo.cas.authentication.principal.DefaultPrincipalAttributesRepository",
      "ignoreResolvedAttributes": false,
      "attributeRepositoryIds": ["java.util.HashSet", [ "azuretostandardusername" ]],
      "mergingStrategy" : "SOURCE"
    },
    "@class" : "org.apereo.cas.services.ReturnAllowedAttributeReleasePolicy",
    "allowedAttributes" : [ "java.util.ArrayList", [ "email", "username", ...] ]
  }

[Pablo Vidaurri]

Hi Tos...

I'm just now getting back into looking into this problem. Thank you for posting some clues that helped me get it working.

#add cn. cn is returned by LDAP that I want to search db with, add it the to the list of attributeList

cas.authn.ldap[1].principalAttributeList: attr1:attr1b,cn,attr2,attr3

cas.authn.ldap[1].principalAttributeId: cn

#USER_ID is the db column name that the SELECT statement is looking for

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

# use cn value to search db with using USER_ID column
cas.authn.attributeRepository.jdbc[0].query-attributes.cn=USER_ID
cas.authn.attributeRepository.jdbc[0].query-type=OR
cas.authn.attributeRepository.jdbc[0].requireAllAttributes=false

This is a lot of trial and error but I did need all items above to get it working.