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
26from daklib.dak_exceptions import DBUpdateError
27from daklib.config import Config
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""",
38"""
39CREATE OR REPLACE FUNCTION has_suite_permission(action TEXT, suite_id INT)
40 RETURNS BOOLEAN
41 STABLE
42 STRICT
43 SET search_path = public, pg_temp
44 LANGUAGE plpgsql
45AS $$
46DECLARE
47 v_result BOOLEAN;
48BEGIN
50 IF pg_has_role('ftpteam', 'USAGE') THEN
51 RETURN 't';
52 END IF;
54 SELECT BOOL_OR(pg_has_role(sp.role, 'USAGE')) INTO v_result
55 FROM suite_permission sp
56 WHERE sp.suite_id = has_suite_permission.suite_id
57 GROUP BY sp.suite_id;
59 IF v_result IS NULL THEN
60 v_result := 'f';
61 END IF;
63 RETURN v_result;
65END;
66$$
67""",
69"""
70CREATE OR REPLACE FUNCTION trigger_check_suite_permission() RETURNS TRIGGER
71SET search_path = public, pg_temp
72LANGUAGE plpgsql
73AS $$
74DECLARE
75 v_row RECORD;
76 v_suite_name suite.suite_name%TYPE;
77BEGIN
79 CASE TG_OP
80 WHEN 'INSERT', 'UPDATE' THEN
81 v_row := NEW;
82 WHEN 'DELETE' THEN
83 v_row := OLD;
84 ELSE
85 RAISE EXCEPTION 'Unexpected TG_OP (%)', TG_OP;
86 END CASE;
88 IF TG_OP = 'UPDATE' AND OLD.suite != NEW.suite THEN
89 RAISE EXCEPTION 'Cannot change suite';
90 END IF;
92 IF NOT has_suite_permission(TG_OP, v_row.suite) THEN
93 SELECT suite_name INTO STRICT v_suite_name FROM suite WHERE id = v_row.suite;
94 RAISE EXCEPTION 'Not allowed to % in %', TG_OP, v_suite_name;
95 END IF;
97 RETURN v_row;
99END;
100$$
101""",
103"""
104CREATE CONSTRAINT TRIGGER trigger_override_permission
105 AFTER INSERT OR UPDATE OR DELETE
106 ON override
107 FOR EACH ROW
108 EXECUTE PROCEDURE trigger_check_suite_permission()
109""",
111"""
112CREATE CONSTRAINT TRIGGER trigger_src_associations_permission
113 AFTER INSERT OR UPDATE OR DELETE
114 ON src_associations
115 FOR EACH ROW
116 EXECUTE PROCEDURE trigger_check_suite_permission()
117""",
119"""
120CREATE CONSTRAINT TRIGGER trigger_bin_associations_permission
121 AFTER INSERT OR UPDATE OR DELETE
122 ON bin_associations
123 FOR EACH ROW
124 EXECUTE PROCEDURE trigger_check_suite_permission()
125""",
126]
128################################################################################
131def do_update(self):
132 print(__doc__)
133 try:
134 cnf = Config()
136 c = self.db.cursor()
138 for stmt in statements:
139 c.execute(stmt)
141 c.execute("UPDATE config SET value = '84' WHERE name = 'db_revision'")
142 self.db.commit()
144 except psycopg2.ProgrammingError as msg:
145 self.db.rollback()
146 raise DBUpdateError('Unable to apply sick update 84, rollback issued. Error message: {0}'.format(msg))