This describes a method that maintains persistent ids in local, independent, postgres databases. Automatic synchronicity is maintained due to each host updating its own database exactly the same for any combination of user and RP. In addition it allows for editing of persistent ids and the association of a persistent id with all members of an RP 'cluster'.

Database

The database consists of a table for relying parties and a table for the persistent id (tgtids).

database definitions

CREATE TABLE rp (
   rpno integer,
   rpid text NOT NULL
);
ALTER TABLE ONLY rp ADD CONSTRAINT rp_rpid_key UNIQUE (rpid);
CREATE TABLE tgtid (
   rpno integer,
   regid character(32),
   tgtid character(32)
);
ALTER TABLE ONLY tgtid ADD CONSTRAINT tgtid_regid_key UNIQUE (regid, rpno);

and a function that either returns an existing value or a new value

tid2 function

 CREATE FUNCTION tid2(character, text) RETURNS character
   LANGUAGE plpgsql AS $_$
declare
  my_tgtid char(32);
  my_rpno integer;
begin
  select into my_tgtid tgtid from tgtid,rp where tgtid.regid=$1 and rp.rpid=$2 and tgtid.rpno=rp.rpno;
  if not found then
     select into my_rpno rpno from rp where rpid=$2;
     select into my_tgtid md5(xxxxxxxxxxxxxxxxxx);
     INSERT INTO tgtid values (my_rpno, $1, my_tgtid);
     if not found then
         return my_tgtid;
     end if;
  end if;
  return my_tgtid;
end;
$_$;

where 'xxxxxxxxxxxxxxxxxx' is some combination of salt, rp and user, e.g.

 xxx..xxx ==  'salt1'||$1||my_rpno||'salt2'

Maintenance of the rp table

Any relying party that gets a persistent id in any way must be preloaded into the rp table, and that all hosts must have the same rpno/rpid associations. At UW we have a script that parses the attribute filter file whenever it changes. That's as good a place as any to automate the process.

Attribute Resolver Configuration

The data connector for the persistent id is now a simple relational database connector.

Data Connector

<resolver:DataConnector id="tgtid2" xsi:type="RelationalDatabase" xmlns="urn:mace:shibboleth:2.0:resolver:dc"
         readOnlyConnection="false" queryTimeout="PT20S">
     <resolver:Dependency ref="uwRegID" />
     <dc:BeanManagedConnection>EptidDataConnector</dc:BeanManagedConnection>
     <QueryTemplate>
         <![CDATA[
            select tid2('${uwRegID.get(0)}', '${resolutionContext.attributeRecipientID}') as tid
         ]]>
     </QueryTemplate>
     <Column columnName="tid" attributeID="eduPersonTargetedID" />
</resolver:DataConnector>

and the definition of EptidDataConnector is in global.xml

eptid bean def

<bean id="EptidDataConnector" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
    p:driverClassName="org.postgresql.Driver"
    p:url="jdbc:postgresql://localhost/idp" p:username="shib" p:password="xxxxxxxxx"
    p:maxActive="10" p:maxIdle="5" p:maxWait="2000" p:testOnBorrow="true"
    p:defaultReadOnly="false"
    p:validationQuery="select 1" p:validationQueryTimeout="5" />

Identity Provider 3

Persistent Id with local databases.

Database

Maintenance of the rp table

Attribute Resolver Configuration