Mercurial > gemma

changeset 1780:48791416bea5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
(Approved) gauge measurement import: Fixed row level security.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 11 Jan 2019 16:48:14 +0100
parents ad1c12e999df
children 0a53c33bc7b2
files pkg/imports/agm.go pkg/imports/gm.go schema/auth.sql schema/gemma.sql
diffstat 4 files changed, 41 insertions(+), 16 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/agm.go	Fri Jan 11 15:40:50 2019 +0100
+++ b/pkg/imports/agm.go	Fri Jan 11 16:48:14 2019 +0100
@@ -65,14 +65,29 @@
 }
 
 const (
-	// TODO: re-add staging_done field in table and fix RLS policy
-	// issue for raw import.
+	// delete the old  and keep the new measures.
+	agmStageDoneDeleteSQL = `
+WITH staged AS (
+  SELECT key
+  FROM waterway.track_imports
+  WHERE import_id = $1 AND
+        relation = 'waterway.gauge_measurements'::regclass
+),
+to_delete AS (
+  SELECT o.id AS id
+  FROM waterway.gauge_measurements oJOIN waterway.gauge_measurements n
+    ON n.fk_gauge_id = o.fk_gauge_id AND n.measure_date = o.measure_date
+    WHERE n.id     IN (SELECT key FROM staged)
+	  AND o.id NOT IN (SELECT key FROM staged)
+)
+DELETE FROM waterway.gauge_measurements WHERE id IN (SELECT id from to_delete)`
+
 	agmStageDoneSQL = `
 UPDATE waterway.gauge_measurements SET staging_done = true
-WHERE id = (
-  SELECT key from waterway.track_imports
+WHERE id IN (
+  SELECT key FROM waterway.track_imports
   WHERE import_id = $1 AND
-        relation = 'waterway.gauge_measurements'::regclass)`
+    relation = 'waterway.gauge_measurements'::regclass)`
 )
 
 func (agmJobCreator) StageDone(
@@ -80,7 +95,10 @@
 	tx *sql.Tx,
 	id int64,
 ) error {
-	_, err := tx.ExecContext(ctx, agmStageDoneSQL, id)
+	_, err := tx.ExecContext(ctx, agmStageDoneDeleteSQL, id)
+	if err == nil {
+		_, err = tx.ExecContext(ctx, agmStageDoneSQL, id)
+	}
 	return err
 }
 
@@ -197,7 +215,9 @@
 
 	ids := []int64{}
 
-	args := make([]interface{}, 18)
+	args := make([]interface{}, 19)
+
+	args[18] = false // staging_done
 
 lines:
 	for line := 1; ; line++ {
@@ -275,6 +295,8 @@
 
 		args[17] = row[headerIndices["originator"]]
 
+		// args[18] (staging_done) is set to true outside the loop.
+
 		var id int64
 		if err := insertStmt.QueryRowContext(ctx, args...).Scan(&id); err != nil {
 			return nil, fmt.Errorf("Failed to insert line %d: %v", line, err)
--- a/pkg/imports/gm.go	Fri Jan 11 15:40:50 2019 +0100
+++ b/pkg/imports/gm.go	Fri Jan 11 16:48:14 2019 +0100
@@ -65,7 +65,8 @@
   value_min,
   value_max,
   date_info,
-  source_organization
+  source_organization,
+  staging_done
 ) VALUES(
   ($1, $2, $3, $4, $5),
   $6,
@@ -80,7 +81,8 @@
   $15,
   $16,
   $17,
-  $18
+  $18,
+  $19
 )
 RETURNING id`
 )
@@ -304,6 +306,7 @@
 					convert(measure.Value_max),
 					msg.Identification.Date_issue,
 					msg.Identification.Originator,
+					true, // staging_done
 				).Scan(&gid)
 				if err != nil {
 					return nil, err
--- a/schema/auth.sql	Fri Jan 11 15:40:50 2019 +0100
+++ b/schema/auth.sql	Fri Jan 11 16:48:14 2019 +0100
@@ -77,8 +77,7 @@
 DECLARE the_table varchar;
 BEGIN
     FOREACH the_table IN ARRAY ARRAY[
-       -- 'gauge_measurements', XXX Removed since this table has currently no
-    -- staging
+        'gauge_measurements',
         'sections_stretches',
         'waterway_profiles',
         'fairway_dimensions',
@@ -143,6 +142,10 @@
 -- Staging area
 -- TODO: add all relevant tables here
 
+CREATE POLICY same_country ON waterway.gauge_measurements
+    FOR ALL TO waterway_admin
+    USING (country_code = users.current_user_country());
+
 CREATE POLICY responsibility_area ON waterway.bottlenecks
     FOR ALL TO waterway_admin
     USING (utm_covers(area));
--- a/schema/gemma.sql	Fri Jan 11 15:40:50 2019 +0100
+++ b/schema/gemma.sql	Fri Jan 11 16:48:14 2019 +0100
@@ -284,11 +284,8 @@
         value_max double precision, -- XXX: NOT NULL if predicted?
         --- TODO: Add a double range type for checking?
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar NOT NULL -- "originator"
-        -- XXX removed staging done temporarily. Currently imported raw data is
-        -- not staged. When importing approved gauge measurements uncomment this
-        -- and add policy to allow select on this table for waterway_admin
-        -- staging_done boolean NOT NULL DEFAULT false
+        source_organization varchar NOT NULL, -- "originator"
+        staging_done boolean NOT NULL DEFAULT false
     )
     CREATE TRIGGER gauge_measurements_date_info
         BEFORE UPDATE ON gauge_measurements