1"""
2add per-suite database permissions
4@contact: Debian FTP Master <ftpmaster@debian.org>
5@copyright: 2012 Ansgar Burchardt <ansgar@debian.org>
6@license: GNU General Public License version 2 or later
7"""
9# This program is free software; you can redistribute it and/or modify
10# it under the terms of the GNU General Public License as published by
11# the Free Software Foundation; either version 2 of the License, or
12# (at your option) any later version.
14# This program is distributed in the hope that it will be useful,
15# but WITHOUT ANY WARRANTY; without even the implied warranty of
16# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17# GNU General Public License for more details.
19# You should have received a copy of the GNU General Public License
20# along with this program; if not, write to the Free Software
21# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
23################################################################################
25import psycopg2
27from daklib.dak_exceptions import DBUpdateError
29statements = [
30 """
31CREATE TABLE suite_permission (
32 suite_id INT NOT NULL REFERENCES suite(id) ON DELETE CASCADE,
33 role TEXT NOT NULL,
34 PRIMARY KEY (suite_id, role)
35)
36""",
37 """
38CREATE OR REPLACE FUNCTION has_suite_permission(action TEXT, suite_id INT)
39 RETURNS BOOLEAN
40 STABLE
41 STRICT
42 SET search_path = public, pg_temp
43 LANGUAGE plpgsql
44AS $$
45DECLARE
46 v_result BOOLEAN;
47BEGIN
49 IF pg_has_role('ftpteam', 'USAGE') THEN
50 RETURN 't';
51 END IF;
53 SELECT BOOL_OR(pg_has_role(sp.role, 'USAGE')) INTO v_result
54 FROM suite_permission sp
55 WHERE sp.suite_id = has_suite_permission.suite_id
56 GROUP BY sp.suite_id;
58 IF v_result IS NULL THEN
59 v_result := 'f';
60 END IF;
62 RETURN v_result;
64END;
65$$
66""",
67 """
68CREATE OR REPLACE FUNCTION trigger_check_suite_permission() RETURNS TRIGGER
69SET search_path = public, pg_temp
70LANGUAGE plpgsql
71AS $$
72DECLARE
73 v_row RECORD;
74 v_suite_name suite.suite_name%TYPE;
75BEGIN
77 CASE TG_OP
78 WHEN 'INSERT', 'UPDATE' THEN
79 v_row := NEW;
80 WHEN 'DELETE' THEN
81 v_row := OLD;
82 ELSE
83 RAISE EXCEPTION 'Unexpected TG_OP (%)', TG_OP;
84 END CASE;
86 IF TG_OP = 'UPDATE' AND OLD.suite != NEW.suite THEN
87 RAISE EXCEPTION 'Cannot change suite';
88 END IF;
90 IF NOT has_suite_permission(TG_OP, v_row.suite) THEN
91 SELECT suite_name INTO STRICT v_suite_name FROM suite WHERE id = v_row.suite;
92 RAISE EXCEPTION 'Not allowed to % in %', TG_OP, v_suite_name;
93 END IF;
95 RETURN v_row;
97END;
98$$
99""",
100 """
101CREATE CONSTRAINT TRIGGER trigger_override_permission
102 AFTER INSERT OR UPDATE OR DELETE
103 ON override
104 FOR EACH ROW
105 EXECUTE PROCEDURE trigger_check_suite_permission()
106""",
107 """
108CREATE CONSTRAINT TRIGGER trigger_src_associations_permission
109 AFTER INSERT OR UPDATE OR DELETE
110 ON src_associations
111 FOR EACH ROW
112 EXECUTE PROCEDURE trigger_check_suite_permission()
113""",
114 """
115CREATE CONSTRAINT TRIGGER trigger_bin_associations_permission
116 AFTER INSERT OR UPDATE OR DELETE
117 ON bin_associations
118 FOR EACH ROW
119 EXECUTE PROCEDURE trigger_check_suite_permission()
120""",
121]
123################################################################################
126def do_update(self):
127 print(__doc__)
128 try:
129 c = self.db.cursor()
131 for stmt in statements:
132 c.execute(stmt)
134 c.execute("UPDATE config SET value = '84' WHERE name = 'db_revision'")
135 self.db.commit()
137 except psycopg2.ProgrammingError as msg:
138 self.db.rollback()
139 raise DBUpdateError(
140 "Unable to apply sick update 84, rollback issued. Error message: {0}".format(
141 msg
142 )
143 )