Relational Database Data Connector

The relational database data connector pulls attribute information from any JDBC capable relational database. The following steps walk through how to set up this connector.

1. Define the Connector

To define a new relational database data connector, create a <DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"> with the following attribute:

id - A unique ID for this data connector used within the resolver configuration

The following advanced options may also be defined as attributes on the <resolver:DataConnector> element.

readOnlyConnection- a boolean flag that determines whether connections to the database should be read-only; defaults to true
queryUsesStoredProcedure - a boolean flag that indicates whether the SQL query, defined in step 3, uses stored procedures or not; defaults to false
cacheResults - a boolean flag that indicates whether search results should be cached for the lifetime of a user's session; defaults to false, deprecated in favor of the <ResultCache> element (see below)
noResultIsError - a boolean flat that indicates whether a query that returns no results should be treated as an error; defaults to false
queryTimeout - timeout in milliseconds of the SQL query; defaults to 5000

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Remaining configuration from the next steps goes here -->

</resolver:DataConnector>

You have to set the "queryTimeout" attribute to "0". This feature is not yet supported by the PostgreSQL JDBC driver and the default value will cause a crash when starting the IdP.

2. Define Dependencies

This step is optional.

One component within the attribute resolver, like data connectors, can depend on information retrieved or constructed by another component. The values from these dependencies may then be used within the query template in the next step. This might be useful if your SQL query depended on the value retrieved from another connector. For example, if you wanted to use an employeeID retrieved from one source to get attributes from another, a dependency on that attribute would allow it to be used as a variable in the SQL query.

Dependencies are expressed by the <resolver:Dependency> with a ref attribute whose value is the unique ID of the attribute definition or the data connector that this connector depends on.

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <resolver:Dependency ref="DEFINITION_ID_1" />
     <resolver:Dependency ref="DEFINITION_ID_2" />
     <resolver:Dependency ref="CONNECTOR_ID_3" />
     <resolver:Dependency ref="CONNECTOR_ID_4" />

     <!-- Remaining configuration from the next steps go here -->

</resolver:DataConnector>

3. Define Failover Data Connectors

This step is optional.

If the data connector encounters an error when trying to retrieve attribute information from the database, one or more failover data connectors can be defined. These connectors are only invoked if this data connector fails (not if simply finds no results) and they are invoked in the order they are listed until one succeeds.

Failover connectors are defined by the <resolver:FailoverDataConnector> element with a ref attribute whose value is the unique ID of the data connector to use in the event that this one fails.

<resolver:DataConnector xsi:type="dc:RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                                          id="UNIQUE_ID">

     <resolver:Dependency ref="DEFINITION_ID_1" />
     <resolver:Dependency ref="DEFINITION_ID_2" />
     <resolver:Dependency ref="CONNECTOR_ID_3" />
     <resolver:Dependency ref="CONNECTOR_ID_4" />

     <resolver:FailoverDataConnector ref="CONNECTOR_ID_1" />

     <!-- Remaining configuration from the next steps goes here -->

</resolver:DataConnector>

4a. Define Identity Provider (Application) Managed Connections

Deployers wishing to allow the data connector to manage connections to the database should follow these instructions; those wishing to allow the Servlet container to manage connections should use the instructions in step 4b.

Tips on Oracle connection configuration.

Identity Provider (application) managed connections are defined by a <ApplicationManagedConnection> element with the following attributes:

jdbcDriver - the fully qualified class name of the JDBC driver used to make connections to the database
jdbcURL - the connection URL for the database
jdbcUserName - the user name used to connect to the database
jdbcPassword - the password used to connect to the database

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Dependency and Failover information would go here -->

     <ApplicationManagedConnection jdbcDriver="DRIVER_CLASS"
                                   jdbcURL="DATABASE_URL"
                                   jdbcUserName="DATABASE_USER"
                                   jdbcPassword="DATABASE_USER_PASSWORD" />

     <!-- Remaining configuration from the next steps goes here -->

</resolver:DataConnector>

In order to use a database you must place the JDBC driver, for your database, in the IDP_HOME/lib directory and the IDP_SRC/lib directory. You must then rerun the install script (to generate a new WAR) and restart your servlet container.

Advanced Options

The <ApplicationManagedConnection> element also allows the following, advanced, configuration attributes controlling how connections are pooled:

poolAcquireIncrement - number of new connections to create when a pool is exhausted; defaults to 3
poolAcquireRetryAttempts - number of times the pool will attempt retry connections, upon a failure; defaults to 36
poolAcquireRetryDelay - number of milliseconds the pool will wait before trying to establish a connection to the database if an error occurs; defaults to 5000
poolBreakAfterAcquireFailure - boolean flag indicating whether the database should be marked as permanently unavailable if a good connection can not be created after the poolAcquireRetryAttempts; defaults to true
poolMinSize - minimum number of open connections that the pool will keep; defaults to 2
poolMaxSize - maximum number of open connections the pool will keep; defaults to 50, a value of 0 indicated no maximum
poolMaxIdleTime - number of seconds a connection may remain idle before being shutdown; defaults to 600
poolIdleTestPeriod - number of seconds the pool will wait before running the next scan for idle connections; defaults to 180

4b. Define Container Managed Connections Configuration

Configuration that use container managed connections can not be tested from the command line.

Deployers wishing to have their Servlet container manage connections to a database should follow these instructions, those wishing to allow the data connector to manage connections should use the instructions in step 4a.

Servlet container managed connections are defined by a <ContainerManagedConnection> element with the following attribute:

resourceName - JNDI location of the Java Connection DataSource

The <ContainerManagedConnection> may also contain any number of <JNDIConnectionProperty> elements that specify the JNDI connection properties appropriate for the container. Refer to your container documentation for these properties. Some common JNDI connection parameters are listed by Sun.

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Dependency and Failover information would go here -->

     <ContainerManagedConnection resourceName="RESOURCE_NAME"  />

     <!-- Remaining configuration from the next steps go here -->

</resolver:DataConnector>

In order to use a database you must place the JDBC driver, for your database, in your servlet's classpath and restart your servlet container. Refer to your servlet container's documentation for instructions.

5. Define the SQL Query

The SQL query used to retrieve attribute information from the database is specified within a single <QueryTemplate> element. The content of the element is a template resulting in a well formed SQL statement and should always be encapsulated in CDATA to ensure proper handling. Variables may be used, including those passed in through dependencies and a special one, $requestContext.principalName, which is always the principal name as supplied by the user authentication.

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Dependency and Failover information would go here -->

     <ContainerManagedConnection resourceName="RESOURCE_NAME" />

     <QueryTemplate>
          <![CDATA[
               SELECT * FROM PEOPLE WHERE userid='$requestContext.principalName'
          ]]>
     </QueryTemplate>

     <!-- Remaining configuration from the next steps go here -->

</resolver:DataConnector>

More information on the template language is available here explaining how variable substitution is accomplished.

6. Define Column Mappings

This step is optional.

By default, the data connector will produce one attribute per column retrieved, using the column name as the attribute's ID, each row as an attribute value, and the column's type as the object type for each value. For example, consider a SQL query SELECT firstname, personid FROM PERSON WHERE userid='${requestContext.principalName}' with a firstname column of type VARCHAR and a personid column of type NUMBER(9). The data connector would create two attributes with ID's of firstname and personid. Each of the values of firstname would be a String and each of the values of personid would be an Integer.

Column mappings allow you to change this behavior by mapping a column name to a specified name and/or specifying an object type for the values retrieved from that column. The mappings are defined by one or more <Column> element with the following attributes:

columnName - name of the database column being mapped; this attribute is required
attributeID - ID to use for the attribute created from the column
type - object type to use for the values of the attribute; acceptable values are: BigDecimal, Boolean, Byte, ByteArray, Date, Double, Float, Integer, Long, Object, Short, String, Time, Timestamp, URL

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <!-- Dependency and Failover information would go here -->

     <ContainerManagedConnection resourceName="RESOURCE_NAME"  />

     <QueryTemplate>
          <![CDATA[
               SELECT * FROM PEOPLE WHERE userid='$principal'
          ]]>
     </QueryTemplate>

     <Column columnName="firstname" attributeID="fname" />
     <Column columnName="personid" type="String" />

</resolver:DataConnector>

7. Result Caching

The data connector may cache query results for a period of time or until too many results are in memory.

To enable and configuration result caching add the <ResultCache> element as the last element in the data connector. The element has the following attributes:

elementTimeToLive - amount of time, in duration notation, that a result will be cached (Default value: 4 hours)
maximumCachedElements - maximum number of results that will be cached at any one time, if this is exceeded the oldest entries will be discard first. (Default value: 500)

8. Putting It Together

A final, complete RDBMS <DataConnector> could look like this.

<resolver:DataConnector xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
                        id="UNIQUE_ID">

     <resolver:Dependency ref="DEFINITION_ID_1" />
     <resolver:Dependency ref="CONNECTOR_ID_3" />

     <resolver:FailoverDataConnector ref="CONNECTOR_ID_1" />

     <ContainerManagedConnection resourceName="RESOURCE_NAME"  />

     <QueryTemplate>
          <![CDATA[
               SELECT * FROM PEOPLE WHERE cn='$DEFINITION_ID_1'
          ]]>
     </QueryTemplate>

     <Column columnName="firstname" attributeID="fname" />
     <Column columnName="personid" type="String" />

     <ResultCache elementTimeToLive="PT1H" />
</resolver:DataConnector>