Source Code for Module dak.dakdb.update84

  1  """ 
  2  add 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   
 25  import psycopg2 
 26  from daklib.dak_exceptions import DBUpdateError 
 27  from daklib.config import Config 
 28   
 29  statements = [ 
 30  """ 
 31  CREATE 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   
 38  """ 
 39  CREATE 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 
 45  AS $$ 
 46  DECLARE 
 47    v_result BOOLEAN; 
 48  BEGIN 
 49   
 50    IF pg_has_role('ftpteam', 'USAGE') THEN 
 51      RETURN 't'; 
 52    END IF; 
 53   
 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; 
 58   
 59    IF v_result IS NULL THEN 
 60      v_result := 'f'; 
 61    END IF; 
 62   
 63    RETURN v_result; 
 64   
 65  END; 
 66  $$ 
 67  """, 
 68   
 69  """ 
 70  CREATE OR REPLACE FUNCTION trigger_check_suite_permission() RETURNS TRIGGER 
 71  SET search_path = public, pg_temp 
 72  LANGUAGE plpgsql 
 73  AS $$ 
 74  DECLARE 
 75    v_row RECORD; 
 76    v_suite_name suite.suite_name%TYPE; 
 77  BEGIN 
 78   
 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; 
 87   
 88    IF TG_OP = 'UPDATE' AND OLD.suite != NEW.suite THEN 
 89      RAISE EXCEPTION 'Cannot change suite'; 
 90    END IF; 
 91   
 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; 
 96   
 97    RETURN v_row; 
 98   
 99  END; 
100  $$ 
101  """, 
102   
103  """ 
104  CREATE 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  """, 
110   
111  """ 
112  CREATE 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  """, 
118   
119  """ 
120  CREATE 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  ] 
127   
128  ################################################################################ 
129   
130   
131 -def do_update(self): 
132      print(__doc__) 
133      try: 
134          cnf = Config() 
135   
136          c = self.db.cursor() 
137   
138          for stmt in statements: 
139              c.execute(stmt) 
140   
141          c.execute("UPDATE config SET value = '84' WHERE name = 'db_revision'") 
142          self.db.commit() 
143   
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)) 
147