Mercurial > gemma
changeset 2915:69292eb68984
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