Mercurial > gemma
changeset 5014:388947a3050d
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;