1 CREATE OR REPLACE FUNCTION pfo_rbac_permissions_from_old (rid integer, nsec text, nref integer) RETURNS integer AS $$
3 os role_setting%ROWTYPE ;
8 mastergroupid integer := 1 ;
9 newsgroupid integer := 0 ;
10 statsgroupid integer := 0 ;
14 SELECT group_id INTO newsgroupid FROM groups WHERE unix_group_name = 'newsadmin' ;
15 SELECT group_id INTO statsgroupid FROM groups WHERE unix_group_name = 'stats' ;
17 SELECT * INTO r FROM pfo_role WHERE old_role_id = rid ;
19 IF nsec = 'project_read' AND nref = r.home_group_id THEN
23 FOR os IN SELECT * FROM role_setting WHERE role_id = rid ORDER BY role_id, section_name, ref_id
25 SELECT group_id INTO opid FROM role WHERE role_id = os.role_id ;
27 IF os.section_name = 'projectadmin' THEN
28 CONTINUE WHEN os.value != 'A' ;
29 IF nsec = 'project_admin' AND nref = opid THEN
33 ELSIF os.section_name IN ('trackeradmin', 'pmadmin', 'forumadmin') THEN
34 CONTINUE WHEN os.value != '2' ;
35 onsec = CASE WHEN os.section_name = 'trackeradmin' THEN 'tracker_admin'
36 WHEN os.section_name = 'pmadmin' THEN 'pm_admin'
37 WHEN os.section_name = 'forumadmin' THEN 'forum_admin' END ;
38 IF nsec = onsec AND nref = opid THEN
42 ELSIF os.section_name IN ('tracker', 'newtracker') THEN
43 CONTINUE WHEN os.value = '-1' ;
44 onsec = CASE WHEN os.section_name = 'tracker' THEN os.section_name
45 WHEN os.section_name = 'newtracker' THEN 'new_tracker' END ;
46 onref = CASE WHEN os.section_name = 'tracker' THEN os.ref_id
47 WHEN os.section_name = 'newtracker' THEN opid END ;
48 onval = CASE WHEN os.value = '0' THEN 1
49 WHEN os.value = '1' THEN 3
50 WHEN os.value = '2' THEN 7
51 WHEN os.value = '3' THEN 5 END ;
52 IF nsec = onsec AND nref = onref THEN
56 ELSIF os.section_name IN ('pm', 'newpm') THEN
57 CONTINUE WHEN os.value = '-1' ;
58 onsec = CASE WHEN os.section_name = 'pm' THEN os.section_name
59 WHEN os.section_name = 'newpm' THEN 'new_pm' END ;
60 onref = CASE WHEN os.section_name = 'pm' THEN os.ref_id
61 WHEN os.section_name = 'newpm' THEN opid END ;
62 onval = CASE WHEN os.value = '0' THEN 1
63 WHEN os.value = '1' THEN 3
64 WHEN os.value = '2' THEN 7
65 WHEN os.value = '3' THEN 5 END ;
66 IF nsec = onsec AND nref = onref THEN
70 ELSIF os.section_name = 'forum' THEN
71 CONTINUE WHEN os.value = '-1' ;
72 onsec = os.section_name ;
74 SELECT moderation_level INTO tmp FROM forum_group_list WHERE group_forum_id = onref ;
75 onval = CASE WHEN os.value = '0' THEN 1
76 WHEN os.value = '1' AND tmp >= 2 THEN 2
77 WHEN os.value = '1' AND tmp <= 1 THEN 3
78 WHEN os.value = '2' THEN 4 END ;
79 IF nsec = onsec AND nref = onref THEN
83 ELSIF os.section_name = 'newforum' THEN
84 CONTINUE WHEN os.value = '-1' ;
87 onval = CASE WHEN os.value = '0' THEN 1
88 WHEN os.value = '1' THEN 2
89 WHEN os.value = '2' THEN 4 END ;
90 IF nsec = onsec AND nref = onref THEN
94 ELSIF os.section_name = 'docman' THEN
95 onsec = os.section_name ;
97 onval = CASE WHEN os.value = '0' THEN 1
98 WHEN os.value = '1' THEN 4 END ;
99 IF nsec = onsec AND nref = onref THEN
103 ELSIF os.section_name = 'frs' THEN
104 onsec = os.section_name ;
106 onval = CASE WHEN os.value = '0' THEN 1
107 WHEN os.value = '1' THEN 3 END ;
108 IF nsec = onsec AND nref = onref THEN
112 ELSIF os.section_name = 'scm' THEN
113 CONTINUE WHEN os.value = '-1' ;
114 onsec = os.section_name ;
116 onval = CASE WHEN os.value = '0' THEN 1
117 WHEN os.value = '1' THEN 2 END ;
118 IF nsec = onsec AND nref = onref THEN
122 ELSIF os.section_name = 'webcal' THEN
123 CONTINUE WHEN os.value = '0' ;
124 onsec = os.section_name ;
127 IF nsec = onsec AND nref = onref THEN
131 ELSIF os.section_name = 'plugin_mediawiki_edit' THEN
132 CONTINUE WHEN os.value = '0' ;
133 onsec = os.section_name ;
136 IF nsec = onsec AND nref = onref THEN
141 RAISE EXCEPTION 'Unknown setting % for role %', os.section_name, os.role_id ;
142 CONTINUE WHEN os.value = '0' ;
143 onsec = os.section_name ;
145 onval = os.value::integer ;
146 IF nsec = onsec AND nref = onref THEN
157 $$ LANGUAGE plpgsql ;
159 CREATE OR REPLACE FUNCTION migrate_role_observer_to_pfo_rbac () RETURNS void AS $$
162 t artifact_group_list%ROWTYPE ;
163 f forum_group_list%ROWTYPE ;
164 p project_group_list%ROWTYPE ;
165 need_loggedin boolean := false ;
167 FOR g IN SELECT * FROM groups WHERE is_public = 1
169 INSERT INTO role_project_refs VALUES (1, g.group_id) ;
170 INSERT INTO role_project_refs VALUES (2, g.group_id) ;
171 PERFORM insert_pfo_role_setting (1, 'project_read', g.group_id, 1) ;
172 PERFORM insert_pfo_role_setting (1, 'new_tracker', g.group_id, 1) ;
173 PERFORM insert_pfo_role_setting (1, 'new_pm', g.group_id, 1) ;
174 PERFORM insert_pfo_role_setting (1, 'new_forum', g.group_id, 1) ;
175 PERFORM insert_pfo_role_setting (1, 'frs', g.group_id, 1) ;
176 PERFORM insert_pfo_role_setting (2, 'project_read', g.group_id, 1) ;
177 PERFORM insert_pfo_role_setting (2, 'new_tracker', g.group_id, 1) ;
178 PERFORM insert_pfo_role_setting (2, 'new_pm', g.group_id, 1) ;
179 PERFORM insert_pfo_role_setting (2, 'new_forum', g.group_id, 1) ;
180 PERFORM insert_pfo_role_setting (2, 'frs', g.group_id, 1) ;
182 IF g.enable_anonscm = 1 THEN
183 PERFORM insert_pfo_role_setting (1, 'scm', g.group_id, 1) ;
184 PERFORM insert_pfo_role_setting (2, 'scm', g.group_id, 1) ;
187 FOR t IN SELECT * FROM artifact_group_list WHERE group_id = g.group_id AND is_public = 1
189 IF t.allow_anon = 1 THEN
190 PERFORM insert_pfo_role_setting (1, 'tracker', t.group_artifact_id, 1) ;
193 PERFORM insert_pfo_role_setting (2, 'tracker', t.group_artifact_id, 1) ;
196 FOR p IN SELECT * FROM project_group_list WHERE group_id = g.group_id AND is_public = 1
198 PERFORM insert_pfo_role_setting (1, 'pm', p.group_project_id, 1) ;
199 PERFORM insert_pfo_role_setting (2, 'pm', p.group_project_id, 1) ;
202 FOR f IN SELECT * FROM forum_group_list WHERE group_id = g.group_id AND is_public = 1
204 IF f.allow_anonymous = 1 THEN
205 IF f.moderation_level = 0 THEN
206 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 3) ;
208 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 2) ;
211 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 1) ;
214 IF f.moderation_level = 0 THEN
215 PERFORM insert_pfo_role_setting (2, 'forum', f.group_forum_id, 3) ;
217 PERFORM insert_pfo_role_setting (2, 'forum', f.group_forum_id, 2) ;
224 $$ LANGUAGE plpgsql ;
226 CREATE OR REPLACE FUNCTION pfo_rbac_full_migration () RETURNS void AS $$
228 mastergroupid integer := 1 ;
229 newsgroupid integer := 0 ;
230 statsgroupid integer := 0 ;
232 DELETE FROM pfo_user_role ;
233 DELETE FROM pfo_role_setting ;
234 DELETE FROM role_project_refs ;
235 DELETE FROM pfo_role ;
236 DELETE FROM pfo_role_class ;
238 INSERT INTO pfo_role_class (class_id, class_name) VALUES (1, 'PFO_RoleExplicit') ;
239 INSERT INTO pfo_role_class (class_id, class_name) VALUES (2, 'PFO_RoleAnonymous') ;
240 INSERT INTO pfo_role_class (class_id, class_name) VALUES (3, 'PFO_RoleLoggedIn') ;
242 PERFORM setval ('pfo_role_class_seq', 3) ;
244 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (1, 'Anonymous', 2, true) ;
245 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (2, 'LoggedIn', 3, true) ;
246 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (3, 'Forge administrators', 1, false) ;
247 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (4, 'News moderators', 1, false) ;
248 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (5, 'Stats administrators', 1, false) ;
250 PERFORM setval ('pfo_role_seq', 5) ;
252 INSERT INTO pfo_role (SELECT nextval ('pfo_role_seq'), role_name, 1, group_id, false, role_id FROM role) ;
254 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 AND r.old_role_id != 1) ;
256 PERFORM migrate_rbac_permissions_to_pfo_rbac () ;
257 PERFORM migrate_role_observer_to_pfo_rbac () ;
259 -- Set up members and permissions for Forge administrators
260 INSERT INTO pfo_user_role (SELECT user_id, 3 FROM user_group WHERE group_id = mastergroupid AND admin_flags = 'A') ;
261 PERFORM insert_pfo_role_setting (3, 'forge_admin', -1, 1) ;
263 -- Set up members and permissions for News moderators
264 SELECT group_id INTO newsgroupid FROM groups WHERE unix_group_name = 'newsadmin' ;
265 INSERT INTO pfo_user_role (SELECT user_id, 4 FROM user_group WHERE group_id = newsgroupid AND admin_flags = 'A') ;
266 PERFORM insert_pfo_role_setting (4, 'approve_news', -1, 1) ;
268 -- Set up members and permissions for Stats administrators
269 SELECT group_id INTO statsgroupid FROM groups WHERE unix_group_name = 'stats' ;
270 INSERT INTO pfo_user_role (SELECT user_id, 5 FROM user_group WHERE group_id = statsgroupid AND admin_flags = 'A') ;
271 PERFORM insert_pfo_role_setting (5, 'forge_stats', -1, 2) ;
274 $$ LANGUAGE plpgsql ;