Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 25 Next »

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.

The JPA Storage Service will be removed in V5 of the IdP, due in 2023. Please migrate prior to that point.

Plugin Installation

Starting with IdP 4.2 you can the install the latest plugin version supported on your IdP version with
.\plugin.sh -I net.shibboleth.plugin.storage.jdbc

Plugin ID

Module(s)

Latest Version

Bug Reporting

net.shibboleth.plugin.storage.jdbc

None

2.0.0; download

https://shibboleth.atlassian.net/browse/JJDBC

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.

  • Remove also the VendorAdapter bean if present.

  • 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

Whatever you do, you MUST ensure the context and id columns are case-sensitively handled and compared. That is a requirement of the API that will be using the database. This is frequently NOT the default behavior of databases such as MySQL.

The specific examples that follow should NOT be assumed to be functional, as they likely are the product of different sources, varying amounts of testing (including none), and may not be current. Drivers get updated frequently and JDBC and database bugs appear and disappear with regularity. When in doubt, always grab new ones when problems appear.

 MySQL
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)
);
 PostgreSQL or H2
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)
);
 Oracle
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)
);

The value column must be arbitrarily wide to allow the JDBC storage service to back IdP Sessions

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:

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.

 Behavioral Options

The behavior of the Storage Service is controlled by the following options

Option Property Name

Default

Description

dataSource

Required

The DataSource to use

cleanupInterval

“PT10M” (or the value of the property idp.storage.cleanupInterval if it is set)

The time between one cleanup and another. A value of 0 indicates that no cleanup will be performed.

retryableErrors

A comma separate list of SQL errors which will cause a failed transaction to be retried (a maximum of transactionRetry times)

transactionIsolation

8 (Connection.TRANSACTION_SERIALIZABLE)

The level of transactional isolation required as described for the Connection Interface
Starting in V2.0.0, if 0 (TRANSACTION_NONE) is specified then the transactional isolation is not set at the Connection level

transactionRetries

3

Number of retries if insertion fails due to database transaction bugs

verify

true

Whether to verify the database connection on startup

localLocking

false

Whether to do thread level locking to arbitrate access (for this IdP) to the the database. This can be useful in high contention situations when multiple transaction retries are happening.

contextSize

255

The size of the ‘context’ column in you database. Only change this if you are using a non-standard DDI

keySize

255

The size of the ‘key’ column in you database. Only change this if you are using a non-standard DDI

valueSize

Integer.MAX_SIZE (231)

The mazimum size of the ‘value’ column in you database. Only change this if you are using a non-standard DDI

 SQL Options

It is possible to redefine any or all all the SQL statements that are sent to the database by the JDBCStorageService. The following is the list

Option Property Name

Default

Notes

preCreateQuerySQL

SELECT expires FROM StorageRecords WHERE context =? AND id=?

The SQL to query the state of the table prior to creating a new record.

Issued in the same transaction as createCreateRecordSQL or createUpdateRecordSQL

createCreateRecordSQL

INSERT INTO StorageRecords(context, id, expires, value, version) VALUES (?, ?, ?, ?, 1)

The SQL to create a new record.

Issued in the same transaction as preCreateQuerySQL

createUpdateRecordSQL

UPDATE StorageRecords SET value=?, version=1, expires=? WHERE context=? AND id=?

The SQL to create a update an expired record (instead of a create)

Issued in the same transaction as preCreateQuerySQL

deleteByContextExpiredSQL

DELETE FROM StorageRecords WHERE context = ? AND expires < ?

The SQL to “reap” away expired records for a given context

deleteByContextSQL

DELETE FROM StorageRecords WHERE context = ?

The SQL to remove all records for a given context

deleteByExpiredSQL

DELETE FROM StorageRecords WHERE expires < ?

The SQL to remove all expired records (as part of the cleanup task)

preDeleteQuerySQL

SELECT version FROM StorageRecords WHERE context =? AND id=?

The SQL to determine whether the a record is the correct one to be deleted.

Issues in the same transaction as deleteRecordSQL

deleteRecordSQL

DELETE FROM StorageRecords WHERE context=? AND id=?

The SQL to delete a specific record.

Issued in the same transaction as preDeleteQuerySQL

preUpdateQuerySQL

SELECT version, expires, value FROM StorageRecords WHERE context =? AND id=?

The SQL to determine the state of a record prior to its update

Issued in the same transaction as updateRecordSQL

updateRecordSQL

UPDATE StorageRecords SET value=?, version=?, expires=? WHERE context=? AND id=?

The SQL to update a specific record

Issued in the same transaction as preUpdateQuerySQL

readAllByContextSQL

SELECT id, expires, value, version FROM StorageRecords WHERE context = ?

The SQL to return all the records associated with a specific context

readAllSQL

SELECT context, id, expires, value, version FROM StorageRecords

The SQL to return all the records

readContextsSQL

SELECT context FROM StorageRecords

The SQL to return all the context names

readRecordSQL

SELECT version, expires, value FROM StorageRecords WHERE context =? AND id=?

The SQL to read a specified record.

updateExpiresByContextSQL

UPDATE StorageRecords SET expires = ? WHERE context = ? AND expires > ?

The SQL to refresh the expiration of all currently unexpired records.

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

 Old JPA Configuration
<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.JPAVendorAdapter"
    class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="database" value="MYSQL" />
</bean>
 After Initial conversion
<bean id="shibboleth.JPAStorageService" 
      parent="shibboleth.JDBCStorageService"
      p:cleanupInterval="%{idp.storage.cleanupInterval:PT10M}"
      p:dataSource-ref="shibboleth.JPAStorageService.DataSource"/>

  • No labels