# HG changeset patch
# User Sascha Wilde <wilde@intevation.de>
# Date 1571837032 -7200
# Node ID 1fef9d8e7eb20f9e469eff668382ac676d8453b4
# Parent  ecbb12a6e8eebf24447e3a47055f180eb0ef3745
Improved performance for RLS on read access.

Only do expensive regional test if data is in staging area (and on
write operations).

diff -r ecbb12a6e8ee -r 1fef9d8e7eb2 schema/auth.sql
--- 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
diff -r ecbb12a6e8ee -r 1fef9d8e7eb2 schema/updates/1313/01.optimize_area_policies.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1313/01.optimize_area_policies.sql	Wed Oct 23 15:23:52 2019 +0200
@@ -0,0 +1,49 @@
+-- This is Free Software under GNU Affero General Public License v >= 3.0
+-- without warranty, see README.md and license for details.
+
+-- SPDX-License-Identifier: AGPL-3.0-or-later
+-- License-Filename: LICENSES/AGPL-3.0.txt
+
+-- Copyright (C) 2019 by via donau
+--   – Österreichische Wasserstraßen-Gesellschaft mbH
+-- Software engineering by Intevation GmbH
+
+-- Author(s):
+--  * Sascha Wilde <sascha.wilde@intevation.de>
+--  * Tom Gottried <tom@intevation.de>
+
+
+ALTER POLICY same_country ON waterway.gauge_measurements
+    TO waterway_admin
+    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));
+
+ALTER POLICY same_country ON waterway.waterway_profiles
+    TO waterway_admin
+    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));
+
+ALTER POLICY responsibility_area ON waterway.bottlenecks
+    TO waterway_admin
+    USING (staging_done OR users.utm_covers(area))
+    WITH CHECK (users.utm_covers(area));
+
+ALTER POLICY responsibility_area ON waterway.sounding_results
+    TO waterway_admin
+    USING (staging_done OR users.utm_covers(area))
+    WITH CHECK (users.utm_covers(area));
+
+ALTER POLICY responsibility_area ON waterway.fairway_dimensions
+    TO waterway_admin
+    USING (staging_done OR users.utm_covers(area))
+    WITH CHECK (users.utm_covers(area));
diff -r ecbb12a6e8ee -r 1fef9d8e7eb2 schema/version.sql
--- a/schema/version.sql	Tue Oct 22 17:56:53 2019 +0200
+++ b/schema/version.sql	Wed Oct 23 15:23:52 2019 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1311);
+INSERT INTO gemma_schema_version(version) VALUES (1313);