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 (2, 'tracker', t.group_artifact_id, 1) ;
197 FOR p IN SELECT * FROM project_group_list WHERE group_id = g.group_id AND is_public = 1
199 PERFORM insert_pfo_role_setting (1, 'pm', p.group_project_id, 1) ;
200 PERFORM insert_pfo_role_setting (2, 'pm', p.group_project_id, 1) ;
203 FOR f IN SELECT * FROM forum_group_list WHERE group_id = g.group_id AND is_public = 1
205 IF f.allow_anonymous = 1 THEN
206 IF f.moderation_level = 0 THEN
207 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 3) ;
209 PERFORM insert_pfo_role_setting (1, 'forum', f.group_forum_id, 2) ;
213 IF f.moderation_level = 0 THEN
214 PERFORM insert_pfo_role_setting (2, 'forum', f.group_forum_id, 3) ;
216 PERFORM insert_pfo_role_setting (2, 'forum', f.group_forum_id, 2) ;
223 $$ LANGUAGE plpgsql ;
225 CREATE FUNCTION pfo_rbac_permissions_from_old (rid integer, nsec text, nref integer) RETURNS integer AS $$
227 os role_setting%ROWTYPE ;
232 mastergroupid integer := 1 ;
233 newsgroupid integer := 0 ;
234 statsgroupid integer := 0 ;
238 SELECT group_id INTO newsgroupid FROM groups WHERE unix_group_name = 'newsadmin' ;
239 SELECT group_id INTO statsgroupid FROM groups WHERE unix_group_name = 'stats' ;
241 SELECT * INTO r FROM pfo_role WHERE old_role_id = rid ;
243 IF nsec = 'project_read' AND nref = r.home_group_id THEN
247 FOR os IN SELECT * FROM role_setting WHERE role_id = rid ORDER BY role_id, section_name, ref_id
249 SELECT group_id INTO opid FROM role WHERE role_id = os.role_id ;
251 IF os.section_name = 'projectadmin' THEN
252 CONTINUE WHEN os.value != 'A' ;
253 IF nsec = 'project_admin' AND nref = opid THEN
257 IF nsec = 'forge_admin' AND nref = -1 AND opid = mastergroupid THEN
260 IF nsec = 'approve_news' AND nref = -1 AND opid = newsgroupid THEN
263 IF nsec = 'forge_stats' AND nref = -1 AND opid = statsgroupid THEN
267 ELSIF os.section_name IN ('trackeradmin', 'pmadmin', 'forumadmin') THEN
268 CONTINUE WHEN os.value != '2' ;
269 onsec = CASE WHEN os.section_name = 'trackeradmin' THEN 'tracker_admin'
270 WHEN os.section_name = 'pmadmin' THEN 'pm_admin'
271 WHEN os.section_name = 'forumadmin' THEN 'forum_admin' END ;
272 IF nsec = onsec AND nref = opid THEN
276 ELSIF os.section_name IN ('tracker', 'newtracker') THEN
277 CONTINUE WHEN os.value = '-1' ;
278 onsec = CASE WHEN os.section_name = 'tracker' THEN os.section_name
279 WHEN os.section_name = 'newtracker' THEN 'new_tracker' END ;
280 onref = CASE WHEN os.section_name = 'tracker' THEN os.ref_id
281 WHEN os.section_name = 'newtracker' THEN opid END ;
282 onval = CASE WHEN os.value = '0' THEN 1
283 WHEN os.value = '1' THEN 3
284 WHEN os.value = '2' THEN 7
285 WHEN os.value = '3' THEN 5 END ;
286 IF nsec = onsec AND nref = onref THEN
290 ELSIF os.section_name IN ('pm', 'newpm') THEN
291 CONTINUE WHEN os.value = '-1' ;
292 onsec = CASE WHEN os.section_name = 'pm' THEN os.section_name
293 WHEN os.section_name = 'newpm' THEN 'new_pm' END ;
294 onref = CASE WHEN os.section_name = 'pm' THEN os.ref_id
295 WHEN os.section_name = 'newpm' THEN opid END ;
296 onval = CASE WHEN os.value = '0' THEN 1
297 WHEN os.value = '1' THEN 3
298 WHEN os.value = '2' THEN 7
299 WHEN os.value = '3' THEN 5 END ;
300 IF nsec = onsec AND nref = onref THEN
304 ELSIF os.section_name = 'forum' THEN
305 CONTINUE WHEN os.value = '-1' ;
306 onsec = os.section_name ;
308 SELECT moderation_level INTO tmp FROM forum_group_list WHERE group_forum_id = onref ;
309 onval = CASE WHEN os.value = '0' THEN 1
310 WHEN os.value = '1' AND tmp >= 2 THEN 2
311 WHEN os.value = '1' AND tmp <= 1 THEN 3
312 WHEN os.value = '2' THEN 4 END ;
313 IF nsec = onsec AND nref = onref THEN
317 ELSIF os.section_name = 'newforum' THEN
318 CONTINUE WHEN os.value = '-1' ;
319 onsec = 'new_forum' ;
321 onval = CASE WHEN os.value = '0' THEN 1
322 WHEN os.value = '1' THEN 2
323 WHEN os.value = '2' THEN 4 END ;
324 IF nsec = onsec AND nref = onref THEN
328 ELSIF os.section_name = 'docman' THEN
329 onsec = os.section_name ;
331 onval = CASE WHEN os.value = '0' THEN 1
332 WHEN os.value = '1' THEN 4 END ;
333 IF nsec = onsec AND nref = onref THEN
337 ELSIF os.section_name = 'frs' THEN
338 onsec = os.section_name ;
340 onval = CASE WHEN os.value = '0' THEN 1
341 WHEN os.value = '1' THEN 3 END ;
342 IF nsec = onsec AND nref = onref THEN
346 ELSIF os.section_name = 'scm' THEN
347 CONTINUE WHEN os.value = '-1' ;
348 onsec = os.section_name ;
350 onval = CASE WHEN os.value = '0' THEN 1
351 WHEN os.value = '1' THEN 2 END ;
352 IF nsec = onsec AND nref = onref THEN
356 ELSIF os.section_name = 'webcal' THEN
357 CONTINUE WHEN os.value = '0' ;
358 onsec = os.section_name ;
361 IF nsec = onsec AND nref = onref THEN
365 ELSIF os.section_name = 'plugin_mediawiki_edit' THEN
366 CONTINUE WHEN os.value = '0' ;
367 onsec = os.section_name ;
370 IF nsec = onsec AND nref = onref THEN
375 RAISE EXCEPTION 'Unknown setting % for role %', os.section_name, os.role_id ;
376 CONTINUE WHEN os.value = '0' ;
377 onsec = os.section_name ;
379 onval = os.value::integer ;
380 IF nsec = onsec AND nref = onref THEN
391 $$ LANGUAGE plpgsql ;
393 CREATE FUNCTION pfo_rbac_full_migration () RETURNS void AS $$
396 DELETE FROM pfo_user_role ;
397 DELETE FROM pfo_role_setting ;
398 DELETE FROM role_project_refs ;
399 DELETE FROM pfo_role ;
400 DELETE FROM pfo_role_class ;
402 INSERT INTO pfo_role_class (class_id, class_name) VALUES (1, 'PFO_RoleExplicit') ;
403 INSERT INTO pfo_role_class (class_id, class_name) VALUES (2, 'PFO_RoleAnonymous') ;
404 INSERT INTO pfo_role_class (class_id, class_name) VALUES (3, 'PFO_RoleLoggedIn') ;
406 PERFORM setval ('pfo_role_class_seq', 3) ;
408 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (1, 'Anonymous', '2', true) ;
409 INSERT INTO pfo_role (role_id, role_name, role_class, is_public) VALUES (2, 'LoggedIn', '3', true) ;
411 PERFORM setval ('pfo_role_seq', 2) ;
413 INSERT INTO pfo_role (SELECT nextval ('pfo_role_seq'), role_name, 1, group_id, false, role_id FROM role) ;
415 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) ;
417 PERFORM migrate_rbac_permissions_to_pfo_rbac () ;
418 PERFORM migrate_role_observer_to_pfo_rbac () ;
420 $$ LANGUAGE plpgsql ;