Mercurial > gemma
view schema/updates/1427/01.fix_rls_policies.sql @ 5650:cb3c4ba0f052 DQR adjustments
Merged default branch
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 29 Jun 2023 13:18:54 +0200 |
parents | 4c658a8f34da |
children |
line wrap: on
line source
DROP POLICY same_country ON waterway.gauge_measurements; DROP POLICY same_country ON waterway.waterway_profiles; DO LANGUAGE plpgsql $do$ DECLARE the_table varchar; condition CONSTANT text = $$ (location).country_code = (SELECT country FROM users.list_users WHERE username = current_user) $$; BEGIN FOREACH the_table IN ARRAY ARRAY[ 'gauge_measurements', 'waterway_profiles'] LOOP EXECUTE format($$ CREATE POLICY same_country_insert ON waterway.%I FOR INSERT TO waterway_admin WITH CHECK (%s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY same_country_select ON waterway.%I FOR SELECT TO waterway_admin USING (staging_done OR %s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY same_country_update ON waterway.%I FOR UPDATE TO waterway_admin USING (%s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY same_country_delete ON waterway.%I FOR DELETE TO waterway_admin USING (%s) $$, the_table, condition); END LOOP; END; $do$; DROP POLICY responsibility_area ON waterway.bottlenecks; DROP POLICY responsibility_area ON waterway.sounding_results; DROP POLICY responsibility_area ON waterway.fairway_dimensions; DO LANGUAGE plpgsql $do$ DECLARE the_table varchar; condition CONSTANT text = $$ (SELECT ST_Covers(a, ST_Transform(CAST(area AS geometry), ST_SRID(a))) FROM users.current_user_area_utm() AS a (a)) $$; BEGIN FOREACH the_table IN ARRAY ARRAY[ 'fairway_dimensions', 'bottlenecks', 'sounding_results'] LOOP EXECUTE format($$ CREATE POLICY responsibility_area_insert ON waterway.%I FOR INSERT TO waterway_admin WITH CHECK (%s) $$, the_table, condition); -- In many cases it is more efficient to check for "staging_done" to -- prevent the more expensive checks for read only access (which is -- allowed for all users, when staging is done). EXECUTE format($$ CREATE POLICY responsibility_area_select ON waterway.%I FOR SELECT TO waterway_admin USING (staging_done OR %s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY responsibility_area_update ON waterway.%I FOR UPDATE TO waterway_admin USING (%s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY responsibility_area_delete ON waterway.%I FOR DELETE TO waterway_admin USING (%s) $$, the_table, condition); END LOOP; END; $do$;