Mercurial > gemma
changeset 4785:1fef9d8e7eb2
Improved performance for RLS on read access.
Only do expensive regional test if data is in staging area (and on
write operations).
| author | Sascha Wilde <wilde@intevation.de> |
|---|---|
| date | Wed, 23 Oct 2019 15:23:52 +0200 |
| parents | ecbb12a6e8ee |
| children | 3b3cf2083730 |
| files | schema/auth.sql schema/updates/1313/01.optimize_area_policies.sql schema/version.sql |
| diffstat | 3 files changed, 70 insertions(+), 8 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth.sql Tue Oct 22 17:56:53 2019 +0200 +++ b/schema/auth.sql Wed Oct 23 15:23:52 2019 +0200 @@ -129,25 +129,38 @@ CREATE POLICY same_country ON waterway.gauge_measurements FOR ALL TO waterway_admin - USING ((location).country_code - = (SELECT country FROM users.list_users WHERE username = current_user) - ); + USING (staging_done + OR (location).country_code = + (SELECT country FROM users.list_users + WHERE username = current_user)) + WITH CHECK ((location).country_code = + (SELECT country FROM users.list_users + WHERE username = current_user)); CREATE POLICY same_country ON waterway.waterway_profiles FOR ALL TO waterway_admin - USING ((location).country_code = (SELECT country FROM users.list_users WHERE username = current_user)); + USING (staging_done + OR (location).country_code = + (SELECT country FROM users.list_users + WHERE username = current_user)) + WITH CHECK ((location).country_code = + (SELECT country FROM users.list_users + WHERE username = current_user)); CREATE POLICY responsibility_area ON waterway.bottlenecks FOR ALL TO waterway_admin - USING (users.utm_covers(area)); + USING (staging_done OR users.utm_covers(area)) + WITH CHECK (users.utm_covers(area)); CREATE POLICY responsibility_area ON waterway.sounding_results FOR ALL TO waterway_admin - USING (users.utm_covers(area)); + USING (staging_done OR users.utm_covers(area)) + WITH CHECK (users.utm_covers(area)); CREATE POLICY responsibility_area ON waterway.fairway_dimensions FOR ALL TO waterway_admin - USING (users.utm_covers(area)); + USING (staging_done OR users.utm_covers(area)) + WITH CHECK (users.utm_covers(area)); CREATE POLICY same_country ON waterway.sections FOR ALL TO waterway_admin
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1313/01.optimize_area_policies.sql Wed Oct 23 15:23:52 2019 +0200 @@ -0,0 +1,49 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2019 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Sascha Wilde <sascha.wilde@intevation.de> +-- * Tom Gottried <tom@intevation.de> + + +ALTER POLICY same_country ON waterway.gauge_measurements + TO waterway_admin + USING (staging_done + OR (location).country_code = + (SELECT country FROM users.list_users + WHERE username = current_user)) + WITH CHECK ((location).country_code = + (SELECT country FROM users.list_users + WHERE username = current_user)); + +ALTER POLICY same_country ON waterway.waterway_profiles + TO waterway_admin + USING (staging_done + OR (location).country_code = + (SELECT country FROM users.list_users + WHERE username = current_user)) + WITH CHECK ((location).country_code = + (SELECT country FROM users.list_users + WHERE username = current_user)); + +ALTER POLICY responsibility_area ON waterway.bottlenecks + TO waterway_admin + USING (staging_done OR users.utm_covers(area)) + WITH CHECK (users.utm_covers(area)); + +ALTER POLICY responsibility_area ON waterway.sounding_results + TO waterway_admin + USING (staging_done OR users.utm_covers(area)) + WITH CHECK (users.utm_covers(area)); + +ALTER POLICY responsibility_area ON waterway.fairway_dimensions + TO waterway_admin + USING (staging_done OR users.utm_covers(area)) + WITH CHECK (users.utm_covers(area));