-- -- PostgreSQL create tgtid database -- SET client_encoding = 'SQL_ASCII'; SET check_function_bodies = false; SET SESSION AUTHORIZATION 'postgres'; -- create shib user if not already done CREATE USER shib PASSWORD 'spud9876'; CREATE USER shibadmin PASSWORD 'spud01234'; -- create the tgtid2 database CREATE DATABASE tgtid2 WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII'; \connect tgtid2 postgres SET client_encoding = 'SQL_ASCII'; SET check_function_bodies = false; SET search_path = public, pg_catalog; CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAGE c; SET SESSION AUTHORIZATION DEFAULT; CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; SET SESSION AUTHORIZATION 'postgres'; REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO PUBLIC; SET SESSION AUTHORIZATION 'postgres'; -- mapping of spno to sp descriptive name CREATE TABLE sp_domains ( spno integer UNIQUE NOT NULL, domain_name text UNIQUE NOT NULL ); REVOKE ALL ON TABLE sp_domains FROM PUBLIC; GRANT ALL ON TABLE sp_domains TO shibadmin; GRANT SELECT ON TABLE sp_domains TO shib; SET SESSION AUTHORIZATION 'postgres'; -- mapping of spid (relying party) to spno CREATE TABLE sps ( spno integer NOT NULL, spid text UNIQUE NOT NULL ); -- CREATE INDEX sps_spno_idx ON sps (spno); -- above create table creates sps_spid_key automatically -- CREATE INDEX sps_spid_key ON sps (spid); REVOKE ALL ON TABLE sps FROM PUBLIC; GRANT ALL ON TABLE sps TO shibadmin; GRANT SELECT ON TABLE sps TO shib; SET SESSION AUTHORIZATION 'postgres'; -- mapping of (regid, spno) to tgtid CREATE TABLE tgtids ( spno integer NOT NULL, regid character(32), tgtid character(32), UNIQUE (spno, regid) ); -- above create table creates tgtids_spno_key automatically -- CREATE INDEX tgtids_spno_key ON tgtids (regid); REVOKE ALL ON TABLE tgtids FROM PUBLIC; GRANT ALL ON TABLE tgtids TO shibadmin; GRANT INSERT,SELECT ON TABLE tgtids TO shib; SET SESSION AUTHORIZATION 'postgres'; -- copy of tgtids for distribution to peers CREATE TABLE tgtids_x ( spno integer NOT NULL, regid character(32), tgtid character(32), status integer ); REVOKE ALL ON TABLE tgtids_x FROM PUBLIC; GRANT ALL ON TABLE tgtids_x TO shibadmin; GRANT INSERT,SELECT ON TABLE tgtids_x TO shib; SET SESSION AUTHORIZATION 'postgres'; -- retrieve or create a tgtid CREATE FUNCTION tid(character, text) RETURNS character AS ' declare my_tgtid char(32); my_spno integer; begin select into my_tgtid tgtid from tgtids,sps where regid=$1 and sps.spid=$2 and tgtids.spno=sps.spno; if not found then select into my_spno spno from sps where spid=$2; if not found then return my_tgtid; end if; select into my_tgtid md5(''long_random_string''||$1||$2||''spud''); INSERT INTO tgtids values (my_spno, $1, my_tgtid); INSERT INTO tgtids_x values (my_spno, $1, my_tgtid, 0); if not found then return my_tgtid; end if; end if; return my_tgtid; end; ' LANGUAGE plpgsql; COMMENT ON SCHEMA public IS 'Standard public schema';