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 IF nsec = 'forge_admin' AND nref = -1 AND rid = 1 THEN
24 SELECT count(*) INTO tmp FROM role_setting WHERE role_id = rid ;
30 FOR os IN SELECT * FROM role_setting WHERE role_id = rid ORDER BY role_id, section_name, ref_id
32 SELECT group_id INTO opid FROM role WHERE role_id = os.role_id ;
34 IF os.section_name = 'projectadmin' THEN
35 CONTINUE WHEN os.value != 'A' ;
36 IF nsec = 'project_admin' AND nref = opid THEN
39 IF nsec = 'forge_admin' AND nref = -1 AND opid = mastergroupid THEN
42 IF nsec = 'approve_news' AND nref = -1 AND opid = newsgroupid THEN
45 IF nsec = 'forge_stats' AND nref = -1 AND opid = statsgroupid THEN
49 ELSIF os.section_name IN ('trackeradmin', 'pmadmin', 'forumadmin') THEN
50 CONTINUE WHEN os.value != '2' ;
51 onsec = CASE WHEN os.section_name = 'trackeradmin' THEN 'tracker_admin'
52 WHEN os.section_name = 'pmadmin' THEN 'pm_admin'
53 WHEN os.section_name = 'forumadmin' THEN 'forum_admin' END ;
54 IF nsec = onsec AND nref = opid THEN
58 ELSIF os.section_name IN ('tracker', 'newtracker') THEN
59 CONTINUE WHEN os.value = '-1' ;
60 onsec = CASE WHEN os.section_name = 'tracker' THEN os.section_name
61 WHEN os.section_name = 'newtracker' THEN 'new_tracker' END ;
62 onref = CASE WHEN os.section_name = 'tracker' THEN os.ref_id
63 WHEN os.section_name = 'newtracker' THEN opid END ;
64 onval = CASE WHEN os.value = '0' THEN 1
65 WHEN os.value = '1' THEN 3
66 WHEN os.value = '2' THEN 7
67 WHEN os.value = '3' THEN 5 END ;
68 IF nsec = onsec AND nref = onref THEN
72 ELSIF os.section_name IN ('pm', 'newpm') THEN
73 CONTINUE WHEN os.value = '-1' ;
74 onsec = CASE WHEN os.section_name = 'pm' THEN os.section_name
75 WHEN os.section_name = 'newpm' THEN 'new_pm' END ;
76 onref = CASE WHEN os.section_name = 'pm' THEN os.ref_id
77 WHEN os.section_name = 'newpm' THEN opid END ;
78 onval = CASE WHEN os.value = '0' THEN 1
79 WHEN os.value = '1' THEN 3
80 WHEN os.value = '2' THEN 7
81 WHEN os.value = '3' THEN 5 END ;
82 IF nsec = onsec AND nref = onref THEN
86 ELSIF os.section_name = 'forum' THEN
87 CONTINUE WHEN os.value = '-1' ;
88 onsec = os.section_name ;
90 SELECT moderation_level INTO tmp FROM forum_group_list WHERE group_forum_id = onref ;
91 onval = CASE WHEN os.value = '0' THEN 1
92 WHEN os.value = '1' AND tmp >= 2 THEN 2
93 WHEN os.value = '1' AND tmp <= 1 THEN 3
94 WHEN os.value = '2' THEN 4 END ;
95 IF nsec = onsec AND nref = onref THEN
99 ELSIF os.section_name = 'newforum' THEN
100 CONTINUE WHEN os.value = '-1' ;
101 onsec = 'new_forum' ;
103 onval = CASE WHEN os.value = '0' THEN 1
104 WHEN os.value = '1' THEN 2
105 WHEN os.value = '2' THEN 4 END ;
106 IF nsec = onsec AND nref = onref THEN
110 ELSIF os.section_name = 'docman' THEN
111 onsec = os.section_name ;
113 onval = CASE WHEN os.value = '0' THEN 1
114 WHEN os.value = '1' THEN 4 END ;
115 IF nsec = onsec AND nref = onref THEN
119 ELSIF os.section_name = 'frs' THEN
120 onsec = os.section_name ;
122 onval = CASE WHEN os.value = '0' THEN 1
123 WHEN os.value = '1' THEN 3 END ;
124 IF nsec = onsec AND nref = onref THEN
128 ELSIF os.section_name = 'scm' THEN
129 CONTINUE WHEN os.value = '-1' ;
130 onsec = os.section_name ;
132 onval = CASE WHEN os.value = '0' THEN 1
133 WHEN os.value = '1' THEN 2 END ;
134 IF nsec = onsec AND nref = onref THEN
138 ELSIF os.section_name = 'webcal' THEN
139 CONTINUE WHEN os.value = '0' ;
140 onsec = os.section_name ;
143 IF nsec = onsec AND nref = onref THEN
147 ELSIF os.section_name = 'plugin_mediawiki_edit' THEN
148 CONTINUE WHEN os.value = '0' ;
149 onsec = os.section_name ;
152 IF nsec = onsec AND nref = onref THEN
157 RAISE EXCEPTION 'Unknown setting % for role %', os.section_name, os.role_id ;
158 CONTINUE WHEN os.value = '0' ;
159 onsec = os.section_name ;
161 onval = os.value::integer ;
162 IF nsec = onsec AND nref = onref THEN
173 $$ LANGUAGE plpgsql ;
175 CREATE OR REPLACE FUNCTION migrate_role_observer_to_pfo_rbac () RETURNS void AS $$
178 t artifact_group_list%ROWTYPE ;
179 f forum_group_list%ROWTYPE ;
180 p project_group_list%ROWTYPE ;
181 need_loggedin boolean := false ;
183 FOR g IN SELECT * FROM groups WHERE is_public = 1
185 INSERT INTO role_project_refs VALUES (1, g.group_id) ;
186 INSERT INTO role_project_refs VALUES (2, g.group_id) ;
187 PERFORM insert_pfo_role_setting (1, 'project_read', g.group_id, 1) ;
188 PERFORM insert_pfo_role_setting (1, 'new_tracker', g.group_id, 1) ;
189 PERFORM insert_pfo_role_setting (1, 'new_pm', g.group_id, 1) ;
190 PERFORM insert_pfo_role_setting (1, 'new_forum', g.group_id, 1) ;
191 PERFORM insert_pfo_role_setting (1, 'frs', g.group_id, 1) ;
192 PERFORM insert_pfo_role_setting (2, 'project_read', g.group_id, 1) ;
193 PERFORM insert_pfo_role_setting (2, 'new_tracker', g.group_id, 1) ;
194 PERFORM insert_pfo_role_setting (2, 'new_pm', g.group_id, 1) ;
195 PERFORM insert_pfo_role_setting (2, 'new_forum', g.group_id, 1) ;
196 PERFORM insert_pfo_role_setting (2, 'frs', g.group_id, 1) ;
198 IF g.enable_anonscm = 1 THEN
199 PERFORM insert_pfo_role_setting (1, 'scm', g.group_id, 1) ;
200 PERFORM insert_pfo_role_setting (2, 'scm', g.group_id, 1) ;
203 FOR t IN SELECT * FROM artifact_group_list WHERE group_id = g.group_id
205 IF t.is_public = 1 THEN
206 IF t.allow_anon = 1 THEN
207 PERFORM insert_pfo_role_setting (1, 'tracker', t.group_artifact_id, 1) ;
209 PERFORM insert_pfo_role_setting (1, 'tracker', t.group_artifact_id, 0) ;
212 PERFORM insert_pfo_role_setting (2, 'tracker', t.group_artifact_id, 1) ;
214 PERFORM insert_pfo_role_setting (1, 'tracker', t.group_artifact_id, 0) ;
215 PERFORM insert_pfo_role_setting (2, 'tracker', t.group_artifact_id, 0) ;
219 FOR p IN SELECT * FROM project_group_list WHERE group_id = g.group_id
221 IF p.is_public = 1 THEN
222 PERFORM insert_pfo_role_setting (1, 'pm', p.group_project_id, 1) ;
223 PERFORM insert_pfo_role_setting (2, 'pm', p.group_project_id, 1) ;
225 PERFORM insert_pfo_role_setting (1, 'pm', p.group_project_id, 0) ;
226 PERFORM insert_pfo_role_setting (2, 'pm', p.group_project_id, 0) ;
230 FOR f IN SELECT * FROM forum_group_list WHERE group_id = g.group_id AND is_public = 1
232 IF f.is_public = 1 THEN
233 IF f.allow_anonymous = 1 THEN
234 IF f.moderation_level = 0 THEN
235 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 3) ;
237 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 2) ;
240 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 1) ;
243 IF f.moderation_level = 0 THEN
244 PERFORM insert_pfo_role_setting (2, 'forum', f.group_forum_id, 3) ;
246 PERFORM insert_pfo_role_setting (2, 'forum', f.group_forum_id, 2) ;
249 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 0) ;
250 PERFORM insert_pfo_role_setting (2, 'forum', f.group_forum_id, 0) ;
257 $$ LANGUAGE plpgsql ;
259 CREATE OR REPLACE FUNCTION pfo_rbac_full_migration () RETURNS void AS $$
261 mastergroupid integer := 1 ;
262 newsgroupid integer := 0 ;
263 statsgroupid integer := 0 ;
265 DELETE FROM pfo_user_role ;
266 DELETE FROM pfo_role_setting ;
267 DELETE FROM role_project_refs ;
268 DELETE FROM pfo_role ;
269 DELETE FROM pfo_role_class ;
271 INSERT INTO pfo_role_class (class_id, class_name) VALUES (1, 'PFO_RoleExplicit') ;
272 INSERT INTO pfo_role_class (class_id, class_name) VALUES (2, 'PFO_RoleAnonymous') ;
273 INSERT INTO pfo_role_class (class_id, class_name) VALUES (3, 'PFO_RoleLoggedIn') ;
275 PERFORM setval ('pfo_role_class_seq', 3) ;
277 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (1, 'Anonymous', 2, true) ;
278 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (2, 'LoggedIn', 3, true) ;
279 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (3, 'Forge administrators', 1, false) ;
280 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (4, 'News moderators', 1, false) ;
281 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (5, 'Stats administrators', 1, false) ;
283 PERFORM setval ('pfo_role_seq', 5) ;
285 INSERT INTO pfo_role (SELECT nextval ('pfo_role_seq'), role_name, 1, group_id, false, role_id FROM role) ;
287 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) ;
289 PERFORM migrate_rbac_permissions_to_pfo_rbac () ;
290 PERFORM migrate_role_observer_to_pfo_rbac () ;
292 -- Set up members and permissions for Forge administrators
293 INSERT INTO pfo_user_role (SELECT user_id, 3 FROM user_group WHERE group_id = mastergroupid AND admin_flags = 'A') ;
294 PERFORM insert_pfo_role_setting (3, 'forge_admin', -1, 1) ;
296 -- Set up members and permissions for News moderators
297 SELECT group_id INTO newsgroupid FROM groups WHERE unix_group_name = 'newsadmin' ;
298 INSERT INTO pfo_user_role (SELECT user_id, 4 FROM user_group WHERE group_id = newsgroupid AND admin_flags = 'A') ;
299 PERFORM insert_pfo_role_setting (4, 'approve_news', -1, 1) ;
301 -- Set up members and permissions for Stats administrators
302 SELECT group_id INTO statsgroupid FROM groups WHERE unix_group_name = 'stats' ;
303 INSERT INTO pfo_user_role (SELECT user_id, 5 FROM user_group WHERE group_id = statsgroupid AND admin_flags = 'A') ;
304 PERFORM insert_pfo_role_setting (5, 'forge_stats', -1, 2) ;
306 DELETE FROM pfo_role_setting WHERE section_name IN ('forge_admin', 'approve_projects', 'approve_news', 'forge_stats') AND role_id IN (SELECT role_id FROM pfo_role WHERE role_class = 1 AND home_group_id IS NOT NULL) ;
309 $$ LANGUAGE plpgsql ;