Mercurial > gemma
changeset 5013:7dff1015283d
Add row level security policies for waterway axis
Enforcing the area of responsibility this way instead of leaving it
up to the importer implementation will also reduce complexity of
statements needed to implement keeping of historic axis data.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 12 Mar 2020 14:49:19 +0100 |
| parents | ae3a1392f9d0 |
| children | 388947a3050d |
| files | pkg/imports/wx.go schema/auth.sql schema/updates/1424/01.add_axis_rls.sql schema/version.sql |
| diffstat | 4 files changed, 41 insertions(+), 8 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/imports/wx.go Wed Mar 11 23:40:44 2020 +0100 +++ b/pkg/imports/wx.go Thu Mar 12 14:49:19 2020 +0100 @@ -86,13 +86,7 @@ const ( deleteWaterwayAxisSQL = ` -WITH resp AS ( - SELECT users.current_user_area_utm() AS a -) DELETE FROM waterway.waterway_axis -WHERE pg_has_role('sys_admin', 'MEMBER') - OR ST_Covers((SELECT a FROM resp), - ST_Transform(wtwaxs::geometry, (SELECT ST_SRID(a) FROM resp))) ` insertWaterwayAxisSQL = ` @@ -107,7 +101,7 @@ LATERAL (SELECT CASE WHEN pg_has_role('sys_admin', 'MEMBER') THEN new_line - ELSE ST_Intersection((SELECT a FROM resp), + ELSE ST_Intersection((SELECT ST_Buffer(a, -0.0001) FROM resp), ST_Node(ST_Transform(new_line, (SELECT ST_SRID(a) FROM resp)))) END) AS new_ax (new_ax) -- Do nothing if intersection is empty:
--- a/schema/auth.sql Wed Mar 11 23:40:44 2020 +0100 +++ b/schema/auth.sql Thu Mar 12 14:49:19 2020 +0100 @@ -106,6 +106,14 @@ END; $$; +-- Tables without staging area +CREATE POLICY hide_nothing ON waterway.waterway_axis + FOR SELECT TO waterway_user USING (true); +CREATE POLICY sys_admin ON waterway.waterway_axis + FOR ALL TO sys_admin USING (true); +ALTER TABLE waterway.waterway_axis ENABLE ROW LEVEL SECURITY; + + -- -- RLS policies for templates -- @@ -178,6 +186,21 @@ USING (true); -- +-- Tables without staging area +-- +-- Use three policies instead of one FOR ALL to avoid costly expressions +-- 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)); +CREATE POLICY responsibility_area_update ON waterway.waterway_axis + FOR UPDATE TO waterway_admin + USING (users.utm_covers(wtwaxs)); +CREATE POLICY responsibility_area_delete ON waterway.waterway_axis + FOR DELETE TO waterway_admin + USING (users.utm_covers(wtwaxs)); + +-- -- RLS policies for imports and import config --
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1424/01.add_axis_rls.sql Thu Mar 12 14:49:19 2020 +0100 @@ -0,0 +1,16 @@ +CREATE POLICY hide_nothing ON waterway.waterway_axis + FOR SELECT TO waterway_user USING (true); +CREATE POLICY sys_admin ON waterway.waterway_axis + FOR ALL TO sys_admin USING (true); + +CREATE POLICY responsibility_area_insert ON waterway.waterway_axis + FOR INSERT TO waterway_admin + WITH CHECK (users.utm_covers(wtwaxs)); +CREATE POLICY responsibility_area_update ON waterway.waterway_axis + FOR UPDATE TO waterway_admin + USING (users.utm_covers(wtwaxs)); +CREATE POLICY responsibility_area_delete ON waterway.waterway_axis + FOR DELETE TO waterway_admin + USING (users.utm_covers(wtwaxs)); + +ALTER TABLE waterway.waterway_axis ENABLE ROW LEVEL SECURITY;