asimba-engine-requestor-jdbc

Requestor JDBC backend

Documentation of the module asimba-engine-requestor-jdbc

Table of contents

• Requestor JDBC backend
  • Table of contents
  • Summary
  • Description
  • Database model
    • SQL statements to create tables
      • MySQL
      • PostgreSQL
  • Configuration asimba.xml
    • Cache control
    • Database Resource configuration
    • Custom Table configuration
  • Cache
  • Baseline configuration example
    • RequestorPool
    • Requestors
    • AuthenticationProfiles
  • Implementation

---

Summary

Requestors can be managed by incorporating them in the asimba.xml file, but to increase maintainability and flexibility, the requestor configuration is preferably stored in a JDBC backend.

---

Description

Requestors can be managed through a database backend, which is implemented in the asimba-engine-requestor-jdbc module. Whenever Asimba needs to lookup a requestor, a database query is executed to retrieve the actual properties of the requestor from the database backend. The result will always reflect the latest changes as they are read from the database.

The requestor-jdbc module initializes Requestors from Requestorpools. As such, this module is responsible for initializing:

• requestorpools, their authentication profiles and properties
• requestors

Note Since version 1.1.2, there is also a caching mechanism for RequestorPools. The cache must be enabled explicitly, and the database backend must be updated to support the date_last_modified attributes for the pool- and the requestor-table.
When using remote-saml2 or remote-aselect as authentication with a JDBC-backed IDP datastore, take care to update the saml2_orgs and aselect_orgs tables with the date_last_modified attribute as well.

---

Database model

The database model that is used to read from, contains the following 5 tables:

• requestorpool_pool: defines the requestorpools
• requestorpool_properties: defines (optional) properties for requestorpools
• requestorpool_requestor: defines the requestors
• requestorpool_requestor_properties: defines (optional) properties for requestors
• requestorpool_authnprofile: defines the available authentication profiles for each requestorpool

SQL statements to create tables

MySQL

CREATE TABLE requestorpool_pool ( 
        id varchar(48) NOT NULL PRIMARY KEY,
        friendlyname varchar(255) NOT NULL,
        enabled boolean DEFAULT TRUE,
        preauthz_profile_id varchar(48) DEFAULT NULL,
        postauthz_profile_id varchar(48) DEFAULT NULL,
        forced boolean DEFAULT FALSE,
        releasepolicy varchar(48) DEFAULT NULL,
        date_last_modified DATETIME NULL
);

CREATE TABLE requestorpool_properties ( 
        pool_id varchar(48) NOT NULL,
        name varchar(255) NOT NULL,
        value varchar(255) NOT NULL,
        PRIMARY KEY(pool_id,name)
);

CREATE TABLE requestorpool_requestor ( 
        id varchar(255) NOT NULL PRIMARY KEY,
        pool_id varchar(48) NOT NULL,
        friendlyname varchar(255) NOT NULL,
        enabled boolean DEFAULT TRUE,
        date_last_modified DATETIME NULL,
);

CREATE TABLE requestorpool_requestor_properties ( 
        requestor_id varchar(255) NOT NULL,
        name varchar(255) NOT NULL,
        value varchar(255) NOT NULL,
        PRIMARY KEY(requestor_id,name)
);

CREATE TABLE requestorpool_authnprofile ( 
        authn_profile_id varchar(48) NOT NULL,
        pool_id varchar(48) NOT NULL,
        order_id SERIAL NOT NULL,
        PRIMARY KEY(authn_profile_id,pool_id)  
);

Update from 1.1.1 to 1.1.2

ALTER TABLE `requestorpool_requestor` ADD COLUMN `date_last_modified` DATETIME NULL AFTER `enabled` ;
ALTER TABLE `requestorpool_pool` ADD COLUMN `date_last_modified` DATETIME NULL AFTER `releasepolicy` ;

ALTER TABLE `requestorpool_pool` ADD INDEX `idx_requestorpool_pool_date_last_modified` (`date_last_modified` ASC) ;
ALTER TABLE `requestorpool_requestor` ADD INDEX `idx_requestorpool_requestor_date_last_modified` (`date_last_modified` ASC) ;

PostgreSQL

CREATE TABLE requestorpool_pool (
  id character varying(48) NOT NULL,
  friendlyname character varying(255) DEFAULT NULL::character varying,
  enabled boolean,
  preauthz_profile_id character varying(48) DEFAULT NULL::character varying,
  postauthz_profile_id character varying(48) DEFAULT NULL::character varying,
  forced boolean,
  releasepolicy character varying(48) DEFAULT NULL::character varying,
  date_last_modified timestamp without time zone,
  CONSTRAINT requestorpool_pool_pkey PRIMARY KEY (id)
) WITH ( OIDS=FALSE );

CREATE TABLE requestorpool_properties (
  pool_id character varying(48) NOT NULL,
  name character varying(255) NOT NULL,
  value character varying(255) NOT NULL,
  CONSTRAINT requestorpool_properties_pkey PRIMARY KEY (pool_id, name)
) WITH ( OIDS=FALSE );


CREATE TABLE requestorpool_requestor (
  id character varying(255) NOT NULL,
  pool_id character varying(48) NOT NULL,
  friendlyname character varying(255) DEFAULT NULL::character varying,
  enabled boolean,
  date_last_modified timestamp without time zone,
  CONSTRAINT requestorpool_requestor_pkey PRIMARY KEY (id)
) WITH ( OIDS=FALSE );

CREATE INDEX idx_requestorpool_requestor_date_last_modified
  ON requestorpool_requestor USING btree (date_last_modified);


CREATE TABLE requestorpool_requestor_properties (
  requestor_id character varying(255) NOT NULL,
  name character varying(255) NOT NULL,
  value character varying(255) NOT NULL,
  CONSTRAINT requestorpool_requestor_properties_pkey PRIMARY KEY (requestor_id, name)
) WITH ( OIDS=FALSE );

CREATE TABLE requestorpool_authnprofile (
  authn_profile_id character varying(48) NOT NULL,
  pool_id character varying(48) NOT NULL,
  order_id serial NOT NULL,
  CONSTRAINT requestorpool_authnprofile_pkey PRIMARY KEY (authn_profile_id, pool_id)
) WITH ( OIDS=FALSE );

Update from 1.1.1 to 1.1.2

ALTER TABLE requestorpool_requestor ADD COLUMN date_last_modified timestamp without time zone;
ALTER TABLE requestorpool_pool ADD COLUMN date_last_modified timestamp without time zone;

CREATE INDEX idx_requestorpool_requestor_date_last_modified
  ON requestorpool_requestor USING btree (date_last_modified);

CREATE INDEX idx_requestorpool_pool_date_last_modified
  ON requestorpool_pool USING btree (date_last_modified);

Configuration asimba.xml

The configuration of the requestor-jdbc module allow alternative database and table settings to overrule the default settings. If no default settings are to be overruled, the model database from the StorageFactory is used, as well as the names for the tables as they are provided in the SQL create table queries. This is the minimal configuration:

    <requestorpoolfactory class="com.alfaariss.oa.engine.requestor.jdbc.JDBCFactory">
    </requestorpoolfactory>

Cache control

The default setting for cache control is to not use caching. If you want to enable caching (recommended!), configure the cache setting like this:

    <requestorpoolfactory class="com.alfaariss.oa.engine.requestor.jdbc.JDBCFactory">
        *<cache enabled=“true” />*
    </requestorpoolfactory>

Database Resource configuration

It is possible to overrule the database connection to use. This can be done by entering a resource-section inside the requestorpoolfactory, like this:

    <requestorpoolfactory class="com.alfaariss.oa.engine.requestor.jdbc.JDBCFactory">
        <resource>
            <environment_context>java:comp/env</environment_context>
            <resource-ref>jdbc/asimba-db</resource-ref>
        </resource>
    </requestorpoolfactory>

The jdbc/asimba-db resource must be configured in the JNI-environment of the container. When using Tomcat, there must be a resource configured in the context.xml file, like this:

<Resource name="jdbc/asimba-“db auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
        username="username" password="password" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/asimba-db”-/>

Custom Table configuration

If you want to overrule the table-names, this has to be done in the <resource>-section of the <requestorpoolfactory> element as well. See the following example:</requestorpoolfactory></resource>

    <requestorpoolfactory class="com.alfaariss.oa.engine.requestor.jdbc.JDBCFactory">
        <resource>
            <environment_context>java:comp/env</environment_context>
            <resource-ref>jdbc/asimba-db</resource-ref>
            <pools table=“[pools-table-name]”></pools>
            <pool_properties table=“[pool-properties-table-name]”></pool_properties>
            <authentication table=“[authentication-table-name]”></authentication>
            <requestors table=“[requestors-table-name]”></requestors>
            <requestor_properties table=“[requestor-properties-table-name]”></requestor_properties>
        </resource>
    </requestorpoolfactory>

Note that all the elements pools, pool_properties, authentication, requestors and requestor_properties are optional.

Cache

Since version 1.1.2 , RequestorPools can be cached. This required the date_last_modified attribute to be part of the pool- and requestor-tables. Caching follows this logic:

• On fetching the data from the database, the timestamp is initialized with the date_last_modified value of pool and/or requestor
• A date with value NULL is considered an unknown date; this will always result in fetching a fresh instance of the requestorpool, effectively disabling the cache feature

When using cache, make sure that the date_last_modified attribute reflects the last modification date of the pool (with pool-properties) or requestor (with requestor-properties). Enough said....

Note when using the SAML2 IDP profile, the date_last_modified setting for a requestor can also control metadata refreshing: an update of the timestamp (to a more recent time) will trigger a metadata to be reloaded (instead of waiting for the metadata to expire before performing a reload).

Baseline configuration example

To configure the requestorpools the same as the baseline configuration, but from the database, you have to create the tables using the create table queries as described earlier. Then, follow the instructions to initialize the tables with the correct records.

RequestorPool

Create one pool:
1) Set the id to ‘requestorpool.1’ and name ‘Baseline JDBC pool’
2) There is no pre-authorization profile
3) There is no post-authorization profile
4) Forced authentication is set to false
5) The release-policy with id ‘asimba.releasepolicy.1’, is configured in asimba.xml

SQL:

INSERT INTO `requestorpool_pool` (`id`, `friendlyname`, `enabled`, `forced`, `releasepolicy`, `date_last_modified`) VALUES ('requestorpool.1', 'Baseline JDBC pool', 1, false, ‘asimba.releasepolicy.1’, NOW());

Requestors

Create two requestors, the Example Client SP and the Shibboleth Example SP

INSERT INTO `requestorpool_requestor` (`id`, `pool_id`, `friendlyname`, `enabled`) VALUES ('localhost/AsimbaSimpleSP', 'requestorpool.1', 'Asimba Simple SP', 1);
INSERT INTO `requestorpool_requestor` (`id`, `pool_id`, `friendlyname`, `enabled`) VALUES ('https://sp.example.org/shibboleth', 'requestorpool.1', 'Asimba Sample Shibboleth SP', 1);

AuthenticationProfiles

Our pool should list all available authentication profiles.

INSERT INTO `requestorpool_authnprofile` (`authn_profile_id`, `pool_id`, `order_id`) VALUES ('local.guest', 'requestorpool.1', 1);
INSERT INTO `requestorpool_authnprofile` (`authn_profile_id`, `pool_id`, `order_id`) VALUES ('local.identifying', 'requestorpool.1', 2);
INSERT INTO `requestorpool_authnprofile` (`authn_profile_id`, `pool_id`, `order_id`) VALUES ('local.asimba.passwd', 'requestorpool.1', 3);
INSERT INTO `requestorpool_authnprofile` (`authn_profile_id`, `pool_id`, `order_id`) VALUES ('local.sms.cm', 'requestorpool.1', 4);
INSERT INTO `requestorpool_authnprofile` (`authn_profile_id`, `pool_id`, `order_id`) VALUES ('local.pwdplussms', 'requestorpool.1', 5);

Implementation

The asimba-engine-requestor-jdbc module is implemented in the class org.com.alfaariss.oa.engine.requestor.jdbc.JDBCFactory . The JDBCRequestor and JDBCRequestorPool are model classes that are initialized from JDBCFactory.