Page Comparison

...

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, by not taking locks when told to. As such, I don't know how the plugin will behave under exceptional conditions.

...

PostgreSQL

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

Code Block

language	sql
title	shibboleth-sp.sql

CREATE TABLE version (
    major int NOT NULL,
    minor int NOT NULL
    );
INSERT INTO version VALUES (1,0);
 
CREATE TABLE strings (
    context varchar(255) NOT NULL,
    id      varchar(255) NOT NULL,
    expires timestamp    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 timestamp    NOT NULL,
    version smallint     NOT NULL,
    value   text         NOT NULL,
    PRIMARY KEY (context, id)
    );

GOTCHA: be sure that your pg_hba.conf file is setup to allow IPv4 MD5 authentication from the network location of your Shibboleth-SP host:

Code Block

language	text
title	~postgres/data/pg_hba.conf

local   all             postgres                      peer
host    all             all              127.0.0.1/32 md5
host    all             all              10.0.0.0/8   md5

On RHEL/CentOS Linux, the odbc-store.so extension (/usr/lib64/shibboleth/odbc-store.so) uses /usr/lib64/libpq.so.
You will need to install the postgresql-libs package which provides /usr/lib64/libpq.so.5 and create a symlink (symbolic link) from the installed libpq to the location where odbc-store.so expects it:

Code Block

language	bash
title	Install postgresql-libs and create symlink

yum install -y postgresql-libs
ln -s /usr/lib64/libpq.so.5 /usr/lib64/libpq.so

Configure the connection string in the shibboleth2.xml file:

Code Block

language	xml
title	shibboleth2.xml

  <StorageService type="ODBC" id="db" cleanupInterval="900">
    <ConnectionString><![CDATA[
Driver=PostgreSQL;Server=127.0.0.1;Port=5432;Database=shibboleth-sp;Uid=shibboleth-sp;Password=shibboleth-sp-password
]]></ConnectionString>
  </StorageService>
  <SessionCache type="StorageService" StorageService="db" cacheTimeout="3600" inprocTimeout="900" cleanupInterval="900" />
  <ReplayCache StorageService="db" />
  <ArtifactMap StorageService="db" artifactTTL="180" />

Versions Compared

Old Version 8

New Version 9

Key

PostgreSQL

Oracle