gforge-db-postgresql 5.3.2+20141104-3+deb8u3 / usr / share / gforge / db / 20100518-pfo-rbac.sql

CREATE SEQUENCE pfo_role_class_seq ;
CREATE TABLE pfo_role_class (
       class_id integer DEFAULT nextval ('pfo_role_class_seq') NOT NULL,
       class_name text DEFAULT '' NOT NULL,
       CONSTRAINT pfo_role_class_pkey PRIMARY KEY (class_id),
       CONSTRAINT pfo_role_class_name_unique UNIQUE (class_name)
) ;

CREATE SEQUENCE pfo_role_seq ;
CREATE TABLE pfo_role (
       role_id integer DEFAULT nextval ('pfo_role_seq') NOT NULL,
       role_name text DEFAULT '' NOT NULL,
       role_class integer DEFAULT 1 NOT NULL REFERENCES pfo_role_class (class_id),
       home_group_id integer,
       is_public boolean DEFAULT false NOT NULL,
       old_role_id integer DEFAULT 0 NOT NULL,
       CONSTRAINT pfo_role_pkey PRIMARY KEY (role_id),
       CONSTRAINT pfo_role_name_unique UNIQUE (role_id, role_name)
) ;

CREATE TABLE role_project_refs (
       role_id integer DEFAULT 0 NOT NULL REFERENCES pfo_role,
       group_id integer DEFAULT 0 NOT NULL REFERENCES groups,
       CONSTRAINT role_project_refs_unique UNIQUE (role_id, group_id)
) ;

CREATE TABLE pfo_role_setting (
       role_id integer DEFAULT 0 NOT NULL REFERENCES pfo_role,
       section_name text DEFAULT '' NOT NULL,
       ref_id integer DEFAULT 0 NOT NULL,
       perm_val integer DEFAULT 0 NOT NULL,
       CONSTRAINT pfo_role_setting_unique UNIQUE (role_id, section_name, ref_id)
) ;

CREATE TABLE pfo_user_role (
       user_id integer DEFAULT 0 NOT NULL REFERENCES users,
       role_id integer DEFAULT 0 NOT NULL REFERENCES pfo_role,
       CONSTRAINT pfo_user_role_unique UNIQUE (user_id, role_id)
) ;

CREATE FUNCTION insert_pfo_role_setting (role_id integer, section_name text, ref_id integer, perm_val integer) RETURNS void AS $$
BEGIN
	IF perm_val != 0 THEN
	   INSERT INTO pfo_role_setting VALUES (role_id, section_name, ref_id, perm_val) ;
	END IF ;
END ;
$$ LANGUAGE plpgsql ;

CREATE FUNCTION migrate_rbac_permissions_to_pfo_rbac () RETURNS void AS $$
DECLARE
	r role%ROWTYPE ;
	nrid integer := 0 ;
	nsec text := '' ;
	nref integer := 0 ;
	nval integer := 0 ;
	opid integer := 0 ;
	agl artifact_group_list%ROWTYPE ;
	pgl project_group_list%ROWTYPE ;
	fgl forum_group_list%ROWTYPE ;
BEGIN
	FOR r IN SELECT * FROM role
	LOOP
		SELECT role_id INTO nrid FROM pfo_role WHERE old_role_id = r.role_id ;
		SELECT group_id INTO opid FROM role WHERE role_id = r.role_id ;

		PERFORM insert_pfo_role_setting (nrid, 'project_read', opid, 1) ;

		nsec = 'project_admin' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;

		nsec = 'forge_admin' ;
		nref = -1 ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'approve_news' ;
		nref = -1 ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'forge_stats' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;

		nsec = 'tracker_admin' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'new_tracker' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'tracker' ;
		FOR agl IN SELECT * FROM artifact_group_list WHERE group_id = opid
		LOOP
			nref = agl.group_artifact_id ;
			nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
			PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		END LOOP ;

		nsec = 'pm_admin' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'new_pm' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'pm' ;
		FOR pgl IN SELECT * FROM project_group_list WHERE group_id = opid
		LOOP
			nref = pgl.group_project_id ;
			nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
			PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		END LOOP ;

		nsec = 'forum_admin' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'new_forum' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'forum' ;
		FOR fgl IN SELECT * FROM forum_group_list WHERE group_id = opid
		LOOP
			nref = fgl.group_forum_id ;
			nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
			PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		END LOOP ;

		nsec = 'docman' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'scm' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'frs' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'webcal' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
		nsec = 'plugin_mediawiki_edit' ;
		nref = opid ;
		nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
		PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;

	END LOOP ;

END ;
$$ LANGUAGE plpgsql ;

CREATE FUNCTION migrate_role_observer_to_pfo_rbac () RETURNS void AS $$
DECLARE
	g groups%ROWTYPE ;
	t artifact_group_list%ROWTYPE ;
	f forum_group_list%ROWTYPE ;
	p project_group_list%ROWTYPE ;
	need_loggedin boolean := false ;
BEGIN
	FOR g IN SELECT * FROM groups WHERE is_public = 1
	LOOP
		INSERT INTO role_project_refs VALUES (1, g.group_id) ;
		INSERT INTO role_project_refs VALUES (2, g.group_id) ;
		PERFORM insert_pfo_role_setting (1, 'project_read', g.group_id, 1) ;
		PERFORM insert_pfo_role_setting (1, 'new_tracker', g.group_id, 1) ;
		PERFORM insert_pfo_role_setting (1, 'new_pm', g.group_id, 1) ;
		PERFORM insert_pfo_role_setting (1, 'new_forum', g.group_id, 1) ;
		PERFORM insert_pfo_role_setting (1, 'frs', g.group_id, 1) ;
		PERFORM insert_pfo_role_setting (2, 'project_read', g.group_id, 1) ;
		PERFORM insert_pfo_role_setting (2, 'new_tracker', g.group_id, 1) ;
		PERFORM insert_pfo_role_setting (2, 'new_pm', g.group_id, 1) ;
		PERFORM insert_pfo_role_setting (2, 'new_forum', g.group_id, 1) ;
		PERFORM insert_pfo_role_setting (2, 'frs', g.group_id, 1) ;

		IF g.enable_anonscm = 1 THEN
		   PERFORM insert_pfo_role_setting (1, 'scm', g.group_id, 1) ;
		   PERFORM insert_pfo_role_setting (2, 'scm', g.group_id, 1) ;
		END IF ;

		FOR t IN SELECT * FROM artifact_group_list WHERE group_id = g.group_id AND is_public = 1
		LOOP
			IF t.allow_anon = 1 THEN
			   PERFORM insert_pfo_role_setting (1, 'tracker', t.group_artifact_id, 1) ;
			END IF ;

			PERFORM insert_pfo_role_setting (2, 'tracker', t.group_artifact_id, 1) ;
		END LOOP ;

		FOR p IN SELECT * FROM project_group_list WHERE group_id = g.group_id AND is_public = 1
		LOOP
			PERFORM insert_pfo_role_setting (1, 'pm', p.group_project_id, 1) ;
			PERFORM insert_pfo_role_setting (2, 'pm', p.group_project_id, 1) ;
		END LOOP ;

		FOR f IN SELECT * FROM forum_group_list WHERE group_id = g.group_id AND is_public = 1
		LOOP
			IF f.allow_anonymous = 1 THEN
			   IF f.moderation_level = 0 THEN
			      PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 3) ;
			   ELSE
			      PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 2) ;
			   END IF ;
			END IF ;

			IF f.moderation_level = 0 THEN
			   PERFORM insert_pfo_role_setting (2, 'forum', f.group_forum_id, 3) ;
			ELSE
			   PERFORM insert_pfo_role_setting (2, 'forum', f.group_forum_id, 2) ;
			END IF ;
		END LOOP ;

	END LOOP ;

END ;
$$ LANGUAGE plpgsql ;

CREATE FUNCTION pfo_rbac_permissions_from_old (rid integer, nsec text, nref integer) RETURNS integer AS $$
DECLARE
	os role_setting%ROWTYPE ;
	onsec text ;
	onref integer ;
	onval integer ;
	r pfo_role%ROWTYPE ;
	mastergroupid integer := 1 ;
	newsgroupid integer := 0 ;
	statsgroupid integer := 0 ;
	opid integer := 0 ;
	tmp integer := 0 ;
BEGIN
	SELECT group_id INTO newsgroupid FROM groups WHERE unix_group_name = 'newsadmin' ;
	SELECT group_id INTO statsgroupid FROM groups WHERE unix_group_name = 'stats' ;

	SELECT * INTO r FROM pfo_role WHERE old_role_id = rid ;

	IF nsec = 'project_read' AND nref = r.home_group_id THEN
	   RETURN 1 ;
	END IF ;

	FOR os IN SELECT * FROM role_setting WHERE role_id = rid ORDER BY role_id, section_name, ref_id
	LOOP
		SELECT group_id INTO opid FROM role WHERE role_id = os.role_id ;

		IF os.section_name = 'projectadmin' THEN
		   CONTINUE WHEN os.value != 'A' ;
		   IF nsec = 'project_admin' AND nref = opid THEN
		      RETURN 1 ;
		   END IF ;

		   IF nsec = 'forge_admin' AND nref = -1 AND opid = mastergroupid THEN
		      RETURN 1 ;
		   END IF ;
		   IF nsec = 'approve_news' AND nref = -1 AND opid = newsgroupid THEN
		      RETURN 1 ;
		   END IF ;
		   IF nsec = 'forge_stats' AND nref = -1 AND opid = statsgroupid THEN
		      RETURN 1 ;
		   END IF ;

		ELSIF os.section_name IN ('trackeradmin', 'pmadmin', 'forumadmin') THEN
		   CONTINUE WHEN os.value != '2' ;
		   onsec = CASE WHEN os.section_name = 'trackeradmin' THEN 'tracker_admin'
		   	       WHEN os.section_name = 'pmadmin' THEN 'pm_admin'
		   	       WHEN os.section_name = 'forumadmin' THEN 'forum_admin' END ;
		   IF nsec = onsec AND nref = opid THEN
		      RETURN 1 ;
		   END IF ;

		ELSIF os.section_name IN ('tracker', 'newtracker') THEN
		   CONTINUE WHEN os.value = '-1' ;
		   onsec = CASE WHEN os.section_name = 'tracker' THEN os.section_name
		   	       WHEN os.section_name = 'newtracker' THEN 'new_tracker' END ;
		   onref = CASE WHEN os.section_name = 'tracker' THEN os.ref_id
		   	       WHEN os.section_name = 'newtracker' THEN opid END ;
		   onval = CASE WHEN os.value = '0' THEN 1
		   	       WHEN os.value = '1' THEN 3
		   	       WHEN os.value = '2' THEN 7
		   	       WHEN os.value = '3' THEN 5 END ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		ELSIF os.section_name IN ('pm', 'newpm') THEN
		   CONTINUE WHEN os.value = '-1' ;
		   onsec = CASE WHEN os.section_name = 'pm' THEN os.section_name
		   	       WHEN os.section_name = 'newpm' THEN 'new_pm' END ;
		   onref = CASE WHEN os.section_name = 'pm' THEN os.ref_id
		   	       WHEN os.section_name = 'newpm' THEN opid END ;
		   onval = CASE WHEN os.value = '0' THEN 1
		   	       WHEN os.value = '1' THEN 3
		   	       WHEN os.value = '2' THEN 7
		   	       WHEN os.value = '3' THEN 5 END ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		ELSIF os.section_name = 'forum' THEN
		   CONTINUE WHEN os.value = '-1' ;
		   onsec = os.section_name ;
		   onref = os.ref_id ;
		   SELECT moderation_level INTO tmp FROM forum_group_list WHERE group_forum_id = onref ;
		   onval = CASE WHEN os.value = '0' THEN 1
		   	       WHEN os.value = '1' AND tmp >= 2 THEN 2
		   	       WHEN os.value = '1' AND tmp <= 1 THEN 3
		   	       WHEN os.value = '2' THEN 4 END ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		ELSIF os.section_name = 'newforum' THEN
		   CONTINUE WHEN os.value = '-1' ;
		   onsec = 'new_forum' ;
		   onref = opid ;
		   onval = CASE WHEN os.value = '0' THEN 1
		   	       WHEN os.value = '1' THEN 2
		   	       WHEN os.value = '2' THEN 4 END ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		ELSIF os.section_name = 'docman' THEN
		   onsec = os.section_name ;
		   onref = opid ;
		   onval = CASE WHEN os.value = '0' THEN 1
		   	       WHEN os.value = '1' THEN 4 END ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		ELSIF os.section_name = 'frs' THEN
		   onsec = os.section_name ;
		   onref = opid ;
		   onval = CASE WHEN os.value = '0' THEN 1
		   	       WHEN os.value = '1' THEN 3 END ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		ELSIF os.section_name = 'scm' THEN
		   CONTINUE WHEN os.value = '-1' ;
		   onsec = os.section_name ;
		   onref = opid ;
		   onval = CASE WHEN os.value = '0' THEN 1
		   	       WHEN os.value = '1' THEN 2 END ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		ELSIF os.section_name = 'webcal' THEN
		   CONTINUE WHEN os.value = '0' ;
		   onsec = os.section_name ;
		   onref = opid ;
		   onval = os.value ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		ELSIF os.section_name = 'plugin_mediawiki_edit' THEN
		   CONTINUE WHEN os.value = '0' ;
		   onsec = os.section_name ;
		   onref = opid ;
		   onval = os.value ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		ELSE
		   RAISE EXCEPTION 'Unknown setting % for role %', os.section_name, os.role_id ;
		   CONTINUE WHEN os.value = '0' ;
		   onsec = os.section_name ;
		   onref = os.ref_id ;
		   onval = os.value::integer ;
		   IF nsec = onsec AND nref = onref THEN
		      RETURN onval ;
		   END IF ;

		END IF ;

	END LOOP ;

	RETURN 0 ;

END ;
$$ LANGUAGE plpgsql ;

CREATE FUNCTION pfo_rbac_full_migration () RETURNS void AS $$
DECLARE
BEGIN
	DELETE FROM pfo_user_role ;
	DELETE FROM pfo_role_setting ;
	DELETE FROM role_project_refs ;
	DELETE FROM pfo_role ;
	DELETE FROM pfo_role_class ;

	INSERT INTO pfo_role_class (class_id, class_name) VALUES (1, 'PFO_RoleExplicit') ;
	INSERT INTO pfo_role_class (class_id, class_name) VALUES (2, 'PFO_RoleAnonymous') ;
	INSERT INTO pfo_role_class (class_id, class_name) VALUES (3, 'PFO_RoleLoggedIn') ;

	PERFORM setval ('pfo_role_class_seq', 3) ;

	INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (1, 'Anonymous', '2', true) ;
	INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (2, 'LoggedIn', '3', true) ;

	PERFORM setval ('pfo_role_seq', 2) ;

	INSERT INTO pfo_role (SELECT nextval ('pfo_role_seq'), role_name, 1, group_id, false, role_id FROM role) ;

	INSERT INTO pfo_user_role (SELECT DISTINCT ug.user_id, r.role_id FROM user_group ug, pfo_role r WHERE ug.role_id = r.old_role_id) ;

	PERFORM migrate_rbac_permissions_to_pfo_rbac () ;
	PERFORM migrate_role_observer_to_pfo_rbac () ;
END ;
$$ LANGUAGE plpgsql ;