JDBCStorageService

Overview

The JDBCStorageService is a database-level compatible replacement for the JPAStorageService and provides a Storage Service on top of an RDBMS.  It communicates directly with the database rather than using Hibernate ORM which has issues with reliability and the lack of provenance of its software artifacts.

It is possible to swap between the JPA and JDBC storage service, and indeed to have different versions running on different nodes.

Plugin Installation

For a detailed guide on how to install plugins, see here.

In summary, use the plugin command that ships with the IdP to install the plugin from either a local file pre-downloaded, from a URL or by pluginId ^4.2

Installation

C:>\opt\shibboleth-idp\bin\plugin.bat -I net.shibboleth.plugin.storage.jdbc

or

$ /opt/shibboleth-idp/bin/plugin.sh -i http://shibboleth.net/downloads/identity-provider/plugins/pluginName/version/URL

Switching from JPAStorageService

If you are currently running with the JPAStorageService you can reconfigure to use the JDBCStorageService relatively easily

• Locate the configuration (search for the class name org.opensaml.storage.impl.JPAStorageService

• Remove the EntityManagerFactory bean, taking note of the dataSource property.

• Change the StorageService bean

  • Replace class="org.opensaml.storage.impl.JPAStorageService" with parent="shibboleth.JDBCStorageService"

  • Remove the constructor parameter and instead add a pointer to the dataSource you noted above p:dataSource-ref="...."

At this stage you should be able to test the configuration.

Once it works you can change bean names appropriately and add any extra configuration as detailed below.

Database Preparation

If you are not upgrading from a JPAStorageService configuration you need to:

• Create the database table for the plugin to use.

• Download the appropriate JDBC driver.

• (Optionally, but recommended) Download a Connection Pooling implementation.

If you are moving from the JPAStorageService you do not need to make any changes to your database and you can use the same configuration for the DataSource as you did for the JPAStorageService.

Creating the Database

Example Schemas are shown below

CREATE TABLE storagerecords (
  context varchar(255) NOT NULL,
  id varchar(255) NOT NULL,
  expires bigint DEFAULT NULL,
  value text NOT NULL,
  version bigint NOT NULL,
  PRIMARY KEY (context, id)
);

CREATE TABLE storagerecords (
  context varchar(255) NOT NULL,
  id varchar(255) NOT NULL,
  expires bigint DEFAULT NULL,
  value text NOT NULL,
  version bigint NOT NULL,
  PRIMARY KEY (context, id)
);

CREATE TABLE storagerecords (
  context varchar2(255) NOT NULL,
  id varchar2(255) NOT NULL,
  expires number(19,0),
  value clob NOT NULL,
  version number(19,0) NOT NULL,
  PRIMARY KEY (context, id)
);

CREATE TABLE StorageRecords (
   context varchar(255) COLLATE Latin1_General_100_CS_AS NOT NULL,
   id varchar(255) COLLATE Latin1_General_100_CS_AS NOT NULL,
   expires bigint DEFAULT NULL,
   value varchar(255) NOT NULL,
   version bigint NOT NULL,
   PRIMARY KEY (context,id)
   );

JDBC Driver

You need to locate, download and verify the JDBC driver for your database and place it in edit-webapp/WEB-INF/lib. After populating edit-webapp/WEB-INF/lib you should execute bin/build.sh or bin/build.bat as appropriate for your environment.

Connection Pooling

We recommend the use of a DataSource that provides connection pooling, which may require installing an additional library as well.

The following libraries provide connection pooling functionality:

• Commons DBCP

• Tomcat JDBC Pool

• BoneCP

• HikariCP

Having located, downloaded and verified the connection pooling jar you should place it in edit-webapp/WEB-INF/lib. After populating edit-webapp/WEB-INF/lib you should execute bin/build.sh or bin/build.bat as appropriate for your environment

Configuration

You need to add the definition of a bean derived from shibboleth.JDBCStorageService into an appropriate configuration file (usually global.xml). The options you can provide to the bear are detailed below.

Examples

In the example below use of Commons DBCP is demonstrated (class="org.apache.commons.dbcp.BasicDataSource", p:url="..." in the DataSource bean). When using other Connection Pool implementations change the class and properties appropriately, e.g.:

• Tomcat DBCP2: class="org.apache.tomcat.dbcp.dbcp2.BasicDataSource", p:url="..."

• Tomcat JDBC Pool: class="org.apache.tomcat.jdbc.pool.DataSource", p:url="..."

• HikariCP: class="com.zaxxer.hikari.HikariDataSource", p:jdbcUrl="..."

    <bean id="my.dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" lazy-init="true"
       p:driverClassName="......"
       p:url="jdbc:hsqldb:mem:StorageService"
       p:username="shibboleth"
       p:retryableErrors="4001, 4002"
       p:password="%{JDBCPassword}" />

    <bean id="JDBCStorageService" parent="shibboleth.JDBCStorageService"
          p:dataSource-ref="dataSource"
          p:transactionIsolation="4"
          p:retryableErrors="40001"
     />

Example conversion from JPAStorageService

<bean id="shibboleth.JPAStorageService"
        class="org.opensaml.storage.impl.JPAStorageService"
        p:cleanupInterval="%{idp.storage.cleanupInterval:PT10M}"
        c:factory-ref="shibboleth.JPAStorageService.EntityManagerFactory" />

<bean id="shibboleth.JPAStorageService.EntityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="storageservice" />
    <property name="packagesToScan" value="org.opensaml.storage.impl" />
    <property name="dataSource" ref="shibboleth.JPAStorageService.DataSource" />
    <property name="jpaVendorAdapter" ref="shibboleth.JPAStorageService.JPAVendorAdapter" />
    <property name="jpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
    </property>
</bean>

<bean id="shibboleth.JPAStorageService" 
      parent="shibboleth.JDBCStorageService"
      p:cleanupInterval="%{idp.storage.cleanupInterval:PT10M}"
      p:dataSource-ref="shibboleth.JPAStorageService.DataSource"/>