5 add per-suite database permissions
7 @contact: Debian FTP Master <ftpmaster@debian.org>
8 @copyright: 2012 Ansgar Burchardt <ansgar@debian.org>
9 @license: GNU General Public License version 2 or later
12 # This program is free software; you can redistribute it and/or modify
13 # it under the terms of the GNU General Public License as published by
14 # the Free Software Foundation; either version 2 of the License, or
15 # (at your option) any later version.
17 # This program is distributed in the hope that it will be useful,
18 # but WITHOUT ANY WARRANTY; without even the implied warranty of
19 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 # GNU General Public License for more details.
22 # You should have received a copy of the GNU General Public License
23 # along with this program; if not, write to the Free Software
24 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
26 ################################################################################
29 from daklib.dak_exceptions import DBUpdateError
30 from daklib.config import Config
34 CREATE TABLE suite_permission (
35 suite_id INT NOT NULL REFERENCES suite(id) ON DELETE CASCADE,
37 PRIMARY KEY (suite_id, role)
42 CREATE OR REPLACE FUNCTION has_suite_permission(action TEXT, suite_id INT)
46 SET search_path = public, pg_temp
53 IF pg_has_role('ftpteam', 'USAGE') THEN
57 SELECT BOOL_OR(pg_has_role(sp.role, 'USAGE')) INTO v_result
58 FROM suite_permission sp
59 WHERE sp.suite_id = has_suite_permission.suite_id
62 IF v_result IS NULL THEN
73 CREATE OR REPLACE FUNCTION trigger_check_suite_permission() RETURNS TRIGGER
74 SET search_path = public, pg_temp
79 v_suite_name suite.suite_name%TYPE;
83 WHEN 'INSERT', 'UPDATE' THEN
88 RAISE EXCEPTION 'Unexpected TG_OP (%)', TG_OP;
91 IF TG_OP = 'UPDATE' AND OLD.suite != NEW.suite THEN
92 RAISE EXCEPTION 'Cannot change suite';
95 IF NOT has_suite_permission(TG_OP, v_row.suite) THEN
96 SELECT suite_name INTO STRICT v_suite_name FROM suite WHERE id = v_row.suite;
97 RAISE EXCEPTION 'Not allowed to % in %', TG_OP, v_suite_name;
107 CREATE CONSTRAINT TRIGGER trigger_override_permission
108 AFTER INSERT OR UPDATE OR DELETE
111 EXECUTE PROCEDURE trigger_check_suite_permission()
115 CREATE CONSTRAINT TRIGGER trigger_src_associations_permission
116 AFTER INSERT OR UPDATE OR DELETE
119 EXECUTE PROCEDURE trigger_check_suite_permission()
123 CREATE CONSTRAINT TRIGGER trigger_bin_associations_permission
124 AFTER INSERT OR UPDATE OR DELETE
127 EXECUTE PROCEDURE trigger_check_suite_permission()
131 ################################################################################
139 for stmt in statements:
142 c.execute("UPDATE config SET value = '84' WHERE name = 'db_revision'")
145 except psycopg2.ProgrammingError as msg:
147 raise DBUpdateError('Unable to apply sick update 84, rollback issued. Error message: {0}'.format(msg))