# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1584104972 -3600
# Node ID 388947a3050d6626c0743628771d56495b22ab5c
# Parent  7dff1015283d37d861e71b266e9ccba14adc0f17
Inline SQL function in row level security policies

This prevents the inner function current_user_area_utm() from being
called once per entry. It's now just called once per statement in
a SubPlan node. Performance impact is significant for statements affecting
many entries.

diff -r 7dff1015283d -r 388947a3050d schema/auth.sql
--- a/schema/auth.sql	Thu Mar 12 14:49:19 2020 +0100
+++ b/schema/auth.sql	Fri Mar 13 14:09:32 2020 +0100
@@ -159,18 +159,33 @@
 
 CREATE POLICY responsibility_area ON waterway.bottlenecks
     FOR ALL TO waterway_admin
-    USING (staging_done OR users.utm_covers(area))
-    WITH CHECK (users.utm_covers(area));
+    USING (staging_done
+        OR (SELECT ST_Covers(a,
+                ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+            FROM users.current_user_area_utm() AS a (a)))
+    WITH CHECK ((SELECT ST_Covers(a,
+            ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
 
 CREATE POLICY responsibility_area ON waterway.sounding_results
     FOR ALL TO waterway_admin
-    USING (staging_done OR users.utm_covers(area))
-    WITH CHECK (users.utm_covers(area));
+    USING (staging_done
+        OR (SELECT ST_Covers(a,
+                ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+            FROM users.current_user_area_utm() AS a (a)))
+    WITH CHECK ((SELECT ST_Covers(a,
+            ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
 
 CREATE POLICY responsibility_area ON waterway.fairway_dimensions
     FOR ALL TO waterway_admin
-    USING (staging_done OR users.utm_covers(area))
-    WITH CHECK (users.utm_covers(area));
+    USING (staging_done
+        OR (SELECT ST_Covers(a,
+                ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+            FROM users.current_user_area_utm() AS a (a)))
+    WITH CHECK ((SELECT ST_Covers(a,
+            ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
 
 -- In the case of sections differentiating between read and write
 -- access is not neccessary: the country code based access check is
@@ -192,13 +207,19 @@
 -- being added in SELECT queries.
 CREATE POLICY responsibility_area_insert ON waterway.waterway_axis
     FOR INSERT TO waterway_admin
-    WITH CHECK (users.utm_covers(wtwaxs));
+    WITH CHECK ((SELECT ST_Covers(a,
+            ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
 CREATE POLICY responsibility_area_update ON waterway.waterway_axis
     FOR UPDATE TO waterway_admin
-    USING (users.utm_covers(wtwaxs));
+    USING ((SELECT ST_Covers(a,
+            ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
 CREATE POLICY responsibility_area_delete ON waterway.waterway_axis
     FOR DELETE TO waterway_admin
-    USING (users.utm_covers(wtwaxs));
+    USING ((SELECT ST_Covers(a,
+            ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
 
 --
 -- RLS policies for imports and import config
diff -r 7dff1015283d -r 388947a3050d schema/manage_users.sql
--- a/schema/manage_users.sql	Thu Mar 12 14:49:19 2020 +0100
+++ b/schema/manage_users.sql	Fri Mar 13 14:09:32 2020 +0100
@@ -60,15 +60,6 @@
     STABLE PARALLEL SAFE;
 
 
-CREATE OR REPLACE FUNCTION users.utm_covers(g geography) RETURNS boolean AS
-    $$
-        SELECT ST_Covers(a, ST_Transform(g::geometry, ST_SRID(a)))
-            FROM users.current_user_area_utm() AS a (a)
-    $$
-    LANGUAGE SQL
-    STABLE PARALLEL SAFE;
-
-
 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
 AS $$
 BEGIN
diff -r 7dff1015283d -r 388947a3050d schema/updates/1425/01.inline_utm_covers.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1425/01.inline_utm_covers.sql	Fri Mar 13 14:09:32 2020 +0100
@@ -0,0 +1,43 @@
+ALTER POLICY responsibility_area ON waterway.bottlenecks
+    USING (staging_done
+        OR (SELECT ST_Covers(a,
+                ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+            FROM users.current_user_area_utm() AS a (a)))
+    WITH CHECK ((SELECT ST_Covers(a,
+            ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
+
+ALTER POLICY responsibility_area ON waterway.sounding_results
+    USING (staging_done
+        OR (SELECT ST_Covers(a,
+                ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+            FROM users.current_user_area_utm() AS a (a)))
+    WITH CHECK ((SELECT ST_Covers(a,
+            ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
+
+ALTER POLICY responsibility_area ON waterway.fairway_dimensions
+    USING (staging_done
+        OR (SELECT ST_Covers(a,
+                ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+            FROM users.current_user_area_utm() AS a (a)))
+    WITH CHECK ((SELECT ST_Covers(a,
+            ST_Transform(CAST(area AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
+
+ALTER POLICY responsibility_area_insert ON waterway.waterway_axis
+    WITH CHECK ((SELECT ST_Covers(a,
+            ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
+
+ALTER POLICY responsibility_area_update ON waterway.waterway_axis
+    USING ((SELECT ST_Covers(a,
+            ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
+
+ALTER POLICY responsibility_area_delete ON waterway.waterway_axis
+    USING ((SELECT ST_Covers(a,
+            ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
+        FROM users.current_user_area_utm() AS a (a)));
+
+DROP FUNCTION users.utm_covers;