ODBCStorageService

1 Overview
2 Reference
- 2.1 Attributes
- 2.2 Child Elements
3 Examples
4 Database Setup
- - 4.1.1 Rough DDL for the database
- 4.2 ODBC Connection Strings
- 4.3 Database-Specific Notes
  - 4.3.1 Microsoft SQL Server
  - 4.3.2 MySQL
  - 4.3.3 PostgreSQL
    - 4.3.3.1 DDL
    - 4.3.3.2 Install postgresql-libs and create symlink
    - 4.3.3.3 shibboleth3.xml
  - 4.3.4 Oracle

Overview

Identified by type="ODBC", this storage option stores data using an ODBC-compliant database. This allows for persistence across software restarts and supports shared access across the nodes of a cluster, provided the ODBC driver and the database support transactions. No explicit database locking is performed inside the plugin itself.

Use of this plugin requires that its extension library odbc-store.so be loaded by the Service Provider via the <OutOfProcess> element's <Library> option.

You should be aware that the unixODBC software and/or many drivers tend to be unstable on non-Windows platforms. They aren't used much, even less often by multi-threaded software, and have been found to contain a lot of apparent race conditions that cause crashes under load. There are several bugs open in the project issue tracker about them, and we encourage that even though there's absolutely nothing we can do about them, simply to warn people off.

Reference

Attributes

Settings specific to this type include:

Name	Type	Default	Description

Name	Type	Default	Description
cleanupInterval	time in seconds	900	Interval in seconds between cleanup runs, when expired entries are swept by a background thread. Set to 0 to disable.
isolationLevel	"SERIALIZABLE", "REPEATABLE_READ", "READ_COMMITTED" "READ_UNCOMMITTED"		Transaction isolation level for database access. Defaults to SERIALIZABLE to prevent race conditions while updating records. Reduce at your own risk, as the code is written to assume the guarantees that level provides.

Child Elements

Name	Cardinality	Type	Description

Name

Cardinality

Type

Description

1

string

Required element containing an ODBC connection string. Exact contents differ across drivers.

Note that you may wish to use a so-called "named" connection defined in odbc.ini (or for Windows inside the ODBC control panel applet or registry) to keep the database password out of this file, since it will be readable by the web server account/user.

0 or 1

whitespace-delimited list of strings

Instructs the plugin to retry certain operations if a matching ODBC status code is returned from the operation.

This is used to detect rolled back transactions caused by optimistic locking and retry them, by including the driver-specific status code that indicates the condition. Under load, you'll typically see problems on most databases that have to be worked around using this setting.

Examples

The below is excerpted from the configuration file to illustrate the key pieces involved.

<OutOfProcess>
	<Extensions>
		<Library path="odbc-store.so" fatal="true"/>
	</Extensions>
</OutOfProcess>

<StorageService type="ODBC" id="db" cleanupInterval="900">
	<ConnectionString>
	DRIVER=drivername;SERVER=dbserver;UID=shibboleth;PWD=password;DATABASE=shibboleth;APP=Shibboleth
	</ConnectionString>
</StorageService>

Database Setup

This plugin obviously requires a database to store its data. The plugin does not automatically create any tables for itself, so this has to be done ahead of time. The rough SQL DDL for the database is the following:

Rough DDL for the database

CREATE TABLE version (
    major int NOT NULL,
    minor int NOT NULL
    )
 
CREATE TABLE strings (
    context varchar(255) not null,
    id varchar(255) not null,
    expires datetime not null,
    version smallint not null,
    value varchar(255) not null,
    PRIMARY KEY (context, id)
    )
 
CREATE TABLE texts (
    context varchar(255) not null,
    id varchar(255) not null,
    expires datetime not null,
    version smallint not null,
    value text not null,
    PRIMARY KEY (context, id)
    )

You may need to adjust data types when moving between database systems, but this has to be done carefully to maintain compatibility. The SQL itself is not separate from the plugin code, so you can't change it without changing the code.

Once the tables exist, you need to prepopulate the version table with the right information, which at the moment should be 1 and 0:

insert into version values (1,0)

ODBC Connection Strings

The primary piece of information the SP configuration needs is the connection string to use, as described earlier. The string has to be self-contained, and needs to identify the database server, the database or tablespace, and the username and password to connect with. All of this is driver specific, and may involve setting up other files on the machine to create an alias for the database server, or even the connection as a whole. See below for specific database tips.

Database-Specific Notes

Microsoft SQL Server

This was the primary test platform way back in the misty past, and was tested under only light load on both Windows and CentOS 5. On Linux, the FreeTDS client and ODBC manager and driver included with the OS were used.

The simplest way to set this up is to use the SQL Server or FreeTDS client and define a server alias so you can give the server a logical name. A typical connection string would then be:

Creating the database with the default DDL worked fine.

MySQL

The connection string may look like as simple as this:

The cryptic OPTION argument above tells ODBC to look for MySql configuration files for connection data. Hence, you should create a MySql config file (ie. /etc/mysql/conf.d/odbc.cnf) accordingly, and make it readable by the user running the shibd process (eg. shibd) only.

Alternatively, you can also specify the DSN in the main config file if you prefer to leave the password there.

Creating the database with the default DDL worked fine with the InnoDB MySQL table type, which is somewhat (but not fully) transactional.

In general, MySQL presents a lot of problems because it doesn't take transactions seriously. It claims to support ACID behavior, but it mixes code for data storage with higher level code that doesn't honor those requirements, and tells you bluntly that many errors will result in only partial transaction rollback. It also has storage engines that operate like Oracle does, not taking locks when told to. As such, we don't know how the plugin behaves under exceptional conditions.

PostgreSQL

In PostgreSQL the datetime data type is called timestamp which requires a small change to the reference SQL from above:

DDL

GOTCHA: Be sure that your pg_hba.conf file is set up to allow IPv4 MD5 authentication from the network location of your SP host:

On RHEL/CentOS Linux, the ODBC driver links itself to an unversioned library, /usr/lib64/libpq.so, but the underlying postgresql package fails to create that symlink, so after installing the postgresql-libs package, you'll need to do so: