Coverage for dak/dakdb/update84.py: 100%

1""" 

2add per-suite database permissions 

3 

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""" 

8 

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. 

13 

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. 

18 

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 

22 

23################################################################################ 

24 

25statements = [ 

26 """ 

27CREATE TABLE suite_permission ( 

28 suite_id INT NOT NULL REFERENCES suite(id) ON DELETE CASCADE, 

29 role TEXT NOT NULL, 

30 PRIMARY KEY (suite_id, role) 

31) 

32""", 

33 """ 

34CREATE OR REPLACE FUNCTION has_suite_permission(action TEXT, suite_id INT) 

35 RETURNS BOOLEAN 

36 STABLE 

37 STRICT 

38 SET search_path = public, pg_temp 

39 LANGUAGE plpgsql 

40AS $$ 

41DECLARE 

42 v_result BOOLEAN; 

43BEGIN 

44 

45 IF pg_has_role('ftpteam', 'USAGE') THEN 

46 RETURN 't'; 

47 END IF; 

48 

49 SELECT BOOL_OR(pg_has_role(sp.role, 'USAGE')) INTO v_result 

50 FROM suite_permission sp 

51 WHERE sp.suite_id = has_suite_permission.suite_id 

52 GROUP BY sp.suite_id; 

53 

54 IF v_result IS NULL THEN 

55 v_result := 'f'; 

56 END IF; 

57 

58 RETURN v_result; 

59 

60END; 

61$$ 

62""", 

63 """ 

64CREATE OR REPLACE FUNCTION trigger_check_suite_permission() RETURNS TRIGGER 

65SET search_path = public, pg_temp 

66LANGUAGE plpgsql 

67AS $$ 

68DECLARE 

69 v_row RECORD; 

70 v_suite_name suite.suite_name%TYPE; 

71BEGIN 

72 

73 CASE TG_OP 

74 WHEN 'INSERT', 'UPDATE' THEN 

75 v_row := NEW; 

76 WHEN 'DELETE' THEN 

77 v_row := OLD; 

78 ELSE 

79 RAISE EXCEPTION 'Unexpected TG_OP (%)', TG_OP; 

80 END CASE; 

81 

82 IF TG_OP = 'UPDATE' AND OLD.suite != NEW.suite THEN 

83 RAISE EXCEPTION 'Cannot change suite'; 

84 END IF; 

85 

86 IF NOT has_suite_permission(TG_OP, v_row.suite) THEN 

87 SELECT suite_name INTO STRICT v_suite_name FROM suite WHERE id = v_row.suite; 

88 RAISE EXCEPTION 'Not allowed to % in %', TG_OP, v_suite_name; 

89 END IF; 

90 

91 RETURN v_row; 

92 

93END; 

94$$ 

95""", 

96 """ 

97CREATE CONSTRAINT TRIGGER trigger_override_permission 

98 AFTER INSERT OR UPDATE OR DELETE 

99 ON override 

100 FOR EACH ROW 

101 EXECUTE PROCEDURE trigger_check_suite_permission() 

102""", 

103 """ 

104CREATE CONSTRAINT TRIGGER trigger_src_associations_permission 

105 AFTER INSERT OR UPDATE OR DELETE 

106 ON src_associations 

107 FOR EACH ROW 

108 EXECUTE PROCEDURE trigger_check_suite_permission() 

109""", 

110 """ 

111CREATE CONSTRAINT TRIGGER trigger_bin_associations_permission 

112 AFTER INSERT OR UPDATE OR DELETE 

113 ON bin_associations 

114 FOR EACH ROW 

115 EXECUTE PROCEDURE trigger_check_suite_permission() 

116""", 

117]