IDENTITY AND ACCESS MANAGEMENT
[an error occurred while processing this directive]

TargetedID database schema

These schema definitions and procedures are for a PostgreSQL database. Other databases, e.g. MySQL, might be similar but, of course, different.

The status table shows if this DB is master or slave.

CREATE TABLE sv_status (
    host text NOT NULL,
    status integer,
    master_priority integer,
    master_ts timestamp without time zone,
    log_tno integer,
    log_pno integer
);
REVOKE ALL ON TABLE sv_status FROM PUBLIC;
GRANT SELECT,UPDATE ON TABLE sv_status TO shib;
GRANT SELECT ON TABLE sv_status TO shibtest;
ALTER TABLE ONLY sv_status ADD CONSTRAINT sv_status_host_key UNIQUE (host);

Domains table is not really used.

CREATE TABLE sp_domains (
    pno integer NOT NULL,
    domain_name text
);
ALTER TABLE ONLY sp_domains ADD CONSTRAINT sp_domains_pno_key UNIQUE (pno);
ALTER TABLE ONLY sp_domains ADD CONSTRAINT sp_domains_domain_name_key UNIQUE (domain_name);

SPS table holds SP info.

CREATE TABLE sps (
    pno serial NOT NULL,
    spid text NOT NULL
);

REVOKE ALL ON TABLE sps FROM PUBLIC;
GRANT INSERT,SELECT ON TABLE sps TO shib;
REVOKE ALL ON TABLE sps_pno_seq FROM PUBLIC;
GRANT SELECT,UPDATE ON TABLE sps_pno_seq TO shib;

ALTER TABLE ONLY sps ADD CONSTRAINT sps_pno_key UNIQUE (pno);
ALTER TABLE ONLY sps ADD CONSTRAINT sps_spid_key UNIQUE (spid);

Tgtids table holds the tgtids. One for each user-sp combination.

CREATE TABLE tgtids (
    tno serial NOT NULL,
    pno integer NOT NULL,
    regid character(32),
    tgtid character(32)
);

REVOKE ALL ON TABLE tgtids FROM PUBLIC;
GRANT INSERT,SELECT ON TABLE tgtids TO shib;
GRANT SELECT ON TABLE tgtids TO shibtest;
REVOKE ALL ON TABLE tgtids_tno_seq FROM PUBLIC;
GRANT SELECT,UPDATE ON TABLE tgtids_tno_seq TO shib;

ALTER TABLE ONLY tgtids ADD CONSTRAINT tgtids_tno_key UNIQUE (tno);
ALTER TABLE ONLY tgtids ADD CONSTRAINT tgtids_pno_key UNIQUE (pno, regid);

Function to test for master server status.

CREATE FUNCTION am_i_master() RETURNS boolean as '
declare
  st integer;
begin
  select into st status from sv_status;
  return st = 2;
end
' language plpgsql;

Function to retrieve a tgtid, generating it when necessary and if possible.

CREATE FUNCTION tid(character, text) RETURNS character
    AS '
declare
  my_tgtid char(32);
  my_pno integer;
begin
  select into my_tgtid tgtid from tgtids,sps where regid=$1
                   and sps.spid=$2 and tgtids.pno=sps.pno;
  if not found then
     if am_i_master() then
        select into my_pno pno from sps where spid=$2;
        if not found then
           select into my_pno nextval(''sps_pno_seq'');
           INSERT INTO sps values (my_pno, $2);
           if not found then
               return my_tgtid;
           end if;
        end if;
        select into my_tgtid md5($1||$2||''some_long_password'');
        INSERT INTO tgtids values (default, my_pno, $1, my_tgtid);
        if not found then
            return my_tgtid;
        end if;
    end if;
  end if;
  return my_tgtid;
end;
' LANGUAGE plpgsql;


[an error occurred while processing this directive]
Jim Fox
UW Technology
Identity and Access Management
University of Washington
fox@washington.edu
[an error occurred while processing this directive]
[an error occurred while processing this directive]
[an error occurred while processing this directive]
Fox's Home

© 1983-2017, University of Washington