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   
 27  from daklib.dak_exceptions import DBUpdateError 
 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  CREATE 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 
 44  AS $$ 
 45  DECLARE 
 46    v_result BOOLEAN; 
 47  BEGIN 
 48   
 49    IF pg_has_role('ftpteam', 'USAGE') THEN 
 50      RETURN 't'; 
 51    END IF; 
 52   
 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; 
 57   
 58    IF v_result IS NULL THEN 
 59      v_result := 'f'; 
 60    END IF; 
 61   
 62    RETURN v_result; 
 63   
 64  END; 
 65  $$ 
 66  """, 
 67      """ 
 68  CREATE OR REPLACE FUNCTION trigger_check_suite_permission() RETURNS TRIGGER 
 69  SET search_path = public, pg_temp 
 70  LANGUAGE plpgsql 
 71  AS $$ 
 72  DECLARE 
 73    v_row RECORD; 
 74    v_suite_name suite.suite_name%TYPE; 
 75  BEGIN 
 76   
 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; 
 85   
 86    IF TG_OP = 'UPDATE' AND OLD.suite != NEW.suite THEN 
 87      RAISE EXCEPTION 'Cannot change suite'; 
 88    END IF; 
 89   
 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; 
 94   
 95    RETURN v_row; 
 96   
 97  END; 
 98  $$ 
 99  """, 
100      """ 
101  CREATE 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      """ 
108  CREATE 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      """ 
115  CREATE 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  ] 
122   
123  ################################################################################ 
124   
125   
126 -def do_update(self): 
127      print(__doc__) 
128      try: 
129          c = self.db.cursor() 
130   
131          for stmt in statements: 
132              c.execute(stmt) 
133   
134          c.execute("UPDATE config SET value = '84' WHERE name = 'db_revision'") 
135          self.db.commit() 
136   
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          ) 
144