--
-- University of Washington's tgtid (ePTID) PostgreSQL schema
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

CREATE PROCEDURAL LANGUAGE plpgsql;
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
SET search_path = public, pg_catalog;

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;

ALTER FUNCTION public.plpgsql_call_handler() OWNER TO postgres;

--
-- Name: tid(character, text); 
-- Retrieves, generating if needed, a tgtid
-- Note that the RP's entity id must exist in the rp table
--

CREATE FUNCTION tid(character, text) RETURNS character
    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;
     if not found then
         return '';
     end if;
     select into my_tgtid md5($1||my_rpno||'random-string');
     INSERT INTO tgtid values (my_rpno, $1, my_tgtid);
     if not found then
         return my_tgtid;
     end if;
  end if;
  return my_tgtid;
end;
$_$
    LANGUAGE plpgsql;


ALTER FUNCTION public.tid(character, text) OWNER TO postgres;
SET default_tablespace = '';
SET default_with_oids = false;

--
-- Holds RP entityId -> number translation
-- the RP number contributes to the tgtid
--

CREATE TABLE rp (
    rpno integer,
    rpid text NOT NULL
);


ALTER TABLE public.rp OWNER TO postgres;
ALTER TABLE ONLY rp ADD CONSTRAINT rp_rpid_key UNIQUE (rpid);
COMMENT ON TABLE rp IS 'relying party number and entity id';

--
-- Holds tgtid for RP and regid pair
--

CREATE TABLE tgtid (
    rpno integer,
    regid character(32),
    tgtid character(32)
);


ALTER TABLE public.tgtid OWNER TO postgres;
ALTER TABLE ONLY tgtid ADD CONSTRAINT tgtid_regid_key UNIQUE (regid, rpno);
COMMENT ON TABLE tgtid IS 'eduPerson targeted id for a regid and relying party';


--
-- Permissions
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

REVOKE ALL ON TABLE rp FROM PUBLIC;
REVOKE ALL ON TABLE rp FROM postgres;
GRANT ALL ON TABLE rp TO postgres;
GRANT ALL ON TABLE rp TO shibadmin;
GRANT SELECT,INSERT ON TABLE rp TO shib;

REVOKE ALL ON TABLE tgtid FROM PUBLIC;
REVOKE ALL ON TABLE tgtid FROM postgres;
GRANT ALL ON TABLE tgtid TO postgres;
GRANT ALL ON TABLE tgtid TO shibadmin;
GRANT SELECT,INSERT ON TABLE tgtid TO shib;