Page Comparison

...

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

Plugin Installation

Info
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

1

2.0.0; download

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

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

...

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="...."

...

Expand

title	MySQL

Code Block

language	sql

CREATE TABLE storagerecordsStorageRecords (
  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)
);

Expand

title	PostgreSQL or H2

Code Block

CREATE TABLE storagerecordsStorageRecords (
  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)
);

Expand

title	Oracle

Code Block

CREATE TABLE storagerecords 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)
);

Expand

title	SQLServerIBM DB2

Code Block

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)clob NOT NULL,
   version bigint NOT NULL,

  PRIMARY KEY (context, id)
  
);

Note
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.

...

We recommend the use of a DataSource that provides connection pooling, which may require installing an additional library as well. The Commons DBCP 2 library is included with the IdP and recommended for this purpose.

Note
Tomcat’s JDBC Pooling driver is known to cause connection leaks and is not supported.

The following libraries provide connection pooling functionality:

Commons DBCP 2
Tomcat JDBC Pool HikariCP
BoneCP HikariCP - Repository Marked Archived on Jun 3, 2024

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

...

Expand

title	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` (2³¹)	The mazimum size of the ‘value’ column in you database. Only change this if you are using a non-standard DDI

Expand

title	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.

...

Expand

title	Old JPA Configuration

Code Block

<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>

Expand

title	After Initial conversion

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

...

Versions Compared

Old Version 14

New Version 30

Key

Plugin Installation

JDBC Driver