Mercurial > gemma

changeset 5014:388947a3050d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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.
author Tom Gottfried <tom@intevation.de>
date Fri, 13 Mar 2020 14:09:32 +0100
parents 7dff1015283d
children df3cab060ed9
files schema/auth.sql schema/manage_users.sql schema/updates/1425/01.inline_utm_covers.sql
diffstat 3 files changed, 73 insertions(+), 18 deletions(-) [+]
line wrap: on
line diff
--- 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
--- 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
--- /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;