Pentaho and SSO Part 1: CAS

Single Sign On allows users to log in once and access multiple applications without logging in again. The paid version of Pentaho comes with a script that enables SSO for Pentaho, but the community edition does not. However, since Pentaho uses Spring Security, you can still set up Pentaho to use SSO. This post will describe how to set up Pentaho to use Jasig CAS, a popular, free, open-source SSO server.

(As an aside, let me say I am not a Pentaho Enterprise Edition customer, nor have I ever seen the Pentaho SSO script. This is a "clean-room" implementation of the configuration.)

Before getting into configuration, an overview of how CAS works would be helpful. When you try to access a protected webpage in Pentaho, the BI-Server checks to see if you are authenticated (and if you are authenticated, checks to see if you are authorized). If you are not authenticated, you are redirected to a login page. When Pentaho is CAS enabled, instead of beign redirected to the Pentaho login page, you are redirected to the CAS login page, which has an address like this:

http://cas.server.address/cas/login

After you login to CAS, you will be redirected back to the original page you tried to access, which probably is the Pentaho PUC. CAS needs to know what page you attempted to see, so it can redirect you there after you authenticate. To tell CAS, the address of the page you attempted to access is appended to the end of the CAS URL. When you are using the Pentaho User Console (PUC), that page is the Spring Security page that checks for authentication:

http://your.cas.address/cas/login?service=http://yourPentaho/pentaho/j_spring_cas_security_check

When it gets the request for the login page, the first thing CAS does is check to see if you have logged into CAS already. If you have not, you are presented with a login page. If you have a correct username and password you are redirected back to http://youPentaho/pentaho/j_spring_cas_security_check. If you have already logged in with CAS (maybe you have logged in before accessing another CAS enabled application), you are immediately redirected back to j_spring_cas_security_check without seeing the CAS login page. In both cases, once you are redirected back to j_spring_cas_security_check, you are assigned a one time use ticket, which is append to that URL. Like this:

http://yourPentaho/pentaho/j_spring_cas_security_check?ticket=ST-3555-McPZ4NKfx6S0EhnCEkHc

Spring Security takes the ticket and checks to see if it is valid by submitting it back to CAS. Like this:

http://your.cas.address/cas/serviceValidate?ticket=ST-3555-McPZ4NKfx6S0EhnCEkHc&service=http://yourPentaho/pentaho/j_spring_cas_security_check

If the ticket is good, CAS responds with the username.  Now that Pentaho knows who you are, the regular Pentaho authorization occurs (checking groups and user access rules). However, for the Pentaho authorization to work, you will either need CAS to point to the database where Pentaho stores its users, or make sure that Pentaho can get group information from the CAS database of users. In this example, we are going to create a new MySQL database of users and point both CAS and Pentaho to it.

The three tables that are needed are a Users table, a groups table (called Authorities) and a many to many table to assigning users to groups.

For the Users:

CREATE TABLE `USERS` (
  `USERNAME` varchar(50) NOT NULL,
  `PASSWORD` varchar(512) DEFAULT NULL,
  `DESCRIPTION` varchar(100) DEFAULT NULL,
  `ENABLED` bit(1) NOT NULL,
  PRIMARY KEY (`USERNAME`)
)

Groups:

CREATE TABLE `AUTHORITIES` (
  `AUTHORITY` varchar(50) NOT NULL,
  `DESCRIPTION` varchar(100) DEFAULT NULL,
  `AUTH_GROUP` varchar(255) NOT NULL DEFAULT 'NONE',
  PRIMARY KEY (`AUTHORITY`)
)

And group assignment:

CREATE TABLE `GRANTED_AUTHORITIES` (
  `USERNAME` varchar(50) NOT NULL,
  `AUTHORITY` varchar(50) NOT NULL,
  PRIMARY KEY (`USERNAME`,`AUTHORITY`),
  KEY `FK7471775DD9EDC77F` (`USERNAME`),
  KEY `FK7471775D41B6DA97` (`AUTHORITY`)
)



Installing CAS:

CAS runs a regular Java webapp. You can run it in the same Tomcat instance as Pentaho, or on another machine.  Once you download CAS, you will need to edit the deployerConfigContext.xml file in the WEB-INF directory to point CAS to the USERS table. In the authenticationHandlers section of the xml file, delete the SimpleTestUsernamePasswordAuthenticationHandler bean, and replace it with this bean:

<bean id="SearchModeSearchDatabaseAuthenticationHandler" class="org.jasig.cas.adaptors.jdbc.SearchModeSearchDatabaseAuthenticationHandler" abstract="false" scope="singeton">
<property name="tableUsers">
  <value>USERS</value>
</property>
<property name="fieldUser">
  <value>USERNAME</value>
</property>
<property name="fieldPassword">
  <value>PASSWORD</value>
</property>
<property name="dataSource" ref="dataSource"/>
<!-- Use the same PW Encoder as your db -->
  <property name="passwordEncoder">  <bean class="org.jasig.cas.authentication.handler.DefaultPasswordEncoder">
    <constructor-arg value="SHA-256"/>
  </bean>
</property>
</bean>


Note that I am using SHA-256 to encode the password. You can use any encoding that you want, MD5 for example.

Finally, point CAS to the right database by adding this to deployerConfigContext.xml:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
 <property name="driverClassName"> <value>com.mysql.jdbc.Driver</value> </property>
 <property name="url"> <value>jdbc:mysql://your_mysqlhost:3306/YOUR_DB</value> </property>
 <property name="username"> <value>DB_USERNAME</value> </property>
 <property name="password"> <value>DB_PASSWORD</value> </property>
</bean>

Now CAS is setup. Start up Tomcat (or whatever Servlet container you like).

Part 2 will go over the Pentaho configuration.