CAS 5 Database Authentication Tutorial

The blog is managed and hosted on GitHub. If you wish to update the contents of this post or if you have found an inaccuracy and wish to make corrections, we recommend that you please submit a pull request to this repository.

This is a short and sweet tutorial on how to configure CAS to authenticate against a database and then resolve/release attributes. Most of the material is based on the available documentation.

This tutorial specifically focuses on:

  • CAS 5.1.0-RC2-SNAPSHOT
  • HSQLDB 2.3.4
  • Java 8
  • Apache Tomcat 8.5.11


To keep things rather simple, we’ll be installing an instance of HSQLDB. For this exercise, I am running 2.3.4, though note that the recipe is for the most part the same when dealing with other database instances as well, whether installed manually or run in a dockerized fashion.

So running HSQL shows me:

[Server@2ff4acd0]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@2ff4acd0]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@2ff4acd0]: Startup sequence initiated from main() method
[Server@2ff4acd0]: Could not load properties from file
[Server@2ff4acd0]: Using cli/default properties only
[Server@2ff4acd0]: Initiating startup sequence...
[Server@2ff4acd0]: Server socket opened successfully in 46 ms.
[Server@2ff4acd0]: Database [index=0, id=0, db=file:mydb, alias=xdb] opened successfully in 349 ms.
[Server@2ff4acd0]: Startup sequence completed in 396 ms.
[Server@2ff4acd0]: 2017-02-21 13:44:37.717 HSQLDB server 2.3.4 is online on port 9001
[Server@2ff4acd0]: To close normally, connect and execute SHUTDOWN SQL
[Server@2ff4acd0]: From command line, use [Ctrl]+[C] to abort abruptly

Great. Moving on…

Create Schema

In my setup, I have two tables: one called USERS where user accounts are kept and another called USERATTRS where user attributes are kept. My USERS table is rather simple, but the USERATTRS follows something of a multi-row setup. You want to learn more about this setup here.

So here goes the SQL:


  attrname VARCHAR(50) NOT NULL,
  attrvalue VARCHAR(50) NOT NULL


VALUES ('mmoayyed', 'TheBestPasswordEver');

INSERT INTO USERATTRS (uid,  attrname, attrvalue)
VALUES ('mmoayyed', 'firstname', 'Misagh');

INSERT INTO USERATTRS (uid, attrname, attrvalue)
VALUES ('mmoayyed', 'lastname', 'Moayyed');

INSERT INTO USERATTRS (uid, attrname, attrvalue)
VALUES ('mmoayyed', 'phone', '+13476452319');

Note that for the time being, I am just keeping the password as plain-text in the table. No encoding or anything has taken place.

Deploy CAS

Hop over to the overlay installation and get CAS built and deployed. The CAS version I am using today is 5.1.0-RC2-SNAPSHOT. It does not matter whether you end up using Maven or Gradle. Choose what fits you best. When you have a baseline functioning build, continue on.

Configure CAS

Follow the steps described here to add the needed CAS modules. Once the module/dependency is added to your build, hop over to the settings page and add the properties.

Note that I did not have to add any additional JARs and such for database drivers. CAS ships with a few automatically and by default

For this tutorial, this is what I actually needed to make this work:

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

I also need to disable static authentication. It would also be very nice if I could turn on DEBUG logs and see what CAS attempts to do:

Build and Deploy

Once you get CAS built and deployed, logs should indicate something like this:

2017-02-21 14:20:18,267 DEBUG [] - <No password encoder shall be created given the requested encoder type [NONE]>
2017-02-21 14:20:18,277 DEBUG [org.apereo.cas.adaptors.jdbc.config.CasJdbcAuthenticationConfiguration] - <Created authentication handler [QueryDatabaseAuthenticationHandler] to handle database url at [jdbc:hsqldb:hsql://localhost:9001/xdb]>

Log in with mmoayyed and TheBestPasswordEver and you should be in. Viola!

Password Encoding

As an extra bonus exercise, let’s turn on MD5 password encoding. The MD5 hash of TheBestPasswordEver is ca541f57a3041c3b85c553d12d3e64a8.

So we will update the database accordingly.

UPDATE USERS SET psw='ca541f57a3041c3b85c553d12d3e64a8' WHERE uid='mmoayyed';

Then configure CAS to handle MD5 password encoding:


Build and Deploy

Once you get CAS built and deployed, logs should indicate something like this:

2017-02-21 14:44:31,884 DEBUG [] - <Creating default password encoder with encoding alg [MD5] and character encoding [UTF-8]>

Build and deploy. Log in with mmoayyed and TheBestPasswordEver and you should be in. Logs may indicate:

2017-02-21 14:45:55,517 DEBUG [org.apereo.cas.util.crypto.DefaultPasswordEncoder] - <Encoded password via algorithm [MD5] and character-encoding [UTF-8] is [ca541f57a3041c3b85c553d12d3e64a8]>
2017-02-21 14:45:55,517 DEBUG [org.apereo.cas.util.crypto.DefaultPasswordEncoder] - <Provided password does match the encoded password>
2017-02-21 14:45:55,519 DEBUG [org.apereo.cas.authentication.AbstractAuthenticationManager] - <Authentication handler [QueryDatabaseAuthenticationHandler] successfully authenticated [mmoayyed]>

Good job! Lets get some attributes now.


Because the USERATTRS follows something of a multi-row setup, we want to make sure CAS can understand the specifics of this schema model. Today, CAS is unable to retrieve attributes as part of authentication directly so we need to set up a separate attribute repository instance that CAS will contact once the user is fully authenticated. In our case, the attribute repository is the same database instance. So the configuration may look something like this:

cas.authn.attributeRepository.jdbc[0].sql=SELECT * FROM USERATTRS WHERE {0}

Once CAS understands the schema, we should then specify which attributes really should be retrieved by CAS.


Note how I am skipping over phone.

The above says, Retrieve attributes firstname and lastname from the repositories and keep them as they are. If we wanted to, we could virtually rename the attributes to for instance TheFir$tN@me and simpleL@stnam3.

Release Attributes

There are multiple ways of releasing attributes. For this tutorial, I am going to release them globally to all applications:


Note how I am skipping over phone.

Build and Deploy

For this to actually be tested, we need a client to which we can release attributes, right? You can use whatever client/application you like, as long as it’s able to retrieve attributes. I ended up using this. When attempting to access the application, I get redirected to CAS. Once I log in and return, I see the following in the CAS logs on startup:

2017-02-21 14:54:04,885 DEBUG [org.apereo.cas.config.CasPersonDirectoryConfiguration] - <Configured multi-row JDBC attribute repository for [jdbc:hsqldb:hsql://localhost:9001/xdb]>
2017-02-21 14:54:04,889 DEBUG [org.apereo.cas.config.CasPersonDirectoryConfiguration] - <Configured multi-row JDBC column mappings for [jdbc:hsqldb:hsql://localhost:9001/xdb] are [{attrname=attrvalue}]>
2017-02-21 14:54:04,890 DEBUG [org.apereo.cas.config.CasPersonDirectoryConfiguration] - <Configured result attribute mapping for [jdbc:hsqldb:hsql://localhost:9001/xdb] to be [{firstname=firstname, lastname=lastname}]>

Which shows that CAS has been able to understand the schema and map columns to attributes. Logging into the client application also shows me:



I hope this brief tutorial was of some assistance to you. Remember that the point here is not to enumerate best practices and such. It’s just to show the possibilities. It’s important that you start off simple and make changes one step at a time. Once you have a functional environment, you can gradually and slowly add customizations to move files, tables and queries around.

Misagh Moayyed

Related Posts

CAS 6.1.0 RC3 Feature Release which I present an overview of CAS 6.1.0 RC3 release.

Apereo CAS 6.1.x - Credential Caching & Proxy AuthN

Learn how you may configure Apereo CAS to capture and cache the credential's password and the proxy-granting ticket in proxy authentication scenarios, pass them along to applications as regular attributes/claims. We will also be reviewing a handful of attribute release strategies that specifically affect authentication attributes, conveying metadata about the authentication event itself.

Apereo CAS 6.1.x - Attribute Repositories w/ Person Directory

An overview of CAS attribute repositories and strategies on how to fetch attributes from a variety of sources in addition to the authentication source, merge and combine attributes from said sources to ultimately release them to applications with a fair bit of caching.

Apereo CAS 6.1.x - Building CAS Feature Modules

An overview of how various CAS features modules today can be changed and tested from the perspective of a CAS contributor working on the codebase itself to handle a feature request, bug fix, etc.

CAS 6.1.0 RC2 Feature Release which I present an overview of CAS 6.1.0 RC2 release.

Apereo CAS - Riffing on Attribute Release Policies

Learn how to release the kraken of attributes to CAS clients, relying parties and service providers using a variety of attribute release policies and authentication protocols, sampled and collected here to fun and profit.

Apereo CAS - Delegated Authentication to SAML2 Identity Providers

Learn how your Apereo CAS deployment may be configured to delegate authentication to an external SAML2 identity provider.

Apereo CAS - Custom Login Fields w/ Dynamic Bindings

Learn how to extend the Spring Webflow model to add custom fields to the CAS login form and the authentication process and take advantage of the additional user-provided data in customized authentication handlers.

Apereo CAS as an OAuth2 Authorization Server

Learn how to configure CAS as an OAuth2 Authorization Server and configure Spring Boot client app to work with it

Apereo CAS - SAML2 Identity Provider Integration w/ Gitlab (also staring HAProxy and LDAP)

Learn how Apereo CAS may act as a SAML2 identity provider for Gitlab and run everything locally on a workstation with Docker and Java.