Mercurial > gemma

comparison schema/auth_tests.sql @ 5025:4c658a8f34da

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix row level security policies for waterway admin Since 'staging_done OR' was added to the conditions to improve performance for read access, it was also allowed to delete and partly update entries with staging_done set to true but otherwise being outside the country of the respective waterway admin. Using an extra policy for each command and using the 'staging_done OR' tweak only FOR SELECT should fix authorization while keeping performance.
author Tom Gottfried <tom@intevation.de>
date Wed, 18 Mar 2020 12:16:42 +0100
parents dfd990a4ac64
children
comparison
equal deleted inserted replaced
5024:36a3dce20232 5025:4c658a8f34da
100 ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326)) 100 ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326))
101 $$, 101 $$,
102 42501, NULL, 102 42501, NULL,
103 'Waterway admin cannot insert data outside his region'); 103 'Waterway admin cannot insert data outside his region');
104 104
105 -- Ensure a USING clause prevents access in an UPDATE
106 SELECT is_empty($$
107 WITH a AS (SELECT users.current_user_area_utm() AS a)
108 UPDATE waterway.bottlenecks
109 SET objnam = 'Now it''s mine',
110 area = ST_geomfromtext(
111 'MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)
112 WHERE bottleneck_id = 'testbottleneck3'
113 RETURNING *
114 $$,
115 'Waterway admin cannot move data from outside his region inside');
116
117 -- Ensure a WITH CHECK or USING clause prevents writing such rows
118 SELECT throws_ok($$
119 WITH a AS (SELECT users.current_user_area_utm() AS a)
120 UPDATE waterway.bottlenecks
121 SET objnam = 'Give-away',
122 area = ST_geomfromtext(
123 'MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326)
124 WHERE bottleneck_id = 'testbottleneck2'
125 RETURNING *
126 $$,
127 42501, NULL,
128 'Waterway admin cannot move data from inside his region outside');
129
130 SELECT is_empty($$
131 WITH a AS (SELECT users.current_user_area_utm() AS a)
132 DELETE FROM waterway.bottlenecks
133 WHERE NOT ST_Covers((SELECT a FROM a),
134 ST_Transform(
135 CAST(area AS geometry), ST_SRID((SELECT a FROM a))))
136 RETURNING *
137 $$,
138 'Waterway admin cannot delete data outside his region');
139
105 -- template management 140 -- template management
106 SELECT lives_ok($$ 141 SELECT lives_ok($$
107 INSERT INTO users.templates (template_name, template_data, country) 142 INSERT INTO users.templates (template_name, template_data, country)
108 VALUES ('New AT', '\x', 'AT') 143 VALUES ('New AT', '\x', 'AT')
109 $$, 144 $$,