Mercurial > gemma

changeset 2915:69292eb68984

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Simplify function and move to appropriate schema Since utm_covers() uses a function from schema users, it can be used only if appropriate access to the schema has been granted anyhow. Using current_user_area_utm() here to have a level of abstraction between utm_covers() and the modelling of the actual responsibility area.
author Tom Gottfried <tom@intevation.de>
date Wed, 03 Apr 2019 15:26:39 +0200
parents 84a40983bb9f
children ea54e63deb40
files schema/auth.sql schema/geo_functions.sql schema/manage_users.sql
diffstat 3 files changed, 13 insertions(+), 21 deletions(-) [+]
line wrap: on
line diff
--- a/schema/auth.sql	Wed Apr 03 12:55:32 2019 +0200
+++ b/schema/auth.sql	Wed Apr 03 15:26:39 2019 +0200
@@ -4,7 +4,7 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018 by via donau
+-- Copyright (C) 2018, 2019 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
@@ -139,15 +139,15 @@
 
 CREATE POLICY responsibility_area ON waterway.bottlenecks
     FOR ALL TO waterway_admin
-    USING (utm_covers(area));
+    USING (users.utm_covers(area));
 
 CREATE POLICY responsibility_area ON waterway.sounding_results
     FOR ALL TO waterway_admin
-    USING (utm_covers(area));
+    USING (users.utm_covers(area));
 
 CREATE POLICY responsibility_area ON waterway.fairway_dimensions
     FOR ALL TO waterway_admin
-    USING (utm_covers(area));
+    USING (users.utm_covers(area));
 
 CREATE POLICY sys_admin ON waterway.stretches
     FOR ALL TO sys_admin
--- a/schema/geo_functions.sql	Wed Apr 03 12:55:32 2019 +0200
+++ b/schema/geo_functions.sql	Wed Apr 03 15:26:39 2019 +0200
@@ -33,20 +33,3 @@
 $$
 LANGUAGE plpgsql
 IMMUTABLE;
-
-CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS
-$$
-DECLARE
-  user_area geometry;
-  utm integer;
-BEGIN
-    SELECT area::geometry FROM users.responsibility_areas INTO user_area
-    WHERE country = users.current_user_country();
-    SELECT best_utm(user_area) INTO utm;
-    RETURN ST_Covers(
-      ST_Transform(user_area, utm),
-      ST_Transform(g::geometry, utm));
-END;
-$$
-LANGUAGE plpgsql
-STABLE;
--- a/schema/manage_users.sql	Wed Apr 03 12:55:32 2019 +0200
+++ b/schema/manage_users.sql	Wed Apr 03 15:26:39 2019 +0200
@@ -84,6 +84,15 @@
     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