1 CREATE SEQUENCE pfo_role_class_seq ;
2 CREATE TABLE pfo_role_class (
3 class_id integer DEFAULT nextval ('pfo_role_class_seq') NOT NULL,
4 class_name text DEFAULT '' NOT NULL,
5 CONSTRAINT pfo_role_class_pkey PRIMARY KEY (class_id),
6 CONSTRAINT pfo_role_class_name_unique UNIQUE (class_name)
9 CREATE SEQUENCE pfo_role_seq ;
10 CREATE TABLE pfo_role (
11 role_id integer DEFAULT nextval ('pfo_role_seq') NOT NULL,
12 role_name text DEFAULT '' NOT NULL,
13 role_class integer DEFAULT 1 NOT NULL REFERENCES pfo_role_class (class_id),
14 home_group_id integer,
15 is_public boolean DEFAULT false NOT NULL,
16 old_role_id integer DEFAULT 0 NOT NULL,
17 CONSTRAINT pfo_role_pkey PRIMARY KEY (role_id),
18 CONSTRAINT pfo_role_name_unique UNIQUE (role_id, role_name)
21 CREATE TABLE role_project_refs (
22 role_id integer DEFAULT 0 NOT NULL REFERENCES pfo_role,
23 group_id integer DEFAULT 0 NOT NULL REFERENCES groups,
24 CONSTRAINT role_project_refs_unique UNIQUE (role_id, group_id)
27 CREATE TABLE pfo_role_setting (
28 role_id integer DEFAULT 0 NOT NULL REFERENCES pfo_role,
29 section_name text DEFAULT '' NOT NULL,
30 ref_id integer DEFAULT 0 NOT NULL,
31 perm_val integer DEFAULT 0 NOT NULL,
32 CONSTRAINT pfo_role_setting_unique UNIQUE (role_id, section_name, ref_id)
35 CREATE TABLE pfo_user_role (
36 user_id integer DEFAULT 0 NOT NULL REFERENCES users,
37 role_id integer DEFAULT 0 NOT NULL REFERENCES pfo_role,
38 CONSTRAINT pfo_user_role_unique UNIQUE (user_id, role_id)
41 CREATE FUNCTION insert_pfo_role_setting (role_id integer, section_name text, ref_id integer, perm_val integer) RETURNS void AS $$
44 INSERT INTO pfo_role_setting VALUES (role_id, section_name, ref_id, perm_val) ;
49 CREATE FUNCTION migrate_rbac_permissions_to_pfo_rbac () RETURNS void AS $$
57 agl artifact_group_list%ROWTYPE ;
58 pgl project_group_list%ROWTYPE ;
59 fgl forum_group_list%ROWTYPE ;
61 FOR r IN SELECT * FROM role
63 SELECT role_id INTO nrid FROM pfo_role WHERE old_role_id = r.role_id ;
64 SELECT group_id INTO opid FROM role WHERE role_id = r.role_id ;
66 PERFORM insert_pfo_role_setting (nrid, 'project_read', opid, 1) ;
68 nsec = 'project_admin' ;
70 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
71 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
73 nsec = 'forge_admin' ;
75 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
76 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
77 nsec = 'approve_news' ;
79 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
80 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
81 nsec = 'forge_stats' ;
83 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
84 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
86 nsec = 'tracker_admin' ;
88 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
89 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
90 nsec = 'new_tracker' ;
92 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
93 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
95 FOR agl IN SELECT * FROM artifact_group_list WHERE group_id = opid
97 nref = agl.group_artifact_id ;
98 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
99 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
104 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
105 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
108 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
109 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
111 FOR pgl IN SELECT * FROM project_group_list WHERE group_id = opid
113 nref = pgl.group_project_id ;
114 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
115 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
118 nsec = 'forum_admin' ;
120 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
121 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
124 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
125 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
127 FOR fgl IN SELECT * FROM forum_group_list WHERE group_id = opid
129 nref = fgl.group_forum_id ;
130 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
131 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
136 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
137 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
140 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
141 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
144 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
145 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
148 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
149 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
150 nsec = 'plugin_mediawiki_edit' ;
152 nval = pfo_rbac_permissions_from_old (r.role_id, nsec, nref) ;
153 PERFORM insert_pfo_role_setting (nrid, nsec, nref, nval) ;
158 $$ LANGUAGE plpgsql ;
160 CREATE FUNCTION migrate_role_observer_to_pfo_rbac () RETURNS void AS $$
163 t artifact_group_list%ROWTYPE ;
164 f forum_group_list%ROWTYPE ;
165 p project_group_list%ROWTYPE ;
166 need_loggedin boolean := false ;
168 FOR g IN SELECT * FROM groups WHERE is_public = 1
170 INSERT INTO role_project_refs VALUES (1, g.group_id) ;
171 INSERT INTO role_project_refs VALUES (2, g.group_id) ;
172 PERFORM insert_pfo_role_setting (1, 'project_read', g.group_id, 1) ;
173 PERFORM insert_pfo_role_setting (1, 'new_tracker', g.group_id, 1) ;
174 PERFORM insert_pfo_role_setting (1, 'new_pm', g.group_id, 1) ;
175 PERFORM insert_pfo_role_setting (1, 'new_forum', g.group_id, 1) ;
176 PERFORM insert_pfo_role_setting (1, 'frs', g.group_id, 1) ;
177 PERFORM insert_pfo_role_setting (2, 'project_read', g.group_id, 1) ;
178 PERFORM insert_pfo_role_setting (2, 'new_tracker', g.group_id, 1) ;
179 PERFORM insert_pfo_role_setting (2, 'new_pm', g.group_id, 1) ;
180 PERFORM insert_pfo_role_setting (2, 'new_forum', g.group_id, 1) ;
181 PERFORM insert_pfo_role_setting (2, 'frs', g.group_id, 1) ;
183 IF g.enable_anonscm = 1 THEN
184 PERFORM insert_pfo_role_setting (1, 'scm', g.group_id, 1) ;
185 PERFORM insert_pfo_role_setting (2, 'scm', g.group_id, 1) ;
188 FOR t IN SELECT * FROM artifact_group_list WHERE group_id = g.group_id AND is_public = 1
190 IF t.allow_anon = 1 THEN
191 PERFORM insert_pfo_role_setting (1, 'tracker', t.group_artifact_id, 1) ;
194 PERFORM insert_pfo_role_setting (1, 'tracker', t.group_artifact_id, 1) ;
195 PERFORM insert_pfo_role_setting (2, 'tracker', t.group_artifact_id, 1) ;
198 FOR p IN SELECT * FROM project_group_list WHERE group_id = g.group_id AND is_public = 1
200 PERFORM insert_pfo_role_setting (1, 'pm', p.group_project_id, 1) ;
201 PERFORM insert_pfo_role_setting (2, 'pm', p.group_project_id, 1) ;
204 FOR f IN SELECT * FROM forum_group_list WHERE group_id = g.group_id AND is_public = 1
206 IF f.allow_anonymous = 1 THEN
207 IF f.moderation_level = 0 THEN
208 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 3) ;
210 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 2) ;
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 FUNCTION pfo_rbac_permissions_from_old (rid integer, nsec text, nref integer) RETURNS integer AS $$
228 os role_setting%ROWTYPE ;
233 mastergroupid integer := 1 ;
234 newsgroupid integer := 0 ;
235 statsgroupid integer := 0 ;
239 SELECT group_id INTO newsgroupid FROM groups WHERE unix_group_name = 'newsadmin' ;
240 SELECT group_id INTO statsgroupid FROM groups WHERE unix_group_name = 'stats' ;
242 SELECT * INTO r FROM pfo_role WHERE old_role_id = rid ;
244 IF nsec = 'project_read' AND nref = r.home_group_id THEN
248 FOR os IN SELECT * FROM role_setting WHERE role_id = rid ORDER BY role_id, section_name, ref_id
250 SELECT group_id INTO opid FROM role WHERE role_id = os.role_id ;
252 IF os.section_name = 'projectadmin' THEN
253 CONTINUE WHEN os.value != 'A' ;
254 IF nsec = 'project_admin' AND nref = opid THEN
258 IF nsec = 'forge_admin' AND nref = -1 AND opid = mastergroupid THEN
261 IF nsec = 'approve_news' AND nref = -1 AND opid = newsgroupid THEN
264 IF nsec = 'forge_stats' AND nref = -1 AND opid = statsgroupid THEN
268 ELSIF os.section_name IN ('trackeradmin', 'pmadmin', 'forumadmin') THEN
269 CONTINUE WHEN os.value != '2' ;
270 onsec = CASE WHEN os.section_name = 'trackeradmin' THEN 'tracker_admin'
271 WHEN os.section_name = 'pmadmin' THEN 'pm_admin'
272 WHEN os.section_name = 'forumadmin' THEN 'forum_admin' END ;
273 IF nsec = onsec AND nref = opid THEN
277 ELSIF os.section_name IN ('tracker', 'newtracker') THEN
278 CONTINUE WHEN os.value = '-1' ;
279 onsec = CASE WHEN os.section_name = 'tracker' THEN os.section_name
280 WHEN os.section_name = 'newtracker' THEN 'new_tracker' END ;
281 onref = CASE WHEN os.section_name = 'tracker' THEN os.ref_id
282 WHEN os.section_name = 'newtracker' THEN opid END ;
283 onval = CASE WHEN os.value = '0' THEN 1
284 WHEN os.value = '1' THEN 3
285 WHEN os.value = '2' THEN 7
286 WHEN os.value = '3' THEN 5 END ;
287 IF nsec = onsec AND nref = onref THEN
291 ELSIF os.section_name IN ('pm', 'newpm') THEN
292 CONTINUE WHEN os.value = '-1' ;
293 onsec = CASE WHEN os.section_name = 'pm' THEN os.section_name
294 WHEN os.section_name = 'newpm' THEN 'new_pm' END ;
295 onref = CASE WHEN os.section_name = 'pm' THEN os.ref_id
296 WHEN os.section_name = 'newpm' THEN opid END ;
297 onval = CASE WHEN os.value = '0' THEN 1
298 WHEN os.value = '1' THEN 3
299 WHEN os.value = '2' THEN 7
300 WHEN os.value = '3' THEN 5 END ;
301 IF nsec = onsec AND nref = onref THEN
305 ELSIF os.section_name = 'forum' THEN
306 CONTINUE WHEN os.value = '-1' ;
307 onsec = os.section_name ;
309 SELECT moderation_level INTO tmp FROM forum_group_list WHERE group_forum_id = onref ;
310 onval = CASE WHEN os.value = '0' THEN 1
311 WHEN os.value = '1' AND tmp >= 2 THEN 2
312 WHEN os.value = '1' AND tmp <= 1 THEN 3
313 WHEN os.value = '2' THEN 4 END ;
314 IF nsec = onsec AND nref = onref THEN
318 ELSIF os.section_name = 'newforum' THEN
319 CONTINUE WHEN os.value = '-1' ;
320 onsec = 'new_forum' ;
322 onval = CASE WHEN os.value = '0' THEN 1
323 WHEN os.value = '1' THEN 2
324 WHEN os.value = '2' THEN 4 END ;
325 IF nsec = onsec AND nref = onref THEN
329 ELSIF os.section_name = 'docman' THEN
330 onsec = os.section_name ;
332 onval = CASE WHEN os.value = '0' THEN 1
333 WHEN os.value = '1' THEN 4 END ;
334 IF nsec = onsec AND nref = onref THEN
338 ELSIF os.section_name = 'frs' THEN
339 onsec = os.section_name ;
341 onval = CASE WHEN os.value = '0' THEN 1
342 WHEN os.value = '1' THEN 3 END ;
343 IF nsec = onsec AND nref = onref THEN
347 ELSIF os.section_name = 'scm' THEN
348 CONTINUE WHEN os.value = '-1' ;
349 onsec = os.section_name ;
351 onval = CASE WHEN os.value = '0' THEN 1
352 WHEN os.value = '1' THEN 2 END ;
353 IF nsec = onsec AND nref = onref THEN
357 ELSIF os.section_name = 'webcal' THEN
358 CONTINUE WHEN os.value = '0' ;
359 onsec = os.section_name ;
362 IF nsec = onsec AND nref = onref THEN
366 ELSIF os.section_name = 'plugin_mediawiki_edit' THEN
367 CONTINUE WHEN os.value = '0' ;
368 onsec = os.section_name ;
371 IF nsec = onsec AND nref = onref THEN
376 RAISE EXCEPTION 'Unknown setting % for role %', os.section_name, os.role_id ;
377 CONTINUE WHEN os.value = '0' ;
378 onsec = os.section_name ;
380 onval = os.value::integer ;
381 IF nsec = onsec AND nref = onref THEN
392 $$ LANGUAGE plpgsql ;
394 CREATE FUNCTION pfo_rbac_full_migration () RETURNS void AS $$
397 DELETE FROM pfo_user_role ;
398 DELETE FROM pfo_role_setting ;
399 DELETE FROM role_project_refs ;
400 DELETE FROM pfo_role ;
401 DELETE FROM pfo_role_class ;
403 INSERT INTO pfo_role_class (class_id, class_name) VALUES (1, 'PFO_RoleExplicit') ;
404 INSERT INTO pfo_role_class (class_id, class_name) VALUES (2, 'PFO_RoleAnonymous') ;
405 INSERT INTO pfo_role_class (class_id, class_name) VALUES (3, 'PFO_RoleLoggedIn') ;
407 PERFORM setval ('pfo_role_class_seq', 3) ;
409 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (1, 'Anonymous', '2', true) ;
410 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (2, 'LoggedIn', '3', true) ;
412 PERFORM setval ('pfo_role_seq', 2) ;
414 INSERT INTO pfo_role (SELECT nextval ('pfo_role_seq'), role_name, 1, group_id, false, role_id FROM role) ;
416 INSERT INTO pfo_user_role (SELECT ug.user_id, r.role_id FROM user_group ug, pfo_role r WHERE ug.role_id = r.old_role_id) ;
418 PERFORM migrate_rbac_permissions_to_pfo_rbac () ;
419 PERFORM migrate_role_observer_to_pfo_rbac () ;
421 $$ LANGUAGE plpgsql ;
423 SELECT pfo_rbac_full_migration () ;