Mercurial > gemma
diff schema/auth.sql @ 1229:d395b2940a82
Improved performance of row level security check on users responsibility areas
by projecting the geographies into a good fitting UTM zone first
before doing the coverage test.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 20 Nov 2018 00:51:33 +0100 |
parents | 930fdd8b474f |
children | 6590208e3ee1 |
line wrap: on
line diff
--- a/schema/auth.sql Mon Nov 19 18:32:06 2018 +0100 +++ b/schema/auth.sql Tue Nov 20 00:51:33 2018 +0100 @@ -89,15 +89,49 @@ -- RLS policies for waterway_admin -- +CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS +$$ +DECLARE + center geometry; +BEGIN + SELECT ST_Centroid(g) INTO center; + RETURN + CASE WHEN ST_Y(center) > 0 THEN + 32600 + ELSE + 32700 + END + floor((ST_X(center)+180)/6)::int + 1; +END; +$$ +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; + -- Staging area -- TODO: add all relevant tables here + CREATE POLICY responsibility_area ON waterway.bottlenecks FOR ALL TO waterway_admin - USING (ST_CoveredBy(area, (SELECT area FROM users.responsibility_areas - WHERE country = users.current_user_country()))); + USING (utm_covers(area)); + CREATE POLICY responsibility_area ON waterway.sounding_results FOR ALL TO waterway_admin - USING (ST_CoveredBy(area, (SELECT area FROM users.responsibility_areas - WHERE country = users.current_user_country()))); + USING (utm_covers(area)); COMMIT;