Mercurial > gemma

diff schema/auth.sql @ 4785:1fef9d8e7eb2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved performance for RLS on read access. Only do expensive regional test if data is in staging area (and on write operations).
author Sascha Wilde <wilde@intevation.de>
date Wed, 23 Oct 2019 15:23:52 +0200
parents 2440d2f86f4e
children 9e077ca97505
line wrap: on
line diff
--- a/schema/auth.sql	Tue Oct 22 17:56:53 2019 +0200
+++ b/schema/auth.sql	Wed Oct 23 15:23:52 2019 +0200
@@ -129,25 +129,38 @@
 
 CREATE POLICY same_country ON waterway.gauge_measurements
     FOR ALL TO waterway_admin
-    USING ((location).country_code
-        = (SELECT country FROM users.list_users WHERE username = current_user)
-    );
+    USING (staging_done
+           OR (location).country_code =
+               (SELECT country FROM users.list_users
+                WHERE username = current_user))
+    WITH CHECK ((location).country_code =
+                 (SELECT country FROM users.list_users
+                  WHERE username = current_user));
 
 CREATE POLICY same_country ON waterway.waterway_profiles
     FOR ALL TO waterway_admin
-    USING ((location).country_code = (SELECT country FROM users.list_users WHERE username = current_user));
+    USING (staging_done
+           OR (location).country_code =
+               (SELECT country FROM users.list_users
+                WHERE username = current_user))
+    WITH CHECK ((location).country_code =
+                (SELECT country FROM users.list_users
+                 WHERE username = current_user));
 
 CREATE POLICY responsibility_area ON waterway.bottlenecks
     FOR ALL TO waterway_admin
-    USING (users.utm_covers(area));
+    USING (staging_done OR users.utm_covers(area))
+    WITH CHECK (users.utm_covers(area));
 
 CREATE POLICY responsibility_area ON waterway.sounding_results
     FOR ALL TO waterway_admin
-    USING (users.utm_covers(area));
+    USING (staging_done OR users.utm_covers(area))
+    WITH CHECK (users.utm_covers(area));
 
 CREATE POLICY responsibility_area ON waterway.fairway_dimensions
     FOR ALL TO waterway_admin
-    USING (users.utm_covers(area));
+    USING (staging_done OR users.utm_covers(area))
+    WITH CHECK (users.utm_covers(area));
 
 CREATE POLICY same_country ON waterway.sections
     FOR ALL TO waterway_admin