|
|
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]
|